There was a user question on how to shrink the log files with dynamic SQL (without hard coding databases names). The script is provided below. But first – make sure that you size the log files properly (large enough to grow) and make sure that you backup regularly the log files (or use simple recovery mode). And do not truncate the logs, shrink them just to the size you need. So, if my log files do not usually grow larger than 4 GB I will use the following script:<\/p>\n
SET NOCOUNT ON<\/span> delete from #tran_log_space_usage where database_name = ‘Exclude_DB’<\/span><\/p>\n EXEC dbo.sp_msforeachdb ‘<\/span> DROP table #tran_log_space_usage<\/span><\/p>\n You can add exceptions for the databases as well (see ‘Exclude_DB’ in script).<\/p>\n","protected":false},"excerpt":{"rendered":" There was a user question on how to shrink the log files with dynamic SQL (without hard coding databases names). The script is provided below. But first – make sure that you size the log files properly (large enough to … Continue reading
\nCREATE table #tran_log_space_usage( <\/span>
\n database_name sysname<\/span>
\n, log_size_mb float<\/span>
\n, log_space_used float<\/span>
\n, status int<\/span>
\n); <\/span>
\ninsert into #tran_log_space_usage <\/span>
\nexec(‘DBCC SQLPERF ( LOGSPACE )’) ; <\/span><\/p>\n
\nDECLARE @log_file VARCHAR(150)<\/span>
\nIF (SELECT DB_ID(”?”)) > 4 and (SELECT DATABASEPROPERTY(”?”, ”IsOffline”)) <>1<\/span>
\nBEGIN<\/span>
\n PRINT ”?”<\/span>
\n IF (select log_size_mb as LogSizeMB<\/span>
\n from #tran_log_space_usage <\/span>
\n where database_name = ”?”) > 4048<\/span>
\n BEGIN<\/span>
\n select @log_file = name<\/span>
\n from [?].sys.database_files where file_id = 2<\/span>
\n USE [?]<\/span>
\n DBCC SHRINKFILE (@log_file , 4048) <\/span>
\n END<\/span>
\n PRINT ”\/*********************************\/”<\/span>
\nEND’<\/span><\/p>\n