Received a ticket that we are running low on Storage on one of our Development Database Servers.
Traced the error back to the fact that though we have a Database Transaction Backup registered it is not scheduled to run.
If I run it right away, I will be backing up old unneeded transaction log data.
And, so let us change the recovery mode to Simple ( that dumps the transaction log), shrink the log files, change the Recovery mode back to it original state.
; with cteLogFile ( database_id , [shrinkStatement] ) as ( select tblSMF.database_id , [shrinkStatement] = STUFF ( ( select ', ' + 'dbcc shrinkfile(' + quotename(tblSMF_Inner.[name]) + ') ' + ' with no_infomsgs' + ';' from sys.master_files tblSMF_Inner where tblSMF.database_id = tblSMF_Inner.database_id and tblSMF_Inner.[type_desc] = 'LOG' FOR XML PATH('') ) , 1 , 1 , '' ) from sys.master_files tblSMF group by tblSMF.database_id ) select [database] = tblSD.[name] , tblSD.recovery_model_desc , tblSD.log_reuse_wait_desc , [sqlSimple] = 'ALTER DATABASE ' + quoteName(tblSD.[name]) + ' set recovery SIMPLE;' , [sqlShrink] = 'use ' + quoteName(tblSD.[name]) + '; ' + cteLF.shrinkStatement , [sqlRestate] = 'ALTER DATABASE ' + quoteName(tblSD.[name]) + ' set recovery ' + tblSD.recovery_model_desc collate DATABASE_DEFAULT + '; ' from sys.databases tblSD inner join cteLogFile cteLF on tblSD.[database_id] = cteLF.database_id where tblSD.recovery_model_desc != 'SIMPLE'
- Transaction Log
- File Sizes
- Can only be shrunk down to initial database file size
- Can not assume that sys.master_files_id is 2 and so check for type_desc = ‘LOG’
- File Sizes
Keep in Mind
Please keep in mind that the SQL above only scripts the steps, it does no execute it.
Copy and Paste to perform actual execution.