Results 1 to 7 of 7

Thread: shrinking log files after backup

  1. #1
    Join Date
    Sep 2002
    Posts
    159

    shrinking log files after backup

    Hello everybody.
    I have SQL2000 sp3 standard
    with 50 db's

    All db set for full recovery
    and autoshrink
    Backup done with Tivoli

    full backup once a week
    log backup done every 12 hrs

    Problem .. shrinking logs

    every 20 min I run job
    DBCC SHRINKFILE (My_db_logFile) for every db

    70% of the time I am getting
    message similar to
    -------------
    Cannot shrink log file 2 (Wholesale_Log) because all logical log files are in use.
    --------------
    1. I checked with sp_who2
    The is no activity on db 'Wholesale_Log' or any other db returning "Cannot shrink..."

    Why i getting "Cannot shrink ..." ?

    even if job runs right after backup of the log files ,I still have messages.

    Thank you

    Alex

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  3. #3
    Join Date
    Sep 2002
    Posts
    159
    Hi Mak,

    what is the script for checkpoint ?

    Thank you

    Alex

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Run

    CHECKPOINT

    from query analyzer, in the context of database

  5. #5
    Join Date
    Sep 2002
    Posts
    159
    Hi

    I tried
    CHECKPOINT
    DBCC SHRINKFILE(Invoice_Log)

    ----

    Cannot shrink log file 2 (Invoice_Log) because all logical log files are in use.
    DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
    ------ ------ ----------- ----------- ----------- --------------
    8 2 142544 3185 142544 3184

    ---
    dbcc sqlref shows

    Database Name = 'Invoice'; Log Size ( MB) = 1113.6172 ; Log Space Used (%) = 7.017683 ; Status = 0;

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    try this.

    use test
    go
    checkpoint
    go
    backup database test to disk = 'd:\test.bak'
    go
    BACKUP LOG test WITH TRUNCATE_ONLY
    go
    dbcc shrinkfile ('test_log',TRUNCATEONLY)
    go
    dbcc shrinkfile ('test_log')
    go

    if it still dont work, send us the result of the following query on that database.

    use databasename
    go
    DBCC LOGINFO
    Last edited by MAK; 07-23-2003 at 02:12 AM.

  7. #7
    Join Date
    Sep 2002
    Posts
    159
    Hi Mak,

    I tried you code

    it works only with
    BACKUP LOG test WITH TRUNCATE_ONLY

    Tivoli keeps logs till next full backup
    and with truncate will break log chain


    Here is my stor proc trying to
    shrink logs

    ALTER procedure generate_dummy_trn
    as
    Declare
    @Sql varchar(300),
    @name varchar(50),
    @Sql2 varchar(300)
    set @sql = ' if object_id(''shrink'') is not Null ' + char(13)+
    ' drop table shrink ' + char(13) +
    ' create table shrink (i smallint)' + char(13) +
    -- ' GO '+ char(13) +
    ' checkpoint'+ char(13)+
    -- ' GO '+ char(13) +
    ' declare @x sysname ' + char(13) +
    ' select @x = rtrim(name) from sysfiles where fileid = 2 '+ char(13)+
    ' select @x ' + char(13)+
    ' DBCC SHRINKFILE (@x,TRUNCATEONLY) '


    declare
    C1 cursor for
    select --top 2
    rtrim(name)
    from sysdatabases

    open c1
    fetch c1 into @name

    while @@fetch_status = 0
    begin

    if (select convert(char(9),DatabasePropertyEx(@name,'Updateab ility'))) <>'READ_ONLY'
    begin
    set @sql2 = ' use ' + @name + char(13) + @sql

    select @sql2
    exec (@sql2)
    end
    -- select @sql2

    fetch c1 into @name
    end
    close c1
    deallocate c1

    Thank you

    Alex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •