April 2, 2009 at 7:49 am
After upgrading from 2005 to 2008, checkdb is failing with the follwing errors. It fails on all but 2 databases that were created right before the upgrade.
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:\MSSQL.1\MSSQL\Data\Academic Reporting.mdf:MSSQL_DBCC9'.
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.
April 2, 2009 at 7:51 am
Looks like your system doesn't have access to the file. Check the account SQL is running under and make sure it has full access to the directory in the error message.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2009 at 7:54 am
GSquared (4/2/2009)
Looks like your system doesn't have access to the file. Check the account SQL is running under and make sure it has full access to the directory in the error message.
At first glance, that's what I thought but it's not the case. Just to make sure I made the SQL service account local administrator. Didn't fix it.
April 2, 2009 at 8:10 am
Check the permissions on the directory D:\MSSQL.1\MSSQL\Data\ and make sure that the SQL service account has access. Note that it is possible to have a dir that administrator has no access to (I've done that in the past)
OS error 5 is a permission denied error
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 2, 2009 at 8:18 am
GilaMonster (4/2/2009)
Check the permissions on the directory D:\MSSQL.1\MSSQL\Data\ and make sure that the SQL service account has access. Note that it is possible to have a dir that administrator has no access to (I've done that in the past)OS error 5 is a permission denied error
Yep, all is good permission wise. If the service didn't have access, sql server wouldn't start as that's where all the databases reside. checkdb does work for 2 of the databases that are in that same directory.
April 2, 2009 at 8:23 am
Are you running this in a job? If so, check the SQL agent permissions.
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 2, 2009 at 8:26 am
GilaMonster (4/2/2009)
Are you running this in a job? If so, check the SQL agent permissions.
I was when I noticed it failed but it also fails when run from management studio.
April 2, 2009 at 8:29 am
Just a wild guess:
is it possible that the files you are trying to access are also part of some other good DBs?
Are you able to open the files from the OS?
April 2, 2009 at 8:38 am
sunny Brook (4/2/2009)
Just a wild guess:is it possible that the files you are trying to access are also part of some other good DBs?
Are you able to open the files from the OS?
This is an error running checkdb so yes the files are from good dbs.
April 2, 2009 at 8:44 am
sunny Brook (4/2/2009)
is it possible that the files you are trying to access are also part of some other good DBs?
The file in question ('D:\MSSQL.1\MSSQL\Data\Academic Reporting.mdf:MSSQL_DBCC9') is al alternate stream on the database file that is being checked. It's a hidden snapshot that CheckDB uses to get a consistent view of the DB so that it doesn't have to take table locks.
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 2, 2009 at 8:47 am
Did an online search and found Paul Randal's blog.. It suggests to check Space issue among others. Here is the link.
http://www.sqlskills.com/BLOGS/PAUL/post/Database-snapshots-when-things-go-wrong.aspx
April 2, 2009 at 8:48 am
sunny Brook (4/2/2009)
Did an online search and found Paul Randal's blog.. It suggests to check Space issue among others. Here is the link.http://www.sqlskills.com/BLOGS/PAUL/post/Database-snapshots-when-things-go-wrong.aspx
I found that one too. There's 160GB free on that drive.
April 2, 2009 at 9:49 am
This might happen when a CHECKDB-created snapshot fails to be removed if something goes wrong while CHECKDB is running. Doesn't happen very often - but when it does the hidden-snapshot (created using alternate NTFS streams on the existing DB files) is not dropped. I've heard anecdotal evidence of this but never seen it for real in a production environment.
Restarting SQL Server should fix the problem. I believe you can check whether the alternate stream (the filename in the error message) exists by running fsutil against it.
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 7, 2009 at 8:33 am
Reboot fixed it.
April 7, 2009 at 8:50 am
Cool (well, kind of). Do you know if there was a failed CHECKDB beforehand?
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply