Log Getting Filled in SQL Server

http://sqlservernation.com/blogs/admin/archive/2009/05/17/why-is-my-log-file-so-big.aspx

It is likely filling up for one of a few common reasons:

1.) You are in full recovery mode but not taking log backups
2.) You are taking those backups but not frequently enough
3.) You are in simple mode but the log files are growing because of large batch inserts/updates/deletes that don't allow the log to truncate behind the scenes automatically.

My guess is it is 1.) or 2.)

The real solution is to understand recovery models and how the log file works and then work out solutions that last long term.

Shrinking the file just means it has to grow again if you don't address the cause and these growths are expensive because no transaction can be considered committed until it can be written to the log file.

Once you get it under control, consider shrinking it and then growing it to an optimal size for future growth and use, keeping maintenance tasks, etc. in mind.

I blogged a bit about shrinking and transactions in a series here: http://www.straightpathsql.com/archives/category/shrinking-transactions/

To find open sessions that may be filling the log file use the sys.dm_exec_sessions;

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

You would probably be looking for a session that's been 'Running' for some time, use the 'status' column to find running sessions.

Can i get which DML statement is causing this situation using sys.dm_exec_session?

I got my T log size 155 GB...While shrinking the file what would be the best size to be shrinked?

DBCC SHRINKFILE('DB_Tlog',??)

The DB is in full recovery model.

Could any one explain me in clear abt this

Can i give this option to free up the log file

DBCC SHRINKFILE('DB_log',0,TRUNCATEONLY)

Parpaa -

Please check out the provided links... Again in FULL RECOVERY mode you are telling SQL Server you want to keep a record of all transactions in your transaction log UNTIL you do a LOG backup. If you are simply doing full backups each night and not doing regular log backups, your log file will just keep growing and growing and growing until you are out of space.

So I would look at what your business recovery needs are. Are you okay goin to the last good nightly backup? Then switch to SIMPLE recovery mode. Do you need to recover to a specific point in time? Then choose FULL mode but do regular LOG BACKUPs.

If you need to keep your log backups because you want to do a point in time recovery you will have to first backup your log. If you don't need to keep your log backups I would switch to SIMPLE and manually issue a BACKUP LOG 'databaseName' with truncate_only. Then do a shrink to bring the file down to empty. A DBCC SHRINKFILE will work in thise case.

I would then grow your log file to an appropriate size and number of VLFs. Please check out the links given and this link to understand the VLF issue: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

MikeWalsh

We are taking Log backups for every 15 min.  but still the log file size is still increasing.

How full is the log file right now? Within that database run this:
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.master_files AS s
WHERE
(s.type = 1 and s.database_id = db_id())
ORDER BY
[ID] ASC

what are the results of above?


If you are doing log backups every 15 minutes then you either have long running transactions (DBCC OPENTRAN show any results?), You are doing very large operations in single transactions or that growth happened during weekend maintenance like index rebuilds/etc. and the space is not actually used in the file (it has been truncated since the last log backup) but the file was once that size and SQL doesn't automatically reclaim that empty space.

Let's see what your free space in the log file looks like and then go from there but if it is mostly empty, consider looking at that link from the SQLSkills blog two posts above and shrink the file then grow it in small chunks.

But remember if it had to get to this size it is either back when log backups weren't being done (if there were such a time?) or some activity at some point (batch inserts over the weekend, db maintnenace, etc.) and it may need to grow to that size again. It is best to keep a file preallocated to the eventual size you expect it to work out to.

Output for above Script.

3PPVC_log      M:\SQL1_LOGS\3PPVC_log.ldf      186666880      992760      2

I found that this is on DELETE Operation performing long time since yesterday.
It would be the reason my Log got increased.