September 9, 2010 at 9:33 am
Maybe something like this would help you see the backup status of all your databases:
-- Most Recent Backups and # of days since ANY type of backup
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
b.recovery_model_desc as 'Recovery Model',
case
when state_desc <> 'ONLINE' then state_desc -- Alert that DB might be ReadOnly, Offline etc...
else ' '
end as 'DB Status'
FROM master.sys.databases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name --AND A.type = 'D'
where B.name not like '%develop%' and b.name not like '%train%'
GROUP BY B.name , a.type, b.recovery_model_desc, state_desc
ORDER BY B.name , LastBackupDate desc,a.type, b.recovery_model_desc, state_desc
September 9, 2010 at 9:37 am
I would invite you to ask your backup person to restore your database to a point in time on another server using the latest "log" backup. That will let you both confirm that DPM is working.
September 9, 2010 at 9:42 am
Steve Jones - Editor (9/9/2010)
I would invite you to ask your backup person to restore your database to a point in time on another server using the latest "log" backup. That will let you both confirm that DPM is working.
And make it an unusual time. 11:42 and 17 seconds or something like that. Justify as that's what'll be necessary if someone 'accidentally' drops a table and you need to restore to the second before the drop.
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
September 9, 2010 at 9:50 am
Gail,
I ran this on a dev server:
select log_reuse_wait_desc,count(log_reuse_wait_desc)
from sys.databases
where recovery_model_desc = 'FULL'
group by log_reuse_wait_desc
and got this:
LOG_BACKUP61
NOTHING 7
I'm waiting on getting the permissions to run DBCC SQLPERF as you suggest. I'll do that ASAP.
I'm hoping that the prevalence of "LOG_BACKUP" there is a good thing, else pls send Marula as offered 😀
Steve, ultimately we will do as you suggest - Gail suggested that also and we need to know for sure here. Much appreciated.
September 9, 2010 at 9:59 am
SwayneBell (9/9/2010)
I'm hoping that the prevalence of "LOG_BACKUP" there is a good thing, else pls send Marula as offered 😀
Nope, the opposite.
For 61 databases, the space in the log is not been reused because SQL is waiting for a log backup to run before it can reuse the space. If that's a long-term condition, your logs are going to be growing, which is not a good thing.
What about on the production server? The one we've been talking about with the full and diff DPM backups.
p.s. on a dev server I'd expect most databases to be in Simple recovery (point-in-time restores are not usually necessary for dev boxes) and hence not requiring log backups at all.
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
September 9, 2010 at 10:14 am
Edited my query to include recovery model.
September 9, 2010 at 10:41 am
GilaMonster (9/9/2010)
SwayneBell (9/9/2010)
I'm hoping that the prevalence of "LOG_BACKUP" there is a good thing, else pls send Marula as offered 😀Nope, the opposite.
For 61 databases, the space in the log is not been reused because SQL is waiting for a log backup to run before it can reuse the space. If that's a long-term condition, your logs are going to be growing, which is not a good thing.
What about on the production server? The one we've been talking about with the full and diff DPM backups.
p.s. on a dev server I'd expect most databases to be in Simple recovery (point-in-time restores are not usually necessary for dev boxes) and hence not requiring log backups at all.
Here are the results for the production server:
LOG_BACKUP29
NOTHING 18
I wonder if, because we're using DPM, if some of the backup activity is masked from SQL Server queries (i.e. because it's a separate application).
Also, I'm arranging to do a restore to a 'wierd time' like you suggested. I admire your clever evilness. I can see where you're going with it.
September 16, 2010 at 8:57 am
Thanks to the input that was provided here, our CIO has initiated a full review of our backup policies and the SLA's we have with our clients.
I owe you all a debt of gratitude.
Best regards,
Steve
September 16, 2010 at 9:09 am
Awesome. It's always better to find these things out BEFORE the pawpaw hits the fan.
You're welcome.
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
September 30, 2010 at 11:01 am
Update:
I'm told that what's happening here is that we do full backups each night. The following morning, the log files are 'deleted' and allowed to fill through the day until the process is repeated that night. The log files are mirrored so the recovery plan would be:
- restore the full backup
- apply the log (either primary or the mirrored version if primary is lost) to recover to a point-in-time.
On a scale of one to ten, does this sound reasonable?
This would explain (perhaps) why so many of our databases in Full Recovery show "LOG BACKUP" in the log_reuse_wait_desc. I.e., it suggests that the 'new' daily logfiles are auto-growing throughout the day.
Am I anywhere close to the truth here? (be gentle Gail)
Steve
September 30, 2010 at 11:14 am
SwayneBell (9/30/2010)
Update:I'm told that what's happening here is that we do full backups each night. The following morning, the log files are 'deleted' and allowed to fill through the day until the process is repeated that night.
Deleted? I most certainly hope not. Deleting a transaction log is a really good way to lose a database.
The log files are mirrored so the recovery plan would be:
Mirrored as in RAID 1? If so, I can get you several recent posts about RAID 1 not being a backup solution
- restore the full backup
- apply the log (either primary or the mirrored version if primary is lost) to recover to a point-in-time.
On a scale of one to ten, does this sound reasonable?
Um.... Personally I'd never run a system like that. Maybe that's just me though.
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
September 30, 2010 at 11:29 am
SwayneBell (9/30/2010)
Update:I'm told that what's happening here is that we do full backups each night. The following morning, the log files are 'deleted' and allowed to fill through the day until the process is repeated that night. The log files are mirrored so the recovery plan would be:
- restore the full backup
- apply the log (either primary or the mirrored version if primary is lost) to recover to a point-in-time.
On a scale of one to ten, does this sound reasonable?
That'd fall under the 'one, maybe a two' heading. GYEAH. They could do simple recovery for that, and they're regrowing every day. This really falls under the header of "Please, just shoot me before we crash?".
Unless I really missed a memo in the 'new things to do' in 2k5, you're not applying a log without a backup of it. You cannot do point in time recovery this way. Is it a mirrored Database, or a mirrored drive, as Gail asked above? This can't be emphasized enough: Raid is not a backup, if that's where their heads are at.
This would explain (perhaps) why so many of our databases in Full Recovery show "LOG BACKUP" in the log_reuse_wait_desc. I.e., it suggests that the 'new' daily logfiles are auto-growing throughout the day.
Yep, and that regrowth time is hitting your performance... every... day... *twitch*.
I hope you can help them get this straightened out before you have to care about it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 30, 2010 at 12:42 pm
Thanks Gail, Craig;
I have followed up with our CIO who has tasked one our managers with getting our act together on this. I'll be involved and will keep you posted.
Once again, I truly appreciate your feedback.
Steve
September 30, 2010 at 1:08 pm
Can you just clarify first whether you mean RAID 1 when you said 'mirrored' or something else.
Also, what exactly does it mean when the logs are 'deleted'?
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
October 1, 2010 at 5:34 am
Hi Gail;
Yes, the logs are mirrored with RAID 1.
The "deleting" is done through your favourite tool, DPM.
Our sysadmin just told me that DPM does the full backup then deletes the logs. I'm reading up on DPM right now (System Center Data Protection Manager 2007 by Ganger and Femling.
Also, I'll get someone to show me the exact job, but our sysadmin says it actually deletes the file once the backup is done.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply