SQL Server : LogFile getting too big or not getting truncated!

There are so many times/ situations when you can find a SQL Server DBA (like myself) completely lost. A classical is: “My DB is STRANDED for a ridiculous reason – LogFile not truncating”. Its simple yet complex, for the fact that it can be avoided most of the times (alright, no design lectures here) – but it gets really tricky at times. And imagine a Prodcution site Manager staring at you while you are glaring your desktop screen with that remote DB Connection and scratching your head carrying those odd expressions…    

Well, here’s a step down approach which I generally consider for self (OK; for my troubled DB rather) when am really trying to “solve” the problem rather than taking a random toll to fix it:

A. DB Recovery Model
The issue can be because of transaction logFile (obviously). Things to checkout:
 - recovery mode of your DB (select name, recovery_model_desc from sys.databases)
 - if its FULL, have you ever taken transactional log backup. If not, better switch the DB to SIMPLE recovery mode (ALTER DATABASE DBName SET RECOVERY SIMPLE)
 - if its FULL and you have taken transactional log backup in past, then try looking up what is holding things up (select name, log_reuse_wait_desc from sys.databases;)

B. Long Running transactions
 - identify open transactions and respective SPIDs will be returned (DBCC OPENTRAN)
 - try to identify who is using those spid’s (exec sp_who2 <spid>) OR (SELECT * FROM sys.dm_exec_sessions WHERE session_id = <spid>;)
 - try to find whats running as part of these SPIDs and kill them if needed/ possible. Use “DBCC INPUTBUFFER(spid)” OR try the script below:

SELECT r.session_id, r.blocking_session_id, s.program_name, s.host_name, t.text
FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions s 
ON r.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE
s.is_user_process = 1 AND
r.session_id = SPID –FROM DBCC OPENTRAN

C. Backup
LogFile truncation cannot happen during the backup/restore operation. You can create a transaction log backup when full/differential backup is occuring. BUT, you cant truncate the logFile while doing so. 

D. Transactional Replication
With transactional replication, the inactive portion of the transaction log is not truncated until transactions have been replicated to the distributor.

E. Database Mirroring
Database mirroring is somewhat similar to transactional replication in that it requires that the transactions remain in the log until the record has been written to disk on the mirror server.

F. Disk Space
Might well be the reason that you are genuinely out of disk space
See if you want to add another logFile using (ALTER DATABASE DBName ADD LOG FILE;)
 

Once that you have truncated the logFile, you now will have to shrink the file to get it to manageable filesize.
- It can be done using EM (DB -> All Tasks -> Shrink -> LogFile) or commandLine by first identifying logFile name and then shrinking it:

select name from sys.database_files where type_desc =’LOG’; –List of logFile
DBCC SHRINKFILE (\’SalesHistory_Log\’, 1000) –shrinks the logFile to 1GB

NOTE: TRUNCATE_ONLY
One of the disastorous ways of dealing with issue and is not recommended by MS in SQLServer 2008 and onwards.
Doing so breaks the transaction log chain, which makes recovering to a point in time impossible because you have lost transactions that have occurred not only since the last transaction log backup but will not able to recovery any future transactions that occur until a differential or full database backup has been created.
BACKUP LOG SalesHistory WITH TRUNCATE_ONLY;
BACKUP LOG SalesHistory TO DISK = \’C:\Backup\SalesHistory.bak\’;  – to backup log to disk