I have SQL Server 2008 64bit with Windows Server 2008 64bit machine.
Total RAM - 16 GB
Sql Server Memory - 12 GB OS Memory - 4 GB
When i am restoring a database of 30 to 35 GB it takes all the resource of the server and the other process get silent due to that i stop the restoring process in middle of execution.
Is this a recent development? Forexample , was it working OK previously? Do you have Instant File Initialization configured on the server - it does in some situations speed up the RESTORE process on the Data files (not Log files)?
What other processes are running besides SQL Server?
-Chad
There are two database in our production server one is production and other is old data backup database. During the weekly maintainance we take the bakup of production database before deleting some old data from it. During the production hour when we try to restore the backup database for extracting some of the old data then the restoring process takes all the resources of the server and process which are running in production database get still for 10 to 15 minuts till the end of restoring process.
Is this a recent development? Forexample , was it working OK previously? Do you have Instant File Initialization configured on the server - it does in some situations speed up the RESTORE process on the Data files (not Log files)?
Hard to say for sure but sounds like IO contention. Check perfmon Physical Disk->avg Disk sec/Read and Avd Disk sec/write during this time.
Do you have data and log on teh same disk? Is the other database on the same disk as the one you are restoring?
-Chad
No Data and log file of production database are in separate Disk. But the back database data file are kept on the same disk where the production database data file located.
Restore Database Sql server 2008
Posted - 08/14/2012 : 21:29:53
