Release unused space after drop table in SQL 2008 Enterprise Edition

Question

Release unused space after drop table in SQL 2008 Enterprise Edition

AER

SSCrazy

Points: 2700
More actions
October 4, 2011 at 8:49 am

#244144

Hi everybody.
Our Company runs SQL Server 2008 Enterprise Edition. The data file of the database is set with Autogrowth of 10%.
I just dropped a big table that consumed ~40% of database size.
Is there a way to release a space now?
I tried all different scenarios from shrinking unchecking the aut ogrowth and resetting the initial size (that did not work), detaching the databse, bringing it offline and restoring it from the compressed backup. Nothing works.
Any help on that would be greatly appreciated.

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

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

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 1

Is your DB in simple/full/or bulk-logged mode?

If not in simple mode, run a transaction log backup for the database you whacked the data from...you'll need to do this at some point anyway.

I assume you ran something like this? (be aware you will fragment the heck out of the tables in this database by running this)

USE [YourDB]

GO

DBCC SHRINKFILE (N'YourDB' , 1024)

GO

Check your index fragmentation and rebuild/reorg as needed

You may also want to review your thoughts on the autogrow 10% option as there's quite a bit of overhead by constantly having to have SQL grow your files. If you know your DB is usually around 100GB and expect growth over the next few months to be 20GB, set the initial size of the DB to 120+GB and allow the DB to use up the space as needed. Depending on your environment (OLTP/OLAP) you may see a slight performance boost by implementing this...(then again you might not...)

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

AER SSCrazy Points: 2700 More actions · Answer 2

Thanks for your reply Jessy. But, Im sorry this reply is for the 1st grader.

I've been in this business quite a long time. And if you read my post with attention it tells you that shrinking was the 1st thing that I tried.

From looking at a lot of different articles I'm almost positive that there is no way to decrease the Initial size of the database if it is set with the Autogrowth option.

I'm just hoping for a magic from some tough SQL folks.

Thank you.

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 3

Yes I read your post thoroughly, and just wanted to make sure "how" you attempted to remove the space by adding the basic TSQL. I sincerely hope it did not come across as condescending 9and that clearly wasn't my intent)

What I was getting at was the TLOG backup. If you are running the DB in full/bulk mode and you haven't cleared that out, you will not be able to free up the space you're looking to get back.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

AER SSCrazy Points: 2700 More actions · Answer 4

Thanks Jessy, I understand where you are coming from.

But my db is running in a Simple Recovery Mode; I should probably mention that before.

But it still would not shrink (looks like mode is not an issue here)

AER SSCrazy Points: 2700 More actions · Answer 5

So, can anybody help me with this issue???

Or there is no help available fot it???

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

This script should help you shrink the data files. However, shrinking the data files will cause a lot of index fragmentation, so you should defragement or reindex all the tables when you are done with the shrink.

I also recommend that you set the auto file growth to a specific amount, instead of a percent.

Shrink DB File by Increment to Target Free Space

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

Paul Bradshaw SSC Eights! Points: 949 More actions · Answer 7

I don't know if this will help, but this is the script I coded to shrink stubborn log files... this can recover a lot of space:

DECLARE @file sysname

SELECT @file=[name] FROM sys.database_files WHERE type_desc='LOG'

DBCC SHRINKFILE(@file, 1)

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY

DBCC SHRINKFILE(@file, 1)

where "MyDatabase" is the name of your database.

If the log file was created with a specific minimum size, you can't shrink it beyond that size.

I know this isn't a direct answer to your question, but I figure it couldn't hurt.

AER SSCrazy Points: 2700 More actions · Answer 8

Thanks for the script.

Unfortunately this script would not work.

The problem is that I reached the Initial Size that was previously incremented on 10% and I cannoyt shrink even 5mb of this damn thing.

And I need to shrink ~ 50GB of space.

The trick is, how to reset the initial Size (if it is any possible)?

I tried to uncheck the Autogrowth option and reset Initial Size. The system not allowing me to do it.

AER SSCrazy Points: 2700 More actions · Answer 9

So, I'm guessing there is no help available for this issue?

Jared SSC Guru Points: 61793 More actions · Answer 10

Just a thought... You mentioned taking it offline and detaching, etc. Do you have the opportunity to restart SQL Server? Not sure if it will help, but I have seen many positive things happen on a restart of the service.

Thanks,

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 11

Paul Bradshaw (10/4/2011)
I don't know if this will help, but this is the script I coded to shrink stubborn log files... this can recover a lot of space:
DECLARE @file sysname
SELECT @file=[name] FROM sys.database_files WHERE type_desc='LOG'
DBCC SHRINKFILE(@file, 1)
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(@file, 1)
where "MyDatabase" is the name of your database.
If the log file was created with a specific minimum size, you can't shrink it beyond that size.
I know this isn't a direct answer to your question, but I figure it couldn't hurt.

WITH TRUNCATE_ONLY is not supported in 2008.

Thanks,

Jared

Jared
CE - Microsoft

AER SSCrazy Points: 2700 More actions · Answer 12

This is a good thought Jared, I will try it and let you guys know when I will have a chance to restart Sevice on this machine

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 13

I hate to ask this and even remotely offer it as a solution but...

Have you tried restarting the MSSQL service or rebooting the box?

EDIT :: Sorry Jared, I didn't see your post until after I click Post!

Another thought, have you run a CHECKDB using these options? NO_INFOMSGS, ALL_ERRORMSGS

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

What you haven't mentioned is where the free space is. Is it in a data file (if so shrinking or backing up the log is a waste of time)? Or is it in the log file?

If you query for unused space, how much space is listed as free?

p.s. No need for CheckDB and, unless the problem is an active transaction or session holding a latch/lock, a restart won't help. Even if it is one of those, a restart's a pretty invasive fix.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass