April 10, 2018 at 3:52 am
Today we have received issue that one of the log file in a database is grown up ot 1.2 TB and the database is configured in transactional replication . how can we shrink the log file wit out effecting the replication or down time ?
Thanks
April 10, 2018 at 5:03 am
First, why is the log so big? Shrinking it is just a matter of issuing the command. It shouldn't take your server down. However, you need to know why the log is that large. Has replication failed? Is replication running so slowly that transactions aren't being cleared? Are you in full recovery and not running log backups? Fix the issue first, then shrink the log.
"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
April 10, 2018 at 6:51 am
the database is in full recovery and we have log backup for every 30 mins
April 10, 2018 at 7:13 am
ramyours2003 - Tuesday, April 10, 2018 6:51 AMthe database is in full recovery and we have log backup for every 30 mins
And the log backups haven't failed? And replication is still online? And you don't have excessive waits? Maybe before shrinking the log, you need to take log backups more frequently. Again, you can just issue the shrink command and if the log is clear (transactions have transmitted through replication and been marked as such), then it will shrink. You don't have to take anything offline. However, that's not the problem. The problem is, why is your log so large. You need to focus there, not on the shrink.
"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
April 10, 2018 at 10:29 am
How old is the oldest transaction? Sounds like you might have a long-ago task that couldn't complete for some reason. Remember, with replication, SQL can't reuse any part of the log until all uses of that log, including replication, have completed.
Run these command on the db to see if the log is being "held" by SQL.
DBCC OPENTRAN
SELECT log_reuse_wait_desc, * FROM sys.databases WHERE name = 'your_db_name'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply