Monday 18 March 2019

Taking log backup when there is no disk space for taking backup


Have you come across a situation in which log file filled up disk and you don't have enough space to take a log backup ? I have come across one ,let me share my learning here
 
One of my application team member contacted me and told their applications development environment is down. When i checked the SQL health i found that database log file have filled up the disk and log reuse wait was showing log backup

So what's next take a log backup right ? but unfortunately in my case since this  was non prod server log backups was never configured and we didn't have enough space for taking large log backup so what else can be done here ? even though there was  no point in time requirement but switching to simple recovery model was not an easy option as this database was part of availability group. Backup to 'NUL' option came to help in this scenario. I triggered a log backup to NUL backup log [databasename] to disk='NUL' which truncated the log file and  i was able shrink the database to  create space in log drive.


What is backup device NUL?
The NUL disk device( it is not NULL)  will discard all information sent to it,even you won't be able to create a file or folder NUL in windows. NUL is a system reserved device name. 



In short we will be able to mimic a backup but actually we won't be  having a backup file and it really don't server the purpose of a backup 😆. If you are taking a log backup to NUL SQL Server will treat it as a valid log backup(it doesn't take database to pseudo-simple recovery model like the truncate_only option we used before SQL 2008). Subsequent log backups will succeed   but you won't be able to restore the subsequent log backups as you took the log backup to NUL so take a differential or full backup if you want to have a restore able log backup chain.

Let me share backup and restore scenario created in my lab machine  where i took a log back to NUL

I took a log backup to NUL and next log backup completed successfully as you you can see below 


When i try to restore all the backup files ,log backup after the backup to NUL fails as earlier log backup with a lesser LSN is missing .


You will end up in same situation if you are trying to restore a differential backup taken after a full backup to NUL




















No comments:

Post a Comment