May 1, 2008 at 4:27 pm
I would like to know why our LOG file is so much larger than our log file using a previous MSDE database and whether our daily backup is effectively truncating it. I would also like to know if there is a way to determine the amount of data in the LOG file at any point.
I have our SQL 2005 Express database set up with the Full Recovery Model. It is scheduled to create a full backup every night using a stored procedure, DB_Backup. Initially there was no statement in the stored procedure to backup the LOG file.
The .mdf file is 706 MB. The .LDF file has grown to 63 MB. The database does not have much daily activity - may 100 added / changed / deleted records per day on average.
Here is the stored procedure:
- - - - -[font="System"]
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE .[DB_Backup]
@vcDbName varchar(128),
AS
declare @vcBackupDevice varchar(128)
declare @vcBasename varchar(32)
declare @vcDOW varchar(32)
SET @vcBaseName = 'Backup'
SET @vcBackupDevice = 'M:\backup\'+@vcDbName+@vcBaseName+'.bak'
BACKUP DATABASE @vcDbName TO DISK = @vcBackupDevice WITH INIT
BACKUP LOG @vcDbName WITH TRUNCATE_ONLY[/font]
- - - - -
Values specific to our database are:
MyDatabase
User
DB_Backup
M:\backup\
I added the last line because the LOG file never decrease in size after a full backup the way it did with MSDE, but the line seems to make no difference.
Is there a way I can check what's inside the LOG file to see if it is truly being truncated with each Full Backup? I tried copying it in order to ZIP it to see how much was really inside, but I can't copy an open file. Detaching the database seems like a hassle. I could shrink the log file, but read that shrinking on a regular basis is not advisable. I would like to be able to check LOG files for other databases without shrinking them to see if the backups are working properly on those databases.
Is there a way to confirm that the LOG file is "emptied" after each full backup?
Is there a way to determine how much is in the LOG file before a backup?
This is my first post here. Thank you.
May 1, 2008 at 8:48 pm
You can use the [font="Courier New"]DBCC SQLPERF(LOGSPACE)[/font] command to get a list of sizes + percent used for every database log on your system.
Log truncation does not shrink the physical file. You must use [font="Courier New"]DBCC SHRINKFILE[/font] if you want that to happen. Don't constantly shrink the log file - if it grows to a certain size (and not because of bad maintenance), leave it at that size. Otherwise, you end up fragmenting the file and there will be delays each time SQL Server has to grow the file while in the middle of doing something.
If you don't require point in time recovery, change the database recovery model to SIMPLE. This eliminates the need to do transaction log backups. Read BOL for more information on recovery models.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 1, 2008 at 9:50 pm
Scott has good information, but also remember a full backup does NOT remove data from the log. You need to do a log backup to clear the space and allow it to be reused.
May 2, 2008 at 8:39 am
Thank you both! DBCC SQLPERF(LOGSPACE) worked to show me how much was in the LOG file. And the WITH TRUNCATE_ONLY line is indeed needed to keep that amount from growing.
I have two more questions related to the LOG file.
1) I run a Full Backup script that includes:
[font="System"]BACKUP LOG @vcDbName WITH TRUNCATE_ONLY[/font]
The Log Space Used (%) dropped from about 14% to about 12%. That sounds like an appropriate difference based on the number of test transactions I performed between backups.
Why doesn't the Log Space Used (%) drop down to near zero?
PLEASE IGNORE THIS QUESTION #2 - I WILL START A NEW THREAD...
2) I would like to backup the LOG file every hour during the day and then run a full backup with the TRUNCATE_ONLY operation. My idea is to name the backup files: MyDatabaseLOG-08.bak where 08 is the hour.
After studying this Forum and other references, I have come up with this script:
[font="Courier New"]USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE .[DB_LogBackup]
@vcDbName varchar(128),
AS
declare @vcHour varchar(32)
SET @vcHour = DATEPART(hh, GETDATE())
BACKUP LOG @vcDbName TO DISK = 'M:\backup\'+@vcDbName+'LOG-'+@vcHour+'.bak'[/font]
My plan is to run this script every hour.
I am assuming that each LOG file backup will contain all the cumulative transactions since the last full backup and allow for point-in-time recovery of the database using just one LOG backup file (the one preceding an incident) and the last full backup file.
Am I on the right track? Any comments? Thank you very much.
May 2, 2008 at 11:07 am
Let's close this thread. I believe I am on the right track. There are a few bugs in my script, above, that I caught. But there is one that I haven't caught. I will [not] start a new thread because this is a separate issue. -- Well, I caught that issue, too. Here is the script that works:
[font="Courier New"]USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE .[DB_LogBackup]
@vcDbName varchar(128)
AS
declare @vcBackupDevice varchar(128)
declare @vcHour varchar(32)
SET @vcHour = DATEPART(hh, GETDATE())
SET @vcBackupDevice = 'M:\backup\'+@vcDbName+'LOG-'+@vcHour+'.bak'
BACKUP LOG @vcDbName TO DISK = @vcBackupDevice[/font]
Many thanks for the suggestions.
May 2, 2008 at 11:16 am
Feel free to start a new thread for new issues.
As to why the log doesn't drop to zero, there are virtual log files inside the physical file and you cannot truncate those if they are active.
I wouldn't use WITH TRUNCATE ONLY in a production server, FYI. You defeat the purpose of log backups.
Also, log backups are log backups. There is no full/diff for them. Full and diff are terms for data backups.
May 2, 2008 at 11:52 am
Got it. Don't use a BACKUP LOG WITH TRUNCATE_ONLY statement in a script for a full backup. That prevents you from running periodic LOG backups. If you don't choose to run periodic LOG backups, set the database to use the Simple Recovery Model, run full backups on a schedule, and forget about the LOG file. The LOG file's growth will be kept in check by the regular full backups.
So if I understand log files correctly using the Full Recovery Model, as soon as you back one up, all its transactions are incorporated into the main database file. If you want to do point-in-time recovery, you need to start by restoring the last full database backup and continue by restoring each of the, say, hourly LOG backups. Start with the first LOG backup after the full database backup and continue forward.
Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply