November 15, 2010 at 9:54 am
:exclamation:I created a table to track db usage within a database unfortunately this table filled with 26GB of data and had to be deleted. Since I did not create a clustered index, the table pages were stored as a HEAP. I suspect that once the table was deleted, the space allocated to the pages could not be released. Using a few scripts to view the existing table/index sizes I have found that the total for all objects is less that 5GB but the database is at 33GB with little free space and a 220MB log file. I have tried rebuilding indexes, moving to a new data file and many other operations to no avail. I would love to hear some suggestions on discovering the source of the allocated space and releasing this space. Thanks in advance for your assistance
November 15, 2010 at 12:10 pm
November 15, 2010 at 12:16 pm
Thanks, The issue is that because the space is allocated it will not be move to the end of the file for truncation and since I delted the table, I can not create a CI on the table to claim the space.
November 15, 2010 at 12:49 pm
Might sound odd, but was the table filled with LOB data?
Example datatypes: text, VARCHAR(max), XML...
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2010 at 12:53 pm
Thanks, The issue is that because the space is allocated it will not be move to the end of the file for truncation and since I delted the table, I can not create a CI on the table to claim the space.
After deleting the table I don't understand why you want to create the Index on that may be I got confused with the question.
November 15, 2010 at 1:08 pm
Craig Farrell,
Yes, The table was using nvarchar(max)
pavan_srirangam,
Sorry, I was just stating that I can not create a clustered Index since I no longer have a table, this was my mistake becuase if I have created the CI and truncated the table it would have released the space.
November 15, 2010 at 1:43 pm
I've seen this before. It's not pretty, and you're going to need downtime to fix this if it's production.
For whatever reason, 2k5 and LOBs don't play nicely. It doesn't want to release the extents, it loses mapping once the row that has the pointer to it goes awol, and is in general all sorts of a pain.
The only clear way I've found to consistently recover the space is to clear the LOB component of a row first, and THEN remove the row. Use an INSTEAD OF DELETE trigger to make sure that always happens if you want to pursue that. This isn't a magic bullet but has solved the issue on 90% of the systems I've worked on after we did the next part:
You're going to need to move everything that's left into another database shell entirely. It's the only way to get rid of the dedicated LOB extents that won't clear. So map out your entire schema. Views, Procs, triggers, the works. Push them to the new db. Then do a data push.
I'm hoping someone has a better fix but this is the only way I've found to make sure you recover it in entirety.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2010 at 1:50 pm
Craig Farrell,
In the back of by head I thought that migrating to an empty db would be the last resort but it is looking like this is my only option. As far as MSSQL is concerned, this data is still live and allocated so I can't think of any other way except as you said, migrate to a clean db shell. I am assuming that during the move to the new db that the allocated data has no place to go since it does not exist on the other side. Is this your thought also?
November 15, 2010 at 2:02 pm
paul.olson.dba (11/15/2010)
Craig Farrell,In the back of by head I thought that migrating to an empty db would be the last resort but it is looking like this is my only option. As far as MSSQL is concerned, this data is still live and allocated so I can't think of any other way except as you said, migrate to a clean db shell. I am assuming that during the move to the new db that the allocated data has no place to go since it does not exist on the other side. Is this your thought also?
Pretty much. Basically, you're going to reshell the other database and it's not going to have any page/extent allotments pre-configured. Then you'll introduce your schema, and it will allocate it's first pages. It will then only allocate as you pump in the data after your schema's in play. Since the old LOB data is no longer associated with 'good' data, it won't be transferred via your preferred data transferral method (SSIS, DTS, Cross database T-SQL, what have you).
I *don't* have this from Microsoft but my understanding after some research at the time is that the data, while non-live, is unrecoverable because the system won't release the extents from being ready for re-use. They are especially hairy to try to remove if an extent of non-LOB data is put into the file after the LOB extents.
When you do build the next db, make sure the LOB data resides in its own ndf file. The fix I used above (and I forgot about the ndf possibilities) isn't a sure thing.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2010 at 2:36 pm
wow, so even if you were to drop the table itself in the original database, the space will not be recovered? is that right?
Lowell
November 15, 2010 at 2:41 pm
Lowell (11/15/2010)
wow, so even if you were to drop the table itself in the original database, the space will not be recovered? is that right?
Apparently, no. It's documented here and there as a known issue for 2k5, but not by MS afaik. Even though the LOB extents can be re-used, they won't de-allocate completely. I do not know the mechanics of why and it would take me a lot of research to re-find the articles I used when I puzzled out my final assumptions.
The beginning of this was a 4 TB system about 5 years ago that we constantly used and abused LOBs in that I couldn't get a copy of (with no LOB data for our reporting sides) to behave itself. Mind, the data itself was only 200-300gig. The LOBs were the rest.
I spent weeks fighting and researching with this and never truly got a satisfactory answer, just a lot of maybes, educated guesses, and logical conclusions from those assumptions.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2010 at 3:42 pm
paul.olson.dba (11/15/2010)
I have tried rebuilding indexes,
this is a known issue, it's almost certainly the indexes holding the space. The data pages are not deleted just deallocated, it makes roll back faster\easier. Check the properties of the index(s) what does the ghosted record count show?
There is a cleanup process that cleans the pages but it runs periodically to clean the data.
What version of SQL Server 2005 do you have?
IIRC truncate does not have this issue!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 15, 2010 at 3:51 pm
Since it had been a while since I looked this up and I hate saying "I kinda remember x, y, and z, good luck!"... I did a bit of research.
Of note were two links, actually linking back to here:
http://qa.sqlservercentral.com/Forums/Topic1011086-146-1.aspx#bm1011126
http://qa.sqlservercentral.com/Forums/Topic993883-146-2.aspx#bm1008735
You need to use DBCC CLEANTABLE to deallocate the empty extents. Apparently TRUNCATE also cures the problem now. The post from miksh seems to have the majority of the subsidiary links you'll want at the end of his post (second link).
Nice to know my memory wasn't bad, just they'd finally come up with something to deal with it.
EDIT: Note, this requires the table still exist. Since you've destroyed the linkages entirely, you may still be stuck with the database rebuild method.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply