Different companies have various requirements and processes for database or database server decommissioning. In this tip we have provided steps that will help you decommission a database and make sure you have a good back-out plan (for example, in case there is a need to bring the database back online). We have also provided scripts that will help you identify dependencies in case there is a need to migrate the database instead of decommissioning.
You may already have a process documented, but this list will help you review most of the dependencies and make sure you are prepared for either a migration or decommissioning.
I have encountered this several times already after different Cumulative Updates/Service Packs…
We set fixed maximum size for SQL Server databases and update the max size when required. But after installing Service Pack and Cumulative Updates the maximum size is reset to 2,097,152 MB for the MSDB log file:
I am going to submit this issue to the Microsoft Connect (hopefully it still works and active) or check if somebody else had similar issues. As this happens only with one system database and only with one file I would suspect that this is a bug.
This is just something to watch out and remember to review after SQL Server patching if you use fixed maximum files sizes.
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:
SET NOCOUNT ON CREATE table #tran_log_space_usage( database_name sysname , log_size_mb float , log_space_used float , status int ); insert into #tran_log_space_usage exec(‘DBCC SQLPERF ( LOGSPACE )’) ;
delete from #tran_log_space_usage where database_name = ‘Exclude_DB’
EXEC dbo.sp_msforeachdb ‘ DECLARE @log_file VARCHAR(150) IF (SELECT DB_ID(”?”)) > 4 and (SELECT DATABASEPROPERTY(”?”, ”IsOffline”)) <>1 BEGIN PRINT ”?” IF (select log_size_mb as LogSizeMB from #tran_log_space_usage where database_name = ”?”) > 4048 BEGIN select @log_file = name from [?].sys.database_files where file_id = 2 USE [?] DBCC SHRINKFILE (@log_file , 4048) END PRINT ”/*********************************/” END’
DROP table #tran_log_space_usage
You can add exceptions for the databases as well (see ‘Exclude_DB’ in script).