Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: SQL Server Shrinking

    OK here's my dilema:

    I have a SQL Server database (in 2000) which is 3 gig in size. I am using bulk-logged as the recovery model (I've tried simple.)

    We import 300,000+ records on a monthly basis. This increases the tranlog with each import (and often times records need to be deleted and re-imported due to bad fields on the import files) and it ends up growing the tranlog to over 1 gig by the end of the day.

    I've tried and tried but I can't shrink the tranlog! I've used every shrink type command and option (with truncate_only, emptyfile, etc.) but I still can't shrink the tranlog.

    The database is set on a once a month backup schedule (which includes backing up the tranlog.)

    Any help would be great! I was able to shrink it once after issuing several commands (i.e. dbcc commands + shrinkfile, shrinkdatabase, etc.) but I can't remember the combination of commands I used and I can't get it to shrink last time now no matter what I do!

    I cannot use BCP or DTS or any other type of SQL Server importing process! I need to use the Access front end to do the importing (linked tables) because there are simply too many checks it needs to do which BCP or DTS can't handle. I do however use some stored procedures to help with the importing process speed.

    Again, Any help would be great as we have 300,000+ records to import today and having a 1 gig tranlog slows things down. How can I shrink a stubborn tranlog which doesn't want to shrink and what's the best type of configuration for this database?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    What I tried

    I tried this and it seemed to work but I don't really like this solution:

    I changed the recover model from bulk-logged to simply
    Then I re-issued the shrink database command.
    Then changed the recovery model back to bulk-logged.
    The tranlog then shrunk down to 20meg from 1 gig.

    It finally shrunk! Any ideas out there?

  3. #3
    Join Date
    Dec 2004
    Posts
    4
    Run this against the database for which you wish to shrink the log file on:


    --set recovery model to simple
    declare @dbname varchar(30)
    declare @sqlstr varchar(1000)

    set @dbname = (select db_name())

    set @sqlstr = 'alter database ' + @dbname + ' set recovery simple'

    exec(@sqlstr)
    go
    --shrink log file
    checkpoint
    declare @dblogfile varchar(30)
    declare @sqlstr varchar(1000)

    set @dblogfile = (select name from sysfiles where fileid = 2)

    set @sqlstr = 'dbcc shrinkfile (' + @dblogfile + ', 1)'

    exec(@sqlstr)
    go
    Last edited by Dr_Clong; 12-30-04 at 15:51.

Posting Permissions

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