Post reply

Successful shrinking of logfile but it still grows

  • April 3, 2006 at 5:01 am

    #171822

    Hi!

    After deleting a lot of records in a database, our logfile grow to araound 2.6 Gb. We decided to shrink the logfile to a size it was before the deletion.

    We did:

    backup log dbname with truncate_only

    dbcc shrinkfile (logfile, 130)

    Both commands succeeded.

    Then we did a full backup of the database.

    The message from Dbcc shrinkfile was "DBCC execution completed". The grid panel showed information about current size, minimum size, used pages and estimated pages. The Taskpad showed the new size of the logfile. Everything seemed fine until the users started using the system. Now the logfile has a size around 1.6 Gb but this varies during the day. Also Used and Free figures varies.

    We have SQL Server 2000 SP3. The logfile is set to automatic growth and the recovery model is full.

    What is wrong? It's like it all virtual pages aren't removed but the logfile did get shrinked. Any ideas are more then welcome.

    Many thank's in advance!

    Regards

    Bjorn

     

  • April 3, 2006 at 8:32 am

    #630430

    You need to take regular log backups. Best way will be to take log backup and shrink the file after that.

     

  • April 3, 2006 at 12:10 pm

    #630493

    As Balmukund asked, are you doing log backups?

    Also, what are your users doing on the database? Some transactions use TEMPDB and will cause it to grow. Are they creating temporary tables (#tablename or ##tablename)? Those use the TEMPDB also.

    -SQLBill

  • April 4, 2006 at 12:57 am

    #630577

    Yes, we take log backups one time per day and full backups during the night. Before we started the deletion, we did a full backup of the database. Should we also have done a log backup? And if so, why?

    As far as I know, I don't think the users use the Temp-database. The application running against the database is just a simple one, mostly used for searching after information and sometimes updating it.

    /Bjorn

  • April 4, 2006 at 9:02 am

    #630693

    Everytime you execute an insert, update, or delete, MORE infromation is written to the log. That's what it is, a log of activity. When you take a log backup, these pages are marked as cleared. They can be removed in a shrink operation.

    But you don't want to be shrinking the log if it will grow. Growths cause pauses in the server and you may experience performance issues when it is growing.

    Instead, find out the what expected size is, and it may be 1.6GB on most days, and give yourself a little pad, maybe 2GB, and set the log to this size. That way it should work fine. You need to do log backups, probably more than one a day.

  • April 4, 2006 at 2:00 pm

    #630813

    I'm with you regarding backuping the log and what it does but what I don't understand is this:

    Given the fact that the system is used as before the shrink operation, this mean (in my eyes is best to say...) that the 130 Mb log should be enough. I think so because before we deleted the records as a one time operation (removing old records in two tables that is not to be used any more), the log had a size of 130 Mb and plenty of space within it. Since the log is set to automatically growth and to my knowledge, no shrinking has occured for a very long time this is a ok size for the log otherwise it would have been larger, correct? If the system is used as normal and we have done a correct shrink operation, why has it grown? Should we also do some some performence action such as update statistic, rebuilding indexes?

    What is not getting in to my head?

    Regards

    Bjorn

  • April 6, 2006 at 6:25 am

    #631190

    Hi ,

    If the Database is not so critical , Change Recovery Model to Simple.

    Take the Backup and truncate log file and shrink the Log file.

  • April 6, 2006 at 12:24 pm

    #631326

    ok, that a possible way to go but why does the log file shrink during the day?  This strange behaviour gives me a headache...

    The database has Auto Shrink enabled and it has been so for a very long time, possible since it was created as a SQL Server 7.0 database. From what I understand Auto Shrink only works if you have simple recovery mode or doing a Log backup. Since we don't have simple recovery mode and we only do log backups at one per day, I can't understand why the log is shrinking on other times during the day? I have checked that we don't have any job or maintance plans that do shrink so the answer isn't there. Can it be "feature" from 7.0 where, if I have understood it correctly, auto shrink always run if it was enabled and not depended on recovery mode or doing backup log?

     

    Regards

    Bjorn

  • April 7, 2006 at 8:41 am

    #631516

    My understanding is that Auto Shrink is an 'when possible' feature. It's not 'timed' to anything. So, it will Auto Shrink during the day if that's when SQL Server determines it's the best time to do so.

    -SQLBill

  • April 7, 2006 at 12:20 pm

    #631576

    Then I have an explination to why the log is reducing in size during the day.

    Thank's all for helping me out

    /Bjorn

     

Viewing 10 posts - 1 through 9 (of 9 total)

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