Re: Disaster freeing space in 200GB database

From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 01/18/05

  • Next message: Andrew J. Kelly: "Re: Backup location" 
    Date: Tue, 18 Jan 2005 16:44:28 -0500

    Ouch. Lets see exactly what went wrong and how to deal with this.

    Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
    You may be confused about the Enterprise Manager display. Use the TASKPAD
    view to get a good look at the overall space and the space used. If you do
    have a lot of free data and log space, then you can proceed to recapture
    that empty space.

    DBCC SHRINKDATABASE is probably not the correct command. I strongly prefer
    DBCC SHRINKFILE since it gives me a finer degree of control. It sounds like
    your log file may not be able to accommodate what you are trying to do so
    lets do it in smaller increments. Run DBCC SHRINKFILE
    (MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller than
    your existing file size. You will have to run it several times, but
    eventually you will get more space.

    If you want to empty a large table, try the TRUNCATE TABLE command. It
    doesn't take up much log space at all. Delete does take up log space.

    These suggestions should get you started. Feel free to ask back for more
    help if you run into more problems. 

    -- 
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert Chapman" <RobertChapman@discussions.microsoft.com> wrote in message
news:5440B4DA-8FF1-4DF7-88B2-B60C6B174668@microsoft.com...
> Hi,
>
> I have been developing a database which a couple of days ago was about
> 180GB, which almost filled up the 200GB hard drive.
>
> In order to free space I used DBCC ShrinkDatabase(logfilename,1) on the
log
> file which was 20GB but this then ADDED 20GB to the database (I understand
> now that you shouldn't try to shrink the log this much).  Using shrink
> database on the data file didn't seem to work - kept running and running
with
> the system idle so I eventually stopped it.  I then attached a 80GB
hardrive
> and added a new datafile to allow the database more temporary room.
>
> Next I tried the DELETE command on a number of tables no longer required
to
> have data in them (I need the tables to still exist but be empty for now
so I
> didn't use DROP) but this then added to the database once again and filled
> the entire new 80GB drive.
>
> I now have 280GB full and no idea how to reduce it in size.  I have read
> everything in Books Online and read many posts and articles on the net
> without really having an idea of what to try next.  Unfortunately I don't
> have any room at all elsewhere to shift parts of the database around.
>
> The transaction log is only 1.7GB and was before the DELETEs as well.
Given
> that DELETEs should swell the log what has happened here? - log stayed the
> same size, data file increased by the amount of data deleted (80GB).  What
is
> filling up the data file?  If it is any help, I have specific tables which
> are now empty of records which are where all the free space should be.
There
> might be some way to shift these to a small database (since empty tables
> shouldn't consume much memory) and then free up the space?  Not sure if
this
> would transfer the huge files across or if it would help anyway with
> shrinking the database...
>
> Appreciate any help at all!
>
> TIA,
>
> Rob
  • Next message: Andrew J. Kelly: "Re: Backup location"

    Relevant Pages

    • Re: Disaster freeing space in 200GB database
      ... If you do have a lot of free data and log space, then you can proceed to recapture that empty space. ... I strongly prefer DBCC SHRINKFILE since it gives me a finer degree of control. ... Senior Database Administrator ... Using shrink> database on the data file didn't seem to work - kept running and running with> the system idle so I eventually stopped it. ...
      (microsoft.public.sqlserver.server)
    • Re: DBCC DBREINDEX and FillFactor
      ... > My database has one data file only, and the data file was 900M of data ... > it does not have to expand in a while; I ran DBCC ... The SHRINKDATABASE by default will leave 10% of your database as free space. ...
      (microsoft.public.sqlserver.server)
    • Re: How do I shrink my logfile?
      ... I followed the instructions in the link but when I run DBCC ... checked sysfiles and, true, it is not there. ... Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE ... I set my database option to AutoShrink. ...
      (microsoft.public.sqlserver.server)
    • Re: Andrew J. Kelly please
      ... Im not Andrew Kelly, but up until he replies, take a loot at DBCC Shrinkfile ... > My database has one data file only, and the data file was 900M of data ... I would like to somehow get some free space back to OS (leaving ...
      (microsoft.public.sqlserver.server)
    • Andrew J. Kelly please
      ... My database has one data file only, and the data file was 900M of data after ... I ran DBCC SHRINKDATABASE. ... the database from EM, looks like 50% free space. ...
      (microsoft.public.sqlserver.server)