+ Post New Thread
Page 1 of 3 123 LastLast
Results 1 to 15 of 36
MIS Systems Thread, massive sim.ldf file - anyone else had there's suddenly grow? in Technical; logged a call with our SIMS team as the log file has reached almost 60GB which is plain daft. The ...
  1. #1

    Join Date
    Sep 2010
    Posts
    548
    Thank Post
    26
    Thanked 62 Times in 58 Posts
    Rep Power
    19

    massive sim.ldf file - anyone else had there's suddenly grow?

    logged a call with our SIMS team as the log file has reached almost 60GB which is plain daft.
    The SIMS team setup the server ages ago but they havent installed any of the SQL management suite to actually look and what logging options have been set?

    Not that happy with installing the suite just in case they grumble about it being unsupported.
    I know they have recently pulled the autumn upgrade and wondering if this might be a reason for this.

  2. #2

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,958
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    It's quite likely that your backups are not truncating the log file.

    What's your backup mechanism?

  3. #3

    creese's Avatar
    Join Date
    Feb 2009
    Location
    -28 31' 48.89", +28 25' 37.42" ... if only.
    Posts
    3,253
    Thank Post
    181
    Thanked 375 Times in 304 Posts
    Rep Power
    174
    Have you tried the ShrinkLDF utility?

  4. #4

    Join Date
    Jul 2007
    Location
    Lancs
    Posts
    388
    Thank Post
    45
    Thanked 21 Times in 19 Posts
    Rep Power
    18
    It's quite likely that your backups are not truncating the log file.
    I second this, we have had this before there is a setting that can be change but can not remember it.

  5. #5

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,958
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    Before attempting to shrink the file (in itself it's not going to cause too much of a problem, apart from being very big) it's best to try and find out why it's been growing, or we'll end up with the same problem again.

    We need to know the database model (simple/full) and the backup mechanism used.

  6. #6

    Join Date
    Apr 2006
    Posts
    388
    Thank Post
    23
    Thanked 95 Times in 61 Posts
    Rep Power
    44
    You can install the SQL Management tools on another box and connect to the database server to figure out what's going on (assuming the DB server accepts remote connections).

  7. #7

    Join Date
    Sep 2010
    Posts
    548
    Thank Post
    26
    Thanked 62 Times in 58 Posts
    Rep Power
    19
    after speaking with the SIMS team they ran a script to change the logging/backup method to simple as it was set to full.
    further investigation suggests that the backup also had a problem and wasnt completing properly and not truncating the logs..

  8. #8

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,958
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    Good job you spotted the problem.

  9. #9
    Quatermass's Avatar
    Join Date
    Mar 2007
    Posts
    84
    Thank Post
    7
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Quote Originally Posted by SHimmer45 View Post
    after speaking with the SIMS team they ran a script to change the logging/backup method to simple as it was set to full.
    further investigation suggests that the backup also had a problem and wasnt completing properly and not truncating the logs..
    We've suffered from the "recovery model Simple/Full issue" bloating LDFs in the past. Having set it to Simple manually a few times (SQL Server Management Studio > Database Properties > Options > Recovery Model) I tend to check it after each SIMS release because several have kindly set it back to Full again!

  10. #10
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,762
    Thank Post
    897
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    86
    So how big is the database now?

    You can also change the autogrowth properties to slow it down. We have it set to 10mb auto growth here.

    Sims mdf is 2gb
    the ldf db is 10gb

    Is there any document saying what a good ratio should be?
    Last edited by zag; 9th December 2011 at 09:07 AM.

  11. #11

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,663
    Thank Post
    516
    Thanked 2,446 Times in 1,894 Posts
    Blog Entries
    24
    Rep Power
    831
    Quote Originally Posted by zag View Post
    So how big is the database now?

    You can also change the autogrowth properties to slow it down. We have it set to 10mb auto growth here.

    Sims db is 2gb, the log db is 10gb
    10gb is huge still... Ours ldf is 1.1Mb at the moment, and it'll grow over the day to probably 10Mb or so, then the backup will truncate it back down to about 1Mb again.

  12. #12

    Join Date
    Sep 2010
    Posts
    548
    Thank Post
    26
    Thanked 62 Times in 58 Posts
    Rep Power
    19
    currently the LDF stands @ 1.5MB.
    the SIMS team have a script to set the properties if SQL studio isnt installed.

  13. #13

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,958
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    Avoid using auto-growth as a management tool.


    Always size the databases correctly, an auto-grow will halt the database engine while the server extends the files.

    Switch on auto-grow by all means, but set it so that the increment is a large size (rather than a percentage). This means that if the server ever needs it (for whatever reason), it will extend the files by a large, defined increment, rather than nudging the file size every so often.


    Forget auto-shrink, do this manually if you must.

    Here's why.

    http://blogs.msdn.com/b/sqlserversto...hrink-off.aspx
    Last edited by jinnantonnixx; 9th December 2011 at 09:39 AM.

  14. #14
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,762
    Thank Post
    897
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    86
    I just reduced to 10mb by using Shrink >> file >> log

    99% was free apparently!

    Set a limit of 1gb now.
    Last edited by zag; 9th December 2011 at 09:51 AM.

  15. #15
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,762
    Thank Post
    897
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    86
    Anyone using backupexec 2010? What settings for the SQL agent are you using to truncate the logs?

SHARE:
+ Post New Thread
Page 1 of 3 123 LastLast

Similar Threads

  1. Has Anyone Else Had This Email From Crucial.
    By daz666 in forum General Chat
    Replies: 7
    Last Post: 8th April 2011, 06:35 PM
  2. Anyone else had (or about to have) an awful lunch?
    By Dos_Box in forum General Chat
    Replies: 25
    Last Post: 8th October 2010, 08:35 PM
  3. Replies: 5
    Last Post: 28th June 2010, 08:41 AM
  4. Replies: 3
    Last Post: 9th September 2008, 06:23 PM
  5. [SIMS] sims ldf file nearly 17 gig
    By edie209 in forum MIS Systems
    Replies: 11
    Last Post: 22nd November 2007, 10:30 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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