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?
 
Not a SQL Server expert, but it looks like you are doing log backups every 2 hours, no? That's what is chewing up all the space.

My basic understanding of how I would generally set up SQL Server backups:

- An overnight full backup. This gets me back to last night without any mucking around.
- The logs aren't backed up unless there is some serious requirement to recover the database to that 2 hour mark in case the hardware failed catastrophically (and you lost everything, including the logs).
- The overnight full backup plus today's log files (not backed up, just the ones on the disk) allow me to restore to any point in time.
- The overnight full backup should truncate the logs, so they never grow beyond that 24h mark.
- If you get really worried about disk space, you can shrink the logs and the DB. The logs will grow as big as they ever needed to be (i.e. on the busiest day).
- Normally keep 3 days of full backups on disk (the disk backups get backed up to tape every night). This allows you to quickly restore without having to recall tapes.
 
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)
 
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.
 
anything I don't mind said:
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.
Click to expand...


That is reflective of the work happening at that time - e.g. do you have some kind of data import or tables being dropped and recreated at that time between 2 and 4?
 
I assume you have a ETL/EOD process or maintenance routine that runs on a Sunday between 00:00 and 04:00?

That's the time when the transaction log is utilising the space on disk.

Once you have backed it up, you will free up the transaction log, but it will not reclaim the space that has been allocated to it on disk.
You could reclaim the space by shrinking the file, but it's not really advised and unless you address the underlying reason why the space is being used it will grow to the same size the next week.

Depending on what is happening between 00:00 and 04:00, you may want to increase the transaction log backup frequency (i.e. every 5 minutes) as this will ensure you don't need a 36GB transaction log.

If you want to reduce the size of your backups and depending on what version of SQL Server you are running, you should look into Backup compression.
 
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?
 
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.
 
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?
 
Why are you doing a reindex and a reorganise? If you're doing a reindex you don't need to do a reorganise afterwards.

To be honest if you have DBA's they really should be managing this stuff for you.

Yes, don't turn off your maintenance. Make sure your reindex runs at least once a week. You should also be running checkdb before backups, etc.

If it was my system I'd turn off the tlog backup between 00:00 and 04:00 on Sunday as its pointless. I'd perform a second full (or diff) backup after the maintenance has finished (at 03:45 for example) and then let the tlog backups start again at 4. It won't fix the issue with the transaction log growing to 36GB, but it will reduce the size of your backup directory and you won't be backing it up.

For the log file size I'd check the indexes on the tables. Make sure that the tables have them and they are appropriate. There was a bug in SQL 2012 which doesn't reclaim space if you have tables without indexes. It's unlikely to be that, but it's worth checking.
 
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.
 
When you did the database upgrade did you change the compatibility level?
Also did you change page verification to checksum?

For your first dbcc checkdb after an upgrade you should also run it with data_purity.

In theory before every database backup you should be running a checkdb - otherwise you may be backing up a database with issues.
 
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.
 
If the database originated on a SQL 2005 server then the data_purity option isn't necessary.

I would ask the question as to why the database is left in compatibility mode (as you are essentially running a SQL 2005 database in a SQL 2012 wrapper, which is kind of pointless as you have no access to the new 2012 features), but wouldn't change it unless Autonomy can confirm that you can.

I would certainly be running a daily CheckDB though (or at worst a weekly one). It's part of basic database maintenance and no one should be advising against it.

This is worth a quick read, http://www.sqlskills.com/blogs/paul/importance-of-running-regular-consistency-checks/
 
We use https://ola.hallengren.com/ SQL Server Backup, Integrity Check, and Index and Statistics Maintenance. Its really good and we use it across all of our 200+ Servers. In its Standard form it creates SQL Agent jobs that you can schedule for your own needs, We happen to of gone down the route of a Central Management Server that Que's the Backups using Powershell to get the quickest backups we can but we still use the code part of the code we just execute the tsql manually. Scheduling Production First then QA etc next. Also Schedules Log Backups Based on criticality of the data from 1 Min intervals to 60Min with Diff Backups set accordingly to meat RPO at RTO.

Also the above "Maintenance plan" will rebuild or reorganize an index based on parameters you pass to it. Defaults set to 5 /30 as per MS recommendation. But it only touches the indexes it needs to.

Also i'm not 100% sure your using compression on your Backups, You Say a 16Gb Db ad Your Backups are 13Gb so seems your not but you could be storing incompressible data. This will make them take lass space and less time to do them at the slight expense of More CPU time. We Backup over 4Tb of Data Nightly across the server and Compress the lot. Test and see.
 
You must log in or register to reply here.
Back
Top Bottom