Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    6

    Unanswered: 95 gig Transaction log!!!

    Hi all, I am a lowly help desk operator who has been thrown into the position of maintaining our SQL server until a new DBA is hired.

    The problem is the transaction log has grown to 95GB. I truncated the log file and it shows 95 GB allocated, 588MB in use. I have researched and found that I need to use the DBCC SHRINKFILE command to free up the space, however, I have no clue how to use this command. I can't seem to find any documents on the syntax to use this with osql from the command line.

    We are running out of space on the drive and everyone is looking at me to do something about it. Any help would be immensely appreciated.

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by c0pe
    Hi all, I am a lowly help desk operator who has been thrown into the position of maintaining our SQL server until a new DBA is hired.

    The problem is the transaction log has grown to 95GB. I truncated the log file and it shows 95 GB allocated, 588MB in use. I have researched and found that I need to use the DBCC SHRINKFILE command to free up the space, however, I have no clue how to use this command. I can't seem to find any documents on the syntax to use this with osql from the command line.

    We are running out of space on the drive and everyone is looking at me to do something about it. Any help would be immensely appreciated.
    See this ...http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=653804&SiteID=1
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Oct 2006
    Posts
    6
    This still doesn't help me much. What I am looking for in instructions on HOW to run the commands to shrink the log file. Do I run it from command line? and if so do I just type in "DBCC SHRINKFILE filename" ?

  4. #4
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    First make sure there are no open transactions:

    Code:
    select * from master.dbo.sysprocesses where open_tran > 0
    then run:
    Code:
    dbcc sqlperf(logspace)
    Use the name retreived above and run dbcc shrinkfile
    Code:
    dbcc shrinkfile([database_Log])
    
    exec sp_spaceused
    if the size does not go down, you can always dump the transaction log
    Code:
    dump tran [databasename] with no_log
    Please do research before executing the last line of code to fully understand what you are about to do.

    Good luck,
    Hope this helped

  5. #5
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    You should run it in query analyzer, assuming you are using sql server 2000

  6. #6
    Join Date
    Sep 2006
    Posts
    17
    I don't have much to add beyond what Reghardt has already said, but I will mention that properly scheduled backups of your transaction log should handle keeping your filesize down quite nicely.

    My guess is that there is no transaction log backup scheduled, or they are scheduled, but have been failing for a while now & havn't been monitored.

    Here is a great guide that should help you with this.
    http://searchsqlserver.techtarget.co...166473,00.html

    Shrinking your log-file will solve your problem now, but it will just happen all over again down the road if you don't set up proper backups.

    In addition, it is a very good idea to put a threshold on all of your transaction logs. That way, if a transaction log starts growing too big, it will only affect the database it is assigned to, instead of filling up your entire server.

  7. #7
    Join Date
    Oct 2006
    Posts
    6

    Thanks!

    This worked perfectly, I was able to get it down to 4.6 gigs. Appreciate all the help.

Posting Permissions

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