November 16, 2009 at 11:14 am
Hello,
I've been using a script to create backups of a small database before I run batch updates 3 or 4 times a day. Originally, it would take a couple minutes and was working fine. Late last week, I realized that one of my SSIS packages was still running after almost 18 hours (usually took 4 to 5 minutes). The step it was stuck on was the backup, which when disabled the package would run in its normal time. I've tried running backups on other databases, but they work fine, so it seems just my database is the issue and it isn't very big at all.
Here are some stats on the database size: (I'm pretty new to this, so I'm still learning what statistics actually matter)
database_sizeunallocated space
1346.69 MB421.52 MB
reserved data index_sizeunused
80368 KB 77600 KB 1400 KB1368 KB
Here's the script:
Declare @FullPath as VarChar (200)
Declare @Server as VarChar(100)
Declare @FileName as VarChar(50)
Declare @Date as VarChar(50)
Set @Server = '\\Ddcdc1.doubledaycanada.com\data\Jeff\0001. DBA\Book Web App Proccess\99. Database_Backups\''
Set @FileName = '.bak'
Set @Date = Convert(VarChar(4),DatePart(yyyy,GetDate())) +
Right('0' + Convert(VarChar(2),DatePart(mm,GetDate())),2) +
Right('0' + Convert(VarChar(2),DatePart(dd,GetDate())),2) +
Right('0' + Convert(VarChar(2),DatePart(HH,GetDate())),2) +
Right('0' + Convert(VarChar(2),DatePart(n,GetDate())),2) +
Right('0' + Convert(VarChar(2),DatePart(SS,GetDate())),2)
Set @FullPath = @Server + @Date + @FileName
--Select @FullPath -- This just shows the result of the above code (full path)
Backup Database BookNMWebApplications
To Disk = @FullPath
With Format;
GO
November 16, 2009 at 12:32 pm
Perhaps a stupid question, but...is it possible that index fragmentation could have an impact on running the backup? The main table in this database has
avg_fragmentation_in_percent = 98.84678748
fragment_count = 3633
page_count = 3642
Is it more likely an issue with the server I'm trying to backup to? Sorry, I'm struggling to figure this out and don't want to continue to run my updates without creating backups beforehand.
Thanks,
Jeff
November 16, 2009 at 12:46 pm
Run that script via SSMS and see what the result is.
When you run it via your package run a sp_who2 to see whats going on with the backup task.
Fragmentation will lead to slightly longer run times as there will be more used pages to back up, but nowhere near to the extent s you are claiming. Its worth defragging the table for all sorts of reasons.
the with format clause is redundant.
---------------------------------------------------------------------
November 16, 2009 at 12:54 pm
You're backing up across the network. Any other network traffic will slow the backup down. Usual recommendation: Backup local, copy remote.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2009 at 1:24 pm
@George:
I've run this script in SSMS multiple times and it just runs and runs. I left it for over an hour and decided to cancel the query.
Point taken on the redundancy of the With Format clause. I've removed it, since the name changes everytime I run this.
@Gail:
Thanks for the best practice tip, I will definitely consider it go-forward. 🙂 However, I've been running this query as is for about 3 months now and had never encountered a problem until last Friday (the 13th no less). I changed the server name to the server that SQL Server runs on and am having the same issue. It just runs and runs... 🙁 I've also run it at night when there really shouldn't be much traffic as the nobody is in the building.
After running the sp_who2, I found the status for the spid that the backup is running as 'SUSPENDED'. What could cause it to suspend? Or is that an expected status while the query is running?
I really appreciate any and all help and advice.
November 16, 2009 at 2:16 pm
suspended means at the time you ran sp_who2 the query was waiting on some resource, not necessarily a problem unless it continues to wait.
To see what it is waiting on check the value of lastwaittype in sys.dm_exec_requests or sysprocesses. Also see if waittime continually rises for the same lastwaittype.
From what we know so far its probably waiting on networkio.
---------------------------------------------------------------------
November 16, 2009 at 2:33 pm
The last_wait _type = LCK_M_U
I noticed when running sp_who2 that there was a value in [BlkBy], which I assume means "blocked by" - I also assumed that the value referred to another spid, so I looked it up and found another block and another until I wound up finding "Symantec Backup Exec(TM) for Windows Servers".
Is it possible that this is the source of my problem? Probably something the network admin installed.
November 16, 2009 at 4:18 pm
I think you have found your problem, blkby does mean 'blocked by', your backup is waiting for the other one to complete. Only one full db backup can run at a time (on the same database).
Speak to the network admin as to what he has done. His backup via backup exec may have hung.
I don't know who is responsible for database backups at your site but they need to sort out the strategy by the sounds of it (backup AND recovery).
to be sure your backups are working this command tells you the last time a full backup was successfully taken:
select a.name, b.backup_finish_date
from master..sysdatabases a left join msdb..backupset b on a.name = b.database_name
where b.backup_finish_date = (select max(backup_finish_date) from msdb..backupset where database_name = a.name and type = 'D')
---------------------------------------------------------------------
November 17, 2009 at 7:12 am
Thanks so much for you help, George. I really appreciate it along with what I've learned from this experience.
We're pretty fexible here, so if I ask him to cancel any backups of databases I manage it won't be a problem. I prefer to be more independant anyway.
Again, much obliged! 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply