December 5, 2014 at 1:18 am
Hi
I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.
Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.
But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line
Also the Server Manager informs me
"We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."
I don't understand how backing up the transaction log could break the "transaction chain"?
Anyone else come across this?
December 5, 2014 at 3:50 am
how are they taking backups, have they indicated this?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 5, 2014 at 4:00 am
That sounds like they're saying that they are already backing up the log, but if the log is that large, that might not be true (likely isn't).
It's pretty much impossible for you to administer a system that you can't connect to in any way. That seems slightly short-sighted.
"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
December 5, 2014 at 4:11 am
Assuming you have got access to run queries on the server it might be worth checking when the log was last backed up and also how much of the log file is being used.
If the log has not been backed up for a while, or ever, have a chat with the Server Manager about recovery objectives and potential loss of data.
If the log file has a highspace used % it could be worth keeping an eye on how much drive space you've got left before it gets full.
December 5, 2014 at 5:07 am
Thanks for the feedback Perry, Grant, TripleAxe
They're using : Dell Netvault Backup
One example db log is
allocated 30436.56 MB, available free space : 30301.75 MB (99%)
Last DB Backup is showing 04/12/2014 21:54:04
Last Log Backup is showing 05/12/2014 10:03:04
sys.dm_os_volume_stats shows the data file has 90gb free space and the log file has 81.25gb space - which initially seems ok, but there are over 50 user db's on this instance and 95% of the logs are all set to grow 10%. Given the level of restricted access at present this isn't something I'm "allowed" to change.
As Grant said "It's pretty much impossible for you to administer a system that you can't connect to"
December 5, 2014 at 5:34 am
ian_massey (12/5/2014)
Last DB Backup is showing 04/12/2014 21:54:04Last Log Backup is showing 05/12/2014 10:03:04
If these dates are DD/MM/YYYY, then they're taking backups. If not, then make sure you're right and bring it to their attention.
When you take a log backup, it applies to the last full backup. You can take a log backup every 5 minutes and without having the last full backup, the log backups are useless. That's what your administrators meant when they told you it'll break the chain. They're referring to the LSN chain.
Allow me to pile on and say that it's not really possible to administer a SQL Server you can't access. If you're responsible for administering the server, then you're going to need to access to it in order to do it right.
December 5, 2014 at 5:40 am
I'm with Ed. It looks like they are doing log backups. So, they are right. If you were to introduce a different set of log backups, it could cause issues on recovery. I guess the next question is, how often are those log backups being run and does that coincide well with the business Recovery Point Objective?
"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
December 5, 2014 at 6:46 am
Ed Wagner (12/5/2014)
When you take a log backup, it applies to the last full backup.
Nope, they're LSN based and are based on the last log backup hence the log backup chain.
The following scenario is possible
Ed Wagner (12/5/2014)
You can take a log backup every 5 minutes and without having the last full backup, the log backups are useless.
as above you just need a full backup and all the logs in sequence following the log chain
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 5, 2014 at 7:04 am
Perry is correct here. The log backups apply to the log backups. They're based on linking each other together, not with the full.
You do need a full backup to get the database up, but then you need all log backups in sequence to match up the LSNs. A full backup (or a diff) and shortcut the logs you need, but the log backups aren't based on the full.
This is why you need all log backups since the earliest full backup you keep. In Perry's scenario, if the full on Friday gets corrupted, you can still go back to the Monday full and recover all data if you have log backups since Monday.
December 5, 2014 at 7:13 am
Yes, they're right. Thanks for the correction, guys. That was very poor wording on my part. You need a full backup, but not only the last one will do.
December 5, 2014 at 7:41 am
Thank you for the LSN explanation re the proprietary software - that makes sense to me.
But I don't have any access to the log file backups, the database backup files or their respective physical locations.
It just seems that until I can get access to these areas then I'm unable to implement any of the normal "administration" duties.
One question I do have : is it possible to find the actual Type of backup performed within SSMS.
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
but is there a further way of finding out whether it was Full or Differential ?
cheers
December 5, 2014 at 7:47 am
ian_massey (12/5/2014)
Thank you for the LSN explanation re the proprietary software - that makes sense to me.But I don't have any access to the log file backups, the database backup files or their respective physical locations.
It just seems that until I can get access to these areas then I'm unable to implement any of the normal "administration" duties.
You need to refer back to any SLA that has been agreed. What separation of duties has been agreed? If you're just required to maintain security and general sql server then this does not stop you doing that. Just ensure they don't come cap in hand asksing you to carry tasks beyond your control.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 5, 2014 at 7:53 am
ian_massey (12/5/2014)
One question I do have : is it possible to find the actual Type of backup performed within SSMS.CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
but is there a further way of finding out whether it was Full or Differential ?
cheers
The backupset system catalog will indicate the type of backup
Backup type. Can be:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 5, 2014 at 7:55 am
Try this
use MSDB
GO
SELECT bs.database_name
, bs.recovery_model
, bmf.physical_device_name
, bs.server_name
, bs.backup_start_date
, bs.backup_finish_date
, bs.backup_set_id
, bs.first_lsn
, bs.last_lsn
, bs.checkpoint_lsn
, bs.database_backup_lsn
, bs.type as BackupSetType
, cast(convert(Numeric(10,2),
(bs.backup_size / 1048576)) as varchar(15)) + ' MB' as Backup_Size
, bs.differential_base_lsn
, bf.filegroup_name
, bf.file_number
, bf.backed_up_page_count
, bf.file_type AS DBFileType
, cast(convert(Numeric(10,2),
(bf.file_size / 1048576)) as varchar(15)) + ' MB' as DBFileSize
, cast(convert(Numeric(10,2),
(bf.backup_size / 1048576)) as varchar(15)) + ' MB' as BackupSize
FROM dbo.backupset bs
INNER JOIN dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
INNER JOIN dbo.backupfile bf ON bs.backup_set_id = bf.backup_set_id
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply