+ Post New Thread
Results 1 to 13 of 13
MIS Systems Thread, Sims .ldf at 46GB in Technical; Looking through the sql data folder and I see the sims .mdf at 760MB but the .ldf is at 46GB ...
  1. #1
    Jobos's Avatar
    Join Date
    Apr 2007
    Posts
    1,153
    Thank Post
    184
    Thanked 52 Times in 45 Posts
    Rep Power
    26

    Sims .ldf at 46GB

    Looking through the sql data folder and I see the sims .mdf at 760MB but the .ldf is at 46GB How on earth did it get this big!

    The databases are backed up each night and I've tried backing up with dbAttach but the .ldf does not shrink.

    What can I do to fix this?

  2. #2

    JJonas's Avatar
    Join Date
    Jan 2008
    Location
    North Walsham, Norfolk
    Posts
    3,158
    Thank Post
    404
    Thanked 445 Times in 330 Posts
    Rep Power
    387
    I think these are the log files. I had a similar problem with Sophos log files yesterday I was able to shrink them with Microsoft SQL Server Management Studio

    I can give instructions if you like, but im no expert (When I shrunk mine it was very much fingers crossed hope it works)
    Last edited by JJonas; 12th December 2012 at 01:50 PM.

  3. Thanks to JJonas from:

    Jobos (12th December 2012)

  4. #3

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,821
    Thank Post
    272
    Thanked 1,140 Times in 1,036 Posts
    Rep Power
    350
    If you open Management studio go to properties of the database and see what the recovery is set to, set this to simple and then run a backup.

  5. Thanks to glennda from:

    Jobos (12th December 2012)

  6. #4
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 491 Times in 375 Posts
    Rep Power
    0
    Check to see if the SIMS database is in FULL recovery mode. Connect to the instance using management studio and open the properties of the SIMS database and go to the Options section. If it is, put it into SIMPLE mode. You can then right click the database and go to tasks and shrink the log file where you can release the used space. This should take it back down to around 1Mb in size.

  7. Thanks to Rawns from:

    Jobos (12th December 2012)

  8. #5

    JJonas's Avatar
    Join Date
    Jan 2008
    Location
    North Walsham, Norfolk
    Posts
    3,158
    Thank Post
    404
    Thanked 445 Times in 330 Posts
    Rep Power
    387
    ^ I can confirm this works its what I did to my Sophos logs yesterday

  9. #6

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    2,028
    Thank Post
    119
    Thanked 513 Times in 347 Posts
    Blog Entries
    2
    Rep Power
    288
    It's almost certainly a problem with your backups at some time. Maybe a while ago, but could still be current.

    Your log file will grow continuously until you back up your database. This is by design.

    If you use simple recovery mode, a backup will truncate the log file. This will allow the physical space the log file occupies to be re-used. It will not shrink the log file - it will continuously recycle the physical file space. You can shrink the log file from the SQL console. Don't shrink it to the minumum value, as it will almost instantly need to auto-grow, hitting the performance a little. This auto-grow will be 10% of the original value, which will soon not be enough, and will need another 10% auto-grow. Rinse and repeat. Far better to resize the log file to something like the same size as your database file.

    If you're OK with SQL queries, you can run this query which wlll tell you the status of your backups.

    Code:
    SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
    case
    when type='D' then '** FULL **'
    when type='I' then 'DIFFERENTIAL'
    when type='L' then 'LOG'
    end as Backup_Type,
    (STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
    ceiling(bkup.backup_size /1048576) as 'Size Meg' ,
    cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',
    server_name, sysdb.crdate
    ,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
    ,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
    as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn
    FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
    where backup_finish_date > DATEADD(DAY, -60, (getdate())) -- Last 60 days
    
    ORDER BY sysdb.name, bkup.backup_finish_date desc
    Last edited by jinnantonnixx; 12th December 2012 at 02:05 PM.

  10. 2 Thanks to jinnantonnixx:

    Jobos (12th December 2012), Rawns (12th December 2012)

  11. #7
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 491 Times in 375 Posts
    Rep Power
    0
    Quote Originally Posted by jinnantonnixx View Post
    It's almost certainly a problem with your backups at some time. Maybe a while ago, but could still be current.

    Your log file will grow continuously until you back up your database. This is by design.

    If you use simple recovery mode, a backup will truncate the log file. This will allow the physical space the log file occupies to be re-used. It will not shrink the log file - it will continuously recycle the physical file space. You can shrink the log file from the SQL console. Don't shrink it to the minumum value, as it will almost instantly need to auto-grow, hitting the performance a little. This auto-grow will be 10% of the original value, which will soon not be enough, and will need another 10% auto-grow. Rinse and repeat. Far better to resize the log file to something like the same size as your database file.

    If you're OK with SQL queries, you can run this query which wlll tell you the status of your backups.

    Code:
    SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
    case
    when type='D' then '** FULL **'
    when type='I' then 'DIFFERENTIAL'
    when type='L' then 'LOG'
    end as Backup_Type,
    (STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
    ceiling(bkup.backup_size /1048576) as 'Size Meg' ,
    cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',
    server_name, sysdb.crdate
    ,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
    ,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
    as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn
    FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
    where backup_finish_date > DATEADD(DAY, -60, (getdate())) -- Last 60 days
    
    ORDER BY sysdb.name, bkup.backup_finish_date desc
    Will this report on the status of your backups if they are taken with dbAttach?

  12. #8

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    2,028
    Thank Post
    119
    Thanked 513 Times in 347 Posts
    Blog Entries
    2
    Rep Power
    288
    Quote Originally Posted by Rawns View Post
    Will this report on the status of your backups if they are taken with dbAttach?
    dbattach should hook into the BACKUP routines of SQL, so yes, it will.

  13. #9
    Jobos's Avatar
    Join Date
    Apr 2007
    Posts
    1,153
    Thank Post
    184
    Thanked 52 Times in 45 Posts
    Rep Power
    26
    Thanks everyone!

    The recovery mode was set to full so now after a backup the .ldf is 1.5MB.

    @jinnantonnixx
    Where is the setting for the minimum size of log file?

  14. #10

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    2,028
    Thank Post
    119
    Thanked 513 Times in 347 Posts
    Blog Entries
    2
    Rep Power
    288
    Using SQL Server Manager, expand the 'Databases' tree, right-click your database and choose 'Properties'
    In the left column, select the 'Files' page, you'll see the see the settings for the files in use by your database, normally one for the database file and one for the log file. There's an 'initial size' field; here is where the size is set. Too small a value means that auto-grows frequently take place, this isn't ideal.

  15. Thanks to jinnantonnixx from:

    Jobos (14th December 2012)

  16. #11

    Geoff's Avatar
    Join Date
    Jun 2005
    Location
    Fylde, Lancs, UK.
    Posts
    11,819
    Thank Post
    110
    Thanked 588 Times in 509 Posts
    Blog Entries
    1
    Rep Power
    226
    Yeah this is basic MSSQL DBA stuff. I'm surprised that Sims doesn't sort this out itself though? I know Capita take a dim view of people prodding the Database directly.

  17. #12


    Join Date
    Dec 2005
    Location
    In the server room, with the lead pipe.
    Posts
    4,681
    Thank Post
    279
    Thanked 783 Times in 610 Posts
    Rep Power
    224
    Quote Originally Posted by Geoff View Post
    Yeah this is basic MSSQL DBA stuff. I'm surprised that Sims doesn't sort this out itself though? I know Capita take a dim view of people prodding the Database directly.
    SIMS does if you use the built-in backup routine. Most people don't use that if they've got full SQL, so the only time it might get triggered (and the log shrunk) is before an upgrade if someone isn't herding the database properly.

  18. #13

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,785
    Thank Post
    715
    Thanked 1,440 Times in 1,193 Posts
    Rep Power
    359
    If you wanted to stay FULL, to get the file to shrink, you need to run a transaction log backup, after which the shrink command works. Just a normal backup doesn't cut it.
    If you're staying SIMPLE mode, then you're good now.
    You should change the growth setting from x % to y MB, to prevent it going exponential! Not sure if that was clear from Jinn's post.
    In the old days, this was blamed on third party tools accessing the db, but they tend to behave themselves now.

  19. Thanks to vikpaw from:

    Jobos (14th December 2012)

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] SIMS LDF growing even though it's in SIMPLE mode?
    By Rawns in forum MIS Systems
    Replies: 19
    Last Post: 31st May 2012, 12:16 PM
  2. [SIMS] massive sim.ldf file - anyone else had there's suddenly grow?
    By SHimmer45 in forum MIS Systems
    Replies: 35
    Last Post: 14th December 2011, 10:34 AM
  3. SIMS Fault at logon on Citrix server
    By Shuriken1 in forum MIS Systems
    Replies: 2
    Last Post: 21st June 2010, 03:47 PM
  4. Replies: 7
    Last Post: 12th October 2008, 08:33 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
  •