December 10, 2012 at 7:19 am
Hi all,
Trying to drop a few old databases (I checked to see no one was using the DB before i moving on). I tried using the GUI and ticking delete backup history and close connections. 40 mins later it hadnt completed so close the connection (I looked and couldnt see anything in Activity monitor to suggest it was still trying)
since it toke so long i wondered if it might be taking an age to remove the msdb data. So tried running the SP instead to separate out the work.
USE [master]
GO
-- Clear backup history of Database
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'myDatabase'
GO
This goes on for 10 mins or so then I get the error:
Msg 64, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
Looking at the SQL Log Its creating a new log at the same time (Which i think means its restarting the service???)
Ive just found that the database was in a Maintenance Plan for backups. I removed it and set the database to SIMPLE. And retried the drop database and this worked. But im still getting the same issue with the MSDB clean up?
Anyone come across this before? This is the first place i have seen Maintenance plans used.. Do they stop database drops if a database is used in one of the plans???? Very strange..
December 10, 2012 at 7:27 am
When you were checking to make sure the databases weren't in use, how did you check that? sp_who2? Something else?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 10, 2012 at 7:34 am
Hi!
sp_who and activity monitor
either show any connections to the db.
After changing the maintenace plans to not include the db for backups its let me drop it with
-- DROP DATABASE mydb
DROP DATABASE [mydb]
GO
But i get the same issue with removing the MSDB db data. currently its been going for 20 mins. which cant be right :/
December 10, 2012 at 7:35 am
How frequently does that maintenance plan run? Is it running while you're doing this?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 10, 2012 at 7:40 am
trans log backups are every 30 mins so with it taking so long they would have come into play yes.
guessing its better to drop a db by putting it into single user mode first. But to be honest i didn't expect to many issues...
December 10, 2012 at 12:26 pm
backup history can take a VERY long time to clean up if there is a lot of history to work through, the supplied code is not very efficient and IIRC the msdb table would benefit from some indexes for this operation.
Easiest workaround is to set up a repeating job just to purge backup history older than a certain date and do this in chunks. There is a maintenance plan task to do this.
---------------------------------------------------------------------
December 10, 2012 at 5:50 pm
Hi All,
Activity monitor will be very slow if there is alot of activity going on. I basically do not use it. Use sp_who2 and look at the database columns to see if anyone is using the database.
I wrote a usp_who2 which writes this data to a temp table that you can use. Check out my blog article for the free code. http://craftydba.com/?p=1000
If you know that a particular login uses the database, just disable it at the server level. Below is code to disable a login from one of my sample databases.
-- Disable the account
ALTER LOGIN [WILDLIFE_ADMIN] DISABLE;
Last but not least, put the database is restricted user mode. The command below kicks off all users except those who are members of the db_owner fixed database role or dbcreator and sysadmin fixed server roles. You will lose any work mid progress with the rollback command. Large transactions will take time to revert, so be careful.
-- Kick off users, roll back current work
ALTER DATABASE [WILDLIFE] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
GO
At that point, you should have full control to drop the database.
-- Which database to use.
USE [master]
GO
-- Delete existing database
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'WILDLIFE')
DROP DATABASE WILDLIFE
GO
Good Luck,
Sincerely
John
John Miner
Crafty DBA
www.craftydba.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply