Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2006
    Posts
    74

    Unanswered: Cannot shrink log file

    Hi

    I'm trying to shrink a log file which keeps growing in sql server 2005.

    The command i'm using is

    dbcc shrinkfile (BusRuleMonitor_log, 5000)

    but getting the below error message.

    Cannot shrink log file 2 (BusRuleMonitor_log) because all logical log files are in use.

    I have tried shrinking the file out of hours when the system is not in use but having no luck. What am I doing wrong. In sqlserver2000 it was pretty enough straight forward.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    riddle me this. how much free space is there? what's your recovery model? are you taking transaction log backups?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2006
    Posts
    74
    I have only 2 GB free space left remaining on the server. The recovery model is simple and we are taking transaction log backups. When performing the backups I have indicated to truncate log files but it's not doing it.

    The only way I can think is to backup the logfile manually with truncate, but that does not sort my problem out in the log term.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by bmistry
    The recovery model is simple and we are taking transaction log backups
    I do not believe this is even possible. If you are in simple, I do not think they even allow you to do a t-log back up. Unless something has changed. I do not currently handle recovery for my shop.

    get back to me when you know what is going on.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2006
    Posts
    74
    Sorry i got my DB's mixed up, there are no t-logs

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    problem solved
    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.

  7. #7
    Join Date
    Jan 2006
    Posts
    74
    I'm fairly new to this it's the database logfile which is not shrinking

    BusRuleMonitor_log.ldf

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    well nothing about this thread makes much sense.

    if you are in simple recovery, transaction should clear from the log once they are committed to the database. of course you have t-logs. That is what your LDF file is. Now, is the log file getting bloated because perhaps you are doing some major ETL operation?

    I have not checked but you said you backed up your T-LOg with truncate only and I believe the command would error if you are in simple recovery.

    So it is really hard to help you when the whole thread does not make any sense.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The log file may be large, but how much of it is used?

    You don't want to be shrinking any file all the timwe, if it's just going to have to grow again. That will incurr needless overhead.

    If you are running out of space, add disk
    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.

  10. #10
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Try running the following code and post the results:

    Code:
    dbcc sqlperf(logspace)
    and
    Code:
    --change the ENTERDBNAMEHERE to the name of your database
    sp_helpdb ENTERDBNAMEHERE
    on the helpdb please post the "status" information, this will tell us what the recovery mode is

    and
    Code:
    dbcc opentran

    All this information will help us understand your problem and give you more directed advise.
    Last edited by Reghardt; 09-23-08 at 17:35.

  11. #11
    Join Date
    Jan 2006
    Posts
    74
    Hi

    not much of the log is used we only need for it to be 50MB

    Below is from the sql. Thanks for getting back to me for some more investigation work, much appreicated.

    Status

    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=Latin1_General_CI_AS, SQLSortOrder=0, IsAutoShrink, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled


    No active open transactions.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  12. #12
    Join Date
    Jan 2006
    Posts
    74
    dbcc sqlperf

    DBname Log size Logspace Status
    master 1.242188 43.39622 0
    tempdb 0.7421875 69.73684 0
    model 6.117188 96.93487 0
    msdb 1.992188 31.37255 0
    ReportServer 0.5390625 68.38768 0
    ReportServerTempDB 0.8046875 46.96602 0
    AdventureWorksDW 1.992188 24.90196 0
    AdventureWorks 1.992188 27.64706 0
    HEAT 362.1797 93.59145 0
    BusRuleMonitor 14718.93 4.234668 0
    Heat_Restore 160.8047 6.813876 0
    Last edited by bmistry; 09-24-08 at 06:38.

  13. #13
    Join Date
    Jan 2006
    Posts
    74
    dbcc sqlperf

    DBname Log size Logspace Status
    master 1.242188 43.39622 0
    tempdb 0.7421875 69.73684 0
    model 6.117188 96.93487 0
    msdb 1.992188 31.37255 0
    ReportServer 0.5390625 68.38768 0
    ReportServerTempDB 0.8046875 46.96602 0
    AdventureWorksDW 1.992188 24.90196 0
    AdventureWorks 1.992188 27.64706 0
    HEAT 362.1797 93.59145 0
    BusRuleMonitor 14718.93 4.234668 0
    Heat_Restore 160.8047 6.813876 0

  14. #14
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Then run the following code:


    Code:
    dump tran BusRuleMonitor with no_log
    go
    use BusRuleMonitor 
    go
    --make sure that FileID 2 is the log file by running "exec sp_helpfile"
    DBCC SHRINKFILE (2, 1)
    go
    That should fix the issue for now.

  15. #15
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    I also noticed that you have autoshrink turned on for your db. I would do a little research about this one, because I have seen a lot of people suggest that this should be turned off in production.

Posting Permissions

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