Solved

checkdb on sql server

Posted on 2013-05-19
3
371 Views
Last Modified: 2013-05-27
I am having a sql server 2008R2. Database is using more like reporting. Data is loaded every night and during the day users are requesting data from databases.
After loading of the data I have a maintenance plan which runs( checkdb on every databases and after that backup databases).
The problem is that the databases are more than 600GB and checkdb completes for about 4hr. Database backups takes nearly the same amount of time. And because the maintenance plan takes so long , it was constantly finishing at 10:00AM at the morning, and the users are starting their worki at 08:00AM, and of course I receive a couple of complains for slow database at the morning.
Could you tell me what can be done in order to fix this problem?
0
Comment
Question by:dedri
3 Comments
 
LVL 10

Expert Comment

by:sqlservr
ID: 39180192
why do you have a requirement of running DBCC CHECKDB on a database Daily Basis,any specific reason,

if you know and specific tables database is updated daily then you can run
DBCC CHECKTABLE on that table and fix,

scherio
you have a database name DB_prod and that database have 20 tables, there is a table named as report table "Daily_Report" then run DBCC checktable command on Daily_Report table, which saves your time
0
 

Author Comment

by:dedri
ID: 39180627
there are updates on a lot of tables, and developers constantly changing something on the server. I don't want to be in a situation where I missed something.
Could you advice me how often I should run DBCC CHECKDB. I thought I have to do it on a daily basis in order to find out corruption as soon as possible and in order to not backup a corrupt database. ETL process during the night takes 5-6 hours and if I have to restore a database before 5 days and run 5 ETL it will be very hard process.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39183092
You could try using the WITH PHYSICAL_ONLY switch.  Here is an article written by one of the architects of DBCC CHECKDB():
CHECKDB From Every Angle: Consistency Checking Options for a VLDB
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loop through SQL parameters and insert to temp table? 4 40
Tsql query 6 22
sql 2008 how to table join 2 16
T-SQL Default value in Select? 5 27
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question