| Author |
 Topic  |
|
influent
Constraint Violating Yak Guru
USA
361 Posts |
 Posted - 11/09/2011 : 14:21:11
|
| Well in my case it didn't make a difference, the PLE still dropped to zero with CHECKDB running against the scrubbed databases. |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 11/09/2011 : 15:40:51
|
"On our busiest and most critical systems, we run checkdb on a backup/restore server"
Me too. |
|
|
|
sql-lover
Yak Posting Veteran
54 Posts |
Posted - 11/13/2012 : 14:23:13
|
Bringing this thread to life!
And thanks Tara for your comments and follow up. Interesting conversation.
I may have the same problem at work. I just created hundreds of maintenance plans using smart jobs. The databases have not been checked or defrag in years. But now we are getting some timeouts issues that coincidentally occur during the time the Integrity Check jobs runs. I do have separate Integrity Jobs though, one per database.
Now, while checking a monthly performance monitor that I have for one of my system, I noticed that page life expectancy drop horrible during the exact time the dbcc checkdb occurs. That's what makes me think there should be some relationship between both.
How I can accomplish the Integrity Check on a different system when the DBA deals with hundreds of databases? Manually will be time consuming. Any automatic or script solution for that? I guess we are talking about these steps
-Restore existing backup for a database
-Run Integrity Check
-Send alert if job fails
-Delete the restored database
I do not see above solution for all databases at the same time, as we will require massive amount of space.
Any comments?
Thanks, |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
|
|
sql-lover
Yak Posting Veteran
54 Posts |
Posted - 11/13/2012 : 14:46:15
|
quote:
Originally posted by robvolk
There's a lot of guidance here:
http://www.sqlskills.com/blogs/paul/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx
Great link :-) ... I actually read it already.
My question was more towards a specific solution for doing offsite db checks, like via scripts.
I would like to adopt an existing process, instead of re-inventing the wheel.
I MUST check my databases. I can't afford a data corruption. My alternative are:
-Doing Integrity Jobs monthly basis (kind of scary)
-Using a separate system.
But I am not willing to take the 2nd approach if it won't be an automatic process and for each and all of my databases. Otherwise, I prefer to live with the PLE issue than taking the risk of ending with corrupted database. |
Edited by - sql-lover on 11/13/2012 14:48:05 |
|
|
|
chadmat
The Chadinator
USA
1955 Posts |
Posted - 11/13/2012 : 14:51:35
|
I thought DBCC CheckDB used disfavoring so as to not flush out your buffer pool? I could swear Paul Randal said that, but maybe I am mistaken.
-Chad |
|
|
|
sql-lover
Yak Posting Veteran
54 Posts |
Posted - 11/13/2012 : 15:08:08
|
quote:
Originally posted by chadmat
I thought DBCC CheckDB used disfavoring so as to not flush out your buffer pool? I could swear Paul Randal said that, but maybe I am mistaken.
-Chad
Well, I checked my perf. monitor and filtered by that exact time; I can see how the PLE goes down almost instantly, when jobs kick in, and goes up an hour or so later, which is basically when all the checks are done.
I should mention that the two servers are really old. Not enough RAM and running SQL 2005. That probably is exacerbating the issue.
My perf. monitor did not run for my SQL 2008 boxes, so I cannot validate there, but the errors come from those two old machines.
There is a high chance the DBCC command has something to do there.
If I can't find an existing offsite dbcc check solution, maybe I will change schedule (big dbs) to monthly and push it a bit, so it won't overlap with the smaller dbs. That may alleviate the problem. |
Edited by - sql-lover on 11/13/2012 15:11:24 |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 11/13/2012 : 17:28:58
|
quote:
Originally posted by chadmat
I thought DBCC CheckDB used disfavoring so as to not flush out your buffer pool? I could swear Paul Randal said that, but maybe I am mistaken.
I'm pretty sure I heard the same thing from him. Maybe it's different for 2005 vs. 2008+?
If this is really a big problem, add more RAM, it's ridiculously cheap now, about $10/GB. We just bought a little over 1 TB for about $12.5K. If you're maxed out on your servers, then upgrade them, or move those databases to better boxes. Otherwise look at piecemeal checks like Paul suggested. |
|
|
|
sql-lover
Yak Posting Veteran
54 Posts |
Posted - 11/14/2012 : 09:37:42
|
quote:
Originally posted by robvolk
quote:
Originally posted by chadmat
I thought DBCC CheckDB used disfavoring so as to not flush out your buffer pool? I could swear Paul Randal said that, but maybe I am mistaken.
I'm pretty sure I heard the same thing from him. Maybe it's different for 2005 vs. 2008+?
If this is really a big problem, add more RAM, it's ridiculously cheap now, about $10/GB. We just bought a little over 1 TB for about $12.5K. If you're maxed out on your servers, then upgrade them, or move those databases to better boxes. Otherwise look at piecemeal checks like Paul suggested.
Agree!
Adding more RAM is a cheap and quick solution, but management refuses to do it, because the downtime. Long history.
I will move these two boxes with all those 200+ databases to a brand new Cluster soon. |
|
|
|
Topic |
|
regular drops of page life expectancy to zero
