June 15, 2015 at 8:51 am
Hi,
fairly new to SQL, and being tasked with copying an SQL2005 DB to an SQL2008R2 Server.
We are running daily full backups through SSMS on the SQL2005 server (OS is still Windows 2003), and I thought I'd simply import this into a new SQL2008R2 DB on a new Windows 2008R2 server. All systems are running virtualized on the same platform, storage is an FC-attached EMC VNX5300.
The backups run without errors.
Now after restoring the Backup into my SQL2008R2 instance I'm running a consistency check in SSMS, and get a huge number of Errors (any chance to retrieve then in english?):
Fehler:(-1073548784) Fehler beim Ausführen der Abfrage 'DBCC CHECKDB(N'TESTDB') WITH NO_INFOMSGS
': 'Objekt-ID 218744132, Index-ID 0, Partitions-ID 72057594234667008, Zuordnungseinheits-ID 72057594250133504 (In-row data-Typ): Seite (1:7118482) konnte nicht verarbeitet werden. Einzelheiten finden Sie in anderen Fehlermeldungen.
Tabellenfehler: Objekt-ID 218744132, Index-ID 0, Partitions-ID 72057594234667008, Zuordnungseinheits-ID 72057594250133504 (In-row data-Typ), Seite (1:7118482). Fehler bei Test (IS_OFF (BUF_IOERR, pBUF->bstat)). Die Werte sind 12716041 und -4.
Objekt-ID 218744132, Index-ID 0, Partitions-ID 72057594234667008, Zuordnungseinheits-ID 72057594250133504 (In-row data-Typ): Seite (1:7118487) konnte nicht verarbeitet werden. Einzelheiten finden Sie in anderen Fehlermeldungen.
The total number of errors is 1570 on a DB of ~300GB:
DBCC CHECKDB (TESTDB) WITH no_infomsgs executed by user-domain\dbuser found 1570 errors and repaired 0 errors.
In desperation I've installed an SQL2005 instance on my new Windows 2008R2 server, and restored the database into this instance without errors, so I guess there's something wrong with my SQL2008R2 instance.
Any idea what causes these inconsistencies?
Kind regards,
Andreas
August 6, 2015 at 11:19 pm
CHECKDB has been improved since SQL Server 2005. It might uncover errors that 2005's CHECKDB cannot. When CHECKDB reports errors, you should avoid using NO_INFOMSGS. CHECKDB usually reports a repair option (at the end of its report), but if iterative CHECKDB repair attempts (if even possible) do not progressively fix the database's structure (as opposed to fixing your corrupted data, because gone is gone), or if CHECKDB starts reporting corruption on system tables, you should throw in the towel. Many of SQL Server's messages are localized, and will change language based upon Windows' locale. But some error messages (usually lower level messages) are compiled within a localized version of SQL Server (such as a German edition). Try installing an English version of SQL Server 2008 R2 and using it for further upgrade testing - perhaps 2008 R2 developer edition is still available from Microsoft's download site. It may be that SQL Server 2008 R2 is fine, but the underlying hardware or OS is not, which means it would be a good idea to install SQL Server 2008 R2 developer edition elsewhere (on a different OS). Keep in mind that garbage can be backed up without error, and can be restored without error (until a later version of CHECKDB is run). You may want to run CHECKCATALOG on 2005. One of the excerpted errors (what little I can understand) concerns an ASSERTION (a sanity check) "IS_OFF (BUF_IOERR, pBUF->bstat)". A quick search led me to http://www.sqlnotes.info/2013/05/02/fix-page-checksum/, but performing those steps up to 1570 times could be daunting. And following those steps doesn't really address the cause of checksum errors - they just make checksum errors go away, leaving each page's payload (i.e. your data) in whatever mangled state it is (which caused the page's checksum to differ for what was written). I can't remember which SQL Server version first enabled checksummed pages optionally. Perhaps checksummed pages are enabled for the database versions running on (attached to) SQL Server 2005, but checksummed pages are automatically enabled on 2008 R2 (I also can't remember when they were changed to being automatically enabled :). It may be that the corruption of 1570 pages (~ 12 MB of the database's 300 GB) happened long ago, only to be discovered after upgrading to 2008 R2. Or it may be that the on-disk locations of the 2008 R2 database's files are in bad shape. To rule that out, take the 2008 r2 database offline, stop all SQL Servers, temporarily move those files to another location off-disk, download SQLIOSIM and configure it to use the exact same locations/disks as the database's files (strictly speaking, file location is where the file system wants to put a file, regardless of the directory/folder name), and let SQLIOSIM run for hours. Hours are needed because some IO errors are intermittent, and some of those may not even be 'experienced' for hours, let alone days. If SQLIOSIM gives the disk and system memory a clean bill of health, it could be that the corruption has been around for years (undetected). Be sure BIOS (and other OS/filter drivers, including anti-virus) are up to date, because they are more privileged than the OS, and thus can change a page after it leaves SQL Server's memory but before it gets to disk. If CHECKDB cannot repair or if you just want to get it done, you can try alternative methods to transfer data, such as BCP, replication, or DTS. Even they will fail when touching the live wire of checksum corruption, but perhaps they will dance around the corruption (as 2005 might currently be doing). Personally, I think running SQLIOSIM is critical, at least for the very small peace of mind that it offers whenever it cannot detect a HW problem.
August 10, 2015 at 1:46 am
This turned out to be a nasty issue with vSphere 5, Intel e1000e Network Drivers and a Windows 2012 VM used as backup target:
Apparently the export/import in SQL2005 did not trigger this issue, but importing into SQL2008R2 resulted in corrupt data. I guess checksums are not verified on restore.
We disabled TCP Offloading for now on the Server, and things have been fine since then.
Regards,
Andreas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply