Post reply

shrink logs

  • June 18, 2003 at 1:05 am

    #160120

    Hi,

    I get a Database which wasn't administrate before. The logfile aute grows and now when I restore the DB the size of the logfile is 20Go and 1% used.

    How can I reduce the space used on the disk by thi file ?

  • June 18, 2003 at 1:29 am

    #461854

    Hi,

    You can either do

    dbcc shrinkfile (dbid, n) --where dbid is the fileid in sysfiles and n is the target MB size

    Or

    backup log yourdbname with truncate_only

    Ritch

    Edited by - Ritch on 06/18/2003 03:09:00 AM

    Edited by - Ritch on 06/18/2003 03:30:42 AM


    "I didn't do anything it just got complicated" - M Edwards

  • June 18, 2003 at 3:05 am

    #461867

    OK dbcc shrinkfile (2, 100,TRUNCATEONLY)

  • June 18, 2003 at 3:30 am

    #461870

    Sorry for the incomplete syntax tsc1, the caffiene has just kicked in

    Also I have tried shrinking logs in EM via taskpad, but it never works. Anyone else found this?

    *I didn't do anything it just got complicated*


    "I didn't do anything it just got complicated" - M Edwards

  • June 18, 2003 at 8:34 pm

    #462024

    I already encounter the same problem and what I did is I shrink the file in a small chunk. On my case, I shrink it by 1GB at a time.

    hope that helps.

  • June 18, 2003 at 9:42 pm

    #462032

    Ritch,

    I use SQL7 and SQL2000 and i find the SQL7 to be a little buggy in this instance, it could be the setup i have here but i find quitting EM and starting again / or a refresh of the taskpad will give a more accurate result, plus it depends on the type of backup mode you are in - simple / bulk / full. As i understand transactions are happening when you shrink a database as well. HTH. Remember you *might* need to backup before shrinking too and truncating logs.


    ------------------------------
    Life is far too important to be taken seriously

  • June 19, 2003 at 1:36 am

    #462051

    Thanks.

    *I didn't do anything it just got complicated*


    "I didn't do anything it just got complicated" - M Edwards

  • June 19, 2003 at 2:22 am

    #462057

    The script below will solve your problem,it runs for bot sql 7.0 an sql 2000.

    -- Caution : database compatibility level must be 7.0 or higher ..

    dbcc shrinkfile ( [Logical Log File Name], truncateonly )

    backup log [Logical Database Name] with truncate_only

    GO

    if exists ( select * from sysobjects where name ='tmp_00000000000001a')

    drop table tmp_00000000000001a

    create table tmp_00000000000001a (cola varchar(10), colb int )

    set nocount on

    insert tmp_00000000000001a (colb) values (1)

    declare @index int

    select @index = 0

    while (@index < 4000)

    begin

    update tmp_00000000000001aset cola = cola where colb = 1

    select @index = @index + 1

    end

    dbcc shrinkfile ( [Logical Log File Name], truncateonly )

    backup log [Logical Database Name] with truncate_only

    GO

    drop table tmp_00000000000001a

    GO

  • June 19, 2003 at 2:23 am

    #462058

    The script below will solve your problem,it runs for bot sql 7.0 an sql 2000.

    -- Caution : database compatibility level must be 7.0 or higher ..

    dbcc shrinkfile ( [Logical Log File Name], truncateonly )

    backup log [Logical Database Name] with truncate_only

    GO

    if exists ( select * from sysobjects where name ='tmp_00000000000001a')

    drop table tmp_00000000000001a

    create table tmp_00000000000001a (cola varchar(10), colb int )

    set nocount on

    insert tmp_00000000000001a (colb) values (1)

    declare @index int

    select @index = 0

    while (@index < 4000)

    begin

    update tmp_00000000000001aset cola = cola where colb = 1

    select @index = @index + 1

    end

    dbcc shrinkfile ( [Logical Log File Name], truncateonly )

    backup log [Logical Database Name] with truncate_only

    GO

    drop table tmp_00000000000001a

    GO

  • June 19, 2003 at 5:46 am

    #462080

    Hi !

    /*

    shrink a log file

    WARNING: change the database name and the logical name of the log file.

    database: MyDB

    log file: MyDB_Log

    */

    USE master

    go

    EXEC sp_dboption 'MyDB', 'trunc. log on chkpt.', 'TRUE'

    USE MyDB

    go

    DBCC SHRINKFILE ('MyDB_Log', TRUNCATEONLY )

    USE master

    go

    EXEC sp_dboption 'MyDB', 'trunc. log on chkpt.', 'FALSE'

    USE MyDB

  • June 19, 2003 at 6:32 am

    #462095

    Here's a link to the script that I used OFTEN.

    http://qa.sqlservercentral.com/scripts/savescript.asp?scriptid=26

    It forces the transaction log to shrink by moving the last active transaction back to the top of the transaction log.

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

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