Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Log file backup growth (how long's a piece of string...) Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 10:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
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

Post #1495172
Posted Monday, September 16, 2013 10:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1495178
Posted Tuesday, September 17, 2013 2:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:09 AM
Points: 296, Visits: 1,147
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.
Post #1495404
Posted Tuesday, September 17, 2013 3:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:57 PM
Points: 416, Visits: 512
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.
Post #1495408
Posted Wednesday, September 18, 2013 6:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:09 AM
Points: 296, Visits: 1,147
[b]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 [databasename] 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.
Post #1495883
Posted Wednesday, September 18, 2013 6:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:09 AM
Points: 296, Visits: 1,147
... also the SQL Server Service Account is in the Administrators' group on the server.
Post #1495887
Posted Wednesday, September 18, 2013 6:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
Beatrix Kiddo (9/18/2013)
... also the SQL Server Service Account is in the Administrators' group on the server.


The Agent can be running under a different account. Check that.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1495890
Posted Wednesday, September 18, 2013 6:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:09 AM
Points: 296, Visits: 1,147
Thanks- I did check that, sorry not to be clear!

Ok, when I comment out

+ replace(convert(nvarchar(50), getdate(), 120), ' ',':') + N'.trn'

it works. Huh?
Post #1495893
Posted Wednesday, September 18, 2013 6:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:09 AM
Points: 296, Visits: 1,147
Ok, I changed it to
+ REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','') + '.

(also on that thread I linked to)

instead, and now it works . So I will have a series of smallish log backup files, rather than one continually growing one, is that right?
Post #1495902
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse