Results 1 to 6 of 6

Thread: dbcc shrinkfile

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: dbcc shrinkfile

    Hi All,

    I have a job that has multiple steps. Step 1 rebuilds the indexes, step 2 truncates the transaction log, and step 3 shrinks the transaction log via dbcc shrinkfile command. The job has been running for quite a while without any problems until this past weekend. The job ran successfully but when I looked at the size of the transaction log, it was the same as before the job ran. I have read on BOL that if part of the logical log is in the virtual logs beyond the target_size mark, SQL Server 2000 frees as much space as possible and issues an informational message. My questions is where is this message stored? How can I read it?

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The informational message is output to the console device, which is the messages window in Query Analyzer or the job step history for SQL Agent jobs.

    -PatP

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    I checked the job history and it showed that the transaction log shrinking step successed. So that doesn't help me.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Then my next guess would be that you had open transactions spanning log segments, preventing the log from shrinking. No error message is issued when that happens.

    -PatP

  5. #5
    Join Date
    Oct 2006
    Location
    San Diego
    Posts
    4

    Shrink Files Solution

    Try the following:

    use your_DB
    go

    checkpoint
    go

    BACKUP database your_DB TO DISK = 'junk1'
    go

    BACKUP LOG your_DB TO DISK='junk2'
    go

    BACKUP database your_DB TO DISK = 'junk3'
    go

    dbcc shrinkdatabase(your_DB)
    go

    dbcc shrinkfile(your_DB)
    go

  6. #6
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Look into
    1. backup log [databasename] with no_log
    and
    2. dump tran [databasename] with no_log

    I usually use number 2 if the dbcc shrinkfile(dbname,1) doesn't work.

    Good luck

Posting Permissions

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