Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #142 > unrolled thread

Shrink Database problem!!!

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Shrink Database problem!!! Dom <dolivastro@gmail.com> - 2011-04-07 12:23 -0700
    Re: Shrink Database problem!!! Gene Wirchenko <genew@ocis.net> - 2011-04-07 14:07 -0700
    Re: Shrink Database problem!!! Erland Sommarskog <esquel@sommarskog.se> - 2011-04-07 23:32 +0200
    Re: Shrink Database problem!!! ravi p <zygonr@airtel.blackberry.com> - 2011-04-08 06:44 +0000
      Re: Shrink Database problem!!! Dom <dolivastro@gmail.com> - 2011-04-08 07:26 -0700

#142 — Shrink Database problem!!!

At 8:00 am Wed, we started a Shrink Database command (though the SSMS
GUI) on a very large and very active database.  The activity monitor
usually show the process is "suspended" (what does that mean?)
although sometimes when I click "Refresh" it says "runnable".

It is now 3:30 pm Thurs, and the Shrink Database is still going on.
Should I be worried about this?  As I said, it is a large database,
with a fair amount of activity, and we recently added a good amount of
data, and deleted some unnecessary tables.

[toc] | [next] | [standalone]

#144

On Thu, 7 Apr 2011 12:23:49 -0700 (PDT), Dom <dolivastro@gmail.com>
wrote:

>At 8:00 am Wed, we started a Shrink Database command (though the SSMS
>GUI) on a very large and very active database.  The activity monitor
>usually show the process is "suspended" (what does that mean?)
>although sometimes when I click "Refresh" it says "runnable".

     The standard meanings are

suspended: The process is not running but has not been terminated.
runnable: The process is eligible to run.  (If a process were waiting
on I/O, it would not be runnable until the I/O completed.)

>It is now 3:30 pm Thurs, and the Shrink Database is still going on.
>Should I be worried about this?  As I said, it is a large database,
>with a fair amount of activity, and we recently added a good amount of
>data, and deleted some unnecessary tables.

     Based on your first paragraph, it is not doing anything.

     I hope someone else who knows details can advise you on what to
do at this point.

     My best guess is that the process has been suspended by
someone/something and that you should unsuspend it.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#146

Dom (dolivastro@gmail.com) writes:
> At 8:00 am Wed, we started a Shrink Database command (though the SSMS
> GUI) on a very large and very active database.  The activity monitor
> usually show the process is "suspended" (what does that mean?)
> although sometimes when I click "Refresh" it says "runnable".
> 
> It is now 3:30 pm Thurs, and the Shrink Database is still going on.
> Should I be worried about this?  As I said, it is a large database,
> with a fair amount of activity, and we recently added a good amount of
> data, and deleted some unnecessary tables.

Yes, you should be worried. By the mere fact that you started Shrink
Database. It is a command you use be very restrictive with. And I 
definitely advice against running it during office hours.

I would recommend that you cancel the operation, and that you schedule
reindexing of the database for the coming weekend, as shrinking introduces
fragmentation.

See also http://www.karaszi.com/SQLServer/info_dont_shrink.asp

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#148

If the state is in suspended, you need to identify on what is the spid or session waiting on. You can do that by running the dmv

select * from sys.dm_os_wait_stats
order by wait_time_ms desc.

select * from sys.dm_os_waiting_tasks where session_id ='user session no'

Once have the information you can compare the session waits with server waits.

To check how much of shrink of database is complete, you can use the query

Select percent_complete from sys.dm_exec_requests where session_id= 'your session id'

The shrinking of database causes fragemtation , you need to update the statistics of the database once the shrink is complete.

the following command calls sp_updatestats to update all statistics for the database.

 EXEC sp_updatestats


for table or index 

UPDATE STATISTICS table_or_indexed_view_name 
    [ 
        { 
            { index_or_statistics__name }
          | ( { index_or_statistics_name } [ ,...n ] ) 
                }
    ] 
    [    WITH 
        [ 
            [ FULLSCAN ] 
            | SAMPLE number { PERCENT | ROWS } ] 
            | RESAMPLE 
            | <update_stats_stream_option> [ ,...n ]
        ] 
        [ [ , ] [ ALL | COLUMNS | INDEX ] 
        [ [ , ] NORECOMPUTE ]

[toc] | [prev] | [next] | [standalone]

#149

On Apr 8, 2:44 am, ravi p <zyg...@airtel.blackberry.com> wrote:
> If the state is in suspended, you need to identify on what is the spid or session waiting on. You can do that by running the dmv
>
> select * from sys.dm_os_wait_stats
> order by wait_time_ms desc.
>
> select * from sys.dm_os_waiting_tasks where session_id ='user session no'
>
> Once have the information you can compare the session waits with server waits.
>
> To check how much of shrink of database is complete, you can use the query
>
> Select percent_complete from sys.dm_exec_requests where session_id= 'your session id'
>
> The shrinking of database causes fragemtation , you need to update the statistics of the database once the shrink is complete.
>
> the following command calls sp_updatestats to update all statistics for the database.
>
>  EXEC sp_updatestats
>
> for table or index
>
> UPDATE STATISTICS table_or_indexed_view_name
>     [
>         {
>             { index_or_statistics__name }
>           | ( { index_or_statistics_name } [ ,...n ] )
>                 }
>     ]
>     [    WITH
>         [
>             [ FULLSCAN ]
>             | SAMPLE number { PERCENT | ROWS } ]
>             | RESAMPLE
>             | <update_stats_stream_option> [ ,...n ]
>         ]
>         [ [ , ] [ ALL | COLUMNS | INDEX ]
>         [ [ , ] NORECOMPUTE ]

Thanks for everything.  According to what Ravi sent me, the job is 77%
finished.  And it should finish in just 14 more hours.  So I'll just
let it go over the weekend.  And thanks, Erland, for the article.

Dom

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web