SQL Server – Database Management – Shrink Log files

Background

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.

 

Script

Code


; 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'



 

Output

 

logfilemanagement

 

Explanation

  1. 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’

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.

One thought on “SQL Server – Database Management – Shrink Log files

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s