December 12, 2010 at 10:55 pm
HI all,
I am getting following error while running dbcc checkdb on one of the my database
i am using SQL SERVER 2005 EXPRESS EDITION
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.
help will be appreciated ................
December 13, 2010 at 12:08 am
Helpful links
http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic23785.aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 13, 2010 at 12:16 am
hey i have already refer to that link...............
database snapshot feature is not available in sql server 2005 express edition
December 13, 2010 at 12:22 am
use sp_lock then find the spid associated with concerned DB and kill them ( but first you can check them whats running behind them by DBCC inputbuffer(spid))
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 13, 2010 at 12:31 am
You might try ALTER DATABASE SINGLE_USER ROLLBACK IMMEDIATE.
This will force the database to a single_user mode (the one running the process) and rollback all transactions when it occurs. Make sure to run your process in the same connection, as well as an ALTER DATABASE MULTI_USER in the same script, or you might lock the world out.
Express isn't good except as a desktop, local server. If you have multiple users connecting to the system, you're best off at least purchasing standard, or making sure you have a single IIS connection to it that you can control, and very lightweight activity.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 13, 2010 at 12:45 am
thanks for the help
its working..................
December 13, 2010 at 12:49 am
How did you resolve ? Always post resolution so that other can get the benefits.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 13, 2010 at 1:52 am
i use sp_lock then find the spid associated with concerned DB...
i kill that Spid.......
December 13, 2010 at 3:21 am
The bigger question here is why exclusive access was needed, ie why CheckDB couldn't use the snapshots it normally does. Were there any other errors in the error log, any errors from earlier?
If you restart the SQL service, does the problem persist?
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
May 20, 2016 at 11:46 am
Evil Kraig F (12/13/2010)
You might try ALTER DATABASE SINGLE_USER ROLLBACK IMMEDIATE.This will force the database to a single_user mode (the one running the process) and rollback all transactions when it occurs. Make sure to run your process in the same connection, as well as an ALTER DATABASE MULTI_USER in the same script, or you might lock the world out.
Express isn't good except as a desktop, local server. If you have multiple users connecting to the system, you're best off at least purchasing standard, or making sure you have a single IIS connection to it that you can control, and very lightweight activity.
Is this against best practices? I have a maintenance plan that performs the DBCC CheckDB and it fails from time to time on one particular server lately. As part of my job, once a week, I have to provide a report on failed jobs/maintenance plans to my manager.
When it fails, I don't have a clear answer other than "it happens sometimes" or the error message will delineate the database that it couldn't get an exclusive lock on. I'd like to perform some "Sherlock Holmes" on the situation and get more detail, but I'm not sure what else to do, plus I'm pretty new at this whole "locking and blocking" thing.
Any advice or suggestions would be great, TIA!
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply