Reducing allocated space

  • I have taken on the administration and support of a sql server DB using sql server 7.0. The DB was not properly sized by the looks of things and the initial space allocated is shown below:

    data file - 5500MB (unrestricted growth)

    Trans log - 6267MB (unrestricted growth)

    As you can see the trans log was given way too much allocated space to begin with.

    I want to be able to reduce the space allocated to the log. I have truncated it and it is using 13MB of the approx 6GB allocated to it. The server the DB is running on is running low on space and i would like to reclaim this space by reducing the allocated size of the log. Is there a way of doing this and maybe also reducing the allocated data file size. Currently the data file is using approx 2GB of the 5.5GB allocated.

    When i go into DB properties and try to do this it gives me the error saying that the new allocated size must be greater than the current allocation.

    Any help is appreciated

  • First, I would recommend you take a backup of the Database and the Transaction Log. Then, you should be able to run dbcc shrinkdatabase ('YourDB', Target%) and shrink the database successfully. For fastest results, start with the target % at 50 and work your way down until it is where you think it should be.

    With all that said, depending on the processing you are doing, the database may grow to that size again. It got there now and it will probably get there again.

    Hope this helps.

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • One note, if the log was this big initially, then you can't shrink below the initial size.

    That being said, shrink it and see, you may need help and there's a script in this site that helps. The log may not grow this big, I've often seen bad backup plans causing the log to grow unnecessarily.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

  • If the initial file size is so big, you can't shrink it to smaller. If the log file grows so big, you can do the follows:

    1, Back up the database,

    2, Run dbcc shrinkdatabase(dbname, truncateonly)

    3, Change the db option to truncate log on checkpoint for sql7, recovery model to simple

    for sql2k.

    4, Run shrinkdatabase(sbname) for shrinking

    both data and log files, Run shrinkfile(filename) for shrinking the file you want to shrink.

    5, Change db option back.

    Hope this will help.

    Robert

  • Forgot to mention. If the initial log file is so big, you don't want it so big, then you can use detach and attach the log file

    to reduce it.

    Robert

  • try to use ap_attch_single_file_db to get a new log file.

  • try to use sp_attach_single_file_db to get a new log file...

    pardon my terrible typing errors(previous reply)

  • Cheers for all this info guys i am going to try the suggestions today so i will get back to you with what worked.

  • As it didnt matter about the transaction data being lost in this case. I dettached the Database using sp_dettach_db, deleted the log file from within explorer and then ran sp_attach_single_file_db. This created a new log file with an allocated size of 1MB instead of 6GB which it was before. Thanks for all your ideas guys.

    Cheers,

    Mike

  • Ok, here is a Stored Procedure I wrote based on the things in this Post. If someone would analyze this to see if I missed anything I'd appreciate it. Thanks!

    Please excuse the formatting, I noticed that this forum does not keep the Indenting I usually code with.

    -- Shrink a Database and Log File

    CREATE Procedure spShrinkDatabase

    (@Database NVarChar(128))

    As

    Set NoCount On

    Declare @DBLog NVarChar(128), @BackUpName NVarChar(128), @BackUpFile NVarChar(255)

    If (@Database Is Not Null) And (Exists(Select * From Master.dbo.SysDatabases Where Name = @Database))

    BEGIN

    --Set @DBLog = @Database + N'_Log'

    Set @BackUpName = @Database + N'_BackUp'

    -- Determine the BackUp File

    Select @BackUpFile = FileName From Master.dbo.SysDatabases Where Name = @Database

    Set @BackUpFile = Reverse(@BackUpFile)

    Set @BackUpFile = SubString(@BackUpFile, CharIndex('\', @BackUpFile, 0), Len(@BackUpFile))

    Set @BackUpFile = Reverse(@BackUpFile) + @BackUpName + N'.bak'--Set the FileName

    Print @BackUpFile

    Print @BackUpName

    -- BackUp the Database

    BackUp Database @Database To Disk = @BackUpFile With Init, NoUnload, Name = @BackUpName, NoSkip, Stats = 10, NoFormat

    DBCC ShrinkDatabase (@Database, TruncateOnly)-- Shrink the Database

    -- Truncate the Log Files

    Declare @LogCursor Cursor

    Set @LogCursor = Cursor Fast_Forward For Select Name From SysFiles Where FileName Like '%.ldf%'

    Open @LogCursor

    Fetch Next From @LogCursor Into @DBLog

    While (@@Fetch_Status <> -1)

    BEGIN

    If (@@Fetch_Status <> -2)

    BEGIN

    DBCC ShrinkFile (@DBLog, 2, TruncateOnly) -- Shrink the Log File

    END

    END

    Close @LogCursor

    DeAllocate @LogCursor

    --Shrink Database last time

    DBCC ShrinkDatabase (@Database)

    END

    Set NoCount Off

    GO

  • I normally take a full backup before and after I do this. I am using SQL Server 7 and have a standby read-only DB that I am shipping logs to once an hr from our main Prod DB and applying it. After this process I would need to restore the latest BAK and then set up the application of the trans logs.

    Another note: If your DB has lot of activity, best to backup your trans logs once an hr if you want to avoid having it grow to big.

Viewing 11 posts - 1 through 10 (of 10 total)

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