February 19, 2015 at 10:58 am
I am trying to shrink a file in order to get back some free space that can be used by other partitions. Unfortunately, it appears that SQL Server thinks that the partition I am trying to shrink has no free space when in fact there is plenty of free space. This is an ultra large table tied to the partition, so I cannot spend much time using DBCC UPDATEUSAGE to try and fix this because it's a 24x7 system and any locking of the time series table causes the whole company to give me phone calls.
Here are the particulars: I have a partition that has one table. It's got around 93 billion rows in it (don't ask why there are so many rows in one partition). Anyhow, if I run this query to find free space:
SELECT name,
[Currently allocated space] = size/1024.0,
[Available free space] = (Size-UsedSpace)/1024.0
FROM
(
SELECT s.name,
CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)*CONVERT(float,8) AS [UsedSpace],
s.size * CONVERT(float,8) AS [Size]
FROM sys.database_files AS s
WHERE (s.type IN (0,1))
) AS x;
it shows the partition with this:
name Currently allocated spaceAvailable free space
MAIN_DATA2201972.7529.5625
However, I know that the available free space is a lie because this table grows at the rate of 6Gb per day and it would have run out of space a lot time ago if this were true. (There's about 140Gb worth of free space on the server itself). I have a job to capture the size of this table on a daily basis, using sp_spaceused:
rows reserved unused thedate
933390594742201950976 KB126341224 KB02/19/2015
930596421832201937088 KB132480184 KB02/18/2015
927773686952201925112 KB138739328 KB02/17/2015
924965567462201914168 KB144958768 KB02/16/2015
922149344562201904696 KB151153800 KB02/15/2015
919315843062201882504 KB157373832 KB02/14/2015
916498344572201873096 KB163627008 KB02/13/2015
913678964662201865928 KB169890624 KB02/12/2015
910867725852201858824 KB176118864 KB02/11/2015
908062593172201850824 KB182343104 KB02/10/2015
905231233822201844096 KB188665376 KB02/09/2015
902489346842201725576 KB194565368 KB02/08/2015
899605048942201720064 KB200920624 KB02/07/2015
896477837322201716352 KB207844944 KB02/06/2015
909323404032201912128 KB179744992 KB02/05/2015
906343625032202824288 KB187300224 KB02/04/2015
903270605732202799904 KB194048000 KB02/03/2015
900503653732202778400 KB200110248 KB02/02/2015
897710703272202752096 KB206217824 KB02/01/2015
894961325022202729744 KB212196216 KB01/31/2015
892210261982202705040 KB218242128 KB01/30/2015
889456811242202684496 KB224395616 KB01/29/2015
893392691792202780944 KB221274624 KB01/28/2015
Usually they say that sp_spaceused is less accurate than say going with other commands, but in this case, I happen to believe sp_spaceused. Unfortunately, shrinking doesn't work, as it thinks there's nothing to shrink, even though there's 126Gb free as of today. Any help on reclaiming this space would be appreciated. Thanks!
February 19, 2015 at 1:27 pm
so its not clear, do you have a partitioned table?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 19, 2015 at 1:30 pm
no, I don't have a partitioned table. Just one table on a separate partition.
February 19, 2015 at 1:38 pm
cmoy (2/19/2015)
no, I don't have a partitioned table. Just one table on a separate partition.
by partition you mean a windows logical disk??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 19, 2015 at 1:42 pm
one table in one file on a separate windows disk (E: drive )
I'm sorry that I used the word "partition"
February 20, 2015 at 2:36 pm
No
SELECT name,
[Currently allocated space] = size/1024.0,
[Available free space] = (Size-UsedSpace)/1024.0
FROM
(
SELECT s.name,
CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)*CONVERT(float,8) AS [UsedSpace],
s.size * CONVERT(float,8) AS [Size]
FROM sys.database_files AS s
WHERE (s.type IN (0,1))
) AS x;
is not a lie, MAIN_DATA filegroup is almost full. sp_spaceused show free space for whole db, so you may have free space in another filegroup or in a log file. RMB on db->Reports->Standard reports->Disk usage will show you where is this free space.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply