Today I am going to talk about an issue that gives me an idea of writing a post about trace flag. And, using trace flag to resolve SQL Server startup issue. Trace flag can be termed as special switches that can help you debug specific types of issue in your environment just like a debug facility in Visual Basic.Net or any other programming language for that matter. For example, for some reason, the SQL Server won’t start and you want to troubleshoot the startup switches aka options may help and -T (trace flag) is one of that startup switch that can help you. If you want to read about different startup options available with SQL Server here is the list. Let us now understand trace flag and specific trace flag. I will also write about Using trace flag to resolve SQL Server startup issue.
What is Trace Flag?
Trace flag is the option that can help you to turn on or off specific behavior of SQL Server. For example, if an SQL Server is not starting up due to some database recovery issue using Trace flag 3608 will help. This has to be used at the command window with startup option i.e. net start MSSQLSERVER /T3608.
This blog post is the outcome of an issue I recently had, to be specific on 26th September. One of our customers was doing monthly maintenance activity and they had done some changes to tempdb along with patching so as they can save on time and avoid multiple restarts. However, they have noticed that the SQL Server keeps on restarting at the end they have asked me for help.
The first thing first, just like every DBA does I too, did a review of SQL Server Error log which reads like below:
2017-09-26 22:09:43.04 spid6s Clearing tempdb database.
2017-09-26 22:09:43.28 spid6s Starting up database 'tempdb'.
2017-09-26 22:09:43.74 spid6s Error: 5161, Severity: 16, State: 1.
2017-09-26 22:09:43.74 spid6s An unexpected file id was encountered. File id 3 was expected but 4 was read
from "T:\MSSQL\Data\tempdb03.ndf". Verify that files are mapped correctly in sys.master_files.
ALTER DATABASE can be used to correct the mappings.
2017-09-26 22:09:43.75 spid6s SQL Server shutdown has been initiated
2017-09-26 22:09:43.75 spid6s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2017-09-26 22:09:44.77 spid6s Error: 25725, Severity: 16, State: 1.
2017-09-26 22:09:44.77 spid6s An error occurred while trying to flush all running Extended Event sessions. Some events may be lost.
2017-09-26 22:09:44.77 Logon Error: 18456, Severity: 14, State: 38.
If you look at the error log details about there is a line Error: 25725, Severity: 16, State: 1 which says SQL Server won’t starts and it is recorded at 22:09:44 and about few milliseconds before this line appear there is a line that reads Error: 5161, Severity: 16, State: 1. Also, the message appears that says there is an issue with tempdb. Since tempdb is very much compulsorily to be recovered SQL Server won’t start here. My good friend Pinal Dave ( B | T ) has already written a post about this I won’t spare much time explaining details but here are the commands I have executed.
Using trace flag to resolve SQL Server startup issue
Command 1: C:\Users\hgoswami>NET START MSSQLSERVER /T3608 Command 2: C:\Users\hgoswami>SQLCMD -S. -E 1> alter database [tempdb] remove file tempdev03 2> GO
What this will do is, it will start SQL Server and switch off database recovery of all databases other than master database because we have used trace flag 3608. Once I was connected to the system I have removed the tempdb file that is mentioned in the error log file and restarted the SQL Server normally. There are many such trace flags, but one has to be very cautious using trace flag as it can adversely affect performance.
In case you are interested in reading some more troubleshooting tips please browse to the Troubleshooting category.
photo credit: Carbon Arc Stop and go -[ HMM ]- via photopin (license)
