+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
MIS Systems Thread, SIMS LDF growing even though it's in SIMPLE mode? in Technical; Is there any reason the SIMS log file would grow even though the database is in simple recovery mode? We ...
  1. #1
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0

    SIMS LDF growing even though it's in SIMPLE mode?

    Is there any reason the SIMS log file would grow even though the database is in simple recovery mode? We have a site where SQL is sucking up almost all of the 16Gb of memory on the server, and it also seems to be causing intermittent CPU spikes. I looked at it last week and the SIMS LDF file was over 1Gb so I freed up the space in the lolg file and it was back at a nice 1Mb. Performance seemed to improve again, but I'm checking it a week later and again, it's sucked up almost all the memory and the LDF file has grown to almost 3Gb and again, it's still in simple recovery mode! The CPU spikes look like they are back as well, and it's got me baffled! I know SQL has a tendency to grab as much memory as it needs but this server is a beast and I can't imagine that it's used so heavily that it needs all of the 16Gb!
    Last edited by Rawns; 30th May 2012 at 09:34 AM.

  2. #2

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,851
    Thank Post
    672
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    @jinnantonnixx probably knows all about this little known fact, but i think this may help: recovery mode = simple but ldf grows

    I'd say, look for what is needing so much space, perhaps some very complex actions, reports or something. You should be able to schedule a nightly shrink db command to help it out, and perhaps alter the growth rate. I'm not sure setting a NO grow option is helpful as i don't know what happens if there is no space, it might reject the transaction.

  3. #3

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,970
    Thank Post
    113
    Thanked 490 Times in 336 Posts
    Blog Entries
    2
    Rep Power
    283
    It's inextricably linked to your backup (or lack thereof).

    In simple mode (and all other modes), the log file grows with every transaction. When you run a backup, the log file is truncated and space within the physical log file is reused.

    Contrary to popular opinion, the log file is used even in simple mode. It cannot be used as part of a restore, but it's vital for the consistency of the database when the server comes back online after, say, a crash or power cut.

    Tell us how you backup - if you say stop SQL service and copy the files, I will come over and set fire to your trousers.
    Last edited by jinnantonnixx; 30th May 2012 at 04:42 PM.

  4. #4

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,970
    Thank Post
    113
    Thanked 490 Times in 336 Posts
    Blog Entries
    2
    Rep Power
    283
    Some succulent nuggets in this thread:

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

  5. Thanks to jinnantonnixx from:

    Rawns (31st May 2012)

  6. #5

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,970
    Thank Post
    113
    Thanked 490 Times in 336 Posts
    Blog Entries
    2
    Rep Power
    283
    SQL performance and memory usage is tricky to troubleshoot from the server's perfmon tool. Far better to use SQL's Activity Monitor. From SQL Server Manager, right-click your server in the object explorer tree view and choose Activity Monitor. Leave it for a few moments the look at the charts and tables to see if anything stands out.

    Look in the Data File I/O table and look for high values in MB/sec and response time.

    Also look in Processes and see if you can find anything with a 'Blocked by' - this indicates a delay caused by a contention.
    Last edited by jinnantonnixx; 30th May 2012 at 04:40 PM.

  7. Thanks to jinnantonnixx from:

    Rawns (31st May 2012)

  8. #6
    User3204's Avatar
    Join Date
    Aug 2006
    Location
    Wirral
    Posts
    769
    Thank Post
    55
    Thanked 66 Times in 62 Posts
    Rep Power
    34
    I dunno about you, but at this time of year all our clerical staff are all putting on the new year 7s for September.

    Plus we also have two years on internal exams, and this means we have about 140 cover lessons per day for a week.

    I had to go through the process last week, as mine had gotten up to 19gb LDF, while my MDF was only 6.5gb. I ran through all the usual stuff, and my MDF increased in size, but the LDF went down to 1mb, and in one week it's gotten back up to 7mb.

  9. #7
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Quote Originally Posted by jinnantonnixx View Post
    It's inextricably linked to your backup (or lack thereof).

    .....

    Tell us how you backup - if you say stop SQL service and copy the files, I will come over and set fire to your trousers.
    I assure you there is a backup mechanism in place. It's all scripted but essentially it's a SQL script to transfer all logins, dbAttach for the actual backup, then a utility to compress the bak files.

  10. #8
    Mcshammer_dj's Avatar
    Join Date
    Feb 2007
    Location
    Portsmouth
    Posts
    936
    Thank Post
    35
    Thanked 164 Times in 132 Posts
    Rep Power
    94
    do you use anything like PARS??

  11. #9
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Quote Originally Posted by Mcshammer_dj View Post
    do you use anything like PARS??
    Nope, the school use SIMS .net.

  12. #10

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,681
    Thank Post
    516
    Thanked 2,451 Times in 1,897 Posts
    Blog Entries
    24
    Rep Power
    832
    Can you post your backup script here? As @jinnantonnixx says, if you have a backup system in place it should be truncating the ldf file.

  13. #11

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,970
    Thank Post
    113
    Thanked 490 Times in 336 Posts
    Blog Entries
    2
    Rep Power
    283
    Use this bit of code to check the backup history for a particular database:

    Code:
    DECLARE @mydatabase as varchar(100)
    
    set @mydatabase = 'your database name goes here'
    
    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 MB' ,
    cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Size GB',
    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
    AND sysdb.name = @mydatabase
    ORDER BY sysdb.name, bkup.backup_finish_date desc
    I have my lighter at the ready....
    Last edited by jinnantonnixx; 31st May 2012 at 09:23 AM.

  14. 2 Thanks to jinnantonnixx:

    Rawns (31st May 2012), vikpaw (31st May 2012)

  15. #12
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Quote Originally Posted by jinnantonnixx View Post
    Use this bit of code to check the backup history for a particular database:

    Code:
    DECLARE @mydatabase as varchar(100)
    
    set @mydatabase = 'your database name goes here'
    
    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 MB' ,
    cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Size GB',
    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
    AND sysdb.name = @mydatabase
    ORDER BY sysdb.name, bkup.backup_finish_date desc
    I have my lighter at the ready....
    Thanks for this @jinnantonnixx. Just ran it and I can see every backup for the previous 60 days, and they are all full backups.
    @localzuk, first we transfer the logins:

    Code:
    osql -S%COMPUTERNAME%\SIMS2008 -Usa -P%SA_PASSWORD% -n -Q"exec %simsdb%.sims.db_p_transfer_login"
    ... then back up using DBAttach:

    Code:
    "%SIMSDrive%\%SQLPath%\Binn\dbattach.exe" /BACKUP /AUTO /SERVER=%COMPUTERNAME%\SIMS2008 /DATABASE=%simsdb% /USER=sa /PASSWORD=%SA_PASSWORD%
    Then the bak files are compressed with @matt40k's compression utility. It's how we do it in every school in the LA.

  16. #13

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,970
    Thank Post
    113
    Thanked 490 Times in 336 Posts
    Blog Entries
    2
    Rep Power
    283
    away from office now, on phone.
    next thing to check is how much of the log file is actually used.
    vik knows which script I mean.

  17. Thanks to jinnantonnixx from:

    Rawns (31st May 2012)

  18. #14

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,851
    Thank Post
    672
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    Quote Originally Posted by jinnantonnixx View Post
    away from office now, on phone.
    next thing to check is how much of the log file is actually used.
    vik knows which script I mean.
    I believe my esteemed colleague is referring to this:

    Code:
    use sims;
    
    dbcc showfilestats
    
    
    SELECT    Name, Filename,
    
    
    CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
    
    
    CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],
    
    
    CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]
    
    
    FROM dbo.sysfiles a (NOLOCK)
    You can get the same numbers if you right click on db, tasks, shrink files, then flip between the two files. But the code is more elegant.
    I'm not sure, but i think if you have a problem with shrinking ldf, then in addition to the normal backups you need to do a transaction log backup. This then allows it to be shrunk. However, this may be a Full Backup mode technique.

    I'm pretty sure in just Simple mode, I run the shrink file and it works.

  19. #15

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,970
    Thank Post
    113
    Thanked 490 Times in 336 Posts
    Blog Entries
    2
    Rep Power
    283
    That's the one. What stats do you get, @Rawns?

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 240
    Last Post: 30th June 2014, 05:43 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. Deleting Files - "File Not Found" even though it is there
    By enjay in forum Windows Server 2008 R2
    Replies: 5
    Last Post: 17th October 2011, 04:19 PM
  4. [SIMS] sims ldf file nearly 17 gig
    By edie209 in forum MIS Systems
    Replies: 11
    Last Post: 22nd November 2007, 10:30 PM
  5. Replies: 4
    Last Post: 9th January 2007, 04:03 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
  •