Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2008
    Posts
    56

    Unanswered: Shrinking the log on a job

    Hi,
    I have a problem using the shrinkfile command in a job. It works fine from SSMS (sql 2005)

    When I use

    DBCC SHRINKFILE (mydb1_log, 1) in SSMS the file shrinks.

    When I run it in a job I get the following msg and the log won't shrink. (I am running the service with a sysadmin domain login)

    Cannot shrink log file 2 (mydb1_Log) because all logical log files are in use. [SQLSTATE 01000] (Message 9008)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528).
    The step succeeded.

    Thanks in advance...

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Is the database in simple recovery mode? Or is this trying to run inside a transaction?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you shrink a file with the database is in use?

    I didn't think so
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2008
    Posts
    56
    The db is in full recovery mode. We run hourly trans log backups. I can shrink the file while the db is in use through SSMS.

    Thanks

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I suspect the shrink step is happening in the later half of the hour. Maybe even just before the log backup is taken. Although, that is not entirely necessary. If you look at the output of dbcc loginfo, the status column shows what the active portion of the log is. You will find, when you get this message, that the active portion is toward the end, and may even only be two logical log files. DBCC SHRINKFILE will move rows data when you shrink a file, but it does not move log entries.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just an observation for what it might be worth, but shrinking the log file is usually a symptom of a problem.

    Shrinking a log file on a scheduled basis without DBA intervention is definitely a sign of a problem. Imagine that you have an air conditioning unit that lets the condensation run into a bucket as part of its design. The transaction backups are like using an employee to periodically empty the bucket. Shrinking the log file is like sending another employee to grind the edges of the bucket down to the water level. SQL Server's automatic log file growth is like assigning yet another employee to come weld new metal onto the bucket whenever the bucket gets full. You are using three employees to do the work of one that had a proper sized bucket!

    Figure out what size your log file needs to be... If you have to guess go overboard and make it bigger than you'll ever need, disk is cheap compared to wasted operations! Set up a transaction log dump often enough to allow you to meet your backup/restore objectives and if you have any questions or problems make transaction log dumps more frequently.

    There are thousands of articles on why you shouldn't shrink databases under normal conditions, but by far the most authoritative and readable one is In Recovery... | Why you should not shrink your data files

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Oct 2008
    Posts
    56
    Thanks for the advice. I read the article and do see the point. The size of the log file is only a problem because we restore a copy of the db to another server that doesn't have alot of space. This process is being eliminated but I need to make sure it happens every night until then.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In that case I would make shirnking the log part of the restore onto the smaller server process.

    We do that all of the time when we restore production back to dev or QA servers for one of our clients.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    As part of that process, you would likely want to alter the database to set the log file to its desired size. If the growth increment of the file is small, then you will not only have file fragmentation on the disk, but you will end up with a large number of logical log files. I went to a talk by Kalen Delaney about how files are structured, and she said that having lots of little logical log files is a performance problem. Unfortunately, I am not sure if it is a problem writing the log, or backing up the log.

Posting Permissions

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