Backup time taking too long

Question

Backup time taking too long

SkyBVI

SSCoach

Points: 15128
More actions
November 12, 2010 at 6:43 pm

#231331

Hi,
On of my databases is taking 6 hours to complete a full backup nightly.
Its 75 gigs. Its very slow according to me. The hardware configuration is fine as earlier the same backup was taking around 1 hr to complete.
Now since some few days its taking too much time. I checked that there were no other maintenance jobs going on at time of the backup,.
When the backup started, i tried to monitor it. I ran sp_who2 and I got:-
there were 3 entries for same spid and all 3 had status as suspended and command as backup database.
I opened activity monitor to see the wait time for all 3 backup database command were very high. The wait type ASYNC_IO_COMPLETION had the highest wait time, the other 2 were backupbuffer and backupio. All otehr processes had 0 as wait time. What else do i have to monitor?
Can I have any idea, whats going on as the backup is very slow.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

sql server is waiting on disk IO which either means there is a disk contention issue or your disk is not able to cope up with the rate of data sql server is wanting to write (slower disk).

Pradeep Singh

Adiga One Orange Chip Points: 27284 More actions · Answer 2

You may use the below query to track the progress of backup in SQL Server 2005 and above.

SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],

B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],

B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM

MASTER..SYSDATABASES A, sys.dm_exec_requests B

WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%'

order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

SkyBVI SSCoach Points: 15128 More actions · Answer 3

What i noticed is that there was an application sqlagent alert engine , database msdb, whose wait time was very large, and I thought that was a hindrance to my backup ,so I killed that process, now after deleteing the process, it again come up but wait time was now 0 and backup speeded up( checked that by pradeep's script of tracking progress of backup mentioned above).

In the details for that killed process was:-

EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

I didnt knew what it was doing , but, It helped me to speed up the backup.

Now hte backup completed in 2hrs rather than 6 hrs.

Was my step appropriate??

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Adiga One Orange Chip Points: 27284 More actions · Answer 4

The process that you killed executes a stored procedure which executes the "Demo" alerts to check if they are working. If this process is causing issues, then you can delete all the "Demo:*" alerts from SQL Agent --> Alerts.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

SkyBVI SSCoach Points: 15128 More actions · Answer 5

@ pradeep

I saw those alerts were shrinking databases and logs, I disabled them.

I will monitor again for the backup execution time.

Thanks Pradeep

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Adiga One Orange Chip Points: 27284 More actions · Answer 6

SKYBVI (11/15/2010)
@ pradeep
I saw those alerts were shrinking databases and logs, I disabled them.

They don't shrink the database, instead they monitor the databases for the defined criteria and alert the operators.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga