checkdb on sql server
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?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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