DB Store in 0 percent - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

DB Store in 0 percent

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2012-07-16 : 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

15732 Posts

Posted - 2012-07-16 : 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
Yak Posting Veteran

84 Posts

Posted - 2012-07-17 : 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

15732 Posts

Posted - 2012-07-17 : 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
Yak Posting Veteran

84 Posts

Posted - 2012-07-17 : 15:14:30

Thanks a lot for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources