December 8, 2016 at 10:04 am
Hi,
I am backing up the transaction log via a maintenance plan every 15 minutes on my secondary high avail server.
It works and creates trn files every 15 minutes.
My problem is that on my primary server - the log file never seems to get smaller only larger. It is as if I am not backing up the log.
I must be missing something...
December 11, 2016 at 2:17 pm
Log backups don't make the file smaller. They mark the space in the file as reusable.
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
December 12, 2016 at 12:21 pm
GilaMonster (12/11/2016)
Log backups don't make the file smaller. They mark the space in the file as reusable.
But if his log is getting physically larger then something is taking up the space in the file.
Once he completes a log backup then yes, the space in the file is marked as reusable but that won't make the file bigger unless a process needs more space than is available.
@OP: Can you run DBCC SQLPERF(LOGSPACE) and post the results here please?
Regards
Kev
December 13, 2016 at 11:20 am
enable auto shrink option for the database ,it will shrink the log file if you are willing to pay price with fragmentation in the database. WHEN DOES AUTOSHRINK OCCOUR
•Both data file and log files can be automatically shrunk. AUTO_SHRINK reduces the size of the transaction log only if the database is set to SIMPLE recovery model or IF THE LOG IS BACKED UP. When set to OFF, the database files are not automatically shrunk during periodic checks for unused space.
•The AUTO_SHRINK option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is larger.
• You cannot shrink a read only database
December 13, 2016 at 11:31 am
goher2000 (12/13/2016)
enable auto shrink option for the database ,it will shrink the log file if you are willing to pay price with fragmentation in the database. WHEN DOES AUTOSHRINK OCCOUR•Both data file and log files can be automatically shrunk. AUTO_SHRINK reduces the size of the transaction log only if the database is set to SIMPLE recovery model or IF THE LOG IS BACKED UP. When set to OFF, the database files are not automatically shrunk during periodic checks for unused space.
•The AUTO_SHRINK option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is larger.
• You cannot shrink a read only database
I'll strongly recommend that you NOT do this.
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2007/03/28/turn-auto_shrink-off/
http://www.sqlskills.com/blogs/paul/auto-shrink-turn-it-off/ (We'll give Paul two articles on the subject in this list)
Size the log appropriately for your workload. There's little point shrinking it down, only to have it grow out to the size needed by your workload, shrink it down again, grow out again, etc.
That's a lot of wasted resource utilization and delay for the workload.
Cheers!
December 13, 2016 at 12:22 pm
I concur, its out of the frying pan in to the fire.
December 14, 2016 at 9:05 am
krypto69 (12/8/2016)
Hi,I am backing up the transaction log via a maintenance plan every 15 minutes on my secondary high avail server.
It works and creates trn files every 15 minutes.
My problem is that on my primary server - the log file never seems to get smaller only larger. It is as if I am not backing up the log.
I must be missing something...
What Gail said really, along with check for long running transactions holding the log space. Also suspended data movements either at the primary or secondary level will cause the transactions to be held in the log until synced to all partners, so check this carefully too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 14, 2016 at 9:14 pm
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply