SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

molly050308
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.
Use MYDB
GO
-- Perform a Full DB backup
--Perform a Transaction log backup
-- shrink the Transaction log file
DBCC SHRINKFILE(MYDB_LOG, 1)
-- backup and truncate log
BACKUP LOG MYDB TO DISK= 'NUL:'
-- shrink the Transaction log file
DBCC SHRINKFILE(MYDB_LOG, 1)
--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

3744 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000