January 9, 2006 at 4:45 am
For the first time in my 5 year DBA career, I needed to restore a database using the point in time restore due to a customer deleting a lot of data that they did not wish to re-enter. I wanted to restore using EM since I believe you can choose a full backup and all subsequent transaction log backups in one operation, perhaps two.
When right clicking on the database, and choosing Restore, I got a reasonable amount of disk access (for about a minute), then high CPU utilization and an ever growing page file usage. I waited about 10 minutes, then killed the task, tried from another workstation and the SQL server itself, all with similar results.
I finally gave up and used QA and scripted the restore myself. Of course, with this method I had to do each transaction log one by one on a separate command, looking up the actual backup file name myself. It worked fine, but what's going on with EM? Was I just impatient, and it's normal for a dialog box to take > 10 minutes to pop up? The actual restore took about 30 seconds once I had the script right.
Server is a dual pentium > 1gigHz with 2 gig ram, plenty of free hard disk space. We do backups to disk and had 679 backup files (which is 3 days worth of full and transaction log backups for various databases). I believe the history file keeps the last thousand entries for each db (about 15 dbs). SQL Server 7 with all service packs. MSDB backup is about 1 gigabyte in size.
Student of SQL and Golf, Master of Neither
January 10, 2006 at 9:03 am
Your problem might lie in huge backup history tables in msdb (backupfile, backupset etc.). Check the sizes and search this site for sp_delete_backuphistory. There are several discussions on this including problems purging backup history that has been accumulating for years.
January 10, 2006 at 10:45 am
Martin is right. Try scheduling something like this to run on a regular basis that will clear backup history older then X days, change 60 days to one of your choice ( note:first time might take a while to run):
USE MSDB
DECLARE @backup_date DATETIME
BEGIN
set @backup_date=(select dateadd (dd, -60, getDate()))
EXEC SP_DELETE_BACKUPHISTORY @backup_date
END
January 10, 2006 at 11:09 am
If this is the problem, (which I don't doubt, and will find out late at night or this weekend), what good is the setting in EM under maintenance plans that reads something like "Limit rows in history table to 1000 rows for this plan"?
Student of SQL and Golf, Master of Neither
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply