MSSQL Tips: Tip #29 – SQL Server Database Decommissioning Check List


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.

Please read my latest MSSQL Tip: “SQL Server Database Decommissioning Check List“.

Check out all of my tips here:


MSSQL Tips: Tip #28 – SQL Server Master Data Services (MDS) Versions

SQL Server Master Data Services (MDS) provides versioning for the MDS models, but what are they used for and what can we do with the versions?

Please read my latest MSSQL Tip: “SQL Server Master Data Services (MDS) Versions“.

Check out all of my tips here:


MSSQL Tips: Tip #27 – SQL Server Master Data Services (MDS) Database Restore Steps


In some cases when you try to restore a Master Dats Services database you can get errors. To complete the restore you may need to perform additional steps.

Please read my latest MSSQL Tip: “SQL Server Master Data Services (MDS) Database Restore Steps“.

Check out all of my tips here:


Shrink database files and DBCC CheckDB

Shrinking database files is never a good idea. Read this article about alternative method on removing free space from the files:

In rare occasion when you decide to do this make sure you run DBCC CHECKDB after shrinking the file. You may encounter allocation errors:

CHECKDB found 1 allocation errors and 0 consistency errors in database…

Some of these errors could be fixed by running table or index rebuild command:




MSSQL Tips: Tip #23 – SQL Server 2016 Query Store Example

New tip with example on how to use SQL Server 2016 Query Store, how to track multiple execution plans and how to force an efficient plan.

Please read the latest MSSQL Tip: “SQL Server 2016 Query Store Example“.

Check out all of my tips here:


MSSQL Tips: Tip #21 – SQL Server 2016 Query Store Introduction


As promised here is the first tip about SQL Server 2016 Query Store.

Please read the MSSQL Tip here: “SQL Server 2016 Query Store Introduction“. Stay tuned for more tips about the Query Store.

Check out all of my tips here:


SQL Server 2016 CTP 2.2 is now available

SQL Server 2016 CTP 2.1 became available on June 24th 2015 about one month after Microsoft made public SQL Server 2016 CTP 2.0.

Today CTP 2.2 is available for download. Among all of the new Database Engine features Query Store stands out as a great addition to the performance tuning tools currently available out of the box.

Stay tuned for the MSSQLTips articles about SQL Server 2016 Query Store.


Backup Default SQL Server Trace files

Default Trace first has been introduced in SQL Server 2005 and could be really useful for Database Administrators in troubleshooting.

There is really great article about what Default Trace is and what you can do with it if you want to learn more:—the-power-of-performance-and-security-auditing/

Today I would like to share with you my solution on how to set up an automated trace files’ backups.

How many times you thought: “If I set this audit last month I would know who changed the server configuration”. Unfortunately sometimes we are too busy with our day to day work or do not think that we will ever need to check who changed that specific option on SQL Server and when it was changed. One of the reasons could be that nobody has access to the SQL Server except you. Or you know what you do and documented every one of your steps. But what about those new applications that require sysadmin privileges on SQL Server to install applications and databases? You ask vendor what are the requirements and their response is: “SQL Server 2005 or higher”. And then you discover that installation process actually changed your SQL Server configuration to enable CLR.

This is not real life example, but very close. It took me a couple of hours to go through the tape backups to find out when I changed the server configuration, because I could not remember changing it at all! So, it WAS an application installation.

The first problem when you go through the tape backups is that it is very time consuming. The tape could be offsite; you need to involve backup guys. The second problem is that the trace file we are looking for may not even be on the tape, especially if you backup trace files only once a day. The number of trace files is limited to five files and the new file is generated when file’s size reaches 20 MB. At the same time the oldest file is deleted.

There are lots of solutions out there to have specific records imported to a database from the trace file. One of the latest articles by Aaron Bertrand could be found here:

But this does not quite work for me. I do not know what I may need next time, so capturing just specific records do not really work for me. Considering that I use DDL triggers for audit and that I need to check old trace files probably once a year, I do not want to import all records to the database, because this is another database to support and this includes backups, maintenance etc.

To save myself time when I needed to check something the next time I came up with solution to backup trace files. This solution will allowed me to make sure that ALL trace files are on the tape backup as well as allow me to will keep an archive of the trace files on the server for the quick access. The solution has been designed and tested for SQL Server 2008 R2.

We want to have all archived Default Trace files on the same server, but in different folder. The files that are archived would also be compressed (zipped).

A little challenge here is that we cannot backup the latest trace file because it will be still in use. We can will only backup “closed” traces.


  1. Archive folder on the server (make sure that SQL Server account or account that will be used to run the archiving job has rights to create and delete files in this folder). To make the scripting more generic I have created the folder under the “Log” folder (location of the Default Trace on your SQL Server instance).
  2. WMI SQL Server Alert.
  3. SQL Server Job to respond to alert (modify job to backup files).
  4. SQL Server Agent “Replace tokens for all job responses to alerts” option is enabled.
  5. Database mail set up.
  6. Operator exists to get test e-mails.
  7. Gzip utility to “zip” archived files.

Note: 5 and 6 only required for the testing. You may skip testing part as this is just to demonstrate files rollover process and response.

Implementation Steps (including testing):

  • This solution requires SQL Server Agent option set to “Replace tokens for all job responses to alerts” which may not be desirable if you are running a very secure environment.

EXEC msdb.dbo.sp_set_sqlagent_properties@alert_replace_runtime_tokens=1

OR set it up using SQL Server Management Studio through the SQL Server Agent Properties:

Note that if SYSTEM account has been removed from logins or SYSADMIN server role you may have issues with WMI alerts.

  • First we need to find location of the Default Trace on your SQL Server instance:
SELECT LEFT(path, LEN(path)-CHARINDEX('\', REVERSE(path)))  FROM sys.traces WHERE id = 1

  • Now – create subfolder for the archived trace files (it could be any other folder that you want to backup to the tapes):

  • Create job to test/confirm that WMI event (trace file rollover) triggers e-mail and that “Replace tokens” option has been configured properly and works. You will need to replace @notify_email_operator_name parameter’s value and @recipients e-mail in the script below. Also replace @profile_name with your DB Mail profile. Remember, that we are just testing WMI now. Do not test this in your production environment!
USE [msdb]
SELECT @ReturnCode = 0
 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA' AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'WMI Response - Default Trace File Rollover',
            @description=N'Sends notifications to DBA when Default Trace File Rollover event occures',
            @notify_email_operator_name=N'DBA_Operator', @job_id = @jobId OUTPUT
-- replace N'DBA_Operator' with operator you created
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sends e-mail in response to alert',
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ''DBServerAlerts'',
    @recipients = '''',
    @body =   ''File Name: $(ESCAPE_SQUOTE(WMI(FileName)));
                        Start Time: $(ESCAPE_SQUOTE(WMI(StartTime)));
                        Server Name: $(ESCAPE_SQUOTE(WMI(ServerName)));
                        Instance Name: $(ESCAPE_SQUOTE(WMI(SQLInstance)))'',
    @subject = ''Default Trace File Rollover event - $(ESCAPE_SQUOTE(WMI(SQLInstance)))'' ;'
-- replace with e-mail you will be using for the testing;
-- replace @profile_name with your DB Mail profile
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave
  • Create alert:
EXEC msdb.dbo.sp_add_alert @name=N'WMI - Default Trace File Rollover',
   @wmi_query=N'select *  from TRACE_FILE_CLOSE',
   @job_name=N'WMI Response - Default Trace File Rollover' -- name of the job created in step 4

Note that @wmi_namespace will be different for the named instance (example: \\.\root\Microsoft\SqlServer\ServerEvents\INSTANCENAME).

  • Initial testing.

It may take some time to get response from this alert when you test this. This will depend on how busy your system is (how often trace files reach 20 MB size). It may take actually several hours on your test SQL Server. To speed up trace files generation I run this script when I do my testing (it runs about 2 minutes on my test server):

CREATE TABLE tempdb.dbo.ThisIsLongNameToFillOutTraceFile
       ThisIsLongNameToFillOutTraceFile_c1 uniqueidentifier NOT NULL  CONSTRAINT column_a_un1 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c2 uniqueidentifier NOT NULL  CONSTRAINT column_a_un2 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c3 uniqueidentifier NOT NULL  CONSTRAINT column_a_un3 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c4 uniqueidentifier NOT NULL  CONSTRAINT column_a_un4 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c11 uniqueidentifier NOT NULL  CONSTRAINT column_a_un5 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c12 uniqueidentifier NOT NULL  CONSTRAINT column_a_un6 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c13 uniqueidentifier NOT NULL  CONSTRAINT column_a_un7 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c14 uniqueidentifier NOT NULL  CONSTRAINT column_a_un8 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c111 uniqueidentifier NOT NULL  CONSTRAINT column_a_un9 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c112 uniqueidentifier NOT NULL  CONSTRAINT column_a_un10 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c113 uniqueidentifier NOT NULL  CONSTRAINT column_a_un11 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c114 uniqueidentifier NOT NULL  CONSTRAINT column_a_un12 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c211 uniqueidentifier NOT NULL  CONSTRAINT column_a_un13 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c212 uniqueidentifier NOT NULL  CONSTRAINT column_a_un14 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c213 uniqueidentifier NOT NULL  CONSTRAINT column_a_un15 UNIQUE,
       ThisIsLongNameToFillOutTraceFile_c214 uniqueidentifier NOT NULL  CONSTRAINT column_a_un16 UNIQUE
       )  ON [PRIMARY]
DROP TABLE dbo.ThisIsLongNameToFillOutTraceFile
GO 3000

Here is example of e-mail that you will get when trace file rollover will occur:

You will see on the server the new file (log_2088.trc) created at the same timetime:

  • Now, when we validated that WMI alerts work and you have server configured properly we can replace the job with the final version:

There will be two steps – to copy closed trace file to the archive folder and to zip the copied file:

Step 1:

Command (in one line):

for /f "tokens=1,2,3,4,5 delims=\ " %a in ("$(ESCAPE_SQUOTE(WMI(FileName)))") do 
xcopy $(ESCAPE_SQUOTE(WMI(FileName))) %a\%b\%c\%d\defaultTraceArchive

Step 2:

Command (in one line):

for /f "tokens=1,2,3,4,5 delims=\ " %a in ("$(ESCAPE_SQUOTE(WMI(FileName)))") do 
cmd /c "%a\%b\%c\%d\defaultTraceArchive\gzip.exe -9 %a\%b\%c\%d\defaultTraceArchive\*.trc"


Now we have everything ready for the automated backup of the Default Trace files.


Final script:




Database Decommissioning Check-List


This check-list could be used in the following cases:

  • Decommissioning of an application which is obsolete and not in use any more.
  • Decommissioning of an application due to the replacement with another application.
  • Upgrading an application to the newer version without data migration (new database will be created).

We do not have steps here to check if database is in use before the decommissioning. This check-list is for the cases where we already know that application is scheduled for the retirement, but some steps could be applicable for the migration of a database to the new hardware.

Certain steps will be applicable to your environment and others will not (depending on activities you perform or depending on specific configurations).


  1. Get Business and/or IT Manager approvals for the database decommissioning.
  2. Notify business users about the database decommissioning.
  3. Have a back-out plan.
  4. Make sure that all software licenses terminated (not scheduled for the renewal).
  5. Create Request For Change if you follow ITEL practices.
  6. Disable the database monitoring.
  7. Make sure that all application services have been disabled and there are no connections to the database. Work with desktop team to make sure that applications removed from the PCs. You may need to monitor the database’s connections for some time to make sure that nobody uses it.
  8. For the critical (for example financial) applications that will be decommissioned make sure that software media (SQL Server installation files, optional – Application installation files) is available in case if auditors request to restore the database.
  9. Backup the database and make sure that database backup is available on the latest tape backup and not overwritten (if you keep only monthly tapes, for example). Create separate archive tape if you need to keep decommissioned databases backup separately.
  10. Verify the latest backup to make sure that it is valid and has no errors.
  11. Disable tape backups after the latest backup has been verified.
  12. Delete old backup files (after the latest backup’s verification), remove database/backup folders if applicable.
  13. Generate scripts or make sure you have latest version of the scripts for the jobs that operate with the database. Delete the jobs.
  14. Set the database to the Offline or Read-Only Mode for the period required by your company’s decommissioning policy.
  15. Script logins that have access to the database and delete logins that do not have access to any other database on SQL Server.
  16. Make sure that all dependencies taken into consideration (Reporting Services, replication, linked servers, SSIS packages etc.).
  17. Remove SQL Server options/features that were used only by this database (for example, FILESTREAM, Full-Text, Reporting Services).
  18. Follow SQL Server decommissioning procedures if this is the last database on the SQL Server.
  19. Update Database Inventory and other related database documentation.