Solved

Dead lock on a transaction cannot delete database

Posted on 2011-02-17
10
2,419 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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