| Author |
 Topic  |
|
|
shiyam198
Yak Posting Veteran
81 Posts |
 Posted - 07/16/2012 : 15:37:31
|
Hi,
I am trying to restore a 600 GB database in SQL server 2008 R2. It's on 0% for last 4 hours.
Is there anyway to find out, if the server is doing anything restore at all or should Kill it and start all over again?
Thanks for your time.
Regards,
Shiyam |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 07/16/2012 : 15:53:39
|
If you're restoring it as a new database on a new server, or with new data and log files, then the system is zero-filling those files before it starts restoring the data. You most likely did not have a certain permission set to allow instant file initialization.
There's more information on it here: http://sqlskills.com/BLOGS/PAUL/category/Instant-Initialization.aspx |
 |
|
|
dolphin123
Starting Member
USA
44 Posts |
Posted - 07/17/2012 : 13:57:16
|
Hi,
Thank you. I read through your link. I see instant file initialization cannot be set on the SQL server but on the service account. The SQL server has more than one database and I can't change it.
I just tested this with a smaller database and it was restored successfully.
What does not having "instant file initialization" going to give me?
Is it going to show me 0 % for a long time, becomes 100 % very quickly?
SELECT sysdb.NAME,
dmv.PERCENT_COMPLETE AS [PercentComplete],
dmv.TOTAL_ELAPSED_TIME/60000 AS [Elapsed_Time_in_Minutes],
dmv.ESTIMATED_COMPLETION_TIME/60000 AS [Time_Remaining_in_Minutes],
(SELECT TEXT FROM sys.dm_exec_sql_text(dmv.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES sysdb, sys.dm_exec_requests dmv
WHERE sysdb.DBID=dmv.DATABASE_ID AND dmv.COMMAND LIKE '%restore%'
ORDER BY percent_complete desc,dmv.TOTAL_ELAPSED_TIME/60000 desc
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 07/17/2012 : 14:44:45
|
It has to write zeros for the data and log files before it can start restoring anything, therefore it will write 600 GB twice. Once it writes the data it also has to perform database recovery from the log portion of the database backup. This can also take a fair amount of time while you're waiting at 100% completion.
Best thing to do is let it continue.quote:
I see instant file initialization cannot be set on the SQL server but on the service account. The SQL server has more than one database and I can't change it.
Perform volume maintenance permission is user & server specific, it is completely independent of SQL Server or the number of databases. Just keep it in mind on future SQL Server installations. |
|
|
|
dolphin123
Starting Member
USA
44 Posts |
Posted - 07/17/2012 : 15:14:30
|
| Thanks a lot for your time. |
|
|
| |
Topic |
|
DB Store in 0 percent
