Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Unanswered: Transaction log full due to poorly configured SQL Database

    Good evening all,

    I am new to Microsoft SQL and have been dumped right into the deep end with an anchor around my ankles.

    The problem I am experiencing is about 6 months back a guy setup a Microsoft SQL server at my client. He then took his bags and left. Then a developer developed an application which uses a database on this sql server. Everything worked well for around 4 months.

    Yesterday IBM Director notified me that I am running dangerously low on disk space on the server. By the time I came in to work this morning I had 12mb free disk space. The evil thing here is the transaction log for the downtime database. It has grown to over 60gb, all because the correct measures were not implemented to prevent this.

    The problem that is occuring is no one can do anything on the database now due to the lack of free space. I have tried reading the sql books online but due to my lack of sql experience and knowledge I am stuck.

    Can someone please help me? What I would like to do is pull my transaction log down to an acceptable size.

    I was thinking of creating a new database and then importing all the data from the current database and then just deleting the current database however am slightly concerned that this may result in some data being lost.

    Thanks in advance,

    Richard

  2. #2
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25
    Try issuing this commands in the SQL Server Query Analyzer...

    Code:
    BACKUP LOG database name WITH TRUNCATE_ONLY
    ...and then...

    Code:
    DBCC SHRINKFILE (the name of the transaction log, 1)
    The name of the transaction log refers to the actual name of the transaction log of your DB that grew with out the extention name and the number 1 refers to how small you wanted to shink the file in megabytes. Like for example you wanted to shrink the transaction log of the pubs database to 2 MB...

    Code:
    DBCC SHRINKFILE (pubs_log, 2)
    And try changing the recovery model of your database as this also affects the growth of the transaction log. If your company is running daily back-ups for example, try changing the recovery model to simple. But it's better for you to research about recovery models yourself. I hope this helps...
    Last edited by trojanz; 12-24-05 at 00:38.

  3. #3
    Join Date
    Dec 2005
    Posts
    4
    TrojanZ, you just became my best friend! Thanks a million for your assistance!

  4. #4
    Join Date
    Dec 2005
    Posts
    4
    Quick question,

    Is there a way of enabling circular logging so that when my log file reaches its max size it starts from the beginning?

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by EkilErif
    Quick question,

    Is there a way of enabling circular logging so that when my log file reaches its max size it starts from the beginning?
    Not on SQL Server. Your options are:

    1. Schedule transaction log backups to occur regularly so that the log file does not fill up.

    2. Set your database recovery mode to 'simple' which will truncate the log file everytime a checkpoint is issued.

    In either case, you should set a maximum growth limit on your log file so that it doesn't chew up all the space on your disk.

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Dec 2005
    Posts
    4
    Thanks hmscott.

    We have since changed the recovery mode to simple, set a maximum size on the transaction log and setup a maintenance plan which will backup the transaction log regularly.

    Possibly overkill but I just really dont want this problem to happen again!

    Kind Regards
    Richard

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by EkilErif
    Possibly overkill but I just really dont want this problem to happen again!
    I'd be inclined to say that setting SIMPLE and a log backup was taking the "belt and suspenders" idea a bit too far, but I can understand why you might do that! It certainly won't hurt anything.

    Glad to see that you've got things under control again anyway!

    -PatP

  8. #8
    Join Date
    Oct 2012
    Posts
    1
    This is an old thread, but still...
    I cannot believe what are you suggesting to Ekil.
    If you switch from full recovery model to simple recovery model, you lose point-in-time restore ability. And that is NOT recommended on production systems.
    In full recovery model, transaction log will grow until it fills all the disk space,
    UNLESS you set regular transaction log backups (e.g. every 1 hour).
    If there was some extraordinary event that made tran log bigger than normal, you could shrink it by this procedure:

    Check the reason why tran log could not be "roll-overed" (truncated) with this tsql:

    select name, log_reuse_wait_desc, recovery_model_desc
    from sys.databases
    where name='mydbname'

    Find out what is logical name of transaction log file:
    sp_helpdb 'mydbname'

    Manually run the JOB that back-up transaction log, and then:
    DBCC SHRINKFILE('MyTransactionLogLogicalName', 500) -- 500 MB. NEVER shrink to 1MB!

    Repeat last two steps (log backup+shrinkfile) several times, until you see that log actually got shrinked. Most of the times 2 cycles will be enough.

    With that method, you are keeping your point-in-time restore ability.

    If you shrink log to close to zero (1MB), it will grow again to its normal operation size.
    So you will just waste you disk resources in shrinking and growing back.
    Instead, see what is your normal operational log size (e.g. 2 GB) and do not shrink the log below that value.

    If you do backup you log WITH TRUNCATE option, you will also lost your point-in-time recovery, so it is not recommended option in production systems.

  9. #9
    Join Date
    Aug 2008
    Posts
    147
    The backup strategy should reflect the agreed RTO and RPO. That should be the starting point.
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  10. #10
    Join Date
    Sep 2004
    Posts
    3
    @BRIGHT5,

    I just uploaded below post in this forum. I think the answere you have provided above has relevance to my question and hence repeating it here too. I am too experiencing similar log file size issue and in my case it is DR setup with LogShipping method which comes in to picture. Could you please go through it and recommend a solution? Thx in advance.

    - Vijay

    <Quote>

    Hi,

    We have a business application using SQL Server 2005 with a considerably big DB size (almost 80GB) . We have configured a DR setup to copy data from primary database (PR) to the DR server with SQL Server LOG SHIPPING method. The PR data is log shipped on an interval of 15 minutes to the DR Sql Server DB. The problem we are facing is at times the transaction log file of the PR server grows beyond a limit and impacts normal application performance resulting in the application becomes non-responsive. To bring it to normal stage we have to then forcefully run Transaction log backup with truncate only command which reduces the transaction log backup size. But then this impacts our DR setup Log Shipping jobs and it breaks/stops to carry out DR log shipping.

    I need some help on how to keep the DR setup in sync throughout and keep the transaction log backup at lower space.

    Thanks for you advice.

    Rgds.

    Vijay

    <UnQuote>

Posting Permissions

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