Check Available Space first prior to backup

Question

Check Available Space first prior to backup

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
September 22, 2008 at 9:22 am

#382746

Hi
As part of a maint routine Im running some healthchecks and then dependant on positive answers executing the following
_{declare @DBName varchar (100)}
declare @newDBName varchar (100)
declare @string varchar (2000)
declare @path varchar (50)
declare @return char (2)
declare @mydate varchar(50)
select @mydate = convert(varchar(8), getdate(), 112) + '_' +
replace(substring(convert(varchar(20), getdate(), 113), 13, 8), ':', '-')
SET @return = CHAR (13) + CHAR (10)
set @path = 'c:\builds\databases' --< you choose
set @dbname = db_name()
set @string = 'BACKUP DATABASE [' +@DBName + '] TO DISK = N' + char(39) + @Path+'\'+ @DBName +'_'+ @mydate+'.bak' + char(39)
set @string = rtrim(@string) + ' WITH INIT , NOUNLOAD , NAME = N' + char(39) + @DBName +'.bak'+ char(39)
set @string = rtrim(@string) + ', NOSKIP , STATS = 10, NOFORMAT '
exec (@string)
go
My question is using SQL can I check the physical disk for adequate space to run this backup prior to the code making the backup.. in the same script ?
many thanks
Simon

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

You could certainly use the outputs of xp_fixeddrives and sp_spaceused (for the given database - or use select * from sys.master_files) to check the free space on the drive and to get the size of the database respectively and then use that data to determine if the backup will have enough space prior to executing or building the script.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 2

thanks for the advice 🙂 went for this (no doubt messy) solution in the end

--------------------------------------------------------------------------------

-- FreeSpace Check

--------------------------------------------------------------------------------

declare @avail int

declare @drive char(1)

declare @dbsize dec(15,0)

declare @logsize dec(15)

declare @bytesperpagedec(15,0)

declare @pagesperMBdec(15,0)

declare @databasesize dec(15,0)

set @drive = 'c'

create table #FreeSpace(

Drive char(1),

MB_Free int)

insert into #FreeSpace exec master..xp_fixeddrives

select @avail = MB_Free from #FreeSpace where drive = 'c'

drop table #FreeSpace

select @dbsize = sum(convert(dec(15),size))

from dbo.sysfiles

where (status & 64 = 0)

select @logsize = sum(convert(dec(15),size))

from dbo.sysfiles

where (status & 64 <> 0)

select @bytesperpage = low

from master.dbo.spt_values

where number = 1

and type = 'E'

select @pagesperMB = 1048576 / @bytesperpage

select @databasesize = ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2))

--print @databasesize

if @databasesize >= @avail

begin

print 'Not Enough Freespace for Backup'

RAISERROR (@errString, 10, 1) WITH LOG, NOWAIT

WAITFOR DELAY '00:00:01'

RAISERROR ('Connection forcibly closed by script', 20, 1) WITH LOG, NOWAIT

end