April 28, 2011 at 10:52 pm
Hi,
I was trying to restore the database which usually took 40 minitues maximum and minimum 30 minitues ,but today when i try to restore the database it took 90 minitues to completed 10% of restoration.
when i check the sys.dm_exec_requests i found it was waiting for for the backupthread.
Sid status command waittype waittime.
55 suspendedRESTORE DATABASEBACKUPTHREAD1186781
Sysprocess view as below
55794000x00881340093BACKUPTHREAD
55249600x008715BACKUPIO
can anyone explain the reason ,solution and where i can more information about it ?
Thanks in Advance.
April 29, 2011 at 9:56 am
I came across some useful links. Please see below.
http://itknowledgeexchange.techtarget.com/sql-server/what-is-the-backupthread-wait-type/
M&M
April 29, 2011 at 11:53 pm
Backup file drive and Restore File Drive is same ?
what is the size of Backup file now ?
Is there any Load on Database at the time of Restore ?
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 30, 2011 at 3:20 am
Syed Jahanzaib Bin hassan (4/29/2011)
what is the size of Backup file now ?
Is there any Load on Database at the time of Restore ?
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
Backup file drive and Restore File Drive is same ?
No
what is the size of Backup file now ?
30 GB
Is there any Load on Database at the time of Restore ?
It will be great help if you tell me how to identify the load of sql server .
April 30, 2011 at 11:20 am
This could be due to the resource contention on the server or the network contention.
Start a profiler trace and a perfmon trace to find the root cause.
This could also be due to the increase in the size of the Backup Size.
Thank You,
Best Regards,
SQLBuddy
May 1, 2011 at 1:54 am
SELECT ST.TEXT,SP.* FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
WHERE SP.STATUS != 'SLEEPING'
ORDER BY SP.CPU DESC
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 1, 2011 at 8:52 pm
i was trying to restore the database which usually took 40 minitues maximum and minimum 30 minitues ,but today when i try to restore the database it took 90 minitues to completed 10% of restoration.
Did your Restore finished Successfully?
Also, Can you tell me which Edition of sql server you are using if it is Enterprise Edition (Try enabling Instant file initialisation feature). It can be enabled by adding your sql server service account into a local security (Perform volume maintenance Tasks) at windows level.
We can avail this feature in Standard Editions also but there is a specific tweaking you need to do.
Check out this for more info http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/
This will ensure that during restoration your data files get intialized fast. These reduces the Database restoration time significantly.
Note: This feature is Applicable for data files only..
Also, take a look through perfmon counters that how busy your Disks are during restoration.
Regards,,
May 2, 2011 at 11:23 pm
During backup was there any huge DML task going on.
during recovery,When the waittype is suspended, this indicates that, data is being spilled to tempdb during recovery. Hence resulting in a slow process.
You can watch the Series 2 or Series 3 webcasts which deal with such issues in
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply