SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Startup options - 3609


SQL Startup options - 3609

Author
Message
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2090 Visits: 2931
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
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3645 Visits: 6500
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
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2090 Visits: 2931
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
Adi Cohn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3645 Visits: 6500
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/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86869 Visits: 45261
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
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2090 Visits: 2931
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
Adi Cohn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3645 Visits: 6500
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/
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2090 Visits: 2931
Thanks Guys

Gail - That is an excellent article!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search