January 31, 2008 at 6:30 pm
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 1, 2008 at 2:00 am
Hi
Did you add a drive, copy, replace or rename any data files files when the SQL Server was stopped, or the databases were offline? The message shows that SQL Server is unable to read the file. Just recall on what you did?
I can also see that some data pages are corrupted. The only solution to this is to restore the backup. You may able to restore only the corrupted portions.
I'll say the first step should be to identify what has happened.
Then take a tail log backup (using WITH NORECOVER option in you BACKUP LOG command)
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
February 1, 2008 at 5:29 am
Nope. No adding of drives or messing with the files.
TLog backup wouldn't do any good, recovery model is simple.
The odd thing is if I put the DB in single-user mode I don't get the error.
The database is up and appears functional; just get the error when I run DBCC. I can SSIS the data to another database. That's probably what I'll end up doing, I'm just trying to understand the root cause of the error.
February 1, 2008 at 5:52 am
Mark,
You might want to put this in the "SQL Server 2005 --> Data Corruption" forum as Paul Randal is often in there and may be able to help...
February 4, 2008 at 3:19 pm
Yup - we found the cause - 3rd party file-system driver for an encryption solution that doesn't cope with NTFS alternate streams. I blogged abotu it at http://www.sqlskills.com/blogs/paul/2008/02/04/SearchEngineQA14Beware3rdPartyFilesystemDriversWithDBCCCHECKDB.aspx
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
March 31, 2008 at 8:30 am
There's no such encryption/decryption software on our server , no space issues either...What else can be the problem ..?
March 31, 2008 at 8:34 am
When I was researching the problem I did see alot of references to seeing a similar issue with SAN and/or Disk drivers. Might want to make sure you have the latest.
I also so a reference to the problem when a database was attached using sp_attach...
March 31, 2008 at 9:02 am
We have an old version of SAN disk driver i.e. v5.03.c...Pls suggest if that can be the cause of problm....however , the checkdb returned no error/issues till yesterday. It was today , when the issue cropped up.
About sp_attach.... It cannot be the proble,..as its about system databases - master and model.
Please advise.
Thanks.
March 31, 2008 at 9:35 am
Anshu Wadhwa (3/31/2008)
There's no such encryption/decryption software on our server , no space issues either...What else can be the problem ..?
I take it you have a similar problem.
Can you post the errors you have and the output of CheckDB if possible?
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
March 31, 2008 at 9:40 am
Here's what the message window shows :
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf:MSSQL_DBCC6'.
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.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
March 31, 2008 at 9:41 am
Here's what the message window shows :
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf:MSSQL_DBCC6'.
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.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
March 31, 2008 at 10:29 am
Hmmm....
What OS are you running? Please confirm that the drive is using the NTFS file system
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
April 1, 2008 at 2:54 am
The system has - Standard Edition of Microsoft Windows Server 2003 , Servic Pack 2 installed.
Yes, it has NTFS file system.
April 1, 2008 at 5:12 am
Can you try creating an explicit snapshot of one of the databases to see if that works?
A regular snapshot creates explicit sparse files. The snapshot that DBCC uses is created as alternate streams on the existing database files (that's how the snapshot is hidden from view). There could be something preventing the server process from creating the alternate streams.
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
April 2, 2008 at 1:24 am
The server has Express edition installed on it , so an explicit database snapshot is not allowed.
Since there's no 3rd party encryption/decryption filter driver , please guide what else could prevent creation of alternate streams.
(please note that the consistency checks were working fine till the previous day and no change has been made after then.)
Many Thanks !
Best Regards,
Anshu
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply