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.

9 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.

  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.

  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

  4. Sarah

    TL for my database is 500 gb and I need to see the TL in order to see what went wrong in one of our delete commands. Restore of db is at 100% but its still spinning. Since I need the TL, I cant shrink it either, any way of knowing how much longer is left for this process to complete?


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

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s