December 2, 2014 at 5:49 am
Hi All,
Every night all our DBCC CHECKDB runs on all our databases. The trouble is one of them is very large and the database is inaccessible whilst this runs. See entry from log below.
DBCC CHECKDB (dbname) WITH physical_only executed by user found 0 errors and repaired 0 errors. Elapsed time: 0 hours 24 minutes 46 seconds
This normally happens fairly shortly after the backup, and normally (but not always) after a series of these entries in the log.
SQL Server has encountered 7976 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2).
Would this cause SQL to automatically run a CheckDB.
Many thanks,
Trevor
December 2, 2014 at 6:04 am
Nothing will automatically cause SQL to run a CheckDB
You probably have a job, or a job step in the backup job, which runs CheckDB on all databases.
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
December 2, 2014 at 6:32 am
It sounds like your server is seriously underpowered. I've seen DBCC impact performance in some ways, but it doesn't take the database offline, at all, by design. So if your database is becoming inaccessible, it means that you're just seeing so much resource contention that nothing can get done.
You're already running the DBCC (and as Gail says, something on your system is running it, it doesn't run on it's own, sounds like a Maintenance Plan to me) in the fastest, least intrusive way, physical only, but, that's only doing half the checks that need to be done. You also need to run the logical checks on your databases, or they may become corrupt internally. Unfortunately, that's going to add to the overhead of your checks.
You may need to re-schedule them so they run less frequently, but you may just need to get more hardware.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 2, 2014 at 6:39 am
Grant Fritchey (12/2/2014)
You may need to re-schedule them so they run less frequently, but you may just need to get more hardware.
Or restore the backups to another server and CheckDB there. Normally I wouldn't suggest that except for really large databases (~TB+)
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
December 2, 2014 at 6:48 am
GilaMonster (12/2/2014)
Grant Fritchey (12/2/2014)
You may need to re-schedule them so they run less frequently, but you may just need to get more hardware.Or restore the backups to another server and CheckDB there. Normally I wouldn't suggest that except for really large databases (~TB+)
Oh yeah, absolutely. But... they'd still have to run the physical only checks on the first server and that's already killing them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 2, 2014 at 6:55 am
Grant Fritchey (12/2/2014)
GilaMonster (12/2/2014)
Grant Fritchey (12/2/2014)
You may need to re-schedule them so they run less frequently, but you may just need to get more hardware.Or restore the backups to another server and CheckDB there. Normally I wouldn't suggest that except for really large databases (~TB+)
Oh yeah, absolutely. But... they'd still have to run the physical only checks on the first server and that's already killing them.
Not necessarily. It's pretty safe to offload the full checkDB and only run any CheckDB on the primary if the run on the secondary fails. Any corruption (physical or logical) in the database will be included within a backup and hence restored on the second server.
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
December 2, 2014 at 7:22 am
GilaMonster (12/2/2014)
Grant Fritchey (12/2/2014)
GilaMonster (12/2/2014)
Grant Fritchey (12/2/2014)
You may need to re-schedule them so they run less frequently, but you may just need to get more hardware.Or restore the backups to another server and CheckDB there. Normally I wouldn't suggest that except for really large databases (~TB+)
Oh yeah, absolutely. But... they'd still have to run the physical only checks on the first server and that's already killing them.
Not necessarily. It's pretty safe to offload the full checkDB and only run any CheckDB on the primary if the run on the secondary fails. Any corruption (physical or logical) in the database will be included within a backup and hence restored on the second server.
Really? I thought physical corruption was possible independent of the backup itself? In fact, I got that impression from a discussion with Paul about it (several years ago, and I'm old, so maybe I'm misremembering).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 2, 2014 at 7:48 am
Hi All,
Thanks for your comments. There are no database Maintenance plans in place, and I have checked every job and cannot see anything relating to CHECKDB. I have run the following commands to see if this shows any jobs running this, but get no results.
SELECT * FROM msdb.dbo.sysjobhistory WHERE message LIKE '%CHECKDB%'
SELECT * FROM msdb.dbo.sysjobsteps WHERE command LIKE '%CHECKDB%'
We are looking to move it to a hosted platform shortly which should resolve the issue, but this is a strange why this is running each day. This is not something I have come across before.
Thanks,
Trevor
December 2, 2014 at 8:04 am
tpsharp (12/2/2014)
Hi All,This normally happens fairly shortly after the backup, and normally (but not always) after a series of these entries in the log.
SQL Server has encountered 7976 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2).
Any chance your OS Admin/Backup Admin are using a tool that does this? In our environment, I see the I/O request log entries that are related to either NetBackup or VM snapshots. I see database backup in the log, though I have never seen the DBCC from them but would consider it possible that an API or powershell could do it. Alternatively, could the backup and DBCC be running from a management server?
December 2, 2014 at 8:29 am
Hi,
There are definitely VM snapshots occurring, I will check out and see when they run to see if this is causing this.
Many thanks everyone for your help.
Trevor
December 2, 2014 at 10:30 am
tpsharp (12/2/2014)
We are looking to move it to a hosted platform shortly which should resolve the issue, but this is a strange why this is running each day. This is not something I have come across before.
It's running because something is running it. SQL does not ever run CheckDB automatically. Probably whatever is running your backups is running a CheckDB afterwards 9I'd have thought before made more sense, but...)
It's not running automatically because of a snapshot, excessive IO, backup job, etc. It's not running automatically period. Something, somewhere is running the command.
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
December 2, 2014 at 12:12 pm
Have you checked the Windows Task Scheduler tasks? I really don't like those because they can be overlooked when you're looking for jobs....
December 2, 2014 at 12:12 pm
Have you checked the Windows Task Scheduler tasks? I really don't like those because they can be overlooked when you're looking for jobs....
December 2, 2014 at 4:40 pm
tpsharp (12/2/2014)
Hi,There are definitely VM snapshots occurring, I will check out and see when they run to see if this is causing this.
Many thanks everyone for your help.
This normally happens fairly shortly after the backup, and normally (but not always) after a series of these entries in the log.
SQL Server has encountered 7976 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2).
Trevor
Are you stating it's a Virtual Machine? It's possible the 'disk(s)' behind the virtual machine are overloaded and cannot handle the IO .. normally the error message tends to relate to disk/storage issues. If it's virtual the LUN might be shared with other VM hosts causing the IO bottle neck.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
December 3, 2014 at 12:57 pm
Could you possibly have a .bat file that runs somewhere that runs the CHECKDB statement?
A Maintenance plan wouldn't use the PHYSICAL_ONLY option. That is some humans doing.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply