June 25, 2013 at 2:25 am
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.
June 25, 2013 at 3:37 am
You have some transaction which uses T log. I would suggest SQL TRACE to see how much WRITEs each batch is taking. So that we can see what is happening in tlog.
http://msdn.microsoft.com/en-us/library/aa173882(v=sql.80).aspx
June 25, 2013 at 3:38 am
June 25, 2013 at 5:26 am
I guess that is my problem - interpreting the Profiler Trace that I did while the TLOG was growing unexpectedly.
Like I said, this application is idle most of the time and the Transaction log growth is a mystery to me..
over and over in the Trace file I am seeing these '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''
I am seeing mostly READS in the Trace and VERY few WRITES.
Can anyone tell me whast this is: N'myapplication.POL_SUPRES_ISS_IND' or N'N'myapplication.FREE_LK_INCR_CD' are these stored procedures that the Application is calling? because if it is, i cannot find it in the Database.
June 25, 2013 at 5:31 am
What you have there is near-impossible to interpret. It's executing a query that was prepared earlier with sp_prepexec (or maybe sp_prepare, can't recall details here). The "N'myapplication.POL_SUPRES_ISS_IND'" is simply a parameter being passed to the prepared query.
Maybe chat with your developers and ask what the app's doing that's so write-heavy?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2013 at 5:36 am
The Mystery is this is an out of the box App that I am told they are not using at the time.
Time to talk to the Vendor for the App? Not sure what else to do
June 25, 2013 at 6:53 am
could be kind of application logging that has been implemented and enabled.
Could it be an insert on a cursor-ed object (ado recordset insert) ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 26, 2013 at 12:59 am
Do you have any maintenance task for this database? If so track tlog during maintenance.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 26, 2013 at 2:59 am
Turns out that there is maintenance happening that is kicked off by the application. I have more digging to do but i believe it is loads and index maintenance - like I said, this is an out-of-the-box app.
I have the Database in full recovery mode with infrequent TLOG backups. any suggestions for what recovery mode would be better suited in order to curb the TLOG growth? At this time, point in time recovery is not important.
Also, what kind of trace template should i use to capture what might be causing the TLOG bloat? I'm assuminng they are calling a procedure that has very few commits - but I really can't be sure.
June 26, 2013 at 3:13 am
If you are absolutely certain you don't need point in time recovery, that restoring to the last full/diff backup is fine, simple recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2013 at 3:17 am
Thanks Gail. I will give simple recoery mode a shot.
any hints for how or what to monitor when i have them kick off their jobs again?
June 26, 2013 at 5:45 am
Fire up Profiler and capture all activity for a specific userid if you know what ID the app uses. If there is a lot of activity you could limit it to capturing anything that runs greater than 2 seconds and see what shows up. If not much then the next time put it down to 1 second. Be prepared for a large trace file.
June 26, 2013 at 5:58 am
Thank you!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply