Prev: Proxy Credentials Frustration
Next: Newbie - Moving a table from one database to another
From: LuckyMan on 1 Jul 2008 13:04
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
From: Tibor Karaszi on 1 Jul 2008 13:31
Use the STATS option of the RESTORE command.

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


"LuckyMan" <LuckyMan(a)discussions.microsoft.com> wrote in message
news:81742B6B-D713-497B-931F-D9835F24251D(a)microsoft.com...
>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

From: Aaron Bertrand [SQL Server MVP] on 1 Jul 2008 14:44
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(a)microsoft.com, "LuckyMan"
<LuckyMan(a)discussions.microsoft.com> wrote:

> 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

From: LuckyMan on 2 Jul 2008 10:18
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...





"Aaron Bertrand [SQL Server MVP]" wrote:

> 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(a)microsoft.com, "LuckyMan"
> <LuckyMan(a)discussions.microsoft.com> wrote:
>
> > 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
>
>
From: Tibor Karaszi on 2 Jul 2008 13:23
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?

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


"LuckyMan" <LuckyMan(a)discussions.microsoft.com> wrote in message
news:F7CD37A0-390E-4F69-B5B6-ABB245271C32(a)microsoft.com...
> 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...
>
>
>
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>> 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(a)microsoft.com, "LuckyMan"
>> <LuckyMan(a)discussions.microsoft.com> wrote:
>>
>> > 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
>>
>>

 | 
Pages: 1
Prev: Proxy Credentials Frustration
Next: Newbie - Moving a table from one database to another