February 23, 2017 at 3:47 am
I take transaction log back up ever 30 minutes - but every day at about 05:30 the back up takes 01:30 and increases each day. Can anyone help.
February 23, 2017 at 3:57 am
It looks as though you've got some other big job going between 05:00 & 05:30 - is there an index rebuild, or a data load happening every day at about that time?
Thomas Rushton
blog: https://thelonedba.wordpress.com
February 23, 2017 at 4:18 am
Thanks for the reply – the issue is the Server is hosted externally and they are trying to say it’s due to the size of our tables. But I’ve checked all our linked servers and extracts and they are all finished well before 05:00. They have a weekly index rebuild but not a daily one.
So I assume it has to be something they are doing internally.
February 23, 2017 at 5:02 am
Log backups aren't affected by the size of tables, just to activity.
Is the 05:30 backup also larger than other log backups, or just slower?
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
February 23, 2017 at 6:07 am
Thanks for all your help.
The normal log bak ups takes around 3 seconds - 15k in size.
The one at 05:30 takes 2.5 minutes and is 1.5GB in size.
The issue is the Web app that uses the SQL DB times out around that time.
The Server is hosted of site - they are saying our tables are to large - but i am saying the must have
some processes running at that time.
February 23, 2017 at 6:27 am
For the log backup at that time to be so large, there must have been some large operation running between 05:00 and 05:30. Log backup sizes are not directly affected by table size, but are determined by the amount of modifications done to the DB
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
February 23, 2017 at 6:33 am
T-log will only hold info on things that happen to your database, such as indexing, Insert/Updates/deletes, etc. If your host is doing things to your database...run away. highly unlikely.
Set up a server-side trace on a schedule and run it from 4:30 to 5:30. Assuming you have checked all of your own jobs to verify there is not some unknown process doing large data updates or processing you are not aware of.
An update to a row will be in the t-log, but the table size will not change the number of rows...
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
February 23, 2017 at 7:08 am
Cool - Thanks guys - This is why this site is rated so highly.
You have given me a few things to ponder !!!!
I'll try what you suggested.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply