March 9, 2015 at 6:32 am
Hi there,
On my SQL server I have a number of customer databases that are backupped every night. It's a full backup. The logfiles keep on growing and growing. For example, I have a 2 GB database, with a 20 GB log file. This continuous growing happens with most of my customer databases. By the way, these customers are contact centers, so they really do have a lot of transactions going on in the database.
But as far as I understood, after a full backup the data in the logfile has no use anymore and is overwritten by new logging data. Only when the unused-and-to-be-overwritten space in the logfile is exhausted, a new chunck of diskspace is allocated to the logfile. If this is the case, why do the logfiles keep growing? You would expect that the logfile at one point in time would hardly ever grow anymore.
Question: what must I do to prevent the log files from always growing?
March 9, 2015 at 6:56 am
Taking full backup of database wouldn't truncate the log, instead you have to take a log backup to truncate the log.
March 9, 2015 at 7:39 am
Read and learn 🙂 http://www.sqlservercentral.com/articles/Administration/64582/
March 9, 2015 at 7:48 am
It's all about the log backup and the recovery model. You can read about it here[/url].
"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
March 9, 2015 at 8:11 am
Hmm, yes I see that I was misinformed or sleeping during training.... probably the latter :doze:
I am going to buy the Professional Microsoft SQL Server 2014 Administration book; we already have a SQL 2014 AAG (2 servers) and I am about to use it in production, so first lets see what best practises are....
Thanks for the replies!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply