Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Restore Database Sql server 2008

Author  Topic 

loknathmahato
Starting Member

27 Posts

Posted - 2012-08-14 : 21:29:53
Hi Friends,

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.

Somebody help me to resolve this issue.

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-14 : 21:54:50
What other processes are running besides SQL Server?

-Chad
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-15 : 01:56:00
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)?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

loknathmahato
Starting Member

27 Posts

Posted - 2012-08-16 : 10:05:06
quote:
Originally posted by chadmat

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.

Thanks
Go to Top of Page

loknathmahato
Starting Member

27 Posts

Posted - 2012-08-16 : 10:31:38
quote:
Originally posted by jackv

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)?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com



Yes. We have recently upgrades our OS from windows server 2003 32 bit to 2008 64 bit and sql server2008 32 bit to 64 bit.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-16 : 14:56:06
Try the DBCC TRACEON (3004, 3605, -1) - this could you some clues at what point and if any unnecessary delays are occuring , which could be causing such an issue. http://www.sqlserver-dba.com/2011/05/slow-sql-server-backup-and-restore-with-dbcc-traceon-3004-3605-1.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-16 : 17:10:18
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
Go to Top of Page

loknathmahato
Starting Member

27 Posts

Posted - 2012-08-17 : 14:08:13
quote:
Originally posted by chadmat

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.
Go to Top of Page
   

- Advertisement -