May 23, 2012 at 5:22 am
Hello Everybody,
I have SQL 2008 R2 server and a production database that was in simple recovery mode. A few weeks ago I changed the recovery mode to full and setup the following script to backup a transaction log every 15 minutes:
BACKUP LOG Production
TO DISK = "H:\TransactionLog\Production_Log.trn"
WITH DESCRIPTION = 'Log Backup for Production'
GO
I also configured a nightly backup maintenance plan that would take a full backup of the database for me. Right now my log is almost 200GB and I don't understand why it would grow if I'm taking full backups every night. I have read hundreds of posts about log backup/truncate/shrink but can not find the right solution.
Since this database already had a log file associated with it, do I have to add another log file to the database? Or does SQL knows that my Production_Log.trn log file is associated with Production database?
I ran the following code to find out and I don't see my .trn file in the list of log files
SELECT name
FROM sys.master_files
WHERE database_id = db_id()
AND type = 1
How do I get my log file under control? Any ideas would be greatly appriciated.
May 23, 2012 at 5:31 am
.TRN usually means its a transaction log backup file, not the actual log file used in the database, this has the file extension .LDF.
If every log backup is running the same command (below).....
BACKUP LOG Production
TO DISK = "H:\TransactionLog\Production_Log.trn"
WITH DESCRIPTION = 'Log Backup for Production'
GO
..... then you are actually adding a new logical file into the physical file on each run of the command, and if you are never purging the logical files from the physical files, it will grow and grow.
Take a look at the backup syntax here as you will need to build in a RETAINDAYS command so that it purges after X amount of days.
May 23, 2012 at 5:40 am
If your database was created using the defaults, then it's in Full Recovery. This means you need to backup the log on a regular basis, at least once an hour depending on your system, maybe even as often as once every 5-10 minutes. The log backups have no direct connection to full backups and a full backup doesn't affect the log. Here's a blog post I wrote on this topic[/url] that may offer more help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 23, 2012 at 5:50 am
if I set retainday=2 then my transaction log will commit changes to the database and shrink to default size? Will I still be able to restore point-in-time or just to the recent full backup?
May 23, 2012 at 5:56 am
Your confusing the TRN and LDF, they are NOT the same thing
TRN = Transaction Log BACKUP
LDF = Transaction Log
How big is the LDF file for the Production database? dbcc sqlperf(logspace)
Also no, performing a log backup will not shrink the LDF file.
Removing files from the TRN will cause the file to decrease in size as you are purging logical files from the physical file.
You can still restore to a point in time as long as the BAK file and TRN files are in sync with each other, putting retaindays=2 will mean that you cannot restore to a point in time 2 days ago, but can for anything < 2 days ago
May 23, 2012 at 6:07 am
anthony.green (5/23/2012)
Your confusing the TRN and LDF, they are NOT the same thingTRN = Transaction Log BACKUP
LDF = Transaction Log
How big is the LDF file for the Production database? dbcc sqlperf(logspace)
Also no, performing a log backup will not shrink the LDF file.
Removing files from the TRN will cause the file to decrease in size as you are purging logical files from the physical file.
You can still restore to a point in time as long as the BAK file and TRN files are in sync with each other, putting retaindays=2 will mean that you cannot restore to a point in time 2 days ago, but can for anything < 2 days ago
ldf file is only 688MB. Will this purge starting from the next trn backup?
BACKUP LOG Production
TO DISK = "H:\TransactionLog\Production_Log.trn"
WITH RETAINDAYS=1
WITH DESCRIPTION = 'Log Backup for Production'
GO
May 23, 2012 at 6:08 am
Yes, and might take some time depending on how many logical files are within the physical file
May 23, 2012 at 6:13 am
Ok I will find out tomorrow. Thank you so much for all of your help!
May 23, 2012 at 7:03 am
actually after remembering, you need to use the INIT command as well, which will allow the media set to be overwritten so it will mess up your PIT Restores.
your best off creating a new TRN file each time the jobs runs rather than sticking them in one big file, or get it to increment the date stamp on the file every day.
take a look at Ola's scripts in my signature, he has some really good solutions.
May 23, 2012 at 7:16 am
That makes sense. Is it not a default switch when doing a backup? I need this 200GB trn file to shrink before I do any further changes. What's the benefit of using multiple trn files? Also, did I setup transaction log backup wrong from the start? What is best practice in doing this type of backup?
Also, why would I need to increment the date stamp on my trn file? What's the syntax of INIT command?
Would it look like this?
BACKUP LOG Production
TO DISK = "H:\TransactionLog\Production_Log.trn" WITH INIT, RETAINDAYS=1, DESCRIPTION = 'Log Backup Production'
GO
May 23, 2012 at 7:21 am
No you didnt set it up wrong, its just you said to constaintly backup to the same file so it keeps adding each TX Log backup to the same file which makes it grow and grow and there is no nice way to purge a backup set.
The only way to make that file shrink is to issue a backup with INIT to purge all the contents, but that will break your log backup chain and will require you to take a full backup to ensure you can do restores with transaction logs.
May 23, 2012 at 8:59 am
Now I'm getting this error
Executed as user: NT AUTHORITY\SYSTEM. The medium on device 'H:\TransactionLog\Production_Log.trn' expires on May 24 2012 10:00:01:000AM and cannot be overwritten. [SQLSTATE 42000] (Error 4030) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
May 23, 2012 at 9:27 am
Do something like this:
DECLARE @diskPath VARCHAR(150)
SET @diskPath = 'C:\TransactionLog\Production_Log'+ CAST(YEAR(GETDATE()) AS CHAR(4)) + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + CAST(DAY(GETDATE()) AS VARCHAR(2)) + '.trn'
--PRINT @diskPath
BACKUP LOG production
TO DISK = @diskPath
WITH RETAINDAYS=1,
DESCRIPTION = 'Log Backup for Production'
GO
But add in minutes or time to the file name. Then it is unique each time. You run this, take a full backup, and then delete your previous file. Then use this to backup your logs.
Jared
CE - Microsoft
May 23, 2012 at 10:26 am
so add the following?
CAST(hour(GETDATE()) AS CHAR(4)) + CAST(minute(GETDATE()) AS CHAR(4))
and i would have to manage multiple files? or are they going to delete themselves?
May 23, 2012 at 10:32 am
In this case, you would have multiple files. Personally, I would then schedule another job or within the same job write code to delete old ones .
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply