Working on SQL Server, ever seen this error message:
The transaction log for database 'mydatabase' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
When this error encountered you will not be able to do any updates to your database and will remain stuck. The point interesting here is that this troubleshooting information provided with this error message is of very less use.
Lets dig into this error to find the cause and solution for this problem.
Cause:
This error suggest that the transaction log is full. We should understand this differently. The disk space on the drive where this database is hosted is full and SQL server is not able to write into transaction log. This normally happen when the transaction log file grew much larger.
There are many options to solve this problem.
1. Quickest resolution for this is to clean-up
some disk space by deleting few unnecessary files (if you are able to find
anything). This is just a temporary solution and soon you might got hit with
this error again if you have not configured any clean up task in SQL Server.
2. The other way is to shirk the
database by simply by right click menu. Remember always,
a.
You
have to first take a backup of your transaction log for disaster recovery.
b.
You
have to detach the database prior to shrinking else it will throw an error.
This can also be done from right click menu
3.
The
third method is only recommended to use if you are not planning to recover your
database (normal in development environments). The method is to set the
database recovery model to simple. Can be done by following path in SQL Server.
Once the recovery model is set to simple you will be able to truncate existing transaction logs and they will not grow in future.
No comments:
Post a Comment