Disaster freeing space in 200GB database

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
Robert Chapman wrote:
> 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
You could have empty space in the files. SQL Server does not attempt to
release unused space in data or log files. Run sp_spaceused on the
database and see what space is unused. You may have more space than you
think. Then I would set an upper limit for the data file on the 200GB
drive so it does not attempt to grow beyond the drive limits.
David Gugick
Imceda Software
www.imceda.com
|||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
|||Sounds like you have a bunch of empty text pages. Did your tables have
text/image columns in them? There's a bug in one version of shrink (fixed in
SP3) that can cause a shrink to allocate more text space - this would
account for the db growth you saw when you ran shrink on the database.
Deletes can't take more space in the data file. I suggest you contact PSS to
help you remedy this as, from what you've described, there's something
strange going on.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OTULvWa$EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Robert Chapman wrote:
> You could have empty space in the files. SQL Server does not attempt to
> release unused space in data or log files. Run sp_spaceused on the
> database and see what space is unused. You may have more space than you
> think. Then I would set an upper limit for the data file on the 200GB
> drive so it does not attempt to grow beyond the drive limits.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||And if you ever need to recapture so much space again in the future, you
might want to try, during off-hours of course, temporarily setting the
RECOVERY MODE to SIMPLE, do your work, then set it back to FULL RECOVERY,
and make an immediate FULL DATABASE backup.
Sincerely,
Anthony Thomas

"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:u5Y5jba$EHA.2540@.TK2MSFTNGP09.phx.gbl...
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
|||Hi Geoff,
Thanks for your suggestions (and everyone else's too).
I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
Database size Unallocated space
257672.06 MB224.60 MB
Reserved Data Space Indexes
Unused
261840344 KB240624184 KB20345472 KB 870688 KB
Current Size Used pages
Estimated pages
839729984128 9729824 9729824
So hasn't exposed any free space however about half of my tables (all the
unindexed ones) are empty so there should be plenty of unused space.
I have tried to run shrinkfile on the data file on the 2nd (80GB) hard drive
which seemed to complete saying 1 row affected and didn't reduce the size of
the file. I am going to try it again with a different value.
I have downloaded Service Pack 3 and updated but this hasn't helped. Paul,
who is PSS?
Hmm, getting a bit desperate here as very limited time to sort this out.
Need to get some idea if I'll have to bail out of this by creating a new
database elsewhere and copying tables over bit by bit (not much storage space
available) and whetehr that has a chance of sorting the problem. Another
possibility is to somehow detach the second data file that sits on the new
80GB drive as I didn't add any fresh data to that, it has simply been filled
up somehow with the results/logs (though doesn't appear as a log of course)
of my table DELETEs, is there any chance of that working?
Appreciate any more ideas if there are any out there?
Thanks,
Rob
"Geoff N. Hiten" wrote:

> 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
>
|||PSS is Microsoft Product Support Services. They offer fee-based and
subscription based support. I believe the current per-incident support
charge is $250. They take credit cards.
http://support.microsoft.com
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:49AB9EEE-C51A-4BED-84BA-8364DBBE538F@.microsoft.com...
> Hi Geoff,
> Thanks for your suggestions (and everyone else's too).
> I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
> Database size Unallocated space
> 257672.06 MB 224.60 MB
> Reserved Data Space Indexes
> Unused
> 261840344 KB 240624184 KB 20345472 KB 870688 KB
> Current Size Used pages
> Estimated pages
> 8 3 9729984 128 9729824 9729824
> So hasn't exposed any free space however about half of my tables (all the
> unindexed ones) are empty so there should be plenty of unused space.
> I have tried to run shrinkfile on the data file on the 2nd (80GB) hard
drive
> which seemed to complete saying 1 row affected and didn't reduce the size
of
> the file. I am going to try it again with a different value.
> I have downloaded Service Pack 3 and updated but this hasn't helped.
Paul,
> who is PSS?
> Hmm, getting a bit desperate here as very limited time to sort this out.
> Need to get some idea if I'll have to bail out of this by creating a new
> database elsewhere and copying tables over bit by bit (not much storage
space
> available) and whetehr that has a chance of sorting the problem. Another
> possibility is to somehow detach the second data file that sits on the new
> 80GB drive as I didn't add any fresh data to that, it has simply been
filled
> up somehow with the results/logs (though doesn't appear as a log of
course)[vbcol=seagreen]
> of my table DELETEs, is there any chance of that working?
> Appreciate any more ideas if there are any out there?
> Thanks,
> Rob
> "Geoff N. Hiten" wrote:
TASKPAD[vbcol=seagreen]
do[vbcol=seagreen]
prefer[vbcol=seagreen]
like[vbcol=seagreen]
so[vbcol=seagreen]
than[vbcol=seagreen]
more
>
|||I don't understand this line:
"So hasn't exposed any free space however about half of my tables (all the
unindexed ones) are empty so there should be plenty of unused space."
Who cares about the empty ones? It's the full ones that you need to be
worried about.
Could you post the results of the following query? You can mask the names,
but put some other indicated in there so we have a reference that you can
translate on your side.
Here's the query:
SELECT objname = OBJECT_NAME(id), indname = [name], indid, groupid, keycnt,
maxirow
,minlen, xmaxlen, dpages, reserved, used, rowcnt, rowmodctr, pgmodctr
FROM dbo.sysindexes
WHERE indid IN(0, 1, 255)
AND id > 1000
ORDER BY reserved DESC
This will tell you which indexes have been reserved the most database pages.
INDID 0 are the data pages that use heap indexes--tables without clustered
indexes defined. INDID 1 are the datapages that use clustered indexes.
INDID 255 are the data pages allocated for BLOB/CLOB data.
I've ordered this by reserved. This will show you which tables have the
bulk of the database usage. However, take a look at the used attribute.
This will show you how many data and index pages have been allocated to this
table, sans BLOB/CLOB data. If reserved is high but used is log, you have
space that you can reclaim. However, given the output of the sp_spaceused
you provided earlier, not only do you have reserved space, 250 GB, you have
high used space, 230 GB.
Now, if you have the defaults for AUTOGROW set to 10% increments, or
increased it to 20%, and, if you were almost out of space when you added the
secondary data file, the first AUTOGROW allocation could have added anywhere
from 25 to 50 GB of new unused data and index pages. Given you have about
20 GB of unused space, I suspect this is what happened.
When was the last time you did a database reorg in with the maintenance plan
wizard or buy rebuilding all of the clustered indexes? If you use the
wizard, you can also have it move all the pages to the beginning of the
file. This would let you shrink the file more easily, which is what I
suspect was happening when you attempted the shrink operation before. It
has to move pages before it can shrink and 250 GB of data just doesn't move
that quickly.
Sincerely,
Anthony Thomas

"Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
news:49AB9EEE-C51A-4BED-84BA-8364DBBE538F@.microsoft.com...
Hi Geoff,
Thanks for your suggestions (and everyone else's too).
I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
Database size Unallocated space
257672.06 MB 224.60 MB
Reserved Data Space Indexes
Unused
261840344 KB 240624184 KB 20345472 KB 870688 KB
Current Size Used pages
Estimated pages
8 3 9729984 128 9729824 9729824
So hasn't exposed any free space however about half of my tables (all the
unindexed ones) are empty so there should be plenty of unused space.
I have tried to run shrinkfile on the data file on the 2nd (80GB) hard drive
which seemed to complete saying 1 row affected and didn't reduce the size of
the file. I am going to try it again with a different value.
I have downloaded Service Pack 3 and updated but this hasn't helped. Paul,
who is PSS?
Hmm, getting a bit desperate here as very limited time to sort this out.
Need to get some idea if I'll have to bail out of this by creating a new
database elsewhere and copying tables over bit by bit (not much storage
space
available) and whetehr that has a chance of sorting the problem. Another
possibility is to somehow detach the second data file that sits on the new
80GB drive as I didn't add any fresh data to that, it has simply been filled
up somehow with the results/logs (though doesn't appear as a log of course)
of my table DELETEs, is there any chance of that working?
Appreciate any more ideas if there are any out there?
Thanks,
Rob
"Geoff N. Hiten" wrote:

> 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
>
|||Hi Anthony,
Thanks a whole lot for your post, it led to a solution. What I found is
that the problem wasn't the DELETEs run but rather a table that had swelled
to almost 100GB! Not quite sure how it occured but there it was sitting at
the top of the list in the query you gave me with rather an unrealistic
number of rows. A quick TRUNCATE TABLE solved the problem and I have used
DBCC SHRINKFILE to return things to order.
Many thanks for your help!
Rob
"Anthony Thomas" wrote:

> I don't understand this line:
> "So hasn't exposed any free space however about half of my tables (all the
> unindexed ones) are empty so there should be plenty of unused space."
> Who cares about the empty ones? It's the full ones that you need to be
> worried about.
> Could you post the results of the following query? You can mask the names,
> but put some other indicated in there so we have a reference that you can
> translate on your side.
> Here's the query:
> SELECT objname = OBJECT_NAME(id), indname = [name], indid, groupid, keycnt,
> maxirow
> ,minlen, xmaxlen, dpages, reserved, used, rowcnt, rowmodctr, pgmodctr
> FROM dbo.sysindexes
> WHERE indid IN(0, 1, 255)
> AND id > 1000
> ORDER BY reserved DESC
> This will tell you which indexes have been reserved the most database pages.
> INDID 0 are the data pages that use heap indexes--tables without clustered
> indexes defined. INDID 1 are the datapages that use clustered indexes.
> INDID 255 are the data pages allocated for BLOB/CLOB data.
> I've ordered this by reserved. This will show you which tables have the
> bulk of the database usage. However, take a look at the used attribute.
> This will show you how many data and index pages have been allocated to this
> table, sans BLOB/CLOB data. If reserved is high but used is log, you have
> space that you can reclaim. However, given the output of the sp_spaceused
> you provided earlier, not only do you have reserved space, 250 GB, you have
> high used space, 230 GB.
> Now, if you have the defaults for AUTOGROW set to 10% increments, or
> increased it to 20%, and, if you were almost out of space when you added the
> secondary data file, the first AUTOGROW allocation could have added anywhere
> from 25 to 50 GB of new unused data and index pages. Given you have about
> 20 GB of unused space, I suspect this is what happened.
> When was the last time you did a database reorg in with the maintenance plan
> wizard or buy rebuilding all of the clustered indexes? If you use the
> wizard, you can also have it move all the pages to the beginning of the
> file. This would let you shrink the file more easily, which is what I
> suspect was happening when you attempted the shrink operation before. It
> has to move pages before it can shrink and 250 GB of data just doesn't move
> that quickly.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Robert Chapman" <RobertChapman@.discussions.microsoft.com> wrote in message
> news:49AB9EEE-C51A-4BED-84BA-8364DBBE538F@.microsoft.com...
> Hi Geoff,
> Thanks for your suggestions (and everyone else's too).
> I have run EXEC sp_spaceused @.updateusage = 'TRUE' with these results:
> Database size Unallocated space
> 257672.06 MB 224.60 MB
> Reserved Data Space Indexes
> Unused
> 261840344 KB 240624184 KB 20345472 KB 870688 KB
> Current Size Used pages
> Estimated pages
> 8 3 9729984 128 9729824 9729824
> So hasn't exposed any free space however about half of my tables (all the
> unindexed ones) are empty so there should be plenty of unused space.
> I have tried to run shrinkfile on the data file on the 2nd (80GB) hard drive
> which seemed to complete saying 1 row affected and didn't reduce the size of
> the file. I am going to try it again with a different value.
> I have downloaded Service Pack 3 and updated but this hasn't helped. Paul,
> who is PSS?
> Hmm, getting a bit desperate here as very limited time to sort this out.
> Need to get some idea if I'll have to bail out of this by creating a new
> database elsewhere and copying tables over bit by bit (not much storage
> space
> available) and whetehr that has a chance of sorting the problem. Another
> possibility is to somehow detach the second data file that sits on the new
> 80GB drive as I didn't add any fresh data to that, it has simply been filled
> up somehow with the results/logs (though doesn't appear as a log of course)
> of my table DELETEs, is there any chance of that working?
> Appreciate any more ideas if there are any out there?
> Thanks,
> Rob
> "Geoff N. Hiten" wrote:
> do
> prefer
> like
> than
>
>