Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bazdunk
Starting Member
6 Posts |
 Posted - 2011-05-05 : 09:27:34
|
| I currently look after a VLDB which is 2TB+ in size, the database started its life in SQL 2000 and was ported to 2005 a few years ago, planned upgrade to 2008 is on the cards. The DB acts as a storage container for XML messages in ‘nText’ fields, moving to varchar(max) soon, each table has a clustered index which are stored in its own file group while the TEXTIMAGE (blob data) are stored within a separate filegroup.The main table which has the bulk of the messages is just under 1.5 TB in size with 140 million rows. We have recently introduced an archive process which marks the rows that are available for archiving, another process follows up which copies the records to the archive DB then nulls the blob field, a third process which runs overnight then deletes the row which were previously nulled.What we find is that the space returned to ‘free space’ within the table is not as expected because when we run ‘sp_spaceused’ on each of the tables each morning and last thing at night is that the unused space increasing dramatically, we now have at least 100+ GB of unused space in the main table.The archive process was put in place to reduce the size of the database in order to pass on cost savings to the customer, we therefore ran the DBCC command SHRINKFILE with the EMPTYFILE option, this has worked successfully in the past but took several weeks to complete.The problem that I now have is that the SHRINKFILE does not seem to be doing any positive activity as the unused space amount is not going down as expected, which did happen in the previous successful attempt of SHRINKFILE.How can I determine if the SHRINKFILE command is doing anything positive as I suspect that the archive process which is a new faster process is now outperforming the SHRINKFILE operation and adding more unused space than the SHRINKFILE operation can remove?Any advice on how to get rid of unused space on VLDB’s where blob data has been deleted would be greatly appreciated, all I have ever found is to export the table data then import to a new table but on a table which has 1.5 TB of data and 140 million rows would take more than a few maintenance windows!! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-05 : 09:53:23
|
| Have you tried DBCC CLEANTABLE? |
 |
|
|
bazdunk
Starting Member
6 Posts |
Posted - 2011-05-05 : 09:57:10
|
| Yes, that has been tried with no change to data sizes of unused space |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-05 : 10:05:16
|
| It's possible sp_spaceused is reporting inaccurate page counts. Have you run DBCC UPDATEUSAGE on the affected tables? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-05-05 : 10:31:30
|
| Do the tables that have NTEXT columns have a clustered index?If they do have clustered indexes, have you defragmented or reindexed the clustered indexes?CODO ERGO SUM |
|
|
|
bazdunk
Starting Member
6 Posts |
Posted - 2011-05-05 : 10:56:50
|
| We tried using DBCC UPDATEUSAGE on the DR DB it still gave the same results, having been gathering the stats on the db every morning and evening for the past 2.5 years.The NTEXT columns have a primary key of BIGINT which is a clustered index which is in a different filegroup from the NTEXT data.The tables are reindexed every week whether they need it or not, we usual claim back between 12 and 18 GB of free table space after the reindex job |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-05-05 : 11:20:31
|
| Which file are you running the shrinkfile on? The file with the clustered index, the file with the textimage, or both?Why are you using SHRINKFILE with the EMPTYFILE option? Are you creating a new datafile to move the data to?CODO ERGO SUM |
|
|
|
bazdunk
Starting Member
6 Posts |
Posted - 2011-05-05 : 15:58:19
|
quote:
Originally posted by Michael Valentine Jones
Which file are you running the shrinkfile on? The file with the clustered index, the file with the textimage, or both?Why are you using SHRINKFILE with the EMPTYFILE option? Are you creating a new datafile to move the data to?CODO ERGO SUM
We are running the SHRINKFILE on the textimage file (there are at least 12 textimage files but only 3 index files for the whole DB) We are running the EMPTYFILE option so that we can move the data into the remaining files and then remove the file to save the customer storage costs. |
|
|
|
|
|
|
|
|
VLDB - Emptyfile - Why?