Post reply

SHRINKDATABASE Not Working

  • November 8, 2007 at 2:08 am

    #153944

    I havea database of 8GB. The underlying tables, at the moment, only take up 3GB. DBCC SHHRINKDATABASE(MyDB, 10) or DBCC SHRINKDATABASE(Mydb,TRUNCATEONLY) will truncate the log file but have no eefect on the database file.

    Any Suggestions?

  • November 8, 2007 at 4:19 am

    #749390

    Hi,

    Which physical file has freespace??

    If its .mdf (datafile) then

    DBCC shrinkfile('Logical MDF filename',3250)--Where the value should be greater than 3GB

    If its .ldf (log file) then the below command

    1.) Backup log dbname with truncate_only

    2.) DBCC shrinkfile('Logical LDF filename',100)--Where the value should be greater than used value

    How to avoid these situation in future?

    1.) Change the recovery model to simple if its not needed in full recover model

    2.) Refer the link http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx

    Regards..Vidhya Sagar
    SQL-Articles

  • November 8, 2007 at 4:34 am

    #749395

    Hi Vidyha

    I have solved it. What happened was that I created a database with an initial size of 3mb. I imported a lot of data and increased its' size to 8GB. I promptly ran out of space.

    I then detached that database to delete its' log file. Obviously when I reattached it the Initial size was set to 8GB. You cannot SHRINKDATABASE below its' original size.

    I just created a new database an exported the tables, deleted the database and recreated it.

    Thanks for the hint about the recovery model, I will change it now.

    Regards

    David Halliday

  • November 9, 2007 at 3:13 am

    #749768

    Unless someone is very experienced in SQL Server, they should never delete a database log file unless advised to do so by Microsoft support.

    Deleting a log file is a last-ditch attempt to get the database available again, after all other options have been tried and failed. Deleting the log file is a good way to corrupt the database. Running out of disk space for the log is NOT, repeat NOT, a reason to delete the log.

    If a log file does fill the disk, add another log file to the database on another disk. Then resolve the problem that caused the log to grow. Consider putting the database into Simple recovery if you do not need to retain log backups. If you are in Simple mode and the log grows, then you just have to make enough disk space available.

    If a log or data file has grown larger than it needs to be, it can be shrunk using DBCC SHRINKFILE - this can shrink a file to smaller than its original size. However, do not shrink a file if you expect it to grow again within 3 months. If you do repeatedly shrink and grow a file you will get NTFS fragmentation that will harm your performance and can only be cured by a disk defrag.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • November 9, 2007 at 6:15 am

    #749807

    I agree, removing log files is irresponsible and flies in the face of what a DBA stands for. And why the obsession to shrink files, do you actually know what happens to the internal structure of a mdf ( or ndf ) when you shrink it?

    A database needs working space to perform optimally.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • November 13, 2007 at 1:20 am

    #750511

    Shuffle, shuffle, shuffle the pages... I wrote a post about this, after encountering way too many people shrinking databases for no good reason. (http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]) I've had an uphill battle with my colleagues about shrinking production databases.

    Why does 100GB free on the disk look better than 10GB free on the disk and 90GB free inside the DB file? There's nothing else on the drive....

    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

  • November 13, 2007 at 4:00 am

    #750548

    Gail

    Thank you very much for your comments. you are preaching to the converted.

    This however is the very start of the development system and I only have 10 GB on the disk

    Regards

    David

Viewing 7 posts - 1 through 6 (of 6 total)

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