Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    28

    Unanswered: transaction log question

    Folks,

    Having some problems with a transaction log. During some large batch loads the log has grown to about 10 GB. It is set to truncate on checkpoint, so the actual space used is back down to less than 200 MB. My question is, how do I shrink the log itself from the file size of 10 GB to, say, 1 GB? I've tried 'backup log stage with no_log', also tried 'dbcc shrinkfile (stage_Log, 1000)', but these don't seem to gain me anything. I'd rather not have to recreate the database to accomplish this.

    Thanks in advance,
    Chris

  2. #2
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51
    Here's my favorite method. It's a stored procedure you can put in your master database and run to shrink the size of you transaction log files.

    It's parameters and usage are explained in the comments.

    I got it from SQLServerCentral.com's script library and then modified it to work with SQLServer 2000.
    Attached Files Attached Files

  3. #3
    Join Date
    Dec 2002
    Posts
    28
    Went to sqlservercentral and got the original for SQL 7. Very cool. Thanks Loach.

Posting Permissions

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