February 2, 2011 at 3:48 pm
Hi,
For error someone run DBCC CHECKDB in production database, now i want disable or cancel this command for prevent this errors on future.
Thanks
February 2, 2011 at 4:44 pm
CheckDB runs database integrity checks. Nothing else. It doesn't cause errors. It should be run regularly to make sure that things aren't broken.
Regardless, there's nothing to disable, checkDB runs when someone runs it or when a job runs it. It does not run automatically.
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
February 2, 2011 at 6:11 pm
DBCC CHECKDB - http://msdn.microsoft.com/en-US/library/ms176064%28v=SQL.90%29.aspx
As Gail mentioned, this doesn't cause errors.
Disabling it, even if allowed, doesn't make sense. Why would you not want to know about potential problems in the database?
February 17, 2011 at 5:15 pm
I want disable dbcc checkdb because my database have 4TB, and then take long time for this commnad to end (around 16h hours).
We have many maintenance plans and jobs and in some cases is confuse for the operators.
Is posible disable?
February 18, 2011 at 2:43 am
Sure, find the job/maint plan that runs it and disable that job. CheckDB does not run automatically. There is a job or maintenance plan that runs it.
However, that is really bad practice and not a solution. You need to be running checkDB somehow so that you can detect corruption before it affects the users, before it becomes irreparable. For some ideas for checkDB on very large databases see - http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx
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
February 21, 2011 at 9:00 am
You need to run checkdb periodically. It is the only way you will detect corruption and the sooner you can detect it, the better.
If it's a burden on the primary server, then restore on a secondary server and run it there. If you don't have 4TB, consider purchasing Red Gate's Virtual Restore, and running it on a server that uses a virtual restore. You need to run checkdb.
Disclosure: I work for Red Gate.
August 28, 2012 at 1:54 pm
dbcc checkdb runs on startup. This is an issue for any VLDB. It particularly is bad for mirroring and cluster failovers.
*****
Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P
August 28, 2012 at 2:00 pm
eric.lyons (8/28/2012)
dbcc checkdb runs on startup. This is an issue for any VLDB. It particularly is bad for mirroring and cluster failovers.
Not that I am aware of.
August 28, 2012 at 2:02 pm
Check your log file.
*****
Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P
August 28, 2012 at 2:08 pm
eric.lyons (8/28/2012)
Check your log file.
I have, not a peep. Haven't seen DBCC CHECKDB run on any of my servers without me running it, and I have been working with SQL Server for 15 years.
August 28, 2012 at 2:09 pm
eric.lyons (8/28/2012)
dbcc checkdb runs on startup. This is an issue for any VLDB. It particularly is bad for mirroring and cluster failovers.
I rebooted my desktop yesterday morning. It has SQL Server 2008 R2 Developer Edition running on it, with several user databases on that, including a 78 Gig bulk-testing database (where I test code on large tables of realistic data). The log file has entries for starting up each database, and recovering each database, and then nothing for 2 hours, when it turned on DBCC TRACEON 3604 for a bunch of SPIDs. No DBCC CHECKDB entries in the log, unless you mean the "Recovery completed for database ..." entries. But those are just getting log and data into sync, to make sure it's consistent.
Where are you getting the data that DBCC CHECKDB runs on startup?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2012 at 2:11 pm
Here is my log file showing what happened at startup. If anyone knows how to disable the dbcc checkdb at startup for USER databases, I'd appreciate that piece of information. FYI, these DBs are NOT VLDB -- yet.
LogDateProcessInfoText
8/19/2012 9:51Server"Microsoft SQL Server 2005 - 9.00.4060.00 (Intel X86)
Mar 17 2011 13:20:38
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
"
8/19/2012 9:51Server(c) 2005 Microsoft Corporation.
8/19/2012 9:51ServerAll rights reserved.
8/19/2012 9:51ServerServer process ID is 3232.
8/19/2012 9:51ServerAuthentication mode is MIXED.
8/19/2012 9:51ServerLogging SQL Server messages in file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
8/19/2012 9:51ServerThis instance of SQL Server last reported using a process ID of 3252 at 8/19/2012 9:45:17 AM (local) 8/19/2012 2:45:17 PM (UTC). This is an informational message only; no user action is required.
8/19/2012 9:51ServerRegistry startup parameters:
8/19/2012 9:51Server" -d D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf"
8/19/2012 9:51Server" -e D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG"
8/19/2012 9:51Server" -l D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf"
8/19/2012 9:51ServerSQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
8/19/2012 9:51ServerDetected 16 CPUs. This is an informational message; no user action is required.
8/19/2012 9:51ServerAddress Windowing Extensions is enabled. This is an informational message only; no user action is required.
8/19/2012 9:51ServerUsing dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
8/19/2012 9:51ServerLock partitioning is enabled. This is an informational message only. No user action is required.
8/19/2012 9:51ServerMultinode configuration: node 0: CPU mask: 0x0000aaaa Active CPU mask: 0x0000aaaa. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
8/19/2012 9:51ServerMultinode configuration: node 1: CPU mask: 0x00005555 Active CPU mask: 0x00005555. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
8/19/2012 9:51ServerAttempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
8/19/2012 9:51ServerAttempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
8/19/2012 9:51ServerDatabase mirroring has been enabled on this instance of SQL Server.
8/19/2012 9:51spid7sStarting up database 'master'.
8/19/2012 9:51spid7s1 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
8/19/2012 9:51spid7s0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
8/19/2012 9:51spid7sRecovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
8/19/2012 9:51spid7sCHECKDB for database 'master' finished without errors on 2009-09-20 18:00:01.870 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid7sSQL Trace ID 1 was started by login "sa".
8/19/2012 9:51spid7sStarting up database 'mssqlsystemresource'.
8/19/2012 9:51spid7sThe resource database build version is 9.00.4035. This is an informational message only. No user action is required.
8/19/2012 9:51spid11sStarting up database 'model'.
8/19/2012 9:51spid7sServer name is 'XXX-CRL-MSSQL01'. This is an informational message only. No user action is required.
8/19/2012 9:51ServerA self-generated certificate was successfully loaded for encryption.
8/19/2012 9:51ServerServer is listening on [ 'any' <ipv4> 1433].
8/19/2012 9:51ServerServer local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
8/19/2012 9:51ServerServer named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
8/19/2012 9:51ServerServer is listening on [ 'any' <ipv4> 1434].
8/19/2012 9:51ServerDedicated admin connection support was established for listening remotely on port 1434.
8/19/2012 9:51ServerSQL Server is now ready for client connections. This is an informational message; no user action is required.
8/19/2012 9:51spid14sStarting up database 'Citrix-Performance'.
8/19/2012 9:51spid16sStarting up database 'SE Sharepoint DB'.
8/19/2012 9:51spid17sStarting up database 'NSMRulestat'.
8/19/2012 9:51spid15sStarting up database 'msdb'.
8/19/2012 9:51spid37sStarting up database 'ORData'.
8/19/2012 9:51spid38sStarting up database 'DiagramDB'.
8/19/2012 9:51spid19sStarting up database 'XPORT'.
8/19/2012 9:51spid20sStarting up database 'XXXXDW_TEST'.
8/19/2012 9:51spid18sStarting up database 'BEDB'.
8/19/2012 9:51spid27sStarting up database 'StopAudit'.
8/19/2012 9:51spid28sStarting up database 'dist_xport'.
8/19/2012 9:51spid30sStarting up database 'OnBase'.
8/19/2012 9:51spid29sStarting up database 'WORKFLOW'.
8/19/2012 9:51spid36sStarting up database 'VisibData'.
8/19/2012 9:51spid34sStarting up database 'HomeDepotPOS'.
8/19/2012 9:51spid35sStarting up database 'DW_Staging'.
8/19/2012 9:51spid32sStarting up database 'QRF'.
8/19/2012 9:51spid31sStarting up database 'Suppliers'.
8/19/2012 9:51spid33sStarting up database 'AlloyNav'.
8/19/2012 9:51spid26sStarting up database 'Scorecard'.
8/19/2012 9:51spid23sStarting up database 'se config DB'.
8/19/2012 9:51spid25sStarting up database 'Maint'.
8/19/2012 9:51spid24sStarting up database 'BI_Reporting'.
8/19/2012 9:51spid22sStarting up database 'landesk'.
8/19/2012 9:51spid21sStarting up database 'XXX'.
8/19/2012 9:51spid32sAnalysis of database 'QRF' (27) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
8/19/2012 9:51spid17sRecovery is writing a checkpoint in database 'NSMRulestat' (5). This is an informational message only. No user action is required.
8/19/2012 9:51spid32sCHECKDB for database 'QRF' finished without errors on 2012-08-19 03:10:03.917 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid26sCHECKDB for database 'Scorecard' finished without errors on 2012-08-19 03:10:05.117 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid38sAnalysis of database 'DiagramDB' (34) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
8/19/2012 9:51spid17sCHECKDB for database 'NSMRulestat' finished without errors on 2009-09-20 18:00:09.840 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid25sCHECKDB for database 'Maint' finished without errors on 2012-08-19 03:14:55.117 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid36sCHECKDB for database 'VisibData' finished without errors on 2012-08-19 03:10:05.883 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid11sCHECKDB for database 'model' finished without errors on 2009-09-20 18:00:06.150 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid29sCHECKDB for database 'WORKFLOW' finished without errors on 2009-09-20 18:00:06.150 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid31sCHECKDB for database 'Suppliers' finished without errors on 2012-04-27 08:41:05.063 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid11sClearing tempdb database.
8/19/2012 9:51spid18sCHECKDB for database 'BEDB' finished without errors on 2009-09-20 18:04:30.660 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid22sRecovery is writing a checkpoint in database 'landesk' (12). This is an informational message only. No user action is required.
8/19/2012 9:51spid33sCHECKDB for database 'AlloyNav' finished without errors on 2009-09-20 18:00:06.150 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid27sCHECKDB for database 'StopAudit' finished without errors on 2012-08-19 03:10:04.650 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid34sCHECKDB for database 'HomeDepotPOS' finished without errors on 2009-09-20 18:00:06.150 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid21sCHECKDB for database 'XXX' finished without errors on 2009-09-20 18:05:31.600 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid24sCHECKDB for database 'BI_Reporting' finished without errors on 2012-08-19 03:14:55.860 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid38sCHECKDB for database 'DiagramDB' finished without errors on 2009-09-20 18:00:06.150 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid22sCHECKDB for database 'landesk' finished without errors on 2009-09-20 18:05:32.457 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid23sCHECKDB for database 'se config DB' finished without errors on 2009-09-20 18:05:51.863 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid11sStarting up database 'tempdb'.
8/19/2012 9:51spid11sCHECKDB for database 'tempdb' finished without errors on 2009-09-20 18:00:06.150 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid17sThe Service Broker protocol transport is disabled or not configured.
8/19/2012 9:51spid17sServer is listening on [ 'any' <ipv4> 5022].
8/19/2012 9:51spid17sThe Database Mirroring protocol transport is now listening for connections.
8/19/2012 9:51spid17sService Broker manager has started.
8/19/2012 9:51spid15sCHECKDB for database 'msdb' finished without errors on 2009-09-20 18:00:06.637 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid28sRecovery is writing a checkpoint in database 'dist_xport' (21). This is an informational message only. No user action is required.
8/19/2012 9:51spid28sCHECKDB for database 'dist_xport' finished without errors on 2009-09-20 18:00:06.150 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid14sRecovery is writing a checkpoint in database 'Citrix-Performance' (6). This is an informational message only. No user action is required.
8/19/2012 9:51spid14sCHECKDB for database 'Citrix-Performance' finished without errors on 2009-09-20 18:00:11.713 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid35sCHECKDB for database 'DW_Staging' finished without errors on 2009-09-20 18:00:06.150 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid16sCHECKDB for database 'SE Sharepoint DB' finished without errors on 2012-08-19 03:00:00.803 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid30sAnalysis of database 'OnBase' (24) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
8/19/2012 9:51spid20sCHECKDB for database 'XXXXDW_TEST' finished without errors on 2009-09-20 18:00:06.150 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid30sCHECKDB for database 'OnBase' finished without errors on 2012-06-15 12:22:43.653 (local time). This is an informational message only; no user action is required.
8/19/2012 9:51spid37sAnalysis of database 'ORData' (33) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
8/19/2012 9:51spid37sCHECKDB for database 'ORData' finished without errors on 2012-08-19 03:00:00.850 (local time). This is an informational message only; no user action is required.
*****
Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P
August 28, 2012 at 2:11 pm
eric.lyons (8/28/2012)
dbcc checkdb runs on startup. This is an issue for any VLDB. It particularly is bad for mirroring and cluster failovers.
No it does not. SQL does not ever run checkDB automatically
What SQL does do on startup is log the date checkDB last ran without error into the error log. The log message is different to the one written by the execution of CheckDB.
eg
2012-06-09 13:01:28.02 spid20s CHECKDB for database 'Testing' finished without errors on 2012-01-24 18:08:24.677 (local time). This is an informational message only; no user action is required.
Note the difference in the two dates.
When CheckDB is run, the message is this one:
2012-08-28 22:10:45.37 spid62 DBCC CHECKDB (Testing) executed by AVALON\Gail found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 34 seconds. Internal database snapshot has split point LSN = 000039d7:00000056:0001 and first LSN = 000039d7:00000055:0001.
Two very different messages.
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
August 28, 2012 at 2:14 pm
eric.lyons (8/28/2012)
8/19/2012 9:51spid17sCHECKDB for database 'NSMRulestat' finished without errors on 2009-09-20 18:00:09.840 (local time).8/19/2012 9:51spid25sCHECKDB for database 'Maint' finished without errors on 2012-08-19 03:14:55.117 (local time).
8/19/2012 9:51spid36sCHECKDB for database 'VisibData' finished without errors on 2012-08-19 03:10:05.883 (local time).
Look at the highlighted dates.
Btw, some of your databases have not had a successful checkDB in 3 years, you may want to rectify that.
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
August 28, 2012 at 2:17 pm
And yet, on a job interview I was very surprised to learn about the HOURS passing waiting for a VLDB to come on line because of dbcc.
*****
Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply