8

I hope you can point me in the right direction. I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit.

I want the script to:

  1. To select all databases, except the system DBs.
  2. To set recovery to simple.
  3. To shrink the log files for every db(.ldf), except the system db

The script:

USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)

declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
    begin
    select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)
    select @isql='USE @dbname checkpoint'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)
    select @isql='DBCC SHRINKFILE @dbname.ldf'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)

    fetch next from c1 into @dbname
    end
close c1
deallocate c1
  • Why, why, why? Also what is "the right direction"? Does the script not work? If so, how? Do you get an error message? What is it? Probably need a USE command in the last block. But again: Why, why, why? – Aaron Bertrand Jun 25 '14 at 11:01
  • Because the .ldf files takes 70% of the disk space on the server. But if you know of a better way, please enlighten me. I dont know if the script works, I cant just run it. I have to be sure it works first, since it a production environment. – Arviddk Jun 25 '14 at 11:08
  • You don't have a dev or test environment where you can test this? Frankly I wouldn't take anything from here, regardless of who wrote it, and apply it to production based only on the assurances of strangers on the Internet... – Aaron Bertrand Jun 25 '14 at 15:06
  • @Arviddk Do you know what are the consequences of changing the recovery model from FULL/BULK LOGGED to SIMPLE? If you are aware, go ahead and do that. – BuahahaXD Mar 22 '16 at 8:27
  • I just wanted to comment on the why for future readers who may be looking to do the same thing. We used to do full SQL backups with transaction logs backups. We since changed over to using Dell AppAssure to do backups which puts us in a place where we don't need transaction log backups. Now we are left with hundreds of databases across multiple servers still set to full with terabytes of LDF files for no reason. This affects backups/restores as well as other things that go around that, replication and such. – Thorin Aug 5 '16 at 18:49
13

Use Script to Shrink Log files of all databases other than the system DBs.

USE MASTER   
GO    
SET QUOTED_IDENTIFIER ON  
GO  
SET ARITHABORT ON  
GO  

DECLARE @DBName NVARCHAR(255),@LogicalFileName NVARCHAR(255),@DBRecoveryDesc Varchar(200)  

DECLARE DatabaseList CURSOR   
FOR   
SELECT name,recovery_model_desc  
FROM sys.databases  
WHERE state_desc = 'ONLINE'  
AND is_read_only = 0  
and database_id>4  
ORDER BY name  

OPEN DatabaseList  
FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc  
WHILE @@FETCH_STATUS = 0     
BEGIN   

SET @LogicalFileName=(SELECT top 1 name FROM sys.master_files AS mf WHERE DB_NAME(database_id)=@DBName and type_desc='LOG')  

If @DBRecoveryDesc='Full'  
Begin  
     Print('Use ['+@DBName+'] 
            GO  

           ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT
           GO   

            DBCC SHRINKFILE ('''+@LogicalFileName+''',10)  
            GO  

            ALTER DATABASE ['+@DBName+'] SET RECOVERY FULL WITH  NO_WAIT
            GO ')  
Print '----------------------------------------------------------- '  
END  

If @DBRecoveryDesc='Simple'  
Begin   
     Print('Use ['+@DBName+']  
            GO  

            DBCC SHRINKFILE ('''+@LogicalFileName+''',10)    
            GO    
 ')  
Print '----------------------------------------------------------- '

END

         FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc
      END  
CLOSE DatabaseList  
DEALLOCATE DatabaseList
  • At first I thought executing ------... would result an in an error but syntax highlighting gave me a hint about what actually would happen. Neat! – ta.speot.is Jul 26 '16 at 1:30
  • Thanks, I am going to try this out in our dev environment and push to prod – Thorin Aug 5 '16 at 18:46
  • You should add "DBCC SHRINKFILE ('''+@LogicalFileName+'_Log'',10) GO" - otherwise the log database will not be shrinked. – Andreas Rehm Dec 26 '18 at 23:14
5

I've always had such an aversion to cursors, that I wrote this as I can better understand it. It's totally based off of AA.SC's answer (thank you by the way), just put in a way that I think. If this jives with what others think, then great. Note, I didn't put it back into Full recovery mode afterward though.

SELECT 
'--', d.name dbName, d.recovery_model, d.recovery_model_desc , mf.name LogicalFileName,
'
use [' + d.name + ']

if(' + cast(d.recovery_model as varchar(5)) + ' = 1)
BEGIN
    ALTER DATABASE ['+ d.name +'] SET RECOVERY SIMPLE WITH NO_WAIT
END
GO 
DBCC SHRINKFILE (''' + mf.name  +''',10)  
GO  
'
FROM sys.databases d
join sys.master_files mf
    on d.database_id = mf.database_id
    and mf.type_desc = 'LOG' 
WHERE d.state_desc = 'ONLINE'  
AND d.is_read_only = 0  
and d.database_id > 4 
--and d.recovery_model = 1
ORDER BY d.name 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.