System.Data.SqlClient.SqlException (0x80131904): The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The statement has been terminated.
I have found posts when people are updating records and a text field contains invalid characters. But it does not seem to be the case in this instance. The issue started towards the end of last week and has occurred every morning since (even weekends when no users are on). Once the issue occurs, every user gets the error when they try and update a record in Kinetic. you can still retrieve records?
We have a seperate SQL server to application servers and the only way to resolve the issue is to reboot SQL and the App servers. If you just reboort the app servers or recycle the app pools, Kinetic will not start. You get an error - Ice.Api.Exceptions.UnknownRestException: The service is unavailable (HTTP status code 503) .
We have disabled 3rd party backup software on the SQL server incase some sort of db locking was happening, but still got the error this morning.
How big is your log file and or disk space? Any chance you’re full and it cannot accept new entries?
If you weren’t updating/upgrading and the issue just started happening… this could be a possibility. Rebooting can clear temporary resource issues and get you going again, but it won’t fix an underlying issue if you’re running out of space.
It’s also possible you have a specific long-running transaction that’s preventing other “write” activities. So, rebooting would terminate that transaction and let others through. Perhaps its something that’s running every night and that’s why you see the issue in the morning… you reboot… and you’re fine until the next time that transaction occurs.
Any additional errors on the SQL Server Logs that may provide breadcrumbs to the underlying issue?
SQL log file is 250Gb, with almost 200Gb free disk space on drive. We saw some errors in the SQL logs re-backups, so have disabled the 3rd party software etc. Agree that it seems something that starts daily, but not finding what at the moment.
Recovery full, we have recently implemented commvault for full backup and transaction log backups. Would need to get full details of frequency. As I said we have disabled this and are currently just doing a full backup in the evening.
Yeah, a full backup is good to capture the whole database, but it doesn’t affect the transaction log. The transaction log backup is what allows the SQL server to truncate the log.
Especially if you’re in Full Recovery, your transaction log is recording every transaction and it’ll continue to grow until you do a log backup that truncates it.
But… sounds like you were doing that via commvault prior to the issue popping up, so, may not be the underlying problem.
Unfortunately I’m not sure what else to check other than to monitor for nightly long-running transactions. Hopefully someone else may have some ideas.
To resolve this issue, check if the size for the database log files are limited and check the free disk space of the database server disks in general. To check if the file size is limited, use Microsoft SQL Server Management Studio. Right click the affected database and check Properties - Files.
Did you check limits on your log size?
In any case maintaining such huge log does not do any good, find out how to truncate it.