Database is 100 percent restored but restore command still running and DB is in restoring mode


100 percent restored. [SQLSTATE 01000]

Processed 52345768 pages for database ‘SQLDBPool’, file ‘SQLDBPool_Data’ on file 1. [SQLSTATE 01000]

Processed 3045777 pages for database ‘SQLDBPool’, file ‘SQLDBPool_Log’ on file 1. [SQLSTATE 01000]

Many times you came across a situation where the Database restore is completed but you can see the restore command is running for a long time and your database status is restoring. We always want to know why database restore is taking so long time.

Let’s first check out what restore is doing internally. Restore session performs below three tasks while you execute the restore command.

  • Data Copy Phase
  • Redo phase
  • Undo phase

The data copy phase involves copying all the data, log, and index pages from the backup of a database to the database files. After the completion of this phase SQL Server reports restore completes 100 percent.

In the Redo phase, all the committed transactions present in the transaction log when the database was backed up are rolled forward. It means all the committed transaction changes applied to the database. Still the database has un-committed transaction so database will be in unusable state.

In the Undo phase, all the uncommitted transactions in the transaction log while the database was backed up are rolled back. If the database is being restored with NORECOVERY, the Undo phase is skipped.

From the above explanation you can understand why the restore command is taking longer time after 100 percent completes.

Advertisements

8 thoughts on “Database is 100 percent restored but restore command still running and DB is in restoring mode

  1. prashant Nathe

    Sir, my transaction log size is 150 in log shipping it restore 100 % but for commuting it will take 30 min, how to reduce this time.

    Reply
  2. Stewart

    In my instance, there shouldn’t be any uncommitted transactions, as all I’ve done with the database is to create it and then restore a .bak into it. Nonetheless, it’s been stuck on 100% for several minutes.

    Reply
  3. Jagadish Babu Budagam

    Actually when we taking backup a check point will occur and commited transactions are flushed and is uncommitted transactions are copied while taking bakcup? and why rolling back and rolling forward happens again while restoring? please clarify

    Reply

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s