January 11, 2011 at 8:52 am
Hi guys,
I am responsible for a database which is accessed via a web front end. The database forms part of a system provided by a third party vendor, so we have no access to source code etc.
The application is causing approximately 4.5gb of data per hour (was 2.5gb before a recent upgrade) to be generated in the transaction log and this is causing our transaction log backups volume to run out of space. Since the system is only in use from 9am - 5pm (approx) but the database is consistently generating 4.5gb transaction logs per hour, I would like to be able to provide information back to the vendor, explaining what the application is doing to cause this growth, with a view to having them correct it. Please note that there are no automated overnight imports into the DB which would explain this
I would like a way to analyse the data modifications to the database (inserts/updates/deletes) which are obviously taking place during none-business hours. My thoughts lean towards running a server side trace or perhaps using a third party auditing tool (would need to be a free one), but I would be very grateful if anyone could provide some suggestions on the best way to tackle this problem.
Many thanks in advance for your help.
January 11, 2011 at 9:04 am
A trace would be your best bet. I'd assume some sort of cursor client side with 1+ updates per row.
Unless you doubled the amount of transactions or activated an auditing system, there's no way the amount of transactions should double like that.
January 11, 2011 at 10:59 am
You can take a look at sys.dm_db_session_space_usage or sys.dm_db_task_space_usage. They might give you indications.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2011 at 11:21 am
If you have default trace active, this will certainly help you filtering what to trace.
Did we have recent autogrow? by Tibor Karaszi
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/06/19/did-we-have-recent-autogrow.aspx
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
January 11, 2011 at 1:08 pm
Thanks very much for the input guys. I'd forgotten all about default trace, although I read that very article a while back. I'll look into all your suggestions.
Kind regards
January 13, 2011 at 3:34 am
Thought you may like to know the outcome. The issue seems to be related to a recent photo module which has been added to the system, hence the BLOB data. Thanks again for your help.
My email to the project manager :
As you may be aware, the transaction log throughput for the XXXX database has almost doubled in size since the recent upgrade and even during non business hours, when the system is not in use, there is almost 4.5 GB of log data being generated. This has caused the combined 3 x days of transaction log backups to exceed the free space available on the database server and we are now in a position where we are only able to retain 1 x days worth of log backups. Obviously we could request that more space be added to the server, however considering the volume space has already been expanded to accommodate the increase in log size and given the high cost of SAN space, the sensible option would be to identify what is causing the growth and prevent it from occurring.
Last night between 11pm and 5am, I ran a trace on the server to capture any write events which occurred since these are responsible for transaction log activity. The statements which are causing the log growth all seem to be similar to the following and they occur very frequently.
begin tran declare @h int exec @h=sp_createorphan 'blob_data.blob_data_blob' WRITETEXT BULK blob_data.blob_data_blob @h WITH LOG
and
update blob_data set blob_data_blob ={fn getimage(79)} WHERE blob_data.blob_data_key =582 exec sp_droporphans commit tran
I am hoping that XXXX can make changes to the application to reduce the frequency of these statements which seem to be consuming resources on the server unnecessarily.
January 13, 2011 at 4:04 am
Did they develop/implement new code or has a part of the application been used that they didn't use previousley.
If it is a new dev part, they should have used the new var...(max) date types in stead of the old text/image ones.
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
January 13, 2011 at 4:17 am
ALZDBA (1/13/2011)
Did they develop/implement new code or has a part of the application been used that they didn't use previousley.If it is a new dev part, they should have used the new var...(max) date types in stead of the old text/image ones.
I agree. I suspect it's a new development but I'm not close enough to the project to know for sure. It's possible it's an existing module that they've bolted on.....but I doubt it.
It'll keep them in work in the future when the data types are deprecated and the system needs to be migrated to a newer platform or am I just being cynical? 😉
January 13, 2011 at 4:45 am
Nope, You're very serious with your observations :doze:
btw : deprecated stuff for sql2005 : http://msdn.microsoft.com/en-us/library/ms143729%28v=SQL.90%29.aspx
for the moment they still plan support for text/image with "Denali" :crazy:
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
January 13, 2011 at 4:48 am
ALZDBA (1/13/2011)
Nope, You're very serious with your observations :doze:btw : deprecated stuff for sql2005 : http://msdn.microsoft.com/en-us/library/ms143729%28v=SQL.90%29.aspx
for the moment they still plan support for text/image with "Denali" :crazy:
I passed the information on to the Project Manager for him to deal with as he sees fit 😉 Thanks for the heads up, I wouldn't have noticed 😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply