October 26, 2011 at 7:01 am
A daily maintenance task is to run a DBCC CHECKDB on all databases including the system databases.
This ran perfectly for a while, but for a few days SOME of the databases fail the command with the error:
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.
Of course I searched the web for possible answers, but none did any good.
The databases which fail the dbcc checkdb do NOT have have a read only filegroup.
The account executing the command does have sufficient privileges, because other db's do not fail and all the database files are in the same location.
We use SQL Server 2005 (64bit) Standard Edition, so making a manual snapshot is no option, because that isn't supported in this version.
These were the options I found, but none of them were the answer for me.
A reboot or restart of the SQL services is not really an option, but when that is the only possible option to try.......
Anyone an other suggestion?
October 26, 2011 at 7:08 am
I hit google in case I could find something useful (Paul Randal's posts... as he's the ms guy who wrote the code!).
That thread might help out even if you seem to have covered most of them => http://www.sqlservercentral.com/Forums/Topic721829-146-1.aspx#bm722087
I've never had that issue nor read about it so that's basically the most I can do for you.
October 26, 2011 at 7:19 am
Are you running DBCC CHECKDB with the one of the repair options? If you do, then the database has to be in single user mode. In this case you shouldn’t use any of the repair options. Those options should be used only if a problem was found.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 26, 2011 at 7:33 am
Thanks for you post.
I forgot to mention that I have plenty of space left on the drive.
Only NTFS drives (no partitions on those drives).
Check I did, after reading Paul Randal's post:
There are no 'old' snapshot hanging around. Only my main databases are listed when I do a select * from sys.databases.
It also can't be a issue with the length of the path to the database files, because databases with longer names and the same location do not have a problem with the dbcc check command.
October 26, 2011 at 7:50 am
The command without a repair option: dbcc check (<database>) with physical_only.
October 26, 2011 at 7:52 am
spidey73 (10/26/2011)
The command without a repair option: dbcc check (<database>) with physical_only.
Why do you choose to not run the full checkdb?
October 26, 2011 at 7:58 am
Something is causing the snapshot creation to fail.
If you're on Enterprise Edition, see if you can create a manual database snapshot on the same drive as the data file. If you can, drop it immediately, it's just to see if you can create one without errors.
Check the SQL error log. There should be more errors there relating to the snapshot creation.
Do you have any 3rd party IO drivers (eg Diskeeper), online disk defrag tools or the like?
Can you restart SQL any time soon?
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
October 26, 2011 at 8:00 am
[/quote]
Why do you choose to not run the full checkdb?[/quote]
I really don't know why. I inherited these servers and I never gave it a second thought.
(lame excuse, I know)
But, like you probably can imagine, the full checkdb, gives the same error.
October 26, 2011 at 8:09 am
spidey73 (10/26/2011)
Why do you choose to not run the full checkdb?
I really don't know why. I inherited these servers and I never gave it a second thought.
(lame excuse, I know)
But, like you probably can imagine, the full checkdb, gives the same error.
My intention was to drag out as much info as possible untill Gail came around. Hopefully giving her all she needs for a quick answer.
You may want to give it a full check to make sure you don't miss something else (assuming you're window is long enough).
October 26, 2011 at 8:19 am
GilaMonster (10/26/2011)
Something is causing the snapshot creation to fail.If you're on Enterprise Edition, see if you can create a manual database snapshot on the same drive as the data file. If you can, drop it immediately, it's just to see if you can create one without errors.
Check the SQL error log. There should be more errors there relating to the snapshot creation.
Do you have any 3rd party IO drivers (eg Diskeeper), online disk defrag tools or the like?
Can you restart SQL any time soon?
Standard Edition, so no manual snapshots.
No errorlog messages at all regarding the dbcc error.
No Diskeeper or defrag tools.
The strange thing is that from the 11 databases (including the system dbs) the same 5 always fail.
October 26, 2011 at 8:26 am
Check permissions. SQL needs full permissions to files and folder.
What are the names of the files of those databases?
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
October 26, 2011 at 8:44 am
spidey73 (10/26/2011)
The strange thing is that from the 11 databases (including the system dbs) the same 5 always fail.
Have these databases come from a legacy\previous version of SQL Server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 26, 2011 at 12:53 pm
Some of the databases that fail the checked came from an other server, but the msdb also fails and that one comes with the installation of course.
The checked did run great on the databases. I restored two databases about a month ago. Both databases are similar and come from the same server. One of them clears the checked, the other one fails.
I checked the permissions. All databases are owned by sa.
The user that runs the maintenance job in which all the checkdb commands are executed had got all permissions to the data folder. (that's why other databases do not have a problem)
Not @work right now, so I'm not able to post the path names of the data files.
October 26, 2011 at 12:58 pm
spidey73 (10/26/2011)
I checked the permissions. All databases are owned by sa.The user that runs the maintenance job in which all the checkdb commands are executed had got all permissions to the data folder. (that's why other databases do not have a problem)
That's not what I asked about. Does the SQL Server service account have full control (in Windows) to the files and folders involved? (all of them)
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
October 27, 2011 at 1:27 am
That's not what I asked about. Does the SQL Server service account have full control (in Windows) to the files and folders involved? (all of them)
The Service account has got full access to the share in which all the SQL files are located.
Isn't it so, that if it was en permission issue, that the checkdb on all the databases would fail?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply