Dropping database from EM taking forever

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Dropping database from EM taking forever

Author

Topic

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-12-29 : 17:54:19

I am trying to drop a database from EM, I selected the option to delete backup and restore history for this database. It has been running for 5 hours taking up 25% of the CPU on a 4 processor machine. Nobody is in the database and there are no blocks.

Any ideas?

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-12-29 : 18:38:57

Tara has some information on this in her blog:

http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1704.aspx

this is becuase you have a huge backup history in MSDB and the routines that MS has to delete from the history are extemely inefficient. Tara offers a script to speed this process up and there are also several alternate fixes suggested in the comments.

-ec

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-12-29 : 18:40:16

Thank you VERY much.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-12-29 : 18:49:29

quote:
Originally posted by jhocutt

Thank you VERY much.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

btw, a script to prune the backup history (like tara blogged on) or similar is something that should be scheduled to run at weekly intervals on every SQL Server. I would consider this a best practice.

I personally use the "add an index to that system table and then run sp_delete_backuphistory" approach mentioned in the comments. Here is the code I run:


-- Create an index to speedup the sp_delete_backuphistory system stored proc
Create index tmp_backupset_ec on backupset (media_set_id);

-- Set some variables and then exec the the sproc
DECLARE @numdays int
DECLARE @oldestdate datetime
SET @numdays = 30
SET @oldestdate = DATEADD(day, -@numdays, getdate())  -- subtract @numdays from current datetime
EXEC sp_delete_backuphistory @oldestdate;

-- Drop the previously created index
Drop index backupset.tmp_backupset_ec

I just put all that code in a scheduled job and run it weekly. change that @numdays variable to the number of days you want to keep.

-ec

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-12-29 : 18:59:28

That I will do right now.
Well after a quick test :)

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-12-30 : 09:00:29

quote:
Originally posted by eyechart

this is becuase you have a huge backup history in MSDB and the routines that MS has to delete from the history are extemely inefficient.

Yet another reason to not use SQL EM. Even SQL QA has a little overhead. Everytime you connect to a server it runs several queries. LOL.

Not including the set commands SQL QA runs all this everytime you connect and SQL EM does even more.


-- Login
SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())

select name 
from master.dbo.spt_values 
where type = 'E' 
	and name in ('OS/2','WINDOWS/NT') 
	and (@@version like '%Microsoft%' or ((@@version like '% 1993 %' or @@version like '% 1992 %') 
	and @@version not like '%Sybase%'))
-- Logout
-- Login
select @@microsoftversion
SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())
select @@spid
select IS_SRVROLEMEMBER ('sysadmin')

You'd think MS could pull back @@MicrosoftVersion and @@Spid at the same time or even with something else, but they don't. The most effient tool a dba has is either osql or isql but I'll take the minor overhead of SQL QA over them anyday. I tend to stay out of SQL EM for almost every task because the overhead it causes is not too much fun and it's query tool has problems with date values.

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2006-01-04 : 08:46:25

I would like to run this (below) to delete old Backup History.

-- Create an index to speedup the sp_delete_backuphistory system stored proc
Create index tmp_backupset_ec on backupset (media_set_id);

-- Set some variables and then exec the the sproc
DECLARE @numdays int
DECLARE @oldestdate datetime
SET @numdays = 30
SET @oldestdate = DATEADD(day, -@numdays, getdate()) -- subtract @numdays from current datetime
EXEC sp_delete_backuphistory @oldestdate;

-- Drop the previously created index
Drop index backupset.tmp_backupset_ec

I looked in the Backupset table in msdb and I have Backup History dating back to 2002!!!!!!!!!!

Am I going to hurt anything running this system stored procedure sp_delete_backuphistory? Also, seems like this is something that the system should take care of itself.. anyone know why I have backup history dating back to 2002?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2006-01-04 : 10:16:50

Well when I cleaned mine out it used 100% of one processor on a 4 processor machine.
But everything seemed to work ok, but being the paranoid I am I ran it late at night.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2006-01-04 : 10:48:46

Thanks! I'll run it off hours !!!

Just wanted to say this post was very helpfull !!! I still don't understand why SQL Server keeps YEARS worth of Backup History..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources