Post reply

Trasaction log is getting full

  • January 19, 2012 at 3:34 am

    #251570

    hi

    we are using sqlserver 2005.

    1. Every day my traction log file is getting filled upto 10GB.

    2. Recovery model of DB :Simple

    3. It was enabled to restricted growth, upto diskspace.

    4. Enabled Auto growth Option

    Do to which i am doing shinking the log file. finally it was reduced to 180MB from 10GB

    .Will i need to uncheck the Auto growth option.

  • January 19, 2012 at 3:44 am

    #1435710

    Check the autogrowth value in case it's verry large.

    Before you shrink the file look at the modified date, and see if you have a daily job,task... that has completed running around that time.

  • January 19, 2012 at 3:48 am

    #1435712

    Sounds like you have a super huge transaction going on during the day that you need to pinpoint! How big is the database itself?

    Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified

  • January 19, 2012 at 3:50 am

    #1435714

    It was happening every day .

    --we are uploading CSV files to database and we are using this db for monitoring purpose.

  • January 19, 2012 at 4:13 am

    #1435721

    it was around 45gb

  • January 19, 2012 at 4:58 am

    #1435733

    What is the resticted size of your logfile in other what is the free spaceof drive where the log file is located.

    it looks like there is insufficient space to grow the log file so ending with error. try to import the data in bunches.

  • January 19, 2012 at 7:15 am

    #1435824

    I strongly recommend setting the log to that larger size and leaving it there. Shrinking it over & over and letting it auto-grow over & over, that's causing fragmentation and performance bottlenecks, every time.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 19, 2012 at 8:30 am

    #1435918

    With the recovery model set to simple, do as already suggested and batch up this import into say 10k rows at a time. After each batch SQL will probably initiate a checkpoint which will allow the same part of the log to be used again thus removing the requirement to log the entire import operation.

    Consider using the CHECKPOINT command between inserts but only if batching the import does not resolve the issue in the first case.

    Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply