SQL Log question

anything I don't mind

anything I don't mind

anything I don't mind

Permabanned
Joined
28 Dec 2009
Posts
13,052
Location
london
I have a database in SQL 2012 sp1. It is 16gb in size.. The log file size is 36gb. I don't have any space issues on the sql server however i do on the backup server where the backups are being copied.

I like to keep two days of backups on the backup server that are copied to tape every day and goes off site. I also have a log backup running every two hours.

The question i have is around log truncation and backing up the log files. I notice that the log files never decrease in size, even though the logs are backed up every two hours, I don't think they are backed up with truncation because in side the maintenance plan sub plan for backing up logs there is no truncation options.

The thing is when this gets backed up it leads to about 100gb of space being used and i would like to avoid backing up 40gb of logs every day.

I assume this is because every time it does a full backup it then has to restart the log backup, because older backups are removed older than two days. this is why it has to always backup the whole 30+gb of log files?

Does this all seem normal to you? This is what the backup folder looks like.

pYPqVtsl.jpg.png

Ideally i would like to reduce the log file down in size but i don't think that is possible as it will just increase, i would also like to avoid having to backup 40gb everyday, how can i avoid big log backups and still have 2 hour restore time?
 
Armageus said:
Looks fairly normal to me.

The log contain anything that has happened since a full backup, and are rotated every 2 hours. The log files will not decrease in size.

Truncation is working, as it is removing everything older than 2 days (full and transaction logs).

If you were to remove say the transaction logs from 05/09/15, then you are limiting your recovery to a full backup from 05/09/15, or the next one at 06/09/15 - so losing the ability to recover to a specific point of time between those. (e.g. you know at 16:05 someone accidentally deleted all the records in a table - you would have to go back to the full backup - losing 16 hours of transactions)
Click to expand...

Is log truncation working though? the actual log file size (non backup log file) never changes 35gbyte.

I would expect the 100mbyte logs every two hours to exist, but what i did not expect was 36gb log file being backed up every day.
 
Yes there is a maintenance job that runs on a sunday at 2am it rebuilds and reorgs the tables and index. Then at 4am it updates statistics.

So if that was the case then by friday, i should no longer have a 36gb log backup in the backup folder, because after two days it clears out the old backups and logs. That is if the large log backup is caused by a maintenance tasks on a sunday. I will monitor the files this week and see if that is the case. As far as i am aware this 36gb log backup remains in the folder continuously. Once it gets removed the next log backup will re add it in. I guess what i could do is turn off the clean up task on the logs and end up with a realy old 36gb log backup in there and then only newer log backups every 2 hours, but i think this may lead to running out of disk space on the back up server.

I don't want to use shrink or reduce the log size down on the sql 2012 server because i have read that is not recommended if you have the space, which i do.

Based on other things i have read it says that the log backup should truncate the logs and result in the log file reducing in size, (without a shrink action). If that is not the case then ill just leave it.

I did once a while a go when it was on another sql server with low space, switch it to simple mode and did a shrink and then back to full and the log file just grew again to 36gb within a few days.

Also i just moved this database from sql 2005 to sql 2012, by doing a backup and restore. The log files then were created from scratch and were not moved across in anyway. This means that its just the size of the log file for that db and is optimal usage, ie it needs it be that big?
 
LFletcher said:
If it only retains 2 days worth of backup files, the large tlog backup file should be gone by Wednesday.

The next log backup shouldn't re-add as you aren't doing any maintenance apart from on Sundays. You only have a large tlog backup on Sunday as you are doing reindex jobs, etc and this is using the transaction log.
I wouldn't turn off the cleanup job as that isn't part of the issue.

The log backup does empty the log file, but it will not reduce the size of the file on disk. It's important to differentiate between how much space is being used in the log now versus how big you see it in the file system.
If you use 36GB on Sunday due to maintenance then assuming you have the space, that's what it will take up on disk. You run a tlog backup, the log empties, but you still use 36GB on disk. By the looks of your tlog backups, you rarely go over 100MB of data in a 2 hour period, so 99% of the time your tlog usage is 100MB or less.

Switching a database to SIMPLE mode just means that aren't logging your operations, so you don't have to manage backing up the log file. When you switch it back to FULL, you needed to start backing up the log, if not it will continue to grow until you have no space left.

Due to you maintenance work, yes your log needs to be 36GB in size.
I'd be looking into what actions are happening in your maintenance to work out why a 16GB database needs a 36GB log file.
Click to expand...

Ok thanks for your help. This explains it perfectly. What i was not realizing is the white space of the log file.

Its just a default rebuild of index and tables and then a default reorganise. I might have to speak to some DBA people who deal with that database to find out why its using so much.

We don't have any slowness with the DB, is it even worth doing a reindex every week? How could i start at looking at why maintenance is causing such a large log file?
 
I don't have dba but do have some guys in an off site support desk who know dba but they don't realy know the site and its not realy their job. I was doing the reindex and reorganise because that is what I thought I had to do.

At the moment the backup kicks off before the reindex. I'll take your advice and adjust the backups and the maintenance.
 
You were correct LFletcher, today the 36gb log file is not in the backup folder.

I have stopped the log backup between 12 and 04 and set it up to an additional backup on sunday's after the reindex before 4.

Thanks for your help.

I have not done a dbcheck since upgrading and i was reading up that it should be on run when you move to sql 2012 from 2005. So i may do that, just hope it doesn't have any issues as then ill have to try and deal with it.
 
Three databases that were moved as a part of the document management system (the london db being the main db of that dms) are still on sql 2005 compatibility level. All these are on torn_page_detection not checksum.

I did read that i should run dbcc checkdb with data_purity but i mentioned that to the guys who were assisting with the upgrade and db move and they said that they don't usually make that change because its not specified in the official instructions.
 
LFletcher said:
Official instructions? I assume this is a third party vendor application rather than one written in house?
Click to expand...

It is autonomy worksite.

Before the upgrade I was aware of the dbcc checkdb and data_purity recommendation when moving to sql 2012 compatibility level. So i mentioned it to the worksite team who specialise in that application. They looked in to it and said there was nothing from authonomy on this topic. I suggested that other DB for example BES (blackberry server) when upgrading you have to run a db app against the db that fixes its compatibility with 2012 and asked if there was any requirements like that for worksite. They said they were not aware of any and just left the DB on sql 2005 compatibility and did no checkdb. So i am bit weary about increasing the level to 2012 and running checkdb.
 
There is no compression being used on the backups as the option is not set to compress.

If it stops backing up the 35gb log every week with the change in config the space problems on the backup server is resolved.

I ran query

USE master
GO

DBCC SQLPERF(logspace)

It showed that 1.4% of 36gb is actually in use in the log file.
 
You must log in or register to reply here.
Back
Top Bottom