Do you have a transacation log file on SQL Server 2008 (in explorer is seen as the LDF file) that has grown beyond the desired size limit? Maybe you are attempting to issue a checkpoint command or a shrinkfile command, but it is not working to reduce the file size.
Possibly you are getting the following error: Cannot shrink log file 2 (<your_log_file_name>) because the logical log file located at the end of the file is in use.
The simple answer is your log file is full. Shrinking will require some free space in the log file to perform the operation. If you have already attempted to resolve this with the common instructions of changing the database from full recovery mode to simple recovery mode, but it hasn’t helped, then there is one more thing you may need to do.
When the recovery mode is set to simple, a checkpoint command can be issued that will reduce the transaction log and remove the definition of previous statements. There is something that will cause the transaction log to not self-maintain though, even in a simple recovery mode. You need to check if the database is waiting on a specific action before the log file can be reduced.
To find out if the database is waiting on an action, you can run the following query:
SELECT name, log_reuse_wait_desc FROM sys.DATABASES
Find your database in the list and see what the description says. If it says “NOTHING” then you should be OK to issue a checkpoint command. If it says “LOG_BACKUP” which is likely if the checkpoint command is not working then the database needs the transaction log backed up before it can reuse the space.
The basic answer is you need the transaction log backed up, but you may have already tried that. If you are receiving the error that a previous backup does not exist, then you will need to perform a full backup of your database.
If you have not already tried the log backup, the following command can quickly back it up for you to disk:
BACKUP LOG TO DISK = 'C:\temp\backup.bak'
After that is complete, you should be able to issue a checkpoint and then a shrink file. The following example will shrink the log file down to 50 MB
DBCC SHRINKFILE(, 50);
That should do it. The main key to remember if you’ve exhausted other resources is to perform a full backup of your database before trying anything else. This seems to be the only way to get the log file shrunk when it is full.