-
Title
"Error in Executing SQL Statement - Message: The transaction log for database 'MessageStats' is f -
Description
When trying to perform an action in the MessageStats console or while performing a gathering you receive an error message that includes the message "Error in Executing SQL Statement - Message: The transaction log for database 'MessageStats' is full"
For example while running a gathering you may recieve a similar error to the one below."The following error occurred while processing a data object for a given gathering: Error in Executing SQL Statement - Message: The transaction log fordatabase 'MessageStats' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases;; SQL Statement =INSERT INTO T_SubjectMsgDetailsSent( SubjectMsgDetailsSentGUID, TaskInstanceGUID, SearchCriteria, MessageID, [Date], Subject, SizeKBytes, Originator )VALUES........" -
Cause
In SQL Server the transaction log file is a fixed size file that starts out empty and fills up as transactions occur in the database. When creating a database along with the file location you can specify a minimum size to start the SQL Server transaction log file out with. This size is just the minimum starting point because as the database is used the transaction log will grow. If the transaction log file fills up and is not allowed to grow or can't grow due to disk free space no more transactions can be written to the database file.
The information provided here is known to work successfully; however, they have not been officially tested by Quest Software Quality Control. If any of these instructions are changed and/or incorrectly used, intentionally or unintentionally, this solution becomes unsupported by Quest Software Support and Development. Quest Software Support and Development recommend to always make a backup of the current database prior to execution of any script(s) or actions that may modify it.
For customization of the MessageStats, please contact our Professional Services Organization: http://www.quest.com/professional-services/ -
Resolution
Option 1
Change the recovery model of the messagestats database to simple to reuse transaction logs. Transactions are still written to the transaction log, but once the transaction is complete and the data has been written to the data file transaction is cleared from the log file. Since this space is reused there is not the ability to do a point in time recovery of the database. You will only be able to recover the database up to the point of the last full backup of the database.Step 1. Open Microsoft SQL Server Management Studio
Step 2. Connect to Database Engine on Server that contains the MessageStats database.
Step 3. Expand Databases
Step 4. Right click on the database name, click Properties.
Step 5. On the options tab click the recovery model drop down list and select simple
There should now be free space in the transaction logs.
Option 2
Backup Database then Backup the Transaction Logs and Truncate (Clear the log file after backup)
Step 1. Open Microsoft SQL Server Management Studio
Step 2. Connect to Database Engine on Server that contains the MessageStats database.
Step 3. Expand Databases
Step 4. Right click on your MessageStats database select Tasks | Backup
Step 5. On the General page for backup type drop down list select "Full"
Step 6 On the General page under the "Destination" heading review or change the location where the backup will be created
Step 7 Click "ok" to start backup
(Once backup is complete move to next step)
Step 8. Open Microsoft SQL Server Management Studio
Step 9. Connect to Database Engine on Server that contains the MessageStats database.
Step 10. Expand Databases
Step 11. Right click on your MessageStats database select Tasks | Backup
Step 12. On the General page for backup type drop down list select "Transaction Log"
Step 13 On the Options page under the "Transaction log" heading make sure "Truncate the transaction log is selected
Step 14 On the General page under the "Destination" heading review or change the location where the backup will be created
Step 15 Click "ok" to start backup
There should now be free space in the transaction logs. -
Additional Information
For more information, please see the Best Practices for maintaining the MessageStats SQL Database guide within the following Solution:
https://support.quest.com/SolutionDetail.aspx?id=SOL71563