December 9, 2012 at 9:29 pm
Hi every one,
We have database(4.6TB) in SQL SERVER 2008 R2.On weekly bases we take full backup from this database.It used to take 7-8 hours for the full backup.but now It is running for 21 hours and still it is not completed.How can I check what is the problem?
Thanks
December 9, 2012 at 11:06 pm
Do you take tape backup? What are the wait types your backup session is hanging on? Check with your backup team to verify the backup throughput (mb/secs).
December 10, 2012 at 3:39 am
Aspet Golestanian Namagerdi (12/9/2012)
.It used to take 7-8 hours for the full backup.but now It is running for 21 hours and still it is not completed.How can I check what is the problem?
Check the whether database have grown in recent past ?
Are you using same disk/storage?
I/O utlization? but for this you need to have some historical data for this
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 10, 2012 at 7:52 am
Here is the waittypes:
waittime lastwaittype
58608032 ASYNC_IO_COMPLETION
51 BACKUPIO
0 BACKUPBUFFER
December 10, 2012 at 7:53 am
and I am using disk for backup operation.
December 10, 2012 at 8:28 am
local disk or NAS\SAN based disk?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 10, 2012 at 8:30 am
It's SAN bases disk(mount point)
December 12, 2012 at 12:05 am
Check the throughput of the SAN system as well as configuration of HBA queue depth.
See the virtual file stats if there are high IO read and Write stall( sys.dm_virtual_file_stats).
Capture IO, Memory and CPU counters
Check Memory Related Perfmon Counters
SQLServer: Memory Manager\Memory Grants Pending (Consistent higher value than 0-2)
SQLServer: Memory Manager\Memory Grants Outstanding (Consistent higher value, Benchmark)
SQLServer: Buffer Manager\Buffer Hit Cache Ratio (Higher is better, greater than 90% for usually smooth running system)
SQLServer: Buffer Manager\Page Life Expectancy (Consistent lower value than 300 seconds)
Memory: Available Mbytes (Information only)
Memory: Page Faults/sec (Benchmark only)
Memory: Pages/sec (Benchmark only)
Checking Disk Related Perfmon Counters
Average Disk sec/Read (Consistent higher value than 4-8 millisecond is not good)
Average Disk sec/Write (Consistent higher value than 4-8 millisecond is not good)
Average Disk Read/Write Queue Length (Consistent higher value than benchmark is not good)
Regards,
Ramasankar,
MCTS,MCITP Sql server 2008.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply