+ Post New Thread
Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 36
MIS Systems Thread, massive sim.ldf file - anyone else had there's suddenly grow? in Technical; I may be missing something here, but i thought that SIMS no longer uses the ldf and causes an increase, ...
  1. #16

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    I may be missing something here, but i thought that SIMS no longer uses the ldf and causes an increase, or is this still tied to the model.
    Mine is currently at 130MB and i don't think it increases much at all. It just cleans itself up and reuses the space. When i moved to my new server i fiddled around a lot, and probably had a lot of long conversations with the SQL Guru ---> @jinnantonnix

    Here's some stuff from my notes.

    @zag - A percentage increase is dangerous like @jinnantonnixx - better to make it a fixed size, the docs i've read, suggest an eighth of something, but i can't remember exactly how it was worked out. I remember working it out based on my 4GB mdf. I think it suggested that the ldf could be up to a quarter of the MDF but dont quote me on that, and the growth would be an eighth. So the DB is set to grow at 100MB. unrestricted and the LDF by 125MB unrestricted.

    If you use management studio, you can right click on the db and use the shrink command but best to do it out of hours and after a backup to be safe. If you choose shrink files, the next page in the wizard will allow you to view each file, it's size and the free space. I currently have a 4.5GB mdf with 20% free and the 130MB LDF with 80% free.

    Despite the free space, in order for the ldf to be truncated you need to have done a transaction log file backup first. Otherwise it just reports successfully but doesn't always reduce the size.

    We do the backups in Management studio only, and it's a scheduled nightly task. I don't routinely backup the logs. The files are then backed up by the Backup exec routine.

    We used to have a sql agent going in, but you have to be careful that it is your only backup system, we had multiple, and then what happens is, if your backup system is doing a grandfather strategy, with daily differentials / incrementals as ours was, any other backup especially a full one will break that differential routine and you can't recover from it. It's not so bad, if it just goes in and does a full backup, but you need to set it up specifically.

    I would like to back up more than once a day, or take the logs out, but find it's probably overkill, as mostly we couldn't afford to go back in time and lose any work, so all we'd do is restore an old backup and recover what work we could on a test system. Then bring back into live. Log shipping and fancy stuff, like playing back transactions just seems too complex for something we might not need to do. Save that job for a summer when there is nothing else planned!

    I do frequently restore the backups to a test server though.

    sorry, digressing a bit. I don't think the LDF should be growing, certainly not to above the size of the MDF. Do you people have third party software that is going in and accessing SIMS, or even a home developed system that is running a report against the server direct.

  2. #17

    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
    Hi Vik - guru? (blush), no I don't think so.

    The log file is always used by SQL server, no matter what database model you use. It's nothing to do with SIMS or any other app, it's the way the SQL server operates.
    Any change made by SIMS, for instance, is first written to the log file by the server. This is so that in the event of the cleaner pulling the socket out of the wall, when the SQL server starts up it will be able to check for uncommitted transaction stored in the log file. It scans through these and commits any un-committed logs into the database.)

    So, no matter what model of database you use, the log file is an integral part of the database system.

    There's some confusion over how the log file is 'used'. It's always used by SQL server, it needs it to maintain a consistent state. But in the case of backups, not so.
    In the case of a 'full' backup model, you can restore the database to a point in time by restoring the database then restoring all or some of the transactions in the log file, to any point in time. This is the most flexible system, but requires planning.
    In the case of the 'simple' database model, the log file is not (and cannot) to used as part of the restore - it is only used by the SQL server to maintain consistency and to recover its state in the event of a crash or power failure. The simple model only permits you restore a whole backup, any changes made after the last backup will be lost.


    Next, why does the log file get bigger? When the SQL server 'knows' that the database has been backed up, it can be sure that the items in the log file before that backup are not needed. After all, we have a backup of that database at that point. The log file is truncated, which means that the portion of disk previously used by the old transaction items can be re-used. A bit like a tape-loop. All being well, and backups running regularly, the log file doesn't grow; it is reused.
    If backups go wrong, the SQL server is unable to truncate the log file is it cannot guarantee that the data can be recovered - we haven't backed up the database, so it must keep the logs in case we need to commit the logs into a restored database to bring it up to date. Of course, the 'tape loop' concept fails as we run out of space, so it must extend the physical size of the file, hence the increase in log size.

    Be aware that truncating the log is not the same as shrinking the log. Truncating the log makes virtual markers in the log indicating that sections of the log file can be re-used. This doesn't resize the file. Shrinking the file actually reduces the physical space the file takes up by re-organising the log file and throwing away unused space. Unless something crazy has happened (as in @SHimmer45 's case) there's no need to bother shrinking the log file.
    Last edited by jinnantonnixx; 10th December 2011 at 10:46 AM.

  3. 2 Thanks to jinnantonnixx:

    vikpaw (10th December 2011), zag (12th December 2011)

  4. #18

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    Quote Originally Posted by jinnantonnixx View Post
    Hi Vik - guru? (blush), no I don't think so.
    ...
    I think your post proves otherwise! Thanks for correcting me again, i'm always interchanging shrink and truncate when i shouldn't be.
    I think my idea about SIMS no longer using it was from when it all moved to SQL 2008 as standard, as it handles log files differently right? I'm not sure exactly how, and i think they now recommend using Simple Mode. Trying to find the notes that mentioned it, but coming up with nothing at the moment.

  5. #19

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,121
    Thank Post
    403
    Thanked 619 Times in 566 Posts
    Rep Power
    180
    Our LDF file is around 40MB. Should I be concerned? The FMS LDF is about 180MB.

  6. #20

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    Quote Originally Posted by Edu-IT View Post
    Our LDF file is around 40MB. Should I be concerned? The FMS LDF is about 180MB.
    No 40 is fine, its more about how much it's growing. 180 for FMS seems big in comparison but it depends on how big the mdf is and what it's initial size is. So long as you're backing up regularly and keep an eye on it it should be fine. In management studio right click on the db, tasks, shrink, files and see how much is free, it's probably mostly empty. right click on db and properties will tell you what the initial size it, that might be the default.

  7. #21
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    We've had endless issues with this since the SQL2008 migration. The migration tools did not put the database's into simple mode after they were migrated not to mention some SIMS upgrades changing the recovery mode. Also the Capita Datafix patches do not work logically. It seem to put the database into simple mode, truncate the log file down to it's base size and then for some crazy reason only known to Capita, put's the databases back into full recovery mode meaning the log file will happily grow and grow again! I have a call logged with them to address this and the reworked patch is waiting the data managers approval. As this has been with him for a month or two now, I got fed up waiting so have put together a batch file to put the DB into simple mode and truncate it:

    Code:
    @echo off
    PushD %~dp0
    
    :: Put the SIMS database into SIMPLE recovery mode and truncate the log file
    ECHO ============================================================================
    ECHO Changing recovery mode for SIMS:
    sqlcmd -S%COMPUTERNAME%\SIMS2008 -Usa -P[password] -Q"ALTER DATABASE SIMS SET RECOVERY SIMPLE"
    sqlcmd -S%COMPUTERNAME%\SIMS2008 -Usa -P[password] -Q"USE [SIMS] DBCC SHRINKFILE (N'sims_log' , 0, TRUNCATEONLY) WITH NO_INFOMSGS"
    ECHO Done.
    ECHO ============================================================================
    ECHO. 
    
    :: Put the FMS database into SIMPLE recovery mode and truncate the log file
    ECHO ============================================================================
    ECHO Changing recovery mode for CCSFMS:
    sqlcmd -S%COMPUTERNAME%\SIMS2008 -Usa -P[password] -Q"ALTER DATABASE %FMSDB1% SET RECOVERY SIMPLE"
    sqlcmd -S%COMPUTERNAME%\SIMS2008 -Usa -P[password] -Q"USE [CCSFMS] DBCC SHRINKFILE (N'ccsfms_log' , 0, TRUNCATEONLY) WITH NO_INFOMSGS"
    ECHO Done.
    ECHO ============================================================================
    ECHO.
    
    :List the databases specified and output their recovery modes
    ECHO ============================================================================
    sqlcmd -S%COMPUTERNAME%\SIMS2008 -Usa -P[password] -Q"SELECT name AS [DB], recovery_model_desc AS [RM] FROM sys.databases WHERE (name='SIMS') OR (name='CCSFMS')"
    ECHO ============================================================================
    EXIT
    Feel free to use it if required, you can always copy the blocks of code and add any additional databases that may exist. You don't need management studio installed to run it either. You can always quickly run it after an upgrade/patch has been applied to ensure the databases are in simple mode as I'm sure a couple of patches have applied to different SIMS systems put the DB back into full recovery mode.
    Last edited by Rawns; 12th December 2011 at 09:16 AM.

  8. #22

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    I noticed that! One of their solutions was to switch recovery model, didn't realise it went back again!

  9. #23
    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 vikpaw View Post
    I noticed that! One of their solutions was to switch recovery model, didn't realise it went back again!
    I'm not sure the patch was always ran in that way either. I think it must have been changed a while back to put the databases back to full recovery mode, no idea when though. It's not ideal with schools that struggle with free space. the large log files also seem to affect performance too. We had one school report major slowdown and their SIMS LDF file had hit over 60Gb! Ran the script above and it shrunk down to 2Mb and performance was suddenly back to normal! We've also had odd reports of FMS instability and again, it's been down to the LDF file growing to a crazy size.

    As we're using CentraStage at our LA, once an upgrade has been applied, we can run this script against every SIMS server and within 10 minuets, we can be sure all the SIMS and FMS databases are still in simple mode.

  10. #24

    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
    If you're curious to see how much of your SQL files are actually in use, as well as the physical size on disk, you can run this command in a query window.


    Code:
    use [the name of your database]
    go
    
    SELECT	Name AS [Logical Filename], Filename AS [Physical Filename],
    
    CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Size on Disk (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)
    Interestingly, we can see that there is a logical file name (Name) and a physical file name (Filename) for the database files and the log files.

    It highlights a gotcha - the DBCC SHRINKFILE command (which @Rawns uses in his excellent scripts) take the logical SQL filename, instead of the physical filename. Normally it's the same, but watch out for it.
    Last edited by jinnantonnixx; 12th December 2011 at 12:20 PM.

  11. Thanks to jinnantonnixx from:

    Rawns (12th December 2011)

  12. #25
    CadlaM's Avatar
    Join Date
    May 2008
    Location
    Hertfordshire
    Posts
    55
    Thank Post
    10
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Always good to read what some of you guys write about SIMS. Happened across this thread by accident and looked at SIMS dbs. I have Options set to Full Recovery model! They were only in here at end of summer getting me on new version of SQL, so going to change that. BUT. as ldf is 1.8GB and 3 times size of data file, just not sure which order to do things? Obviously Backup first (done every night). Rec model first , to reduce file size, or Shrink?

    Then I'll tell you about my SOLUS 3 / firewall troubles :-(

  13. #26
    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 CadlaM View Post
    Always good to read what some of you guys write about SIMS. Happened across this thread by accident and looked at SIMS dbs. I have Options set to Full Recovery model! They were only in here at end of summer getting me on new version of SQL, so going to change that. BUT. as ldf is 1.8GB and 3 times size of data file, just not sure which order to do things? Obviously Backup first (done every night). Rec model first , to reduce file size, or Shrink?

    Then I'll tell you about my SOLUS 3 / firewall troubles :-(
    Changing the recovery model of the database won;t actually do anything to tour log file size. It will only stop it from growing further. I've also found that if you truncate the database without changing the recovery model to simple, it barely makes any difference to the size of the log file either. Therefore, I would backup your database first of all, change the recovery model to simple mode and then truncate the database either using SQL management studio or using the script I posted above.
    Last edited by Rawns; 13th December 2011 at 04:52 PM.

  14. Thanks to Rawns from:

    CadlaM (13th December 2011)

  15. #27
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    4,084
    Thank Post
    813
    Thanked 353 Times in 277 Posts
    Blog Entries
    60
    Rep Power
    280
    Our server filled it's hard disk up and the LA pointed to a file (didn't say which) growing huge as people weren't logging off overnight and not allowing SIMS to do it's cleaning up. Could that be the cause?

  16. #28
    CadlaM's Avatar
    Join Date
    May 2008
    Location
    Hertfordshire
    Posts
    55
    Thank Post
    10
    Thanked 1 Time in 1 Post
    Rep Power
    0

    Thumbs up

    Quote Originally Posted by Rawns View Post
    Changing the recovery model of the database won;t actually do anything to tour log file size. It will only stop it from growing further. I've also found that if you truncate the database without changing the recovery model to simple, it barely makes any difference to the size of the log file either. Therefore, I would backup your database first of all, change the recovery model to simple mode and then truncate the database either using SQL management studio or using the script I posted above.
    Thanks Rawns, and CAM too. Carried out the above and ldf is now down to "tiny". That's a technical term. File size reduced after another backup by SIMS system manager after the truncate. Don't get the problem of users not logging of, we use Ranger to log EVERYONE off. Hee Hee.

    Now, about Solus 3 and firewalls.....

  17. #29

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    Quote Originally Posted by CAM View Post
    Our server filled it's hard disk up and the LA pointed to a file (didn't say which) growing huge as people weren't logging off overnight and not allowing SIMS to do it's cleaning up. Could that be the cause?
    Yep it's usually the ldf. do as above and check the file, and maybe change recovery model, or just regularly backup and shrinkfile. I don't think SIMS does any 'cleaning up'. I have users staying logged in all the time, though am in Simple mode. The only issue with users logged in, is that they keep a connection open and the bloomin' homepage keeps making calls to the server. I guess in theory, this could always be sending transactions even during the backup period. EDIT: But they are only reports that are running, it's not like anyone is editing data. Or are they? I have had to set a rule that all users must be logged out by midnight, so if i need to, i can kick them off their computers and do any maintenance i want.

  18. #30

    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
    Hey Vik, I'm sure you have this script (or some variant), but this tells you who (or what) is active on the system.

    It's not the same as who's left their workstations on because the SIMS application creates an SQL connection on demand, then deletes that connection so it can never be 100% accurate, but it's something.

    It works on multi-hosted servers, too, provided your database names start with 'sims'. Change as needed.

    You can schedule it to make a 'naughty list' of who to blame when your upgrades go wrong.
    Code:
    use master; 
    select 	sysprocesses.loginame as [Logged-in user],	sysprocesses.hostname as [Machine name],	sysdatabases.name as [Database name] from sysprocesses 
    join sysdatabases on sysprocesses.dbid = sysdatabases.dbid where sysdatabases.name like 'sims%' order by sysdatabases.name
    Last edited by jinnantonnixx; 14th December 2011 at 09:25 AM.

  19. 2 Thanks to jinnantonnixx:

    fiza (30th January 2012), Rawns (14th December 2011)

SHARE:
+ Post New Thread
Page 2 of 3 FirstFirst 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
  •