Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 unexplained Tlog growth
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 06/25/2013 :  04:40:09  Show Profile  Reply with Quote
SQL Server 2008R2

We have an application (on a DEV box) that I am told they are not using yet but the Transaction log for this Database grows in excess of 18GB everyday. Since this is a Dev box with limited resources, I am forced to shrink the log on an almost daily basis because of drive space issues.
-- Perform a Full DB backup
--Perform a Transaction log backup
-- shrink the Transaction log file
-- backup and truncate log
-- shrink the Transaction log file
--Perform a Full DB backup

Right now, I have change the recovery mode to simple because even with doing TLOG backups every 15 mins in Full Recovery Mode, the log can grow as much as 4 gb in just 15 mins. I will see how the DB and Tlog files handle being in simple recoery mode (how much growth)

One thing that I need some help analyzing is the Profiler Trace that I did while the transaction log was in a 'growth spurt'

there are 'RPC: Completed' messages in the profiler trace - one after he other - like this:
exec sp_execute 988,511,35230,990,N'myapplication.POL_SUPRES_ISS_IND',14,N''
exec sp_execute 988,511,35230,576,N'N'myapplication.FREE_LK_INCR_CD',14,N''
exec sp_execute 988,511,35230,662,N'N'myapplication.INIT_PAC_REDRW_IND',14,N''

These 'RPC' calls are being made from the application (hostname is app server name, login is sql login that was setup for the app to access the database)

Hope that's not too vague. Any help or guidance would be appreciated.

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 06/25/2013 :  09:16:45  Show Profile  Reply with Quote
Seems like you are doing the right things but there is lot of activity that require log file usage.

Theoretically at least, if you do frequent log back ups, any VLFs that can be cleared will be cleared, so even if the physical log file remains unchanged, it will reuse the space. So if you do frequent log backups then you should not have to shrink the log file.

The RPC completed messages look like they are adhoc queries run from the client applications. You might want to look at what those queries do, and run one in a controlled environment to see what the log growth characteristics are.

But, all of what I said above is if you need to use Full or Bulk-logged recovery models. Now that you have changed it to Simple recovery model, all of that is moot. If your log file is still growing, that just means there are long transactions that take up space.

You can run the following commands to see various aspects of the log file. In the DBCC loginfo command, the Status column indicates whether a VLF is in use (i.e., cannot be cleared) or not. 2 is in-use, 0 is not in use.
DBCC SQLPERF(logspace) 
DBCC loginfo
select name,log_reuse_wait_desc from sys.databases
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000