Path: csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!border3.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!border4.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!npeer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!post01.iad.highwinds-media.com!newsfe15.iad.POSTED!5788d997!not-for-mail
From: ravi p <zygonr@airtel.blackberry.com>
Subject: Re: Shrink Database problem!!!
Newsgroups: comp.databases.ms-sqlserver
Reply-To: dolivastro@gmail.com
References: <326ccf75-9f52-4e98-8b88-deb9f63fd7d8@s9g2000yqm.googlegroups.com>
Message-ID: <20114824351usenet@terrranews.com>
Lines: 40
X-Complaints-To: abuse@teranews.com
NNTP-Posting-Date: Fri, 08 Apr 2011 06:44:04 UTC
Organization: TeraNews.com
Date: Fri, 08 Apr 2011 06:44:04 GMT
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver: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 ]