February 1, 2008 at 6:20 am
Dunno if anyone else has seen this problem. Have a test SQL server (SQL2005 standard, SP1) running on Win2K3 Server (standard) with about 6 databases on it.
All of the databases give the same error with a DBCC checkdb:
Msg 5172, Level 16, State 15, Line 1
The header for file 'E:\Data\dbawork.mdf:MSSQL_DBCC14' is not a valid database file header. The PageAudit property is incorrect.
Msg 5120, Level 16, State 9, Line 1
Unable to open the physical file "E:\Data\dbawork.mdf:MSSQL_DBCC14". Operating system error 0: "(null)".
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
DBCC results for 'dbawork'.
DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.
DBCC results for 'sys.sysrowsetcolumns'.
There are 538 rows in 5 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 78 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 89 rows in 1 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 538 rows in 5 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 78 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 89 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 120 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 47 rows in 1 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 419 rows in 7 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 111 rows in 1 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 225 rows in 1 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 111 rows in 19 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 14 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 133 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 102 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'dbawork'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Couple more things:
1) if I create a new DB on the server, the problem exists on the newly created database (I know the model database is probably the problem)
2) sp_attach... was not used on these databases.
3) product support has been less than helpful.
4) Backups from this machine yield the same issue when restored to another machine.
5) DBCC CheckDB runs fine if I put the DB in single user mode.
I'd like to understand the problem as well as come up with a solution to solve it. I saw this issue beforeThe last time I saw this issue I had to DTS the data off the box to another box and DTS it back on.
Most of the posts around this issue relate to someone using sp_Attach and sp_detach, which were not used.
There is some info on the 'net indicating a possible problem with the drive array controller.
Anyone have any ideas?
Mark
February 3, 2008 at 9:09 am
The problem is that the database snapshots which DBCC CHECKDB uses to run onlien in SS2005 can't be created (this is why you don't see the problem when running in single-user mode - no snapshot is necessary).
I suspect you have some kind of file system filter driver installed that doesn't cope with NTFS alternate streams on files (the way that CHECKDB's database snapshots are created). I've seen this before with a 3rd-party encryption solution that was causing CHECKDB to fail in a similar manner but that was quite a while ago when I still owned the CHECKDB code.
Is this the case for you? I don't think theres's anything else this can be - you can try creating your own database snapshot and running CHECKDB against that. If it works, my hypothesis above is correct.
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
February 3, 2008 at 9:13 am
Paul,
Exactly! I wasn't aware they were using netlib to encrtypt the data on the drive until I dug a little deeper.
Thanks so much for your help! This one has me chasing my tail for a while....
Just started as a full-time DBA a month ago for the company I'm at and still in 'discovery' mode.
I think we can accomplish the same thing using EFS (built into windows encryption); this will make things much simpler.
Mark
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply