Log file backup growth (how long's a piece of string...)

Question

Log file backup growth (how long's a piece of string...)

Beatrix Kiddo

SSC-Dedicated

Points: 32407
More actions
September 16, 2013 at 5:13 am

#395489

If your log backup file was as follows:
2,176 KB at 10 this morning
10,617 KB at 11 this morning
18,615 KB at 12 midday
would that seem like an unusual amount of growth to you? (I know approx 8 MB an hour might not seem like a lot, and it's early days yet, but I wondered what others thought.)
It has just been switched to Full recovery model, with the log being backed up every hour. Do the figures above suggest that it needs to be backed up much more frequently than that? (The database is about 58 GB.)
(2008 R2 Standard Edition)

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 1

It does depend on the amount of transactions that are happening within the tables / applications.

I would suggest that the T-LOGs are backed up every hour or even every 2 hours. the full backup should be done once a day.

But all of this does depend on your disaster recovery plan and system availability. A 58gb database is not large in SQL terms - but is the data ALL required ? - could you archive any old data and therefore reduce the size of the database ?

Do you do any other maintenance ? Truncate tables / shrink tables/databases ? to return free space back to the system ?

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 2

... and at 1 pm it was 31,795 KB - a fairly big jump, no?

Thanks for the reply. The log file is backed up every hour, and the database is backed up daily. The database is fairly new and all the data is needed, but there are read-only filegroups.

Right now there's no maintenance plan in place, which is why I'm concentrating on it now. I know that the indexes need an overhaul, but I was sidetracked by watching the growth of the log file. I do think this is the week of the month when it would have the most activity, but should I be backing up the log file more frequently than this?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What is the exact command you're using to back up the log?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 4

BACKUP LOG 'databasename'

TO DISK='E:\path\databasename_LOG_BACKUP.bak'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

No WITH options?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 6

Beatrix Kiddo

SSC-Dedicated

Points: 32407

September 16, 2013 at 7:25 am

#1650434

No WITH options currently.

Thanks.

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 7

Maybe worth reading this..

http://technet.microsoft.com/en-us/library/ms186865.aspx

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Try running this:

RESTORE HEADERONLY FROM DISK = 'd:\bu\mm.bak' ;

But on your log backup. I'll bet you you're stacking the files.

"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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 9

When I run that I get 7 rows back. Is that the sort of thing you suspected?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Yes. You're appending the log backups all to the same file. Hence each time you take a log backup, the file gets bigger by the size of the log backup that just ran.

Not a good idea. Write your backup statement so that each time the backup runs it backs up to a new file, easiest way is to stamp the date and time into the file name.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 11

Beatrix Kiddo (9/16/2013)
When I run that I get 7 rows back. Is that the sort of thing you suspected?
Thanks

Yep. It's as Gail says.

"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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 12

Brilliant, thank you. I must confess I woke up in the middle of the night and that exact thought popped into my brain. I'm just run so ragged at the moment I'm making stupid mistakes.

Thanks very much.

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 13

maintain your hourly backup in different files.

And size do depend on the transactions that is going in intermediate hour. 🙂

and rest, the masters (like Gail,, grant Etc. )have themselves provided some great explanations. Follow Them.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 14

Write your backup statement so that each time the backup runs it backs up to a new file, easiest way is to stamp the date and time into the file name.

Me again. I'm trying to take this advice, and timestamp my backup files.

I did a search and found some help on this site here.

But my own version of it (below) brings back an error message.

declare @backupfile nvarchar(2000)

set @backupfile = N'E:\path\databasename_LOG_BACKUP_' + replace(convert(nvarchar(50), getdate(), 120), ' ',':') + N'.trn'

BACKUP LOG [i]databasename[/i] TO DISK = @backupfile

The error message:

Msg 3201, Level 16, State 1, Line 6
Cannot open backup device 'E:\path\databasename_LOG_BACKUP_2013-09-18:13:04:40.trn'. Operating system error 123(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 6
BACKUP LOG is terminating abnormally.

Googling suggests that this is a permissions issue, but I can backup to E:\path\databasename_LOG_BACKUP etc. using the GUI, so does that mean it's not a permissions issue?

(I also thought it might be a space issue but there is absolutely tons of space on the E drive.)

Thanks again.