December 30, 2013 at 8:09 am
Hello,
I've started getting errors in my nightly maintenance job during the update statistics portion. They're all slightly different, but along these lines:
Executing the query "UPDATE STATISTICS [dbo].[allocations]
WITH FULLS..." failed with the following error: "The operating system returned error stale page (a page read returned a log sequence number (LSN) (2055:16:1) that is older than the last one that was written (0:0:0)) to SQL Server during a read at offset 0x00000036790000 in file 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\DATA\\tempdb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
and
Executing the query "UPDATE STATISTICS [dbo].[dwellings]
WITH FULLSCA..." failed with the following error: "The operating system returned error incorrect checksum (expected: 0x1dc2cbc0; actual: 0x1dc2cbc0) to SQL Server during a read at offset 0x0000003fade000 in file 'C:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\DATA\\tempdb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I'm running a dbcc checkdb as it says, but "see books online" is not a very helpful pointer to more information. Does anyone know what this kind of error indicates?
EDIT: DBCC CHECKDB reports 0 allocation and 0 consistency errors on the database in question. Yet I seem to be getting these errors almost nightly.
January 8, 2014 at 7:50 am
Well, I've tried rebooting the server, and moving the time of the job in case it was getting interrupted by the network backup agent, but I still continue to get errorrs during this job every single night.
Now the errors are incomprehensible. Here's last night's error for example:
Date1/8/2014 2:00:00 AM
LogJob History (Nightly backup.Maintenance)
Step ID1
ServerTELLER2
Job NameNightly backup.Maintenance
Step NameMaintenance
Duration00:26:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: TELLER2\Administrator. ...gress: 2014-01-08 02:00:31.37 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[ModelDrill] WITH FULLSC...".: 41% complete End Progress Progress: 2014-01-08 02:00:31.37 Source: Update Statistics Task Executing query "use [ReportServer] ".: 43% complete End Progress Progress: 2014-01-08 02:00:31.38 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[ModelItemPolicy] WITH F...".: 45% complete End Progress Progress: 2014-01-08 02:00:31.38 Source: Update Statistics Task Executing query "use [ReportServer] ".: 46% complete End Progress Progress: 2014-01-08 02:00:31.38 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[ModelPerspective] WITH ...".: 48% complete End Progress Progress: 2014-01-08 02:00:31.38 Source: Update Statistics Task Executing query "use [ReportServer] ".: 50% complete End Progress Progress: 2014-01-08 02:00:31.39 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[Notifications] WITH FUL...".: 51% complete End Progress Progress: 2014-01-08 02:00:31.39 Source: Update Statistics Task Executing query "use [ReportServer] ".: 53% complete End Progress Progress: 2014-01-08 02:00:31.40 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[Policies] WITH FULLSCAN...".: 54% complete End Progress Progress: 2014-01-08 02:00:31.40 Source: Update Statistics Task Executing query "use [ReportServer] ".: 56% complete End Progress Progress: 2014-01-08 02:00:31.41 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[PolicyUserRole] WITH FU...".: 58% complete End Progress Progress: 2014-01-08 02:00:31.41 Source: Update Statistics Task Executing query "use [ReportServer] ".: 59% complete End Progress Progress: 2014-01-08 02:00:31.42 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[ReportSchedule] WITH FU...".: 61% complete End Progress Progress: 2014-01-08 02:00:31.42 Source: Update Statistics Task Executing query "use [ReportServer] ".: 62% complete End Progress Progress: 2014-01-08 02:00:31.43 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[Roles] WITH FULLSCAN ".: 64% complete End Progress Progress: 2014-01-08 02:00:31.43 Source: Update Statistics Task Executing query "use [ReportServer] ".: 66% complete End Progress Progress: 2014-01-08 02:00:31.44 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[RunningJobs] WITH FULLS...".: 67% complete End Progress Progress: 2014-01-08 02:00:31.44 Source: Update Statistics Task Executing query "use [ReportServer] ".: 69% complete End Progress Progress: 2014-01-08 02:00:31.44 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[Schedule] WITH FULLSCAN...".: 70% complete End Progress Progress: 2014-01-08 02:00:31.44 Source: Update Statistics Task Executing query "use [ReportServer] ".: 72% complete End Progress Progress: 2014-01-08 02:00:31.46 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[SecData] WITH FULLSCAN ...".: 74% complete End Progress Progress: 2014-01-08 02:00:31.46 Source: Update Statistics Task Executing query "use [ReportServer] ".: 75% complete End Progress Progress: 2014-01-08 02:00:31.46 Source: Update Statistics Task Executing query "UPDATE STATISTICS [dbo].[Segment] WITH FULLSCAN ...".: 77% complete End Progress Progress: 2014-01-08 02:00:31.46 Source: Update Statistics Task Executing query "use [ReportServer] ".: 79% complete End Progress Progress: 2014-01-08 02:00:31.47 Source: Update Statistics Task Executing query "UPDATE STATISTICS... The package execution fa... The step failed.
How the heck am I supposed to debug this if the error text is being abbreviated?
January 8, 2014 at 7:55 am
Log the job output to a file (properties of the job step -> advanced).
However, you have larger problems, run the following, post the full and complete results and start doing some investigation of the IO subsystem, looks like you've got a corrupt database there, possibly stale reads as well (IO subsystem returning data that should have been overwritten).
Do you have a clean backup? One from before the corruption occurred? When did CheckDB start failing?
DBCC CheckDB('<database name>') WITH NO_INFOMSGS, ALL_ERRORMSGS
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
January 8, 2014 at 8:14 am
I'll try the logging thing, thanks.
The job's been failing for a while. I can probably get a backup from before, but it'd be useless because of the amount of data change.
January 8, 2014 at 8:19 am
Why didn't you address the problem when this or the scheduled checkDB job started to fail? (You do have a scheduled CheckDB job?)
You're probably going to lose data as a result of ignoring the corruption. It's not going to go away, it's probably not fixable without data loss if there's no backup that can be restored.
The CheckDB output?
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
January 8, 2014 at 8:59 am
GilaMonster (1/8/2014)
Why didn't you address the problem when this or the scheduled checkDB job started to fail? (You do have a scheduled CheckDB job?)
Sorry, I didn't understand the whole scope of the problem. At first I only got these once in a while, and when I checked the next morning the job would run fine manually and there where no errors reported by CheckDB.
For the record, "DBA" isn't my job title, I just got stuck taking care of this because I was the only one who knew SQL.
You're probably going to lose data as a result of ignoring the corruption. It's not going to go away, it's probably not fixable without data loss if there's no backup that can be restored.
The CheckDB output?
I ran your CheckDB command on each database on the server (not including the system ones). Each one just reported that "the command completed successfully" and nothing else. The log recorded a message pretty much like this for each datbase:
DBCC CHECKDB (grm_practice) WITH all_errormsgs, no_infomsgs executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 10 minutes 18 seconds. Internal database snapshot has split point LSN = 00075d7f:00006317:0001 and first LSN = 00075d7f:00006316:0001. This is an informational message only. No user action is required.
I've configured logging to a file and I'm going to rerun the job to see if I can get some useful error messages.
January 8, 2014 at 9:52 am
And of course, now that I have logging set up, the job runs successfully.
Maybe tonight I'll get a useful error.
Thanks for your help.
January 8, 2014 at 10:32 am
alanm 5339 (1/8/2014)
I ran your CheckDB command on each database on the server (not including the system ones). Each one just reported that "the command completed successfully" and nothing else. The log recorded a message pretty much like this for each datbase:
System DBs too.
If they also come back clean, then you have some form of intermittent corruption, which is still bad. Get someone to check the storage subsystem and schedule a regular database integrity check (before the backup is usually a good time to schedule it)
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
January 8, 2014 at 12:54 pm
They came back clean. I'll see what I can do about getting the storage situation looked at.
February 5, 2014 at 10:30 am
Grasshopper,
Where you able to find out the root cause of this issue? I'm having the same issue with updating stats and running certain queries. The checksum error always comes back on the tempdb. All dbcc checkdb executions come back clean on this db. I'm running SQL 2012 in a virtual environment and all the data resides on an EMC VNXe SAN. Thanks for any additional assistance you can provide.
February 5, 2014 at 1:17 pm
Unfortunately no. I asked the systems admin to check on the storage arrays but he came up with nothing. I get errors maybe 3-4 times a month, usually on the weekends. checkdb always comes up clean (scheduled or manual).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply