Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Startup options - 3609 Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 6:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:29 AM
Points: 1,378, Visits: 2,684
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

Post #1450095
Posted Tuesday, May 7, 2013 6:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:50 AM
Points: 2,122, Visits: 5,474
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/
Post #1450101
Posted Tuesday, May 7, 2013 6:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:29 AM
Points: 1,378, Visits: 2,684
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
Post #1450115
Posted Tuesday, May 7, 2013 7:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:50 AM
Points: 2,122, Visits: 5,474
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/
Post #1450150
Posted Tuesday, May 7, 2013 8:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:29 AM
Points: 43,022, Visits: 36,182
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 2008, MVP
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

Post #1450161
Posted Tuesday, May 7, 2013 8:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:29 AM
Points: 1,378, Visits: 2,684
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
Post #1450162
Posted Tuesday, May 7, 2013 12:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:50 AM
Points: 2,122, Visits: 5,474
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/) . 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/
Post #1450281
Posted Tuesday, May 7, 2013 5:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:29 AM
Points: 1,378, Visits: 2,684
Thanks Guys

Gail - That is an excellent article!

Post #1450373
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse