3

I have a 9 GB MS SQL Server 2000 database that has about 1 GB of actual data in it. It is on a shared web host. I need to reduce the size to avoid paying extreme hosting charges.

I have tried using DBCC SHRINKDATABASE and DBCC SHRINKFILE. They both do not shrink the database. (And yes, I know that I shouldn't actually use them.)

DBCC SHRINKDATABASE(db_name) returns:

DbId  FIleId CurrentSize MinimumSize UsedPages EstimatedPages
23    1      1114808     128         1113824   1113824
23    2      63          63          56        56

DBCC SHRINKFILE(1) returns:

DbId  FIleId CurrentSize MinimumSize UsedPages EstimatedPages
23    1      1114808     128         1113824   1113824

In the general properties page, the size is listed as 8,709 MB. Space available is listed as 0 MB. In the files properties page, the main file is listed with an initial size of 8,652 MB.

DBCC SHOWCONTIG(bigTable) returns:

- Pages Scanned................................: 807
- Extents Scanned..............................: 103
- Extent Switches..............................: 102
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 98.06% [101:103]
- Logical Scan Fragmentation ..................: 0.37%
- Extent Scan Fragmentation ...................: 1.94%
- Avg. Bytes Free per Page.....................: 68.5
- Avg. Page Density (full).....................: 99.15%

Running sp_spaceused, I get this:

databasesize: 9131.94 MB
unallocated space: 377.95 MB

Running sp_spaceusused bigTable, I get this:

Rows: 56,095
Reserved: 6,419,736 KB
Data: 252,656 KB
Index_size: 4,640 KB
Unused: 6,162,440 KB

Running sp_spaceusused bigTable2, I get this:

Rows: 2,791
Reserved: 2,362,744 KB
Data: 114,232 KB
Index_size: 200 KB
Unused: 2,248,312 KB

All the rest of the tables are small.

I asked my host to back up the database, drop and recreate it with a lower initial size, and then restore it. They tried that, and this was their answer: "We followed the steps but it did not fix the issue, btw, SQL server had enlarged the database size and its current size is more than 9 GB." (It was 7GB before they attempted this.)

Is it possible my database needs the 8GB of unused space? Assuming that it doesn't, what other options do I have for shrinking it?


I was able to download a copy from my webhost to work with on my local computer. I installed the trial version of SQL Server 2008 and attached the database. I've run ALTER INDEX ALL ON BIGTABLE REORGANIZE, ALTER INDEX ALL ON BIGTABLE REBUILD, and DBCC SHRINKDATABASE. Nothing has changed. I am still getting the same basic responses from the various commands listed above.

3
  • What version of SQL Server is the server running?
    – mrdenny
    Aug 17, 2009 at 20:32
  • can we see the result of: DBCC SHOWCONTIG (BigTable) Aug 18, 2009 at 7:28
  • It is running SQL Server 2000. I've updated the original post to include this information, plus the result of DBCC SHOWCONTIG. Aug 18, 2009 at 13:13

6 Answers 6

2

What is the recovery mode of this database? Before running DBCC_SHRINKDATABASE you must backup your transaction logs. I am guessing the recovery mode is Full? Which is probably best in most cases.

How frequent are your database backups for this DB? and when was the last run transaction log backup.

you can also try DBCC SHRINKDATABASE (DatabaseName, TRUNCATEONLY);

3
  • Recovery model is simple. I don't know how frequent the backups are. I will ask my host to see if they can answer your questions. I've tried all combinations of DBCC SHRINKDATABSE and DBCC SHRINKFILE and none of them do anything for me. Aug 17, 2009 at 15:32
  • well if it is simple then transaction log backups wouldnt help, but a full backup and then a DBCC SHRINKDATABASE should do the trick. sometimes you have to run DBCC SHRINK more than once once proceeding the full backup. Aug 17, 2009 at 17:33
  • I have had success with this scenario by switching the db to FULL recovery mode, then backup/truncating the tlogs and then was able to shrink. When that is done, put the db back in SIMPLE mode.
    – StingyJack
    Jan 21, 2019 at 11:52
2

When you had them drop the database, recreate it at a smaller size, and restore the database nothing should have changed. When you restore a database the first thing that SQL Server does is drop the database that is currently there.

Using the DBCC SHRINKFILE command is the correct method to take. You just need to get SQL to allow you to move some data around.

You may need to rebuild the indexes on the tables in order to compact the data pages in order to allow SQL Server to remove some of the free space from the tables. When you use DBCC SHRINKFILE to shrink the physical file it will move around the data pages within the file, so that all the empty pages are at the end of the file, then it can release the data pages. However if your pages are only partially full then the SQL Server will not combine them to fill them more when using this command.

This can be done however using the reindex commands (the actual commands vary depending on the version of SQL Server in use). This will compact the records some (depending on the fillfactor level specified when the table/indexes were created.

You should also know that SQL Server 2000 and earlier had issues when dealing with text and ntext data types and moving around data within the data pages. These older versions of SQL Server would leave data fragments laying around the data files which you wouldn't be able to clean up as they aren't associated with any rows.

3
  • +1:Now that's what I call an answer! Also showing your age by knowing that little gem in SQL Server 2000 ;-) Aug 17, 2009 at 21:08
  • Thanks. I find it kind of sad that the company that David is paying to manage his servers didn't know that and did it anyway.
    – mrdenny
    Aug 18, 2009 at 0:11
  • I tried DBCC REINDEX, and it did not do anything. The database is running SQL Server 2000, which suggests I'm running into the bug you mentioned. I now have the database on my system. If I get it converted to SQL Server 2008, will I then be able to clean up this extra data? Aug 18, 2009 at 13:15
1

Your problems a most likely related to deleted text or ntext data not freeing up the deleted space - this is the same in all versions of the page.

The rebuilding indexes suggestion was bogus - the SHOWCONTIG clearly and sp_spaceused clearly show that the wasted space isn't in the indexes. The backing up the log suggestion was also bogus - the sp_spaceused shows the extra space is in the data files, not the log.

Do you have any text/ntext columns in the table? If so, the only way to get this space back in 2000 and before is to create a new table and then export/import the data into it. Only in 2005 did I add in LOB compaction to shrink and ALTER INDEX ... REORGANIZE.

Also, beware that there have been two bugs in shrink that would actually expand the amount of space taken by the text/ntext data - make sure you're on SP4.

Thanks

3
  • The table in question is largely an ntext column. I've upgraded the database to SQL Server 2008, but ALTER INDEX ... REORGANIZE doesn't appear to work for me, though it's entirely possible I'm doing something wrong when trying to use it. Aug 19, 2009 at 13:03
  • Do you mean it fails or it doesn't get rid of the extra LOB space? Aug 19, 2009 at 16:00
  • Sorry, "does not work" is pretty vague. I tried it once, and it finished running, but when it was done, the database and table sizes were still the same. I tired it a second time and the computer appeared to be locked up after about 15 minutes. I either ended up having to end it with the task manager, or kill the power on the computer (I can't remember exactly which it was). Aug 20, 2009 at 13:22
0

As a thought, can you take a backup and download it locally, then upgrade it to SQL 2008, and then try to shrink it on your own machine? If it works, then uploaded it back up. Drop the current one and replace it with the newly shrunk one. Also this means you have to upgrade your account to SQL 2008, but atleast you can prove locally if it will work.

2
  • I tried this, and nothing I tried on my local computer appeared to have any affect on the database size. Aug 19, 2009 at 12:59
  • Now that Paul's jumped in go with what he says. Great blog by the way Paul. Aug 20, 2009 at 10:15
0

David,

Please could you post the definitions of the large tables?

<ignore ref="see comments" editor="self"> One common problem resulting in very large, unshrinkable tables is the inappropriate choice of datatypes or data structures. The tables may be using fixed-size datatypes for variable-size data. For example, if you have a field defined as char(100) and many entries are less than the full 100 characters (or even NULL, for that matter), the table will still require all 10 characters of storage. In this case, a better choice would be varchar(100), which will only store the data, and will not pad the data unnecessarily. </ignore>

Some other possibilities are that your hosting provider may be using the TRUNCATEONLY or NOTRUNCATE qualifiers (and thereby not allowing data to be moved to unallocated pages, completely missing the point of your request), or they may be specifying the target percentage incorrectly (the target percentage is the amount of free space relative to the data stored that you want after the shrink operation). In both of these cases, you could simply ask for the SQL commands that they are issuing, or to see a screenshot of the dialog box prior to issuing the shrink directive, if they are using the GUI to shrink the database.

I hope this helps.

Jessica

3
  • The database is for FogBugz and the table is BugEvent. It looks like the columns are all reasonably defined. Most of the data is in a column that is type "ntext". It is full text of incoming emails, including the BASE64 encoded attachments. They ran the backup and restore, but I am the one who ran the SHRINKDATABASE and SHRINKFILE commands. I've updated the question to include the commands I ran and their results. Aug 17, 2009 at 17:52
  • If the problem was a data type issue the space wouldn't be shown as free. Padding values stored in a fixed width field will show as used space, not blank space.
    – mrdenny
    Aug 17, 2009 at 20:33
  • @mrdenny: I stand corrected if SQL 2000 reports the space usage values correctly. I do, however, seem to recall that this was a problem in a past version of SQL Server from a long, long time ago in a database far, far away (maybe 6.x or 7?). I remember spending hours trying to figure out why my database files kept growing even though the space used was reportedly considerably smaller than the allocated size (this was before I fully understood the difference between char and varchar). Anyone else remember this bug? The horrors of SQL 6.x have resulted in partially repressed memories... Aug 21, 2009 at 14:35
0

A reply to an old post, I know, but it may help others: Could it be that you're missing running the "checkpoint" statement? I've experienced that running this command will flush changes to disk, allowing the DBCC shrink* commands to execute as intended whereas they didn't prior to running the checkpoint statement: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/checkpoint-transact-sql?view=sql-server-ver15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .