Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
molly050308
Starting Member
1 Post |
 Posted - 2013-06-25 : 04:40:09
|
| SQL Server 2008R2We 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 MYDBGO-- 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 backupRight 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-25 : 09:16:45
|
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 loginfoselect name,log_reuse_wait_desc from sys.databases |
 |
|
|
|
|
|
|
|
unexplained Tlog growth