Re: DBCC shrinkfile

From: Paul S Randal [MS] (prandal_at_online.microsoft.com)
Date: 03/30/04 

Date: Tue, 30 Mar 2004 09:50:52 -0800

Yes. It issues a lot of IO and takes short term X page locks. In internal
tests we've seen up to 20% drop in transaction throughput, depending on the
exact workload and hardware configuration. This is unavoidable due to the
operations shrink has to perform.

What proportion of the database size is free-space? Consider not doing the
shrink unless you're really desperate for the disk space or you *know* the
database size won't grow again. If you shrink, the odds are that the
database will have to grow again anyway. As always, depends on your exact
workload etc etc

It is always 'safe' to do a shrink.

Regards 

-- 
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@hotmail.com> wrote in message
news:uBcDfwnFEHA.2876@TK2MSFTNGP09.phx.gbl...
> would DBCC shrinkfile cause any blocking or any hit to an OLTP environment
> while its running. Ive got a lot of extra space on some data files that I
> want to shrink and was wondering if its safe to do it during our peak
> hours... What does it do internally ? Any locking ,etc..Using SQL 2000
>
>

Relevant Pages

  • Re: How do I reduce the physical size of the file?
    ... Switch to the database we are attempting to shrink the logs for. ... DECLARE @LogicalFileName SYSNAME, ... Modified the inner loop so it tested the dx time so long overruns did not ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction log keeps growing
    ... Introduced in SQL Server 7.0 was the ability automatically grow and to ... shrink the physical size of database data and transaction log files. ...
    (comp.databases.ms-sqlserver)
  • Re: How to PURGE a transaction log?
    ... This doesn't shrink the actual file size. ... Make sure you understand backup and restore architecture for 6.5 *really* ... make sure you understand the database architecture regarding database ... doing dummy transactions and DUMP TRANSACTION until the log have moved ...
    (microsoft.public.sqlserver.setup)
  • Re: Shrinking the MDG while Maintaining Indexes
    ... A Reindex or IndexDefrag will essentially rebuild the indexes (or the table ... of data it will probably not grow again unless you shrink it. ... Fragmentation and I will explain all this and more. ... that it seemed like the database grew each night. ...
    (microsoft.public.sqlserver.server)
  • Re: PRIMARY filegroup is full - no its not!
    ... sp_helpdb should report the increased size immediately after the ALTER. ... > auto-shrinking, BUT auto shrink is not turned on. ... > large file size after I expand the file using "ALTER DATABASE DataLoad ... > data files by subtracting the reserved space reported by sp_spaceused ...
    (microsoft.public.sqlserver.server)