Solved

Dead lock on a transaction cannot delete database

Posted on 2011-02-17
10
2,266 Views
Last Modified: 2012-05-11
Hi,

  i was trying to delete a database and cannot be deleted because it is on single user.tried to kill the process i.e spid and tried running "exec sp_dboption 'res_powerfuse_uk', 'single user', 'FALSE' ". I was getting error message
 "Msg 1205, Level 13, State 68, Line 1
Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Line 1

   could you please let me know how i can delete this database as i need to retstore it ASAP
ALTER DATABASE statement failed.
0
Comment
Question by:t250
  • 5
  • 4
10 Comments
 
LVL 6

Expert Comment

by:PIERCGG
ID: 34919572
Have you tried going into the Services on your server and stopping and restarting the SQL Server Service?  That should stop the process from running so you can delete the db.
0
 

Author Comment

by:t250
ID: 34921257
piercgg,

    yes i have tried restarting the sql services and tried deleting still cannot delete the database
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34921421
then, try stop sql and  rename yr database "physical" file to something before start it back

Once start, that database should be in some kind of "problem" mode.
which can be detach.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:t250
ID: 34924204
JoeNuvo,

   tried it still the same prioblem..
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 34924374
Hum?
well, them stop sql, rename you file back, start yr SQL and check if below link can help you

http://remidian.com/mssql/remove-sql-server-database-from-single-user-mode.html
0
 

Author Comment

by:t250
ID: 34924393
joenuvo,

  tried this link before posting in this forum.below is the error message iam getting

Transaction (Process ID 136) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34924424
after you rename the SQL data & log file name and start SQL Engine back.
using Management Studio, does the (Single User) flag disappear?

and attach script not help at all?

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'databasename'
GO

Open in new window

0
 

Author Comment

by:t250
ID: 34924430
JoeNuvo,

  i cannot restart SQL now but will try later as users logged on to that server as that server hosts other databases as well...
0
 

Author Comment

by:t250
ID: 34933064
joenuvo,

   deleted the database by renaming the data and log files,thanks for your help..
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34933071
Ah.. in that case, you have accept the wrong comment as solution.

Anyway, glad that your problem solved.

Good Day!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question