Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dead lock on a transaction cannot delete database

Posted on 2011-02-17
10
Medium Priority
?
2,631 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:t250
ID: 34924204
JoeNuvo,

   tried it still the same prioblem..
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

963 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