question

dosberg avatar image
dosberg asked

Checkdb on Instance startup

I took a new job and inherited several Sql 2005 instances that have DBCC checkdb configured to run on startup of the instance. On the restart of a prod instance the other day, it took an hour before I got "Recovery Complete." It isn't to be a SQL agent job nor a schedule task. Where else could Sql Server be configured to do this?
dbcc
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
There's a startup procedure that could be doing this. Check out sp_procoption in the BOL to see more information about it.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Good to know.
0 Likes 0 ·
Tim avatar image
Tim answered
The only time I have seen it do this on startup on my server is when the database did not have a chance to close properly on shutdown. IE Power failure, hardware issue, etc. The only other time it runs on my server is during a scheduled job or when I run it manually.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Oh yeah, I completely spaced that. If you power off the server or in some other way stop the service, not allowing a shut down, it will do a consistency check on startup
0 Likes 0 ·
Hey Grant, thanks for the push over 2000. I am glad it was you who sent me over. Now I can edit.
0 Likes 0 ·
Congrats on the 2k. You've absolutlely boosted the quality around here.
0 Likes 0 ·
congrats TRAD on the 2k!
0 Likes 0 ·
dosberg avatar image
dosberg answered
It isn't sp_procoption, no user stored procs in master and "scan for startup procs" has a run values of zero. All the shutdowns were clean, command issued through SSMS. Any other possiblities? This one has got me stumped.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.