question

basit 1 avatar image
basit 1 asked

Database Backup

Hello Every one, I have one Microsoft SQL Server 2008 (RTM) Standard Edition (64-bit). Here we have a database which is on 30 GB. I have created a maintanece plan to take the backup of this database , The backup job run for 5-6 hour sometime it take even more time to complete. Why this is happening and is there any way to reduce this time. Thanks Basit Khan
jobsbackups
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
That's an insanely long time for such a small amount of data. You either have a very intense load on the system, an extremely small amount of memory, really slow disks, or possibly something is configured wrong. I can't say for sure without a lot more information. I would suggest checking sys.dm_exec_requests while your backup is running to see if it is being blocked by another process and to see what resources it is waiting on. [Here is an article][1] on several things you can do to speed up backups. Look through that and see if anything is applicable to you. [1]: http://technet.microsoft.com/en-us/library/ms190954(v=SQL.105).aspx
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

basit 1 avatar image basit 1 commented ·
We have window 2008 Server with Datacenter Edition with 2 processor and 8 GB of RAM,and i have check no process id blocking the backup command
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
And what resources are the backup waiting on? It will show in the DMV sys.dm_exec_requests. Is the maintenance plan doing anything apart from backing up the database?
0 Likes 0 ·
basit 1 avatar image basit 1 commented ·
No, only takeing the backup of the database.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Then you still need to look at the waits to understand what is causing it to run slow. That will help you to then fix it.
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
Do you have your database and backupfile on the same disk? If not: Since you're running RTM, it might also be a good idea to check if backup issues were fixed in a service pack.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

basit 1 avatar image basit 1 commented ·
No, both are on different disk...This is happeing for this server only..
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
WITH CTE AS ( SELECT session_id , DB_NAME(a.database_id) AS databasename , UPPER(command) AS [operation] , CAST(t.text AS XML) AS sql_text , wait_type , CAST(percent_complete AS NUMERIC(4, 1)) AS percent_complete , start_time , DATEADD(ms, estimated_completion_time, GETDATE()) AS end_time FROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS t WHERE percent_complete > 0 ) SELECT @@SERVERNAME AS Servername , databasename , [operation] , percent_complete , DATEDIFF(second, GETDATE(), end_time) AS sec_remaining , wait_type , session_id , sql_text , end_time , start_time , DATEDIFF(second, start_time, GETDATE()) sec_running , DATEDIFF(second, start_time, end_time) AS sec_needed FROM CTE ORDER BY sec_remaining;
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image Wilfred van Dijk commented ·
Run this script during backup and (hopefully) you'll see the answer. Tip: create a job which frequently dumps the output in a table.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.