Another Back up Question

No. Only transaction log backups will truncate the transaction log. This is not the same as shrinking the transaction log. It simply marks space as available in the transaction log for reuse.

You may not have anyone working between 6:00 PM and 8:00 AM, but what other processes are running during the evening after hours? Are there processes that run at night for end of day processing? Do you have any database management processes running like index rebuilds? All of this gets logged? What about individuals who may be working late for what ever reason, reporting or getting extra work entered due to an unual amount of activity?

Okay, here is a scenario for you.

At 12:10 PM Wednesday afternoon your server crashes and corrrupts your database forcing a restore. You attempt to restore from the full backup you took Tuesday night, but the file is corrupt and can't be restored. Your copy is also corrupt. Because you truncated the transaction log prior to the Tuesday night full backup, you can't go back to Monday nights full backup and restore to 12:00 PM on Wednesday using the transaction log backups from both Tuesday and Wednesday up until the crash? Now what are you going to do? Tell your CEO that you just lost an entire days worth of data?

there are index re-org and rebuild happening as part of the back up maintinance plan. these happen before the full back up or the DB file

other than that nothing else. We are not a big organisation so while there might be the odd occurance of someone working late it is not common. Its a finacnce DB (NAV) and we work next to finance so would know if they were always working late

There is nothing else updating the DB.

The problem seems to be that there is a lot of free space with in the log. Other than doing a shrink Task what other options do i have to reduce it's size?

Unless you are having space issues on the disk, don't. If it has grown to that size, it has for a reason. Shrinking it will only make it grow again.

I always test restore db from full bk weekly. You talked about the IF and and made it worse. Why not assume the worst: the whole place blown up, your bk at offsite has only data a week ago. How much data you loose? If first t-log bk is corrupted, can you restore next t-log bk? 😉

To avoid all the IF you must restore immediately all full bk and t-log bk to make sure you have valid bk. Otherwise I don't have to work IF I won the jackpot.

One thing i didn't get an answer on is. When the full back up runs is it backing up both data files as the DB is spread over two files. One is a Primary filegroup and the other is Data filegroup 1

I ask as the the backup size has been the same size for the last 3 nights and the last 3 before that are the same

The back up size is 649mb

while the two data files on the server are 684mb and 106mb

i know the DB is being written to as its a finance system and would expect the back to be around the size of the 2 files added togehter. or have a got that wrong??

Cheers

IF the whole data center is blown up. Actually, for us it would be more like a plane crashed into the building - we would lose at most 56 hours of data based upon when the incidenat happens and where the daily incremental backups of all of our backed up databases are.

We are currently looking at replicating our backup shares to one of our other sites so we would have access in case of an incident in our data center.

I need to go back to your other statement that it is okay to truncate the log, just before you do a full backup because it is just a few minutes. If that is the case, then why do you need to truncate the log? I don't understand. As soon as you perform a new full backup, you have reinstated the log chain and all transactions are being captured. What is the purpose of forcibly breaking the log chain every night just before your nightly backup? That makes absolutely no sense whatsoever and is putting your organization at risk for no reason. I highly recommend that you read the article I link to in my signature.

A full backup will back up all data in all datafiles - unless you specifically run filegroup backups. Unless you have a very large database, I would not recommend filegroup backups.

Each data file is going to have space that is used, and space that is still free. The backup only backs up space that is used in the data file - so, between the two files you only have a total of 649mb used. That is normal and will only get larger as more data is added to the system.

You might not see a jump in size for the database for several weeks. It all depends upon how each page is filled, because all pages in the database that have data are backed up. If there is a page in the database with a single row on it - that page would be backed up. The next night, a new row is added to the page - the same page is backed up and the size won't change.

Unfortunately test loading a backup does not also guaranttee that when it is needed that it will still be good. Isn't Disaster Recovery Planning and Business Continuity Planning the essesnce of IF? If you don't plan for the event, what happens when it occurs?

I have been in this career field long enough to know that things can and will go wrong. I have seen brand new 9-track magnetic tapes go bad immediately after being used. Disk sectors on hard drives can go bad, and even with error correction, it is possible for files to become corrupt as a result.

By truncating the transaction log just prior to a full backup, you are placing your company at an unnecessary level of risk, even if you don't realize it. You really should read the article the Jefffrey Williams is recommending.

This is NOT always true. Couple of my SQL 2005 db's never go back to the begining of the log. The log usage is contantly above 95% and ate more space even I full bk nightly until I truncated the log.

take my advice: immediately restore all full bk and log bk at other sites to make sure all of your bk valid. Otherwise, a corrupted log bk will prevent you from restoring next log bk :w00t:

There are several reasons why transactions in the transaction log cannot be cleared. If the transaction cannot be cleared, then the log file will not be truncated during the backup and the log file will continue to grow.

Truncating the log at that point is not fixing the problem. It is avoiding the issue and putting your company at risk.

Two examples, there are others:

1) The database was setup with replication and the agent is not running. Until the agent runs, the transactions can't be cleared. Truncating the log breaks the log chain and breaks replication.

2) A process has an open transaction in that database. As long as there is an open transaction - the log cannot be truncated and reused. Again, breaking the log chain is not a way to solve this kind of problem.

There is no reason on 2005 or above to ever use that command.

The only way to control the size of the transaction log is by running transaction log backups. It may mean running them more frequently. Full backup and differential backups do not mark committed transaction in the transaction log as backed up. These two forms of backup simply backup enough of the t-log to allow for a consistant restore of the database corresponding to the end of the backup.

Even testing the backups with restores is no guaranttee. Why do you think I have multiple copies of our files? On an off-site server, to multiple backup tapes, even multiple servers just within the server room. If one file goes corrupt, I still have other copies to try.

DR/BC Planning is planning for something to go wrong and having something in place to mitigate the risks.

Why is it then then when i do a back up of the log that the back up is about the size of the log but the log has 98% of it's space free

Because SQL Server records the transactions in the transaction log and they are not cleared until you perform the backup. Right after you perform the backup, all data in the file has now been marked as reusable - so, it says it is 98% free.

Check the free amount right before a scheduled transaction log backup and you'll see that it is used. How much it is used is dependent upon how many transactions have occurred since the last backup.

Normal operations are those things that the end users perform. Daily activities that are initiated by the application. Maintenance operations are going to be those things you run - backups, update statistics, rebuild indexes, etc...

All types of operations are stored in the transaction log. Rebuilding indexes can store a lot of data in the transaction log, that is why you may see a very large transaction log, where during normal operations it is not needed.

There is no benefit to shrinking the file - since it is just going to grow again when you perform your maintenance operations. And, there is no real downside/problem to having a larger transaction log other than using the space on disk. Besides, that disk space should be dedicated to SQL Server and explicitly to the log files - so nothing else is going to use it anyways.