Backup Transaction Logs (incremental?)

Mindflux

I'm testing out the SQL Backup v5 trial. I like how quickly the full backup was performed on my DB!

I have in my SQLEM maintenance plans a backup that is performed hourly for transaction logs. This by nature is incremental (AFAIK). Would the same scheduled task in SQLBackup v5 be the same? I ran two transaction backups back to back and both were about the same size (500k). Just minutes apart the second one would seemingly be considerably smaller. Would I have needed to enable to erase transaction log entries checkbox to do what I was looking for?

Thank you!

Jason Cook

Hi,
When you perform a transaction log backup, it will record all transactions since the last log backup - regardless of if you have "truncated the log" (using the "Remove inactive entries from transaction log" option).

Your logic in that the second file should be smaller is correct, but this will only be apparent with larger files (25MB+), since with very small files the overhead of the backup container and associated log entries for the backup command will be the main factor - not the contents of the log file for non-backup related transactions.

The option "Remove inactive entries from transaction log" relates to the size of the transaction log file. If this option is selected it will remove any chunks of the transaction log that are now "inactive", i.e. all of the transactions have finished, and the backup task has archived them for future reference. This then allows the log to shrink in size, or frees up the space for future log use.

Therefore, leaving this option unchecked would cause the log to grow indefinitely, until such time that the log is truncated.

Hope that helps,
Jason

Mindflux

Jason,
Thank you. When I do 1 hr transaction log backups with SQLEM it is apprent that the size changes quite readily however. One hour it can be 100k, the next it can be 20k etc. This depends on the level of work being done but I can see by the size of our log when my users were most active on the database.

That's why I was confused because these were both very similar in size. Right now my log file is a bit out of whack, though. It's got a lot of inactive (empty) space according to SQLEM with very little data in it. I need to shrink it down which I guess remove inactive entries would do if I decided to tick that check box, right?

Jason Cook

Truncating the log / "Remove inactive entries from transaction log" will indeed remove the inactive entries, but it will not shrink the file size down.

If you want to physically shrink the size of the file(s) there are two options, one shrinks a single file (e.g. the log file), one shrinks all of the files in a database:

DBCC SHRINKDATABASE (http://msdn2.microsoft.com/en-us/library/ms190488.aspx)

DBCC SHRINKFILE (http://msdn2.microsoft.com/en-us/library/ms189493.aspx)

Due to the nature of the command, the CPU usage and I/O load can be noticable for a sustained period of time (which depends on the size of the files in question), so should only be executed during a period of low utilisation.

Hope that helps,
Jason

Mindflux

Ah yes. Forgive me, it's early morning here yet. I had my truncate and shrink mixed up.

Thanks again for the help. The backup test I did last night took a less than a third of the time the SQLEM backup took, so this is promising. The only thing I cannot seem to do with the GUI scheduling is use the 'copyto' function, I can use mirror.. but that will not allow me to copy to another attached drive (backup) on the system. can this only be done with the dos command or the stored procedure?

Jason Cook wrote:

Truncating the log / "Remove inactive entries from transaction log" will indeed remove the inactive entries, but it will not shrink the file size down.

If you want to physically shrink the size of the file(s) there are two options, one shrinks a single file (e.g. the log file), one shrinks all of the files in a database:

DBCC SHRINKDATABASE (http://msdn2.microsoft.com/en-us/library/ms190488.aspx)

DBCC SHRINKFILE (http://msdn2.microsoft.com/en-us/library/ms189493.aspx)

Due to the nature of the command, the CPU usage and I/O load can be noticable for a sustained period of time (which depends on the size of the files in question), so should only be executed during a period of low utilisation.

Hope that helps,
Jason

Jason Cook

In step 4 of the backup wizard (step 5 of the scheduling wizard) there is an option called "Copy backup to network", which is the GUI representation of the COPYTO keyword.

Jason

Mindflux

Jason Cook wrote:

In step 4 of the backup wizard (step 5 of the scheduling wizard) there is an option called "Copy backup to network", which is the GUI representation of the COPYTO keyword.

Jason

Yes but for whatever reason that does not allow me to select a local drive E drive in this case is a usb storage device attached to the SQL server. I assumed this was the mirror function and not the copy function.

Jason Cook

When you use the browser, at the top left where it says "Network Shares", you can change this to the local server, and backup to a local drive. There should be no technical restrictions to using a local drive.

To clarify the difference between the MIRRORFILE and COPYTO keywords, the mirroring occurs during the backup itself (so using a USB drive will slow the backup down), the copying occurs after the backup.

Jason

Mindflux

Jason Cook wrote:

When you use the browser, at the top left where it says "Network Shares", you can change this to the local server, and backup to a local drive. There should be no technical restrictions to using a local drive.

To clarify the difference between the MIRRORFILE and COPYTO keywords, the mirroring occurs during the backup itself (so using a USB drive will slow the backup down), the copying occurs after the backup.

Jason

Jason,
Yes I selected the local server however E: does not show up, but I can manually enter E:\path\to\backup so I'm going to give that a go.

Right now I use SQLEM to backup and then robocopy to copy to the E: drive as a second step in the process.

I appreciate your ZIPPY responses. What a company!