Error: DBMS error code: 9002, The transaction log for database is full
If a connection attempt is made into an ArcSDE SQL Server database in which the transaction log is full, the following error occurs:
"Failed to connect to database. Operation Failed."
Upon further review of either the direct connect log file or the ArcSDE service log file, the following error can be seen:
"DBMS error code: 9002 The transaction log for database is full."
Every ArcSDE SQL Server database consists of two files, an .mdf file and .ldf file. The .mdf file is the primary data file and is the starting point of the database and points to the other files in the database. The .ldf is the log file which holds all of the log information used to recover the database. All database modifications are written to this log file prior to these modifications are implemented within the database, thus allowing the database to roll back or restore transactions in the event of either an application error or hardware failure.
When a transaction log file grows until the log file uses all the available disk space and cannot expand any more, data modification operations can no longer be performed on the database.
Solution or Workaround
In order to address this issue, please consider the following:
The size of the SQL Server transaction log can be monitored using the following command, executed in a new query window:
This command can be used to monitor the amount of space used in a transaction log and provides transaction log space usage statistics for all databases, displaying the current size of the transaction log and the percentage of log space used for each database.
It is always recommended to take full backup of all your databases and consult your database Administrator before following the given suggestions.
- Perform a Transaction log backup; once completed, the transaction log is truncated.
- Free disk space so that the transaction log can grow automatically.
- Increase the size of the transaction log; this only works if there is sufficient space available on disk.
Please review the following links for additional suggestions,
Troubleshooting a Full Transaction Log
Recover from a full transaction log in a SQL Server database
To prevent encountering a full transaction log in the future on an ArcSDE database, please review the related articles which discuss Esri’s recommendations for setting up SQL Server Backups and the three different recovery models for SQL Server.