Recovery and Shrink

Question

I am trying to change the Recovery Mode from Full to Simple if the database doesn't have any Full or T.Log backups setup from past week days.

The problem I am facing with the below script is it's selecting database name twice and spitting the code to execute along with data files, butIi need only log files to shrink and change the database to Simple recovery mode.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--ALTER PROCEDURE [dbo].[usp_shrink_log]
--WITH Recompile
--AS
--BEGIN
declare
    @isql varchar(2000),
    @dbname varchar(64),
    @logfile varchar(128);

declare c1 cursor
for
SELECT distinct(D.[name]) AS [database_name]
    ,(Select BS3.Name where BS3.type='1') as Logfile
FROM 
    sys.databases D
LEFT JOIN  
    ( 
    SELECT BS.[database_name],  
    MAX(BS.[backup_finish_date]) AS [last_log_backup_date]  
    FROM msdb.dbo.backupset BS  
    WHERE BS.type = 'L'  
    GROUP BY BS.[database_name]  
    ) BS1  
ON D.[name] = BS1.[database_name] 
LEFT JOIN  
    ( 
    SELECT BS.[database_name],  
    MAX(BS.[backup_finish_date]) AS [last_data_backup_date]  
    FROM msdb.dbo.backupset BS  
    WHERE BS.type = 'D'  
    GROUP BY BS.[database_name]  
    ) BS2  
ON D.[name] = BS2.[database_name] 
LEFT JOIN 
    ( 
    SELECT NAME,database_id,type
    from sys.master_files MF
    ) BS3
ON D.Database_id=BS3.database_id
WHERE 
        D.[recovery_model_desc] <> 'SIMPLE'
    and D.[name] not in ('master', 'model', 'msdb', 'tempdb',
        'ReportServer', 'ReportServerTempDB')
    AND BS1.[last_log_backup_date] IS NULL
    OR  BS1.[last_log_backup_date] < BS2.[last_data_backup_date]
    and BS3.type='1'
ORDER BY D.[name];

open c1;
fetch next from c1 into @dbname, @logfile;

While @@fetch_status <> -1
begin
    select @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE'

    print @isql
    --exec(@isql)

    select @isql='USE ' + @dbname + ' checkpoint'

    print @isql
    --exec(@isql)

    select @isql='USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'
    print @isql
    --exec(@isql)

    fetch next from c1 into @dbname, @logfile
end

close c1;
deallocate c1;
--END

score 0 · Answer 1 · edited Nov 30 '17 at 16:19

0

I fixed my own code :) how ironic.

Here is the piece need to change (around line 43)

SELECT NAME,database_id,type
from  sys.master_files MF
Where type = 1 -- I added this bit!

Hope this code helps some one here.

edited Nov 30 '17 at 16:19

Oreo

1,566
1
10
22

answered Nov 30 '17 at 14:19

JayM

41
7

score 0 · Answer 2 · answered Nov 30 '17 at 15:28

Here is a much-cleaned-up version without the WHILE cursor.

Do not run this until you check with the business, to make sure they are happy changing over to SIMPLE Recovery Mode!

USE [master];
GO
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

--ALTER PROCEDURE [dbo].[usp_shrink_log]
--WITH Recompile
--AS
--BEGIN
    DECLARE @isql VARCHAR(8000);
    SET @isql = '';

    SELECT @isql = @isql + 'ALTER DATABASE ' + QUOTENAME(D.[name]) + ' SET RECOVERY SIMPLE;
USE ' + QUOTENAME(D.[name]) + ';
CHECKPOINT;
DBCC SHRINKFILE (' + QUOTENAME(BS3.[name]) + ', 1);
'
    --SELECT D.[name], BS3.[name]
    FROM (
        SELECT *
        FROM sys.databases
        WHERE [recovery_model_desc] <> 'SIMPLE'
          AND [name] NOT IN ('master', 'model', 'msdb', 'tempdb',
            'ReportServer', 'ReportServerTempDB')
        ) D
    LEFT JOIN (
        SELECT BS.[database_name]
            ,MAX(BS.[backup_finish_date]) AS [last_log_backup_date]
        FROM msdb.dbo.backupset BS
        WHERE BS.[type] = 'L' -- Log Backups
        GROUP BY BS.[database_name]
        ) BS1 ON D.[name] = BS1.[database_name]
    LEFT JOIN (
        SELECT BS.[database_name]
            ,MAX(BS.[backup_finish_date]) AS [last_data_backup_date]
        FROM msdb.dbo.backupset BS
        WHERE BS.[type] = 'D' -- Differential Backups
        GROUP BY BS.[database_name]
        ) BS2 ON D.[name] = BS2.[database_name]
    LEFT JOIN sys.master_files BS3 ON D.Database_id = BS3.database_id
        AND [type] = '1' -- LOG
    WHERE (
            BS1.[last_log_backup_date] IS NULL
            OR
            BS1.[last_log_backup_date] < BS2.[last_data_backup_date]
            )
    ORDER BY D.[name];

    PRINT(@isql);
    --EXEC(@isql);
--END

It outputs one of these per row:

ALTER DATABASE [My lovely DB] SET RECOVERY SIMPLE;
USE [My lovely DB];
CHECKPOINT;
DBCC SHRINKFILE ([My lovely Log File], 1);

Recovery and Shrink

2 Answers2