Script to get Log file name of database, change full recovery to simple, shrink log file and set back to full recovery

Question

I am trying to create a script to get the name of Log file of a database, set to simple recovery, shrink the log file from this database and set back to full.
So far, I have came up with this script:

DECLARE @RestoreDbName nvarchar(2000);
declare @Name nvarchar(2000)

set @restoreDbName = 'dbname'


DECLARE cur CURSOR FOR
SELECT name
FROM [sys].[database_files] 
where [type] = 1 and name like @restoreDbName

ALTER DATABASE @RestoreDbName
SET RECOVERY SIMPLE;  
GO 

OPEN cur
FETCH NEXT FROM cur INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC SHRINKFILE(@Name, 1)
    FETCH NEXT FROM cur INTO @Name
END
CLOSE cur
DEALLOCATE cur
GO  
       
 ALTER DATABASE @RestoreDbName
 SET RECOVERY FULL;  
 GO  

The script will be running from a powershell, does not give any error message, but when I check the Log file of the database is still the same as before, has not been shrunk.

Any suggestions, what am I doing wrong?

Answer 1

You need to confirm that the result of the following statement is NOTHING before you try to shrink the log.

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = N'YourDb';

If it isn't NOTHING then something is holding back the log and preventing truncation. See this post.

You should also consider why the log got so large in the first place: if you don't need log backups then why keep it in FULL mode? And why shrink it down if it's going to need the space again?

---

You also have some issues with your script:

• Add CHECKPOINT after the first SET RECOVERY SIMPLE statement.
• ALTER DATABASE is not parameterizable, so you need to use dynamic SQL.
• You need to filter the database_files query to get only the log files.
• The cursor can be a local variable, which removes the need for manual deallocation.
• Use STATIC cursors on system views, because they can be unstable.
• 1 Mb log size is almost certainly far too small even for tiny databases. Consider a more suitable size.

DECLARE @RestoreDbName sysname = 'dbname';
DECLARE @sql nvarchar(1000) = N'
ALTER DATABASE ' + QUOTENAME(@RestoreDbName) + '
SET RECOVERY SIMPLE;

CHECKPOINT;

DECLARE @Name sysname;
DECLARE @crsr CURSOR;

SET @crsr = CURSOR LOCAL STATIC FOR
SELECT name
FROM ' + QUOTENAME(@RestoreDbName) + '.sys.database_files
where type = 1
  and type_desc = ''LOG'';

OPEN @crsr;

WHILE 1=1
BEGIN
  FETCH NEXT FROM @crsr INTO @Name;
  IF @@FETCH_STATUS <> 0 BREAK;
  
    DBCC SHRINKFILE(@Name, 10)
END;
       
ALTER DATABASE ' + QUOTENAME(@RestoreDbName) + '
SET RECOVERY FULL;
';

EXEC sp_executesql @sql,
  N'@RestoreDbName sysname',
  @RestoreDbName;