June 11, 2009 at 10:12 am
Hi,
We have sql server 2005 and have scheduled dbcc checkdb job weekly and log backup job daily every hr. On weekend dbcc checkdb job starts at 9:00PM and at the same time log backup also running.
Is this allowed or do I need change the log backup shcedule?
thanks
June 11, 2009 at 10:25 am
Try to Run the Backup Log at a seprate time than DBCC CHECKDB. This wil help minimize performance issues if any. but of your Database and log sizes are BIG, it will matter a lot.
how much time does your log back take approx... and how much time does the DBCC Take..
Maninder
www.dbanation.com
June 11, 2009 at 11:12 am
You will see contention between these two processes.
I'd suggest getting the DBCC run first. If there are problems with the database, you want to know prior to running backups.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 11, 2009 at 1:41 pm
Grant Fritchey (6/11/2009)
You will see contention between these two processes.I'd suggest getting the DBCC run first. If there are problems with the database, you want to know prior to running backups.
This is not quite correct. You may see contention on memory or cpu utilizatioin, but a CHECKDB will not block any other processes unless you specifically utilize the TABLOCKX hint. CHECKDB by default creates an internal database snapshot and performs the integrity check on the snapshot.
BTW - you really wouldn't want to stop performing transaction log backups for several hours while you are running an integrity check as that will leave you in a rather risky situation if your system were to crash during that time.
And yes, performing a full integrity check on a 400GB database is going to take a couple of hours.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 11, 2009 at 1:45 pm
Jeffrey Williams (6/11/2009)
Grant Fritchey (6/11/2009)
You will see contention between these two processes.I'd suggest getting the DBCC run first. If there are problems with the database, you want to know prior to running backups.
This is not quite correct. You may see contention on memory or cpu utilizatioin, but a CHECKDB will not block any other processes unless you specifically utilize the TABLOCKX hint. CHECKDB by default creates an internal database snapshot and performs the integrity check on the snapshot.
BTW - you really wouldn't want to stop performing transaction log backups for several hours while you are running an integrity check as that will leave you in a rather risky situation if your system were to crash during that time.
And yes, performing a full integrity check on a 400GB database is going to take a couple of hours.
Not to be to persnickety, but I didn't say it would cause blocking. Running a series of full backups and a series of consistency checks simultaneously will, as you say, cause contention, just not blocking.
I agree. They should leave the log backups running.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 11, 2009 at 1:53 pm
Here's the other issue. If you find errors in DBCC, and you've just run your backup, it might be useless. I'd argue you should get the integrity checks done, and then run a backup or page someone based on results.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 1, 2016 at 10:40 pm
Hi Grant Fritchey,
So, you are saying when dbcc checkdb and log backup job run at same time, there will be contention.
When the two jobs are running at same time, dbcc checkdb is not doing consistency/integrity checks on any of the databases. Log backup job is getting completed successfully. When I manually restart the dbcc checkdb job after few hours making sure there will be no parallel log backup running, the dbcc checkdb job is performing consistency/integrity checks on all databases as desired.
Could you please let me know the reason for this behavior...how to fix it
June 2, 2016 at 3:01 am
7 year old thread.
Please create a new thread for a new question.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply