How to troubleshoot SQL server Error 9002

-->

SQL server database mainly contains two files, one is data file & another is log file. Data file contains the information about data while log file contains the information about transactions in the database. In simple recovery model, log files are managed automatically but in case of bulk-logged & full recovery model database administrators are responsible to manage the size transaction log file. You can perform this by shrinking the transaction log file.

Sometimes, SQL server database generates an error message 9002 when it found insufficient log space. This error message will appear when SQL server database is online or in recovery mode. If database is online and struggling with log space problem then you can read the database but unable to do any update on it. If log fills during recovery then SQL server marks database in suspect mode. 

Space Full

-->

The log file for database '%.*ls' is full. Back up the transaction log for the database to free up some log space.

 

Work Around: As error message states, if the size of log file is full then immediately take the backup of database and issue some free space for the log file. Now the point comes in your mind, how to perform this. To do this follow the below given steps:

• Backup the transaction log.
• Issue some disk space.
• Move the log file to a disk drive (secondary storage media) with sufficient space.
• Enlarge the log file.

These are the possible user action required to perform the SQL server recovery. Detail description is given below:

Backup the transaction log: If you are using simple recovery model then backup of transaction log is not possible. Take the backup of transaction log file, if you are using bulk-logged or full recovery model.

Issue some disk space: Regardless of recovery models, freeing the disk space allow recovery system to enlarge the transaction log file automatically.

Move the log file to a disk drive: If you are unable to free some disk space to the transaction log file then move the log files to a disk drive with sufficient space.