check database integarity takes long

Question

check database integarity takes long

sqlfriend

SSC Guru

Points: 52794
More actions
April 15, 2012 at 5:42 pm

#254098

I have a weekend maintenance plan to check database integrity on server A. It starts at 10:00 pm.
I noticed the job takes quite long about 6 hours. we have another job scheduled on Server B at 2:00 am, which has a step to restore a database to Server A. It failed at 2:50 am, the error is insufficient memory internal on server A.
I guess this could be because the check integarity job still runs, then the restore a db needs memory, I wonder why it takes so long. Besides regular databases, we have about 40 read-only databases server A. Maybe it takes longer to do check integarity for readonly databases?
So for read-only databases, maybe we don't need to do the check integerity task, correct? If I remove them for check db integrity, this may save some time?
Thanks,

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

You absolutely do need to do integrity checks of read only databases. A misbehaving IO subsystem is not polite enough to check the read only property before it mangles the data or log file.

CheckDB alone won't cause insufficient memory errors. Sounds like max memory's not set or there are other memory-related problems. My bet it max memory's not set or is set too high.

As for the length of checkDB, that's a factor of the database size, IO capacity and available CPU. The larger the DBs, the longer checkDB will take.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

I just did a dbcc checkdb for one of the readonly archived db, it takes about 8 minutes. we have about 60 archived database, so 6 hours may make sense.

The other job to restore a db on this server A, that may compete memory while there is also a checkdb job going on?

Yes, this server we don't have max memory set, this is a server that has 7 GB memory.

So I will set the max memory to 5 GB (5120), leave 2 GB for OS.

Does that sound right?

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

sqlfriends (4/15/2012)
Yes, this server we don't have max memory set, this is a server that has 7 GB memory.

Yup, as I thought. That's your problem right there.

CheckDB does a huge number of reads, so it will grow SQL's buffer pool. Backup and restore use memory outside the buffer pool which, if the buffer pool is using the full 7GB (as it is allowed to without max memory set), there will be none available.

So I will set the max memory to 5 GB (5120), leave 2 GB for OS.

That's a fair initial setting.

Chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

Thank you, I will read it.

By the way, I see in microsoft site:

http://msdn.microsoft.com/en-us/library/ms178067(v=sql.100).aspx

Server Memory Options

It says:

Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access.

So it first recommend to use the default configuration.

Does that mean we should use default memory configuration first, if not working, then we manually set the max memory?

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

This is one area where I and many others disagree with Book Online. Set the max server memory always. See chapter 4 of the book I linked to.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

GilaMonster (4/16/2012)
This is one area where I and many others disagree with Book Online. Set the max server memory always. See chapter 4 of the book I linked to.

And I have to agree with Gail. Forgot to do this at a previous employer when first setting up some new x64 SQL Server systems. Interesting to see a server die when you choke off the memory that the OS needs to run.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Lynn Pettis (4/16/2012)
Forgot to do this at a previous employer when first setting up some new x64 SQL Server systems. Interesting to see a server die when you choke off the memory that the OS needs to run.

Same. Was interesting to see the SQL Server buffer pool using 47.6GB of memory on a server that had 48GB. Couldn't connect to the server, couldn't connect to SQL and we hadn't enabled remote DAC so we couldn't even get in that way. Had to force a cluster failover (by connecting to the passive node) to get things working again.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101200 More actions · Answer 8

You should have something in place that checks for disk hardware errors, with immediate notification and fast response.

And do proper monitoring of the suspect pages table and the SQL error log for page errors.

Naturally make sure you have a current backup/image of all read-only dbs.

Given that, I wouldn't spend resources doing frequent integrity checks on read-only databases. Monthly or even quarterly checks should be good enough in most cases.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

CapnHector SSCoach Points: 16143 More actions · Answer 9

Lynn Pettis (4/16/2012)
GilaMonster (4/16/2012)
This is one area where I and many others disagree with Book Online. Set the max server memory always. See chapter 4 of the book I linked to.
And I have to agree with Gail. Forgot to do this at a previous employer when first setting up some new x64 SQL Server systems. Interesting to see a server die when you choke off the memory that the OS needs to run.

had a server that did not have max memory set and it just about died when we tried plugging in a USB HDD. Lots of fun on that one.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]