April 2, 2008 at 1:28 am
How much free space is there on the drive where the database is stored?
Did you move the databases at all?
Something has to have changed between when it worked and when it didn't - the ability of DBCC to create the hidden snapshot is purely based on the file-system allowing it, not on anything inside SQL Server (I wrote that code)
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:43 am
Could you check the windows event log please? See if there are any errors in there from around the time of the DBCC. Check the system and application logs.
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, 2008 at 3:14 am
There's enough free space on the drive where the database is stored, about 436 GB is free.
No databases have been moved at all.( However , the problem is with system databases only.)
No , change has been made for file system settings also.
Event logs read -
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\model.mdf:MSSQL_DBCC6
An error occurred while executing the following query: "DBCC CHECKDB([master]) WITH PHYSICAL_ONLY".
On server: "EMEA-MGT1".
SQL error number: "071F".
SQL error message: "A database snapshot cannot be created because it failed to start.
Thanks
April 2, 2008 at 3:29 am
The only thing I can think of is that something crashed and caused a hidden snapshot to stay around. In that case another hidden snapshot cannot be created. If this is the case then restarting the server should fix the problem - it's the only way to remove existing alternate streams.
Without having access to the system and knowing all the operations that have happened to the server I can't tell. Did any CHECKDBs fail for any reason other than snapshots?
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 3:37 am
Just the system databases? Interesting. Master, Model and MSDB all giving the same error?
Are the user databases on a different drive?
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, 2008 at 3:53 am
All system databases - master/msdb/model - give the same error.
Checkdb for the other db's ( user db's ) run successfully and do not report any error.
The user databases are placed on the same drive as sytem databases.
Please advise.
Thanks & Regards,
Anshu
April 2, 2008 at 3:56 am
And just to be sure, you could successfully run CHECKDB on master, model, msdb the day before? No crashes?
Are the user databases set to auto-close?
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 4:02 am
I haven't run CHECKDB on SQL Express for a long time so just for my peace-of-mind I ran CHECKDB on master, model, and msdb on an Express instance - it works fine. It *has* to be something specific to your situation that is the issue here.
Can you try restarting the Express instance?
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 4:09 am
Yes, we could successfully run CHECKDB on master, model, msdb the day before.No crashes.
Yes,the user databases are set to auto-close.
April 2, 2008 at 4:14 am
Can you set one of the user DBs to not auto-close and then run CHECKDB on it twice? I want to see if it succeeds or not.
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 4:55 am
Thanks a lot, guys - for all your help and guidance.
I shall try restarting the SQL instance and also will try changing the setting - auto close to false and check. But I can do it only after business hours.
I'll share the result with you.
Thanks again !
Cheers,
Anshu
April 8, 2008 at 4:48 am
Hi...
We tried restarting the Instance... and even rebooted the server...but it didn't help.
REstarting the instance gave an error saying that maser db could not be read.
Hence had to restore the master db.
Thanks for your cooperation.
Regards,
Anshu
October 22, 2009 at 10:32 am
We experienced the same error messages this morning. The system dbs would all fail on the CHECKDB, the user databases would work correctly.
We first tried bringing down the SQL Agent and running CHECKDB with TABLOCK against MSDB. This was successful. We then tried creating a new database and this failed. We discovered that the permissions granted to the group SQLServer2005MSSQLUser on the database folder was incorrect, it had apparently been changed sometime after the last successful backup of the system dbs. We changed the permissions and were able to get the CHECKDB, backup and create new db completed.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply