April 2, 2014 at 4:42 am
Hi Team,
We are using SQL 2008 R2 database for SAP Application. With in this We are maintaining DR server for this as distributed servers for both Application and Database. Normally the log grows 50-100 MB for every 30min , but in each month last week each log is generating 3 to 4GB file. Due to this huge file there was lots of delay in log shipping to DR.
We confirm that there is no extra work work was running from application users. The DB growth shows normal as earlier but there is lots of variance in log growth for every month. May I know what causes this excessive log growth.
We would happy to provide the logs if needed. Appreciate your quick help in advance.
Thank you
Regards
Raghu
April 2, 2014 at 5:23 am
Does your maintenance run at this time?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 2, 2014 at 8:09 am
Is there a specific time frame during each day when the logs are larger or is every log backup larger?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2014 at 9:21 am
1. Create daily/hourly job that will record log total and used size to some table.
2. For several days, constantly run server-side trace which will capture processes with duration > xx min/sec. Periodically load trace file into a table. If you concern about performance overhead, you can run extended event session instead.
3. For those days/hours when your log growth large, find from trace table which processes were running at that time-frames which were not present at other times.
April 2, 2014 at 1:25 pm
HI,
Normally every month from date 1st to 23th the log is generating normally 50 - 100 MB.
From 24th - 28th Each log is generating upo 3 to 4GB. After 28th the behaviour will be normal as earlier. For more information i've attached ERROR LOG to this post.
Regards
Raghu
April 2, 2014 at 1:29 pm
K. There is some process that is running that starts on the 24th (possibly month end). You need to identify the process. But since it is a constant each month, it is a scheduled process it would seem.
To find what is causing the excess growth, you could adapt this article to your needs.
http://www.sqlservercentral.com/articles/Log+growth/69476/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply