Post reply

Release unused space after drop table in SQL 2008 Enterprise Edition

  • 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.

  • October 4, 2011 at 9:10 am

    #1390195

    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

  • October 4, 2011 at 9:20 am

    #1390207

    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.

  • October 4, 2011 at 9:36 am

    #1390218

    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

  • October 4, 2011 at 9:41 am

    #1390222

    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)

  • October 4, 2011 at 3:05 pm

    #1390416

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

    Or there is no help available fot it???

  • October 4, 2011 at 3:23 pm

    #1390423

    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

  • October 4, 2011 at 3:34 pm

    #1390427

    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.

  • October 4, 2011 at 3:38 pm

    #1390430

    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.

  • October 6, 2011 at 7:52 am

    #1391201

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

  • October 6, 2011 at 8:07 am

    #1391209

    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

  • October 6, 2011 at 8:10 am

    #1391213

    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

  • October 6, 2011 at 8:10 am

    #1391214

    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

  • October 6, 2011 at 8:28 am

    #1391225

    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

  • October 6, 2011 at 8:35 am

    #1391233

    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

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

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