Percent of advance in Restore database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LuckyMan - 01 Jul 2008 18:04 GMT

I am making a huge restore database with sql server 2000 and I need to know
how to find the percent of advance of the operation. I cannot find it

Reply to this Message

Tibor Karaszi - 01 Jul 2008 18:31 GMT

Use the STATS option of the RESTORE command.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>I am making a huge restore database with sql server 2000 and I need to know
> how to find the percent of advance of the operation. I cannot find it

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 01 Jul 2008 19:44 GMT

Like Tibor said, before you run the restore command, you can change it so
that it includes stats in the output as the restore completes (e.g. If you
run a t-sql restore command from a query window and add STATS = 10 to the
command, it will attempt to report a line item to the results pane eevery
time it hits a 10% threshold, at 10%, 20%, etc. Sometimes this may be a bit
off depending on I/O.).

There are two cases where this doesn't help you:

(a) when you restore through the GUI (don't do this anyway!); and,

(b) if you are trying to determine the progress of a restore you already
started.

Once the restore is started, in SQL Server 2000, I don't know of any way to
determine the percentage complete. In SQL Server 2005, this is available
via a column "percent_complete" in the new DMV sys.dm_exec_requests. This
is only non-zero for certain operations, but IIRC RESTORE is one of them
(also works for BACKUP DATABASE, DBCC SHRINKFILE / SHRINKDATABASE, and
possibly some of the new ALTER INDEX commands as well).

On 7/1/08 1:04 PM, in article
81742B6B-D713-497B-931F-D9835F24251D@microsoft.com, "LuckyMan"

> I am making a huge restore database with sql server 2000 and I need to know
> how to find the percent of advance of the operation. I cannot find it

Reply to this Message

LuckyMan - 02 Jul 2008 15:18 GMT

Someone sent me another brief solution:
RUN COMMAND:
sp_who2 ACTIVE

THEN COMMAND: CHANGING SPID WITH SPID NUMBER

DBCC OUTPUTBUFFER (SPID)

AND YOU WILL SEE A RESULT SET LIKE THIS:
Output Buffer

-----------------------------------------------------------------------------

00000000 04 00 00 5b 00 3a 20 00 79 01 00 00 00 ab 42 00 ...[.: .y....«B.
00000010 8b 0c 00 00 01 00 14 00 36 00 34 00 20 00 70 00 ‹.......6.4. .p.
00000020 65 00 72 00 63 00 65 00 6e 00 74 00 20 00 72 00 e.r.c.e.n.t. .r.
00000030 65 00 73 00 74 00 6f 00 72 00 65 00 64 00 2e 00 e.s.t.o.r.e.d...

> Like Tibor said, before you run the restore command, you can change it so
> that it includes stats in the output as the restore completes (e.g. If you
[quoted text clipped - 22 lines]
> > I am making a huge restore database with sql server 2000 and I need to know
> > how to find the percent of advance of the operation. I cannot find it

Reply to this Message

Tibor Karaszi - 02 Jul 2008 18:23 GMT

That should require that the command was actually executed with the STATS option. And if you did
that, why not look directly in the window from where you executed the command?

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Someone sent me another brief solution:
> RUN COMMAND:
[quoted text clipped - 40 lines]
>> > I am making a huge restore database with sql server 2000 and I need to know
>> > how to find the percent of advance of the operation. I cannot find it

Reply to this Message