SQL SERVER Transaction log full

Question

How to free Transaction log space in SQL SERVER 2005:

I have Transaction log size = 70 GB and there four transaction logs 1,2,3,4 in different drives. Through DBCC SQLPERF(LOGSPACE) I found that the transaction log is Full (uses 100 %) and I want to free up the space in transaction log and I don't want T-log backup. I don't have space to backup the transaction log. And my DB is in Replication state.

1. How can I free up the Transaction log or
2. Instead of 3 transaction log files, can I have only one log file or
3. If shrink method what to do with Replication?

Answer 1

Perform the following sequence of statements:

BACKUP LOG <db_name> WITH TRUNCATE_ONLY  
--or save to log to other drives on disk if required
GO

CHECKPOINT
GO

--replace 2 with your actual log file number.
DBCC SHRINKFILE (2, 100)     

If the log does not shrink then check the reason of log file growth. More information about this can be found here:

http://sqlreality.com/blog/ms-sql-server-2008/troubleshooting-the-full-transaction-log-problem/

Answer 2

Will shrinking the files individually accomplish what you want?

DBCC SHRINKFILE (N'LogFile', 1)

Answer 3

It was already discussed about the recovery model switch on replicated databases here. and you can read some documentation as well on MSDN.

If you don't want to make T-log backups because of space restraints you can put the replicated database in Simple recovery model and try the:

Perform a CHECKPOINT. The database in the SIMPLE recovery mode clears THE LOG out on each checkpoint.

DBCC SHRINKFILE (N'LogFileName', 1) -- shrink the log file to 1 MB 

After that change it back to Full. In order to remove some of your DB's log files you first have to empty the ones meant for removal and then remove them. Details here.

Answer 4

Check the following article from Microsoft.

http://msdn.microsoft.com/en-us/library/ms175495.aspx

and i want to free up the space in Transaction log and I dont want T-log backup

MS does not support this.

Transaction log backup IS NEEDED HERE. Get a drive, do it.

If you don't do it, you can not free the log.

Without freeing the log you can not shrink the files. Ergo, you need to make a backup, whether you want to or not.

If you don't care about the transaction logs the databases should have been created in simple recovery mode to start with.