August 25, 2016 at 10:48 pm
my db size is 3 TB and is configured in log shipping . Log file of the primary database is full how to reduce the size of the log file .
August 26, 2016 at 12:31 am
This was removed by the editor as SPAM
August 26, 2016 at 2:15 am
If the log file is full, then reducing the size of the file should be the last thing that you want to do.
Full means no free space in the file, essentially either the file is too small for the normal workload, or something's preventing log reuse. In the first case, you want to increase the size of the file. In the second, you want to find out what's preventing log reuse and fix it.
In no case should you be trying to make the file smaller. To start, it won't work (the file is full), but more importantly it's completely the wrong approach.
"This 5-litre barrel is full, better replace it with a 2-litre barrel"
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
August 26, 2016 at 6:26 am
Please, please, please, listen to what Gail is saying.
If the log is full and you're doing log shipping, make darned sure that you're shipping the log often enough. If it's once an hour currently, maybe it needs to be very 1/2 hour. Examine this. Also, check your log backups. How frequently are they occurring? Maybe they need to be increased. Are there blocking or locking issues that are preventing the log from being properly truncated?
You've got a lot to worry about before you just reduce the size of the log. Please, again, listen to Gail.
"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
August 26, 2016 at 6:57 am
Grant Fritchey (8/26/2016)
Please, please, please, listen to what Gail is saying.If the log is full and you're doing log shipping, make darned sure that you're shipping the log often enough. If it's once an hour currently, maybe it needs to be very 1/2 hour. Examine this. Also, check your log backups. How frequently are they occurring? Maybe they need to be increased. Are there blocking or locking issues that are preventing the log from being properly truncated?
You've got a lot to worry about before you just reduce the size of the log. Please, again, listen to Gail.
+1
August 28, 2016 at 11:52 pm
you can also take the help of this link: http://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply