Solved

Dead lock on a transaction cannot delete database

Posted on 2011-02-17
10
2,137 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
 

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Authentication and Win NT Authentication Issues 20 39
backups - Strategies 1 13
Sql query for filter 12 21
Mssql SQL query 14 27
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now