November 3, 2011 at 9:33 am
We have a fairly simple job that runs nightly at 7pm which executes a sproc containing:
EXEC sp_MSFOREACHDB'DBCC CHECKDB ( ''?'') WITH NO_INFOMSGS'
Nothing fancy here.
Until recently this job ran daily in just over a few minutes (< 20), however, in the past 10 days or so it has been all over the place.
10/26 - 22 hrs, 34 mins
10/27 - 0 hrs, 50 mins
10/28 - 3 hrs, 3 mins
10/29 - 2 hrs, 39 mins
10/30 - 25 hrs, 4 mins (job stopped)
11/01 - 3 hrs, 25 mins
11/02 - 5 hrs, 57 mins
11/03 - still running...
Also to note: The output from the job is logged to a text file and typically doesn't return any consistency errors or things out of the ordinary. Running on SQL 2008, SP2, 64-bit, Windows 2003 EE
Does anything stick out that might be causing these drastic fluctuations?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 3, 2011 at 10:10 am
nothing jumps out at me, I do a checkdb once a month using
EXEC sp_MSforeachdb 'USE [?]; DBCC CHECKDB'
it then reads the error log and spits any errors into an email
takes 25 mins to run for me, just got over 100GB in total of databases to check through and the server isn't the best, but it is usually constant at around 25 mins
November 3, 2011 at 10:15 am
IO contention? CheckDB is IO intensive, if something else is hammering the IO subsystem at the time it could cause this.
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
November 3, 2011 at 10:17 am
anthony.green (11/3/2011)
I do a checkdb once a month using
Is once a month often enough? Do you retain all your backups (logs included) for a full month?
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
November 3, 2011 at 10:25 am
GilaMonster (11/3/2011)
anthony.green (11/3/2011)
I do a checkdb once a month usingIs once a month often enough? Do you retain all your backups (logs included) for a full month?
The DB is in simple recovery and is hosted by a 3rd party web hosting management provider, there is a weekly full and daily diff's. Granted it is not the best of situations and until I can get the CFO to sign off a new upgraded server from the hosting provider I'm pretty much stuck with it.
I recently moved into this position around 2 months back as the company is a relativly new .COM and hadn't had a DBA before myself.
The hosting company, keep 4 weeks worth of backups on their CommVault, before passing the last full which is taken on the last Friday of the 4 weekly rotation to tape which is kept for 12 months.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply