Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2728
  • Last Modified:

Dead lock on a transaction cannot delete database

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
t250
Asked:
t250
  • 5
  • 4
1 Solution
 
PIERCGGCommented:
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
 
t250Author Commented:
piercgg,

    yes i have tried restarting the sql services and tried deleting still cannot delete the database
0
 
JoeNuvoCommented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
t250Author Commented:
JoeNuvo,

   tried it still the same prioblem..
0
 
JoeNuvoCommented:
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
 
t250Author Commented:
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
 
JoeNuvoCommented:
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
 
t250Author Commented:
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
 
t250Author Commented:
joenuvo,

   deleted the database by renaming the data and log files,thanks for your help..
0
 
JoeNuvoCommented:
Ah.. in that case, you have accept the wrong comment as solution.

Anyway, glad that your problem solved.

Good Day!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now