June 21, 2011 at 7:07 am
you dont seem to have made any provision for scenarios when a database name is passed in that is in Simple recovery mode
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 21, 2011 at 12:42 pm
Perry Whittle (6/21/2011)
you dont seem to have made any provision for scenarios when a database name is passed in that is in Simple recovery mode
Apparently, I'm missing the context of this comment. Could you clarify?
June 21, 2011 at 1:42 pm
Brandie Tarvin (6/21/2011)
Perry Whittle (6/21/2011)
you dont seem to have made any provision for scenarios when a database name is passed in that is in Simple recovery modeApparently, I'm missing the context of this comment. Could you clarify?
gmamata7's stored procedure attempts a transaction log backup without ever checking the recovery model of the database name that has been passed in
IF @BackupType = 'LOG'
BEGIN
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SET @BackupName = @DBName + ' Log Backup'
SET @BackupPath = @BackupPath + @DBName + '_Log' + @Prefix + '.trn'
SELECT @BackupPath AS Log_Backup_Path
BACKUP log @DBName
TO DISK = @BackupPath
WITH NOFORMAT, NOINIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
At some point i would expect a check to make sure the recovery model is not Simple before the log backup is attempted otherwise the proc will error unnecessarily
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 21, 2011 at 8:29 pm
GilaMonster (6/13/2011)
There's a verify at least (clear from the output you posted) and that will be counted in the time for the step, not the time for the backup.
---
I had posted a similar question and can offer some stats plus additional info:
Even when the full backup of 100GB is done by Maint Plan, it appears to do a backup/verify that takes longer than the actual bkp time (as per MSDB).
That may be quite a lot longer, depending on the state of the SAN where the backup file goes and the other activities that affect the SAN at that time.
In my case, there was some activity on the SAN (Exchange backups!) and a lot of fragmentation, which caused havoc with backup and ETL time. In the stats below, you can see how the situation improved as the SAN was defragmented (allowing bigger LUNs) and the competing activity was rescheduled at other times.
Below, "B/V Step" means the time-stamps in the maintenance log for just this database full backup (overwrite) which - at its best - is cca 30 minutes longer than the actual db backup as logged in the MSDB wheareas at the peak of the trouble, it took an extra 3 hours after the actual backup completed!
It would be interesting to know what exactly happens after backup completes, hope Gail has some info on the internals there. You will notice another huge spike in the elapsed time towards the end ... apparently, some extra Exchange backups had to be run at the same time ...
MSDB B/V Step Bkp File(GB)Data File(GB)
(m) (elapsed) ---------- ------------
83266125845.06130766.38 (has old data)
78250108677.12130766.38 (purged data)
66218108601.12130766.38 (mdf stationary)
68258108834.12130766.38
77226109378.12130766.38
73262109368.12130766.38
47200109414.12130766.38
59268121809.48130766.38
47181109191.12130766.38
55227109218.12130766.38
56222109354.12130766.38
54223109581.12130766.38
67226109690.12130766.38
52213109487.12130766.38
53213122163.34130766.38
44126109275.12130766.38
4594109291.12130766.38
4694109366.12130766.38
58252109797.12130766.38
56242109969.12130766.38
4989110092.12130766.38
91280121558.2 130766.38
56127109878.12130766.38
5592109828.12130766.38
5189110057.12130766.38
June 22, 2011 at 8:01 am
Perry Whittle (6/21/2011)
Brandie Tarvin (6/21/2011)
Perry Whittle (6/21/2011)
you dont seem to have made any provision for scenarios when a database name is passed in that is in Simple recovery modeApparently, I'm missing the context of this comment. Could you clarify?
gmamata7's stored procedure attempts a transaction log backup without ever checking the recovery model of the database name that has been passed in
Ah. That's a good point. I missed that.
June 22, 2011 at 8:19 am
Brandie Tarvin (6/22/2011)
Ah. That's a good point. I missed that.
So did I initially 🙂
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply