June 30, 2014 at 11:13 am
I have a script that:
- Takes a transaction log backup of my database;
- Checks for transaction log size significantly larger than normal;
- If tlog files are significantly large than normal, try to reduce to more normal size;
- Backup DB.
The DB backup is then restored onto two other servers, to provide nightly refreshed QAS and reporting DBs.
The problem is that, once every week or two, our outsourced DBA team decides that they need to shift the DB into SIMPLE recovery mode, and back to FULL. Clearly, they don't understand the consequences, as they don't take a full backup when they're done, as so our transaction log jobs fail for the next 16-18 hours, until my script finally takes a full backup.
The problem is that I can't tell if it's safe to take my transaction log backup or not. While my script plugs away and normally gets the full backup taken, it gets logged as having failed, and certain followup steps are not taken.
So, my question: Is there a query I can run that will tell me whether or not a database has a "current database backup"?
I've search the site, with no luck. Since SQL Server itself tells me that I'm in this state, surely there's a way I can check for myself?
Notes:
- I know that it's generally a bad idea to constantly shrink transaction log files. The code's in place to handle those relatively rare cases where some long-running process has caused the t-log files to grow from the normal 4-5 GB I allow to 20-30 GB. The restore process is a big part of why I'm doing this - the files never need to be very large in the reporting environment, so allowing space for them to be huge would be wasteful.
- I'm working on approaching the problem from the other angle (getting the DBAs to stop changing the recovery mode, since as far as I can tell it doesn't accomplish any more than doing a tlog backup itself would); however, it would be nice to simply skip the tlog backup if it's inappropriate.
- I'm pretty sure I could figure out a way to accomplish this with TRY CATCH - however, preventing the problem in the first place seems cleaner that using the failure to decide whether it should be skipped or not.
R David Francis
June 30, 2014 at 12:03 pm
This will get you the last backup by type:
SELECT D.name, B.type, MAX(B.backup_finish_date)
FROM sys.databases D
LEFT JOIN msdb.dbo.backupset B ON D.name = B.database_name
GROUP BY D.name, B.type
ORDER BY D.name, B.type
This may help also:
SELECT DB_NAME(database_id),
CASE WHEN last_log_backup_lsn IS NULL
THEN 'Need to take a FULL backup first!'
ELSE 'No Full Needed'
END
FROM sys.database_recovery_status
- I'm working on approaching the problem from the other angle (getting the DBAs to stop changing the recovery mode, since as far as I can tell it doesn't accomplish any more than doing a tlog backup itself would); however, it would be nice to simply skip the tlog backup if it's inappropriate.
Uh, no. Setting the recovery from full to simple and doing a log backup are not related.
The point of performing a log backup is to allow for a point in time recovery. The secondary benefit is to keep the size of the log in check.
Changing back and forth from full to simple as part of maintenance seems silly at best. It sounds as if you need another set of DBA's
Take a look at Ola Hallengren's maintenance scripts. In the backup script, if there is no current full backup, the log backup will automatically change to full backup.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 30, 2014 at 1:26 pm
You can also use:
DBCC TRACEON (3604)
GO
DBCC DBINFO (dbname-here)
It also has last dbcc checkdb date, and plenty of other stuff.
Full disclosure, someone stumped me on an interview with the last checkdb run, and I found it thanks to Google.
July 1, 2014 at 4:51 am
RD Francis (6/30/2014)
The problem is that, once every week or two, our outsourced DBA team decides that they need to shift the DB into SIMPLE recovery mode, and back to FULL.
Smack them hard across the head!!
RD Francis (6/30/2014)
The problem is that I can't tell if it's safe to take my transaction log backup or not. While my script plugs away and normally gets the full backup taken, it gets logged as having failed, and certain followup steps are not taken.So, my question: Is there a query I can run that will tell me whether or not a database has a "current database backup"?
Yep, here you go
DECLARE @MyDB SYSNAME
SET @MyDB = 'somedb'
SELECTbs.server_name
, bs.user_name
, bs.database_name
, bs.backup_start_date
, bs.backup_finish_date
FROM msdb..backupset bs
INNER JOIN master.sys.databases d ON bs.database_name = d.name
WHERE bs.database_name = @MyDB AND
bs.server_name = CAST(SERVERPROPERTY(N'Servername') AS sysname)
AND bs.[type] = N'D' AND bs.backup_finish_date > d.create_date
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 1, 2014 at 5:29 am
Perry your example doesn't seem to be telling me what i thought I should find.
i think the OP is asking if he can test whether he can take a log backup or not, because a FULL backup is in place?
I think your query is just finding if a full backup exists, ever, and not if it's safe for a backup?
I would think this would have to test last_LSN of a full backup to something that potentially could be take now?, and not the created date of the database?
Lowell
July 1, 2014 at 5:38 am
a bit of research on my side seems to tell me this DMV can help: sys.database_recovery_status :
if the database in question is in this resultset, it does not have a valid FULL backup, so any log backup will fail.
:
SELECT db.name,db.recovery_model_desc,st.last_log_backup_lsn,*
FROM master.sys.databases db
inner join sys.database_recovery_status st
ON db.database_id = st.database_id
WHERE db.recovery_model_desc='FULL'
AND st.last_log_backup_lsn IS NULL
Lowell
July 1, 2014 at 9:47 am
Michael L John (6/30/2014)
Uh, no. Setting the recovery from full to simple and doing a log backup are not related.
The point of performing a log backup is to allow for a point in time recovery. The secondary benefit is to keep the size of the log in check.
My best guess is that the DBAs are concerned about the size of the transaction log.
After switching a FULL recovery DB to SIMPLE recovery, all completed transaction will be released, freeing their space in the t-log file(s).
After taking a t-log backup on a FULL recovery DB, all completed transactions will be released, freeing their space in the t-log file(s).
My point being that, if you're trying to free space in the transaction log of a FULL recovery DB, taking a t-log backup will do the job just as well as switching to SIMPLE and back to FULL, without destroying the ability to do point-in-time recovery. A point I want to get through to these DBAs.
AND - Thanks to all for the suggestions - I hope to be able to try them out shortly!
R David Francis
July 8, 2014 at 3:02 pm
Lowell (7/1/2014)
a bit of research on my side seems to tell me this DMV can help: sys.database_recovery_status :if the database in question is in this resultset, it does not have a valid FULL backup, so any log backup will fail.
:
SELECT db.name,db.recovery_model_desc,st.last_log_backup_lsn,*
FROM master.sys.databases db
inner join sys.database_recovery_status st
ON db.database_id = st.database_id
WHERE db.recovery_model_desc='FULL'
AND st.last_log_backup_lsn IS NULL
This looks like the most complete solution.
Michael L John's second query is almost identical. However, when I ran it I got some odd results, where a SIMPLE mode DB reported it was ready for a log backup.
There are cases (probably when restoring a DB) where a DB in SIMPLE mode will have a non-NULL value in last_log_backup_lsn. If you only check for that value, you may think you can run a t-log backup on a DB - but if it's in SIMPLE mode of course, you can't.
My final query checks for a DB not in SIMPLE mode, with a non-NULL last_log_backup_lsn; that should indicate that a t-log backup can be done on the DB.
Again, thanks to all for the assistance!
R David Francis
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply