[Last Call] Learn how to a build a cloud-first strategyRegister Now


Log Getting Filled in SQL Server

Posted on 2010-01-02
Medium Priority
Last Modified: 2012-05-08
How to find which session/job making log file getting filled from long time?
What is the best way to SHRINk log file?
What is the value/size i have to specify to shrink log file optimally?
Question by:parpaa
LVL 60

Expert Comment

ID: 26162205
LVL 13

Expert Comment

ID: 26162229
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/

Expert Comment

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


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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  


Author Comment

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

Author Comment

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


The DB is in full recovery model.

Could any one explain me in clear abt this

Author Comment

ID: 26162625
Can i give this option to free up the log file

LVL 13

Expert Comment

ID: 26162763
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


Author Comment

ID: 26162820

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

Expert Comment

ID: 26162892
How full is the log file right now? Within that database run this:
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]
sys.master_files AS s
(s.type = 1 and s.database_id = db_id())

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.

Author Comment

ID: 26162950
Output for above Script.

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

Accepted Solution

MikeWalsh earned 1000 total points
ID: 26162960
So that is telling me your log file is pretty much "empty". I don't know why it grew but it was likely some or more of the causes mentioned in my post above or discussed on my blog post category I linked a few above.

I would say shrink the log and grow it to a size that is appropriate with the VLFs in mind from that SQLSkills post and watch it closely over time to see when it starts growing.

Out of curiosity, before shrinking and regrowing, how many rows does DBCC LOGINFO in that database return? Still do the shrink and regrow with the VLFs in mind but just shoot out the answer when and if you get a second.

Author Comment

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question