The problem looks like the MSSQL can not write a new transaction log when its trying to rollover and you probably have some where in the properties set to shutdown the MSSQL server once the logs can not rolled to to a new file once the the old log reach the maximum size
I would check to see if some of the SQL processes or exe are blocked by
or may you could try to add some of the SQL processes like sqlsrvr.exe to the trusted application policy I assume
Sorry, but I don't believe the transaction log ever rolls to another file. It normally re-uses space in the same file (can be unrestricted growth too). MSSQL server is stable and we've not seen any events of that shutting down.
HIPS is not enabled currently so we can rule that out too.
Sorry I mistaked it with trace logs apology for that
Each time the file grows, that new
chunk of space will be placed wherever the OS can find room for it, most
likely NOT at the end of the existing file.
I would recommend:
1. Move the log file onto a seperate volume from the database
2. Physically defrag the log file and the database file - you'll have
to shut down SQL to do this
3. Change the growth rate from 10% to some fixed size. As the log file
grows, that 10% chunk will get bigger and bigger, taking longer and
longer to create.
4. Establish a regular backup job to do transaction log backups, this
will help keep the log file from growing.
From what you describe it looks like there is an open transaction which is causing the transaction log to grow continiously. You can look at the Activity Monitor and check the list of open connections. Usually it would be one of the sql query / stored proc running for a long time -
I would advise opening a support case (if you have not already) to troubleshoot the root cause.
This problem started when attempting a shrink of the database file (it was 50% free space). This gave an error and afterwards we started having issues with the transaction log's growth. It would grow based on the auto-grow increment until it reached a certain size, around 2.6GB and stop--then the McAfee service for ePO Application Server would crash.
I implemented a fix similar to what allamiro described. Shrinking the transaction log by itself and then capping it at 1GB. It can no longer reach 2.6GB and so the Application Server does not crash. This will limit our ability for recovery, but has fixed the service crashing issue.
We are using the simple recovery model, so I've never had to do transaction log backups in the past (whereas for full recovery model, it is a necessity). I will definitely take your advice and look at the Activity Monitor to see what is causing the problem. Can I simply remove that transaction which is causing the log to explode?
The transaction log will grow as long as there is an open transaction - no matter which recovery model is in use. Until the open transaction is committed, the transaction log will not be truncated -
if the transaction log is set to a fixed size and SQL needs to grow the log, it will cause further logging to fail - so not a very good idea in my opinion.
Killing the transaction will usually help - but not recommended if you dont know what the implications of doing that are. That is why its better to have this investigated.
Yeah the only problem we have now is:
"The transaction log for database '<db_name>' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases" which appears in the Windows Application Event Log for MSSQLSERVER.
Isn't there a way to keep the transaction log at a certain size and have it just reuse space in there for the more recent transactions while truncating the old ones? We keep a close eye on our ePO and would only need to rollback the database 1 day at most (if ever; havent ever yet needed to).
I can understand that best practice would be to unrestrict growth and do regular log backups, however our ePO database size is well maintained and therefore the log shouldnt be growing so rapidly. I will look at the Activity Monitor and see what transaction is causing the rapid growth when unrestricted size.
I realized that this is, although related to ePO, mostly a SQL problem and I thank McAfee for any assistance.