February 19, 2009 at 6:09 am
Hi all,
I'm running SQL Server 2000 and am looking after a fairly large database which consists of two files as below:
V10_Data85gb (83gb used, 2gb free)
V10_Data2167.5gb (163gb used, 4.5gb free)
The vast majority of this space is taken up by a single table called tblStoredQuotes.
Running sp_spaceused against this table returns the following:
tblStoredQuotes3103444243592864 KB78839448 KB207256 KB164546160 KB
*this is immediately after having run sp_spaceused @updateusage='TRUE'
As you can see, over 164gb of this space is unused and yet I cannot reclaim it.
- The table already includes a clustered index.
- I've tried defragmenting all indexes on the table and this doesn't help. dbcc showcontig doesn't show high levels of fragmentation either.
- I've run the dbcc cleantable statement but this doesn't seem to have done anything either.
Creating a copy of the table DOES get rid of the unused space but this takes in the region of 14 hours to run since it has to copy over 3 million rows. However, once we went live with the copied table, the amount of unused space continued to creep up once again until we found ourselves in our current situation about a month later...
Hopefully this is enough background info. I have two questions based on this:
1) What do I need to free up this unused space? I understand the difference between unused space and unallocated space and know how to shrink the database file in order to reclaim unallocated space. However, if anyone can think of anything else I can try to get rid of this unused space (i.e. it is already allocated to the table) I would be very grateful to hear from you!
2) When looking at the table and running sp_spaceused every 5 seconds or so to see how things are changing, the amount of unused space is increasing far more quickly than the amount of used space. Why would it do this? I have some idea that it must be to do with how the data is physically being stored but can't understand what would cause this. Any ideas?
This is starting to become quite a serious problem for me as the amount of disk space available on the server is running out rapidly and the client are beginning to lose patience with us so any help/ advice is very much appreciated.
Many thanks in advance,
Dan
February 19, 2009 at 6:53 am
I think you will need to post your table schema as well. and the showcontig results if you have them.
Whats the fillfactor on the clustered index?
Is the row size around 4K so you are limited to 1 row per page?
---------------------------------------------------------------------
February 19, 2009 at 7:19 am
Hi, thanks for the reply.
This is the table layout:
CREATE TABLE [dbo].[tblStoredQuotes] (
[fldEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldQuoteRef] [int] NOT NULL ,
[fldTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldFirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldMiddleName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldLastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldAddress1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldAddress2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldAddress3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldAddress4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldAddress5] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldPostCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldEString] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldIString] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldSynopsis] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldSeedPath] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldGroup] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldEngineOutput] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldQTELEDI] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldTopTen] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldUnderwritingNotes] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldPremiumBreakdown] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldPIN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldExtra] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldWhenTakenUp] [smalldatetime] NULL ,
[fldDateTime] [smalldatetime] NULL ,
[fldBrokerID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldQID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldLST] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldTakenUp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldTrans] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldMTAQuote] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldCoverTo] [smalldatetime] NULL ,
[fldIPromptRef] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldDLLName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldQuoteType] [int] NULL ,
[fldAgent] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldBirthDate] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SOBLOB] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Unfortunately I don't have much say over this as I'm not a developer, just the person who has to try and make sure everything keeps ticking over. Originally there were 9 text columns but 5 of these have been reduced to varchars as I thought this would help (although it doesn't seem to have done).
The fillfactor on the clustered index for this table is set to 90.
I don't have the results of the showcontig to hand but will post when I can get them again.
Thanks,
Dan
February 19, 2009 at 7:42 am
post index definitions as well.
I am wondering about row sizes here. not quite sure why copy to a new table compacts it so much though. (but must be a clue to the problem). I think you should be querying sysindexes to see how many data pages the table is using and compare to no. of rows., try to get a picture of how this data is being stored. Text columns I believe are stored seperate to the main table so investigate that as well.
More suggestions on investigations than solutions at the moment I am afraid.
---------------------------------------------------------------------
February 19, 2009 at 7:56 am
I don't know if this will mean anything to anyone but I ran the following command:
dbcc shrinkdatabase (dbmainV10, TRUNCATEONLY)
go
which returned the following resultset:
dbidfileidcurrentsizeminimumsizeusedpagesestimatedpages
311108799921281066103210661024
3132143343210240002098397620983976
312447128128447128128
I've don't really use shrinkdatabase as I prefer to use shrinkfile and so aren't really familiar with these results. It looks like the number of pages in use is far higher than it ought to be though... ?
February 19, 2009 at 8:37 am
Index definitions as requested:
CREATE UNIQUE CLUSTERED
INDEX [PK_tblStoredQuotes] ON [dbo].[tblStoredQuotes] ([fldQuoteRef])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
CREATE
INDEX [IX_fldEmail] ON [dbo].[tblStoredQuotes] ([fldEmail])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
CREATE
INDEX [IX_fldWhenTakenUp] ON [dbo].[tblStoredQuotes] ([fldWhenTakenUp])
WITH
FILLFACTOR = 90
,DROP_EXISTING
ON [PRIMARY]
I hope this helps someone to figure out what's going on with this!
Thanks,
Dan
February 19, 2009 at 9:25 am
As your table has four columns with the text datatype, you may encounter the situation described by the below MS information article, especially at you indicated that creating a copy of the table does reduced the used space. The article does contain a solution but effectively is copying the table, as you have done.
http://support.microsoft.com/kb/324432
DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns
The DBCC SHRINKFILE and DBCC SHRINKDATABASE commands will not move data and reduce the file size if the EstimatedPages value equals the MinimumSize value that is reported when the command completes. Because of data modification queries on tables with binary large object (BLOB) data types (text, ntext, image), it is possible to have many uniform extents allocated that are sparsely populated with data (for example, only 1 of the 8 pages that the extent controls is allocated), which will cause the DBCC SHRINKFILE and the DBCC SHRINKDATABASE commands to over-estimate the number of EstimatedPages, and the commands do not perform the shrink operations. Typically, in cases where this behavior is experienced, the size of a full database backup is up to 8 times smaller than the sizes of the files of the database after you run a DBCC SHRINKFILE on the database files.
SQL = Scarcely Qualifies as a Language
February 20, 2009 at 5:55 pm
I found early on in SS 7,2000 that copying the database is much faster if you drop the indexes first and then rebuild them after the copy because SS is building the indexes as it copies to the new DB. Since clustered indexes physically reorder the data, then each row requires that action. It is much less time consuming to do it once.
My thought is to drop the indexes, use shrinkdatabase and then recreate the indexes. If they are pointing to empty space, that can cause some bloat as indicated in the quote from MS.
February 23, 2009 at 3:35 am
Hi all,
Thanks for your advice so far.
When attempting to shrink the database using dbcc shrinkdatabase should I be using notruncate or truncateonly? I would have thought that truncateonly would be the best choice but this runs very quickly (a couple of minutes) and doesn't seem to make any difference. When I run it with notruncate it runs for far longer so I haven't actually let it run to completion yet as I'm not sure if it's going to make any more difference than truncateonly did.
Also, does anyone know if a problem such as this is likely to continue if we upgrade to SQL Server 2005?
Many thanks for your help so far,
Dan
February 23, 2009 at 4:29 am
danchard (2/23/2009)
Hi all,Thanks for your advice so far.
When attempting to shrink the database using dbcc shrinkdatabase should I be using notruncate or truncateonly? I would have thought that truncateonly would be the best choice but this runs very quickly (a couple of minutes) and doesn't seem to make any difference. When I run it with notruncate it runs for far longer so I haven't actually let it run to completion yet as I'm not sure if it's going to make any more difference than truncateonly did.
BOL is very good on dbcc shrinkdatabase and dbcc shrinkfile, check it out. truncateonly does not move any data about and just releases unused space back to the OS from the end of the database file to the last allocated extent, If the last allocated extent is at the end of the file you will get no space back. notruncate moves all the data to the front of the file but does not release space back to the OS at the end, so thats the last option you want.
Two things: shrinking a file where data is all moved to the front of the file, shrink does not care what extents it moves where, so your table will be fragmented by the shrink, long term you will be worse off. Also using shrink database is also shrinking your log file unnecessarily, go back to using shrinkfile.
Also, does anyone know if a problem such as this is likely to continue if we upgrade to SQL Server 2005?
Many thanks for your help so far,
Dan
Not sure. I think there may be differences in how SQL 2005 stores text files (or at least options you can specify on how they are stored). worth researching but that a big step to upgrade in the hope of helping this issue.
Your clustered key is an int field. does the value of this key naturally increment for each insert, and is its value updated once inserted? If it is increased in value for each insert and is never then updated you can increase the fillfactor on your clustered index to 100% as the table will not be subject to page splitting.
My gut feeling is your problem is associated with the text fields, which I presume are large, so you may be limited in space you can recover.
---------------------------------------------------------------------
February 24, 2009 at 6:58 am
Read-->Use DBCC CLEANTABLE to Reclaim Unused Space in Your SQL Server Tables
at-->http://www.devx.com/tips/Tip/21762
MJ
February 24, 2009 at 10:01 am
Hi.
Thanks for the suggestion but I'd tried running dbcc cleantable already and it didn't have any affect.
I've now been asked to shift my focus from freeing up the excess space to working out what is causing it? Has anybody else ever looked after a database with a table that took up incredibly high levels of unused space seemingly with every insert?
Any advice is most welcome!
Thanks,
Dan
May 21, 2010 at 9:03 am
[font="Verdana"]
danchard (2/24/2009)
Hi.Thanks for the suggestion but I'd tried running dbcc cleantable already and it didn't have any affect.
I've now been asked to shift my focus from freeing up the excess space to working out what is causing it? Has anybody else ever looked after a database with a table that took up incredibly high levels of unused space seemingly with every insert?
Any advice is most welcome!
Thanks,
Dan
[/font]
[font="Verdana"]I suffered from same and worked out every suggestion put here. but at last it was [highlight]DBCC SHRINKFILE[/highlight] which help me out to reclaim 80 MB space out of 400 Mb unused. 😀
(I had a table with 4 TEXT data type fields. and i tried to get free space by making void a TEXT column to NULL.)[/font]
December 27, 2011 at 4:50 am
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply