June 4, 2015 at 7:27 am
hi,
Please help me to understand about the logfile growth.
My current db log is around 190GB ( on May30 th morning) suddenly a week ago it grown to 230 GB(May31st morning) and next day 280 GB(June 1st morning). Then it become constant there after
Our db has reorg job that reorgs every index everyday and we have transaction log backup for every 15 min.
We have replication set up in this env. This db is publisher db
Our replication failed on June 1st evening and we noticed June 2nd morning. Our subscriber server is not responded. So we restarted the subscriber
My doubts are
1) Is there any relation b/n this log file growth and replication failure
2) If I shrink down to again 190 GB, is it go back again to 280 GB in 1 or 2 days and will become constant?
June 4, 2015 at 7:34 am
ramana3327 (6/4/2015)
1) Is there any relation b/n this log file growth and replication failure
Probably not. Replication can cause log file growth if the distributor is down, the subscriber's status doesn't matter
2) If I shrink down to again 190 GB, is it go back again to 280 GB in 1 or 2 days and will become constant?
No clue whatsoever. To answer that question someone would have to know what caused the log to grow in the first place.
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 4, 2015 at 9:13 am
How to find out what caused the log to grow
Is there any way
June 4, 2015 at 1:01 pm
ramana3327 (6/4/2015)
How to find out what caused the log to growIs there any way
You can use extended events, server side trace, or some such to capture the cause - but not unless you already have that means in place.
Here are some links to material to help with that
http://bit.ly/1JoYwrH - Log Growing
XE version - http://bit.ly/FileSizeChange
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
June 4, 2015 at 1:05 pm
ramana3327 (6/4/2015)
How to find out what caused the log to growIs there any way
The log file grows because of DML activity in the database. It could be cause by someone having a large process that they ran and rolled back a few times. It could be caused by lots of writes. It could be caused by index reorganization; that's a fully-logged operation. Page splits are also fully-logged operations. The bottom line is that if the log file has grown, it did so because it needed the space at some point.
My question is about your daily index reorganization. Do you really need to do it every day? After you reorganize everything, do you update your statistics as well?
June 4, 2015 at 1:09 pm
Not way after the fact, no. Even immediately following the event it may be hard to know why it happened. You'd be better off setting up monitoring through extended evens to watch for log growth if you don't know what's happening on your system.
"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
June 4, 2015 at 1:51 pm
Grant Fritchey (6/4/2015)
Not way after the fact, no. Even immediately following the event it may be hard to know why it happened. You'd be better off setting up monitoring through extended evens to watch for log growth if you don't know what's happening on your system.
+10
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
June 4, 2015 at 3:34 pm
I think extended events are available in enterprise edition only. We are using standard
June 4, 2015 at 3:44 pm
Nope, you have access to extended events in SE.
June 4, 2015 at 5:21 pm
ramana3327 (6/4/2015)
I think extended events are available in enterprise edition only. We are using standard
Extended events are available in all versions of SQL Server.
"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
June 4, 2015 at 8:57 pm
ramana3327 (6/4/2015)
I think extended events are available in enterprise edition only. We are using standard
Others have answered the edition question already.
Is your version of SQL Server 2008, 2008R2, 2012, 2014 or something else?
Some of the Extended events are different for tracking database growths in the different releases of SQL Server. If you follow along the XE article I posted, you will see that I posted a 2008 and a 2012 version (different articles - just follow the links).
You definitely want to be using the correct XE event for the SQL Server version you are running.
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
June 7, 2015 at 3:23 pm
Thanks everyone
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply