November 4, 2010 at 6:45 am
Hi,
After discovering that one our disks on one of our DB-servers where running full, i found out that msdb was reaching 23gb..
So, trying to work out the issue, and to clean up the database i started sp_delete_ backuphistory with only 1 day at the time, but it takes about 15 min to complete per day (and i need to clean up over 1 year), doing more than 50k records at the time is not considered a good idea (Google was my friend).
So, i decied to start creating some indexes on the db, so that the process could work faster, and also be able to be faster for later use.
Though, this process is taking alot long than what i expected.. It's been running for 12 hours stright now, and im only running 1 part of the query..
use msdb
go
--backupset
Create index IX_backupset_backup_set_id on backupset(backup_set_id)
go
Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)
go
Create index IX_backupset_media_set_id on backupset(media_set_id)
go
Create index IX_backupset_backup_finish_date on backupset(backup_finish_date)
go
But studding the storedprocedure closer, i dont need a index on backup_set_uuid for example.. so my question is 2 parted..
1. How long should it take to complete this index (there are 600 000 rows in the table in question). CPU usage is quite low, it spikes every 10-15 sec.
2. Can i stop the query safly, and redefine the query?
/Marius
November 5, 2010 at 2:34 am
It's now been running for over 31 hours..
While creating/updateing indexes, the database is not locked by any means? A collegae of mine is trying to run an update of a pice of software we are using, but he claims it times out during the backup phase of the upgrade. Is this related?
How much time should be used to create the indexes? Does it take just as long as selecting every row in the database or something like that?
/Marius
November 5, 2010 at 8:37 am
I have no idea how long it will take to create the index(es) but if you are low on space then that could be causing some of the issues. Indexes require space and creating indexes require log space. What is the msdb log set to grow by?
Yes the backup is timing out because of the index creation.
Here's a simple test to prove that (run on a dev box):
USE msdb;
Go
BEGIN TRAN
Create index IX_backupset_backup_set_id on backupset(backup_set_id);
--select text to here and run it
ROLLBACK TRAN
in another query window run a backup command, doesn't matter what you backup.
Then run this in another query window:
SELECT
DER.session_id,
DER.request_id,
DER.start_time,
DER.status,
DER.command,
DER.database_id,
DER.user_id,
DER.connection_id,
DER.blocking_session_id,
DER.wait_type,
DER.wait_time,
DER.last_wait_type,
DER.wait_resource,
DER.open_transaction_count,
DER.percent_complete,
DER.estimated_completion_time,
DER.cpu_time,
DER.total_elapsed_time
FROM
sys.dm_exec_requests AS DER
You should have row for backup database that has a blocking session id and wait type of LCK_M_SCH_S
Then run the Rollback Tran in the first query window and the backup will complete.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply