May 19, 2010 at 9:44 am
is there best practice that I need to follow in order to manage the size of transaction log of one of the databases that I have configured log shipping for?
the log shipped db transaction log is configured for a transaction log backup every 5 minutes. but the size of transaction log is over 50 gigs now. how can I reclaim that space without messing up log shipping?
thanks
May 19, 2010 at 10:41 am
Do you have replication transactions that have not been published to a subscriber ?
May 19, 2010 at 10:45 am
Of that 50GB, how much free space is in the log?
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
May 19, 2010 at 11:20 am
I dont have any replication setup.
And 99% of the space in the transaction log file is free. But how do I reclaim it without messing up log shipping?
May 19, 2010 at 11:23 am
DBCC Shrinkfile should be fine.
The second part of this question is to find out what caused your log file to grow to 50GB. Some process ran away and caused all of that growth. You need to find that process and fix it - otherwise you will need to shrink the log file again sometime in the future.
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
May 19, 2010 at 11:30 am
Jason is right. Shrinking the log file will make the log file smaller in size. It is only suggested to shrink the log file to the Max size you might ever need it to grow else, you will be in the same situation sooner or later.
What Jason meant by finding out what caused this growth is very important and tried to be avoided. Something like Bulk delete / inserts / updates might have caused it, try finding those.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
May 19, 2010 at 11:43 am
Thanks Bru.
If you need help finding what is causing the log growth, you can check out my article on the topic.
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
May 19, 2010 at 11:45 am
will shrink not mess up the log shipping configuration?
May 19, 2010 at 11:48 am
I've done it before without repercussions.
Here is also somebody else who has done it
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
May 19, 2010 at 12:01 pm
Ok will go ahead and run this:
DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
lets see what happens..
May 19, 2010 at 4:06 pm
Another reason to find what caused the log file to blow out to 50GB is that if it happens again it will mess up your log shipping. It's reasonable to assume that whatever caused the blow out was a single transaction, which means once it starts you won't get the log backued up again until it completes. Then you will have a 50GB log to backup, copy and restore to the destination server. This could out your log shipping seriously behind.
As a rule of thumb the log file shouldn't be more than 10-20% of the size of the data.
Leo
Striving to provide a better service.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 19, 2010 at 4:51 pm
That is a very good reason!!
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
May 19, 2010 at 4:54 pm
There are no really rules about transaction log's sizing. It depends on too many factors. The best answer is the latest Paul Randal's Q&A publication in SQL Server Magazine: http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12625/Default.aspx
May 19, 2010 at 5:17 pm
magasvs (5/19/2010)
There are no really rules about transaction log's sizing. It depends on too many factors.
True, but it is good to have something to baseline against, that's why it's called a rule of thumb. If I had a log file regularly going over 20% of the data on a moderate to large DB, I would investigate and see why this is happening and if I could prevent it by doing things like more regular backups etc.
The basic reason for keeping the log size down being things like I mentioned with log shipping, but also database recovery times. If you have a single tran generating a 50GB log file, what is going to happen if the server goes down or the tran errors at 49.9GB of transactions and this now has to be rolled back? Your DB or parts of it won't be available for a while as one of my clients discovered when they had to roll back a 60GB log after the log drive filled up, and the db was in rollback for about 5 hours and generating masses of blocks. If they had set an upper limit on the log file the tran would have failed much earlier and rolled back in minutes and they could have fixed the query (and user) that caused the issue.
Having some figure to work with at least allows you to raise the alarm bells when the log file seems too large, even if you later decide the new size is acceptable in your situation.
Leo
Striving to provide a better service.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 19, 2010 at 5:30 pm
You can compare the time of the largest transaction logs' backup with index optimization jobs schedules. For some of my databases log grows dramatically during index rebuild tasks.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply