This article covers methods to resolve SQL Server Error 9002 for our customers. SQL Server Error 9002 happens when the SQL Transaction Log file becomes full or indicated the database is running out of space. A transaction log file increases until the log file utilizes all the available space in disk. A quick fix is to create more space for log file.
To Fix SQL Server Error 9002:
1. Backup Transaction Log File
Incase, SQL database that you are using is full or out of space, you should free the space. For this purpose, it is needed to create a backup of transaction log file immediately. Once the backup is created, the transaction log is truncated. If you do not take back up of log files, you can also use full or Bulk-Logged Recovery model to simple recovery model.
2. Free Disk Space
Generally, the transaction Log file is saved on the disk drive. So, you can free the disk space which contains Log file by deleting or moving other files on order to create some new space in the drive. The free space on disk will allow users to perform other task and resolve SQL Error Log 9002 The Transaction Log for Database is Full.
3. Move Log File to a Different Disk
If you are not able to free the space on a disk drive, then another option is to transfer the log file into a different disk. Make sure another disk in which you are going to transfer your log file, has enough space.
i. Execute sp_detach_db command to detach the database.
ii. Transfer the transaction log files to another disk.
iii. Now, attach the SQL database by running sp_attach_db command.
4. Enlarge Log File
If the sufficient space is available on the disk then you should increase the size of your log file. Because, the maximum size for a log file is considered as 2 TB per .ldf file.
To enlarge log file, there is an Autogrow option but if it is disabled, then you need to manually increase the log file size.
i. To increase log file size, you need to use the MODIFY FILE clause in ALTER DATABASE statement. Then define the particular SIZE and MAXSIZE.
ii. You can also add the log file to the specific SQL database. For this, use ADD FILE clause in ALTER DATABASE statement.
Then, add an additional .ldf file which allows to increase the log file.