SIMS data files?

[mrhall]

Our Main data file is about 22Gb, and our log file is 158Gb.

 

Is this unusual?

 

Can we do anything to trim the log file down? Is this the log since the beginning of time?

 

Thanks

[Roberto]

Just to be clear, this log isn't a log in the normal non-database way of things. Do not just delete it, whatever else you do.

 

My first thought is that your database is in full recovery mode and you don't have the transaction log backups configured. Actually, the first question should be even more basic than that. How are you backing the server up, is it "SQL Aware" and is it correctly targetted to this database. I'm going to point you to this web page: https://dallasdbas.com/why-is-my-sql-log-file-huge/ - read the whole article and make sure you understand all of it before you do anything.

 

Lastly, be careful. If my comment about the logs in the first line of this reply is news to you then, and I can't emphasise this enough, get help from someone who does understand MSSQL databases (whoever supports your SIMs install maybe?) and don't try fixing this on your own.

Edited December 6, 2021 by Roberto

[DrCheese]

If an SQL log is that big then it indicates that the database hasn't been backed up properly for a long time. Make sure you run a backup that is application aware, eg knows how to deal with SQL databases.

[mrhall]

Absolutely! I wasn't going to delete... Just curious as to file size - will keep reading things before I do anything.

How large is your school's data file? Curious as to whether we've got a monster - or whether we're about normal!

[Koldov]

Judging by the responses so far it isn't 'normal'...

 

What size educational establishment are you at?

 

We are only a small school c.350 pupils, but if it's any good for reference:

 

SIMS.MDF = 1.98 GB

SIMS.LDF = 61.9 MB

 

This was installed by Capita and (I'm not in today, but from memory) runs a backup every night (scheduled task calls a .BAT file which calls a .SQL file) so I'm guessing that does the heavy lifting in reducing log file size as mentioned above.

 

Plus I have been known to run the Capita supplied 'ShrinkDBLog.bat' when I remember...

[jthompson]

Our Main data file is about 22Gb, and our log file is 158Gb.

 

Is this unusual?

 

Yes, but it should be easily rectified by proper backups.

Our SIMS mdf file is a little larger than yours (30GB) and the associated ldf file for it is about 2.5GB. The majority of that 30GB is due to InTouch, which we used to use for emailing parents. All of those InTouch messages are stored in a big fat table within the SIMS db (dreadful).

As others have mentioned, the size of the ldf file will be kept in check with regular SQL-aware backups. When performing a backup, SQL Server will incorporate the end result of all of those logged transactions back into the database, thus keeping the log file from growing endlessly. I suspect your SQL Server isn't being backed up regularly, as it should. It'll probably only be doing it whenever there's a SIMS update being installed, as SOLUS will initiate a db backup, but you want to make sure that you're doing that on a daily basis, really.

 

If you're expecting your current backup job(s) for that server to be doing SQL-aware backups, it'd be worth double-checking that. Veeam, for instance, can back up a server that happens to be running SQL, but will only execture proper SQL backups as a part of that if the job has the application-aware processing option turned on.

[mavhc]

The Log file is a list of transactions that have happened since the last backup, in case you need to restore from a corrupted database. Thus if it's very large, the system thinks there's been no backup

[howartp]

Absolutely! I wasn't going to delete... Just curious as to file size - will keep reading things before I do anything.

How large is your school's data file? Curious as to whether we've got a monster - or whether we're about normal!

 

We're 1800 pupils.

 

MDF is 25GB

LDF is 2GB