SQL Startup options - 3609

Question

SQL Startup options - 3609

SQLSACT

SSC-Insane

Points: 21838
More actions
May 7, 2013 at 6:25 am

#273702

Hi All
I have been told that if the drive that contains my tempdb has failed, I need to start SQL Server in minimal config mode.
My question is, can't the same thing be achieved with starting SQL with trace flag 3609?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

If you start the server using minimal configuration, the tempdb is created at the default directory instead of the directory that it is configured to use. It will also create it as the size of the model database instead of the configured size. If you use the trace flag, tempdb won't be created at all. Notice that the trace flag 3609 is not supported and at most articles that explain this trace flag, you'll see the warning that this trace flag should be used only if Microsoft support team directed you to do it. In short - DON'T USE THE TRACE FLAG 3609.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Adi Cohn-120898 (5/7/2013)
If you start the server using minimal configuration, the tempdb is created at the default directory instead of the directory that it is configured to use. It will also create it as the size of the model database instead of the configured size. If you use the trace flag, tempdb won't be created at all. Notice that the trace flag 3609 is not supported and at most articles that explain this trace flag, you'll see the warning that this trace flag should be used only if Microsoft support team directed you to do it. In short - DON'T USE THE TRACE FLAG 3609.
Adi

Thanks

After checking around - Would you say 3608 is a better choice then?

Thanks

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

I would say that the best choice in this case is to start the server in minimal configuration. Why would you want to use undocumented trace flags instead of using a working and supported way? Also, as far as I know, trace flag 3608 skips running the recovery process for all user databases and MSDB. This would not help you at all in case that the drive that has tempdb on is not working.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

See https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 5

Adi Cohn-120898 (5/7/2013)
I would say that the best choice in this case is to start the server in minimal configuration. Why would you want to use undocumented trace flags instead of using a working and supported way? Also, as far as I know, trace flag 3608 skips running the recovery process for all user databases and MSDB. This would not help you at all in case that the drive that has tempdb on is not working.
Adi

Thanks

I've checked out a few posts regarding a situation when the tempdb drive is no longer available - Most of which suggest using -T3608 + Minimal Config

http://www.sqlservercentral.com/Forums/Topic1040397-1550-1.aspx

http://www.jimmcleod.net/blog/index.php/2008/09/25/tempdb-disk-goes-missing/

Can the problem be fixed with just Minimal Config?

Thanks

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 6

As far as I know it should work if the server's default directory for new databases is not the on the same drive that cannot be accessed anymore. Personally this would be the first approach that I would try. If for some reason it wouldn't work (or if the default directory is on the same drive that cannot be accessed anymore) I would use trace flag 3608. Take into consideration that I've seen articles that claim that you do need both (trace flag 3608 and minimal configuration), other articles that claim that you only need the trace flag and some articles and presentation that claim that minimal configuration is all that is needed. Because of that I guess that each one of those options will work. I also suggest that you'll read the article that GilaMonster wrote and referenced in the thread ( https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/%5B/url%5D) . Her articles are great source of learning materiel.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLSACT SSC-Insane Points: 21838 More actions · Answer 7

SQLSACT

SSC-Insane

Points: 21838

May 7, 2013 at 5:49 pm

#1613178

Thanks Guys

Gail - That is an excellent article!