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.