May 7, 2009 at 7:35 am
I am having a problem with a database check running past the allocated maintenance window.
The command I run is below ...
SQLMAINT -CkDB -CkAl -CkCat -CkTxtA
This is the typical result for the database check...
Starting maintenance of database on 5/4/2009 10:12:44 PM
[1] Database : Check Data and Index Linkage...
** Execution Time: 11 hrs, 13 mins, 49 secs **
[2] Database : Check Data and Index Allocation...
WARNING: Database is currently being used by 977 user(s).
This may cause spurious problems to be reported.
** Execution Time: 0 hrs, 1 mins, 11 secs **
[3] Database : Check System Data...
** Execution Time: 0 hrs, 0 mins, 6 secs **
[4] Database : Check Text/Image Data Allocation...
WARNING: Database is currently being used by 966 user(s).
This may cause spurious problems to be reported.
** Execution Time: 0 hrs, 0 mins, 46 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance for database on 5/5/2009 9:28:36 AM
SQLMAINT.EXE Process Exit Code: 0 (Success)
Some times I get errors during the check, but they are not related to data corruption I believe ...
[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has been disconnected. You must reconnect to perform this operation.
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 831: [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to deallocate a kept page.
The database in question is about 220 gig between two data files. The Check DB starts at about 10:15 PM every night. I have other maintenance plans that run at 12:00 AM for much small DB's, and they are all run at the same time. I have also been getting errors on the DB check for these ...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 701: [Microsoft][ODBC SQL Server Driver][SQL Server]There is insufficient system memory to run this query.
This problem started on April 1st 2009. The maintenance plan never ran over 4 hrs before that, now it runs over 12 hrs on a regular basis. This started happening shortly after applying SQL 2005 SP 3.
Any help would be greatly appreciated.
May 7, 2009 at 8:13 am
Well first off, you only need to run the CHECKDB part - it does all the other stuff too.
By running too long, you mean it's running longer than usual - that usually means that its found some corruption.
Run the following for your databases and post the results:
DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS
Also check the error log for signs of corruptions too. While this is all running, figure out what backups you have in case you're going to have to restore them.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 7, 2009 at 9:39 am
Thanks for the quick response! I will run the DBCC CheckDB command tonight, and let you know what it comes back with.
I didn't see any error messages in the log that would lead me to believe there was any database corruption. I am crossing my fingers!
May 7, 2009 at 4:41 pm
The DBCC CHECKDB () WITH ALL_ERRORMSGS, NO_INFOMSGS run successfully in 1 hour and 8 minutes. I guess my database isn't corrupt. That would have been a real drag.
I can't seem to figure out why this consistency check is taking soo long to run at night??
May 7, 2009 at 6:05 pm
Which SP level of 2005 are you at? There was a bug that could possibly cause this: http://kbalertz.com/949199/Error-message-queries-database-SNAPSHOT-isolation-level-enabled-Server-deallocate.aspx
In terms of why it takes a long time to run - what else is happening in the database while it's runnin?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 8, 2009 at 12:23 pm
We are currently running on SQL 2005 SP 3 (9.00.4035.00).
I saw that hot fix while researching this problem a while back, but it says to apply it to SP 2 and we are on SP 3. Also, I only saw the Unable to deallocate kept page error once.
Below is a summary of what runs at night...
- Exception Alerts (Generate Emails)
- Missing Invoices
- Billing Setup Descrepencies
- UnPaid Invoices
- etc ...
- These all run in under 15 seconds.
- Log Shipping Backups for smaller DBs.
- SQL Maint runs on all smaller DBs at 12:00 am.
I have an excel spreadsheet that lists every job and its duration for last night, but it is 663 rows. If you think it will help, I can send it to you.
The only items I see in the error log are ...
- AppDomain 5327 (.dbo[runtime].5341) is marked for unload due to memory pressure.
- Downgrading backup log buffers from 1024K to 64K
Please let me know if there is any additional information I can provide that will shed a light on this issue.
Thanks!
May 8, 2009 at 7:19 pm
Are you running 64 or 32 bit? And what Edition of SQL Server? Standard?
I'm wondering if you're getting memory paged out while CHECKDB is running.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 9, 2009 at 11:49 pm
Unfortunately we are running the 32 bit Enterprise Edition of SQL, because the software vendor does not support 64 bit!
The server is Windows 2003 R2 32 bit. We have the /PAE boot.ini switch set. SQL is setup to use AWE. We choose NOT to set the /3GB switch in fear of starving the OS of VM. The server has 4 quad core processors, and 32 GB of RAM. The storage system is an EMC AX4.0 with a fiber channel connection.
May 10, 2009 at 11:48 am
I'm guessing that's your problem then. They've just announced support for Lock Pages In Memory for 32-bit - should be out in the next month or two. See here.
[Edit: thought I deleted this post right after I posted it - LGIM setting is only on 64-bit, not 32-bit. Coffee issue when I posted that.]
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 11, 2009 at 11:36 am
We already have the AD Account that the SQL Services run under setup with the Lock Pages in Memory user right. The link you sent me refers to the Standard Edition, and we are using the Enterprise Edition?
May 11, 2009 at 12:31 pm
I know - see the edit in the post above - I thought I'd deleted it right away as it's wrong.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 11, 2009 at 3:15 pm
No problem.
Do you have anymore ideas on what I can do to further troubleshoot this issue?
May 11, 2009 at 3:33 pm
DO you see any error messages in sql errorlog (not exact but something similar)
spid73 A time-out occurred while waiting for buffer latch -- type 4, bp 00000000B3FE1180, page 1:2554, stat 0x2c0010b, database id: 10, allocation unit Id: 72057594045530112, task 0x0000000003E7AB08 : 0, waittime 300, flags 0x2000000021, owning task 0x00000000046905C8. Not continuing to wait.
May 11, 2009 at 3:46 pm
Paul Randal (5/10/2009)
[Edit: thought I deleted this post right after I posted it]
Delete doesn't do anything anymore. Couple issues with people posting insults and then deleting the posts after others replied harshly.
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
May 11, 2009 at 4:43 pm
I didn't see anything like that in the SQL Error Log. The Check DB started at about 10:00 PM last night, and all I say in the error log were a bunch of Log Shipping informational messages, and the DBCC CheckDB results for the smaller DBs. At 6:00 AM the next day I saw the below error message...
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576
Which was followed by a bunch or memory clerk, cache store, user store, object store, etc... messages.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply