Transaction Log growing in Sql Server

Transaction log in SQL Server is one of the most important parts of a SQL Server , as well as one of the most common generators of problems I see online.The following are the causes for transaction log growth,

 

1.Due to Uncommitted transactions. We can find if there are any open transactions using DBCC OPENTRAN.
2.Running DBCC REINDEX, CREATE INDEX operations with the database in Full Recovery model.
3.Running extremely large transactions like Bulk Insert, Select Into commands.
http://support.microsoft.com/kb/317375/

 

The following are the proactive measures in order to minimize the unexpected log file growth,

 

1.If you do not want point in time recovery of your databases then you can change the recovery model to Simple.
2.Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
3.Configure the automatic expansion of transaction log in terms of MB instead of %.
4.Backup the transaction log regularly to delete the inactive transactions in your transaction log if you are using full or bulk logged recovery model.
5.You can switch the recovery model to Bulk logged from full recovery model if you perform some bulk inserts, select into, bcp, alter index, create index commands because these operations will be minimally logged in bulk logged recovery model and after those operations are completed you can switch over to full recovery model.

 

Solutions

1. Take full backup of your database
2. Take t-log backup of your database frequently say every 30 or 15 minutes so that log file will not grow drastically
3. Shrink if you do not have any free space. You can perform this operation manually if required.
4. Generally avoid shrinking the database and keep it as the last option.

If you are in full recovery, then no it won't be truncated.run the following command:

 

SELECT DATABASEPROPERTYEX('Lorenzo347', 'RECOVERY')

If it returns FULL or BULK_LOGGED, then you will have to backup the log, either to disk or specifying the truncate only to get it truncate the space off.  If you do that, then you might as well change your recovery model to SIMPLE and be done with this problem.  If it says you are already in SIMPLE, then it should shrink without problem.

 

backup log <your database name> with truncate_only

 

Shrink Log files

 

DECLARE @LogFileName varchar(100)

SELECT @LogFileName = rtrim(name)

FROM dbo.sysfiles

WHERE Name like '%_log%'

dbcc SHRINKFILE(@LogFileName, 2)

Shrinks the log file  to 2 MB

http://msdn.microsoft.com/en-us/library/aa258824(SQL.80).aspx

Note:Detaching and deleting the log is definitely not advisable   At best it forces you to take your database offline. Worst case is that you invalidate your entire database and have to  restore from backup.