All disk space used, after deleting rows the table has 6GB "unused"space but I cannot release it or shrink the mdf.

Question

All disk space used, after deleting rows the table has 6GB "unused"space but I cannot release it or shrink the mdf.

syen.j

Mr or Mrs. 500

Points: 594
More actions
February 9, 2017 at 2:45 am

#327091

Dear,
We have a table with 100 million rows and 1 clustered index (primary key).
The hard disk is full.
We are deleting rows in batches of 1000 each time in the table but this does not decrease the mdf size.
After days of deletion, the "table size report" says the table has 6GB Unused space.
But again the mdf size is not decreasing using shrink methods.
I read this could have to do with the fragmentation on the clustered index.
I did a Reoganise of that index but this has no impact on the space issue.
I cannot do a Rebuild of that index because I don't have enough free disk space to execute that.
So I am fully stuck now.
Any options left?

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 1

Hi,
can you post the size of the different objects types in you database.
Index
Table
etc. pp
And how big is the row count after deleting the rows, maybe there where more insert then deletes in the time?
If you delete such a big count of rows, the file should be able to shrink.

Kind regards,
Andreas

syen.j Mr or Mrs. 500 Points: 594 More actions · Answer 2

The database is not even in use at the moment, we disconnected all clients before starting to delete. No inserts are happening.

tblEvents

Reserved 87GB
Data 59GB
Indexes 12GB
Unused 6.1GB

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Deleting rows doesn't reduce the size of the mdf file. The only operation that can do that is a file shrink. What command are you using to do the shrink? If you're using the GUI, please press the Script button at the top of the Shrink database window. What are the results if you run sp_spaceused against the database. The "Unused" value in the table report refers to the amount of unused space in data pages used by the table, for example if you have wide rows or set a low fill factor on your clustered index. It's not available for other tables to use or to return to the OS with a shrink operation.

John

syen.j Mr or Mrs. 500 Points: 594 More actions · Answer 4

John Mitchell-245523 - Thursday, February 9, 2017 5:03 AM
Deleting rows doesn't reduce the size of the mdf file. The only operation that can do that is a file shrink. What command are you using to do the shrink? If you're using the GUI, please press the Script button at the top of the Shrink database window. What are the results if you run sp_spaceused against the database. The "Unused" value in the table report refers to the amount of unused space in data pages used by the table, for example if you have wide rows or set a low fill factor on your clustered index. It's not available for other tables to use or to return to the OS with a shrink operation.
John

Yes we are using the shrink method in the SQL Server Management Studio, we used shrink file, shrink database, shrink file no truncate... nothing helps.
Wheen deleting more, only the "unused" value goes up.
sp_spareused is not possible to run agains a database only on a table, and it gives the same results as I just mentioned.

tblEvents
Rows 146 million
Reserved 87GB
Data 59GB
Indexes 12GB
Unused 6.1GB

While the rows decrease and unused increases, still nothing is possible to get shrinked.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

syen.j - Thursday, February 9, 2017 5:11 AM
Yes we are using the shrink method in the SQL Server Management Studio, we used shrink file, shrink database, shrink file no truncate... nothing helps.

Please post the command in the way I described.

sp_spareused is not possible to run agains a database only on a table, and it gives the same results as I just mentioned.

Please run EXEC sp_spaceused in the context of the database and post the results. (There should be two result sets.)

John

syen.j Mr or Mrs. 500 Points: 594 More actions · Answer 6

John Mitchell-245523 - Thursday, February 9, 2017 5:23 AM
syen.j - Thursday, February 9, 2017 5:11 AM
Yes we are using the shrink method in the SQL Server Management Studio, we used shrink file, shrink database, shrink file no truncate... nothing helps.
Please post the command in the way I described.
sp_spareused is not possible to run agains a database only on a table, and it gives the same results as I just mentioned.
Please run EXEC sp_spaceused in the context of the database and post the results. (There should be two result sets.)
John

OK these are the result (unable to copy paste directly)

database size: 78GB
Unallocated space: 2.14MB

Reserved 87GB
Data 59GB
Indexes 12GB
Unused 6.1GB

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

And the shrink command?

OK, so your database is full up. Since your table results look very similar to your database results, I'd say that's the only table in the database. Is that right? How many rows have you deleted from the table, and how many have been inserted during the same time? Are there any triggers on the table?

John

syen.j Mr or Mrs. 500 Points: 594 More actions · Answer 8

John Mitchell-245523 - Thursday, February 9, 2017 7:05 AM
And the shrink command?
OK, so your database is full up. Since your table results look very similar to your database results, I'd say that's the only table in the database. Is that right? How many rows have you deleted from the table, and how many have been inserted during the same time? Are there any triggers on the table?
John

Like I mentioned multiple time, we tried all shrink commands, we tried all GUI shrink commands, we are not inserting anything. There or no triggers on the thable. All is static.
This is the only big table, the rest are small tables (4).
We have a lot of experience in shrinking and maintaining SQL, this is not a beginner issue.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 9

syen.j - Thursday, February 9, 2017 2:45 AM
Dear,
We have a table with 100 million rows and 1 clustered index (primary key).
The hard disk is full.
We are deleting rows in batches of 1000 each time in the table but this does not decrease the mdf size.
After days of deletion, the "table size report" says the table has 6GB Unused space.
But again the mdf size is not decreasing using shrink methods.
I read this could have to do with the fragmentation on the clustered index.
I did a Reoganise of that index but this has no impact on the space issue.
I cannot do a Rebuild of that index because I don't have enough free disk space to execute that.
So I am fully stuck now.
Any options left?

can you provide more detail of the particular table, any indexes and column types

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

syen.j Mr or Mrs. 500 Points: 594 More actions · Answer 10

Perry Whittle - Thursday, February 9, 2017 7:22 AM
syen.j - Thursday, February 9, 2017 2:45 AM
Dear,
We have a table with 100 million rows and 1 clustered index (primary key).
The hard disk is full.
We are deleting rows in batches of 1000 each time in the table but this does not decrease the mdf size.
After days of deletion, the "table size report" says the table has 6GB Unused space.
But again the mdf size is not decreasing using shrink methods.
I read this could have to do with the fragmentation on the clustered index.
I did a Reoganise of that index but this has no impact on the space issue.
I cannot do a Rebuild of that index because I don't have enough free disk space to execute that.
So I am fully stuck now.
Any options left?
can you provide more detail of the particular table, any indexes and column types

Below you can find screenshots of the database and table.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 11

rebuild the indexes on the events table and then check the free space in the mdf

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

syen.j Mr or Mrs. 500 Points: 594 More actions · Answer 12

Perry Whittle - Thursday, February 9, 2017 10:03 AM
rebuild the indexes on the events table and then check the free space in the mdf

We cannot rebuild because we are out of space. Rebuilding takes space. Reorganise is possible but it did not fix anything.

Please before posting answers, read the original post please, it mentions we already tried the basic stuff like delete rows, shrink, reorganise and rebuild (impossible). With no result. the database is fully unused and static. We can see the 'table rows' decreasing and the 'unused space' increasing. Size stays the same.

jcourtjrSQL SSC Eights! Points: 865 More actions · Answer 13

try sp_spaceused 'tablename', 'true' for the table that you are deleting from, we ran into this yesterday. For some reason meta data on the table was stopping the shrink and it needed to be updated/refreshed.

Michael L John One Orange Chip Points: 27056 More actions · Answer 14

syen.j - Thursday, February 9, 2017 12:46 PM
Perry Whittle - Thursday, February 9, 2017 10:03 AM
rebuild the indexes on the events table and then check the free space in the mdf
We cannot rebuild because we are out of space. Rebuilding takes space. Reorganise is possible but it did not fix anything.
Please before posting answers, read the original post please, it mentions we already tried the basic stuff like delete rows, shrink, reorganise and rebuild (impossible). With no result. the database is fully unused and static. We can see the 'table rows' decreasing and the 'unused space' increasing. Size stays the same.

Giving people an attitude, more then one time on this thread, is not a very good way to get help.

You said:
Like I mentioned multiple time, we tried all shrink commands, we tried all GUI shrink commands, we are not inserting anything. There or no triggers on the thable. All is static.
This is the only big table, the rest are small tables (4).
We have a lot of experience in shrinking and maintaining SQL, this is not a beginner issue.

Your questions certainly do sound like a beginner issue because you are looking for some magic to fix your issue. There is no magic.

Deleting X number of rows at a time will probably take a lot of time.
One suggestion may be to:
1. Create a view that selects only the records that you want to keep from this table.
2. Use BCP against this view to export the records you want to keep.
3. Drop the table.
4. Shrink the file using this syntax
USE [YourDataBase] GO DBCC SHRINKFILE (N'YourDataBase_DataFile' , SizeYouWant) GO
5. Recreate the table without keys and indexes. You did generate the script to re-create this table and indexes before you dropped it, correct?
6. BCP the data back into the table.
7. Add the primary key
8. Add the remaining indexes

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/