Urgent help for shrinking a database

[Dom]

I have a database that is usually shrunk every month. This takes
anywhere from 15 minutes to 1 hour. But this time, it has been
running over 24 hours and I see no evidence (looking at the file
sizes, and the free space on the disk, in windows explorer) that it
is
being shrunk at all, and the log file went from
60M to 659M.

The disk that it sits on has plenty of free space. And No one is
using
the database.

When I look at the activity monitor, I find that the process is
executing DbccFilesCompact, and the status is now suspended. If I
look at the "locks by object" I find that the process ID has several
locks, almost all of the Page type.

I don't want to kill it, because I think I might lose data. How do I
get out of this?

[Bob Barrows]

Post by Dom
I have a database that is usually shrunk every month. This takes
anywhere from 15 minutes to 1 hour. But this time, it has been
running over 24 hours and I see no evidence (looking at the file
sizes, and the free space on the disk, in windows explorer) that it
is
being shrunk at all, and the log file went from
60M to 659M.
The disk that it sits on has plenty of free space. And No one is
using
the database.
When I look at the activity monitor, I find that the process is
executing DbccFilesCompact, and the status is now suspended. If I
look at the "locks by object" I find that the process ID has several
locks, almost all of the Page type.
I don't want to kill it, because I think I might lose data. How do I
get out of this?

You, or someone else named Dom, asked a very similar question back in April
in comp.databases.ms-sqlserver and got several answers, including the
excellent advice to stop shrinking your database, which you seem to have
ignored.

Either cancel the job or wait for it to complete. Your choice. Just be aware
that the cancellation process might take as long as the process took to
reach the point at which you are clicking the Cancel button. Here is ravi's
reply, which you seem to have thought was really helpful:
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 ]

[Dom]

Post by Bob Barrows

I have a database that is usually shrunk every month.  This takes
anywhere from 15 minutes to 1 hour.  But this time, it has been
running over 24 hours and I see no evidence (looking at the file
sizes, and the free space on the disk, in windows explorer) that it
is
being shrunk at all, and the log file went from
60M to 659M.
The disk that it sits on has plenty of free space. And No one is
using
the database.
When I look at the activity monitor, I find that the process is
executing DbccFilesCompact, and the status is now suspended.  If I
look at the "locks by object" I find that the process ID has several
locks, almost all of the Page type.
I don't want to kill it, because I think I might lose data.  How do I
get out of this?

You, or someone else named Dom, asked a very similar question back in April
in comp.databases.ms-sqlserver and got several answers, including the
excellent advice to stop shrinking your database, which you seem to have
ignored.
Either cancel the job or wait for it to complete. Your choice. Just be aware
that the cancellation process might take as long as the process took to
reach the point at which you are clicking the Cancel button. Here is ravi's
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 ]- Hide quoted text -
- Show quoted text -

Thanks for the information. Concerning why the database is shrunk,
that comes from the higher ups. I remember informing them that it was
not a good idea, and I even circulated an article I got from this
group, but no luck. I still have to report that the DB was shrunk. I
have to admit though, the database and the log file gets very large,
which is why they want it shrunk.

According to the query you sent, it is now 70% finished, so I'll give
it another day.