x

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?
more ▼

asked Oct 01, 2010 at 11:04 AM in Default

dosberg gravatar image

dosberg
122 9 10 12

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest
There's a startup procedure that could be doing this. Check out sp_procoption in the BOL to see more information about it.
more ▼

answered Oct 01, 2010 at 11:06 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

Good to know.
Oct 01, 2010 at 11:09 AM Tim
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Oct 01, 2010 at 11:08 AM

Tim gravatar image

Tim
36.4k 35 41 139

+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
Oct 01, 2010 at 11:27 AM Grant Fritchey ♦♦
Hey Grant, thanks for the push over 2000. I am glad it was you who sent me over. Now I can edit.
Oct 01, 2010 at 11:29 AM Tim
Congrats on the 2k. You've absolutlely boosted the quality around here.
Oct 01, 2010 at 11:35 AM Grant Fritchey ♦♦
congrats TRAD on the 2k!
Oct 01, 2010 at 12:30 PM DaniSQL
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 01, 2010 at 12:50 PM

dosberg gravatar image

dosberg
122 9 10 12

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x47

asked: Oct 01, 2010 at 11:04 AM

Seen: 1427 times

Last Updated: Oct 01, 2010 at 11:04 AM