Estimate Backup Size before backup strat

Question

Estimate Backup Size before backup strat

SQLServer2012

SSC Veteran

Points: 210
More actions
October 21, 2012 at 9:18 pm

#264531

Experts,
I am new to SQL Server,i have requirement to run backup using stored procedure.
I am using a stored procedure to call backup command,before backup need to include a step to estimate backup size and compare with disk available space size.If enough space available on the drive then only my backup need to kickoff else terminate process.
How to estimate backup size and compare with Disk within same stored procedure ?
Thanks in Advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

saggi.sql SSC Enthusiast Points: 114 More actions · Answer 1

start backup , if the backup will not be big than available space it will go successfully otherwise it will be failed by throwing space issue error

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

thirudwh (10/21/2012)
Experts,
I am new to SQL Server,i have requirement to run backup using stored procedure.
I am using a stored procedure to call backup command,before backup need to include a step to estimate backup size and compare with disk available space size.If enough space available on the drive then only my backup need to kickoff else terminate process.
How to estimate backup size and compare with Disk within same stored procedure ?
Thanks in Advance.

though this can be acheived by multiple scripts btu still there should be proper capacity planning (proactive approach) to do this

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

cooljagadeesh SSC Eights! Points: 959 More actions · Answer 3

using sp_helpdb <dbname> u wil get the size of db

then u proceed further

SQL Show SSCertifiable Points: 6143 More actions · Answer 4

Which Sql Version do you have? If you do not use Backup Compression, below query can help to calculate backup size (approximately 90%)

-- Data Size

Declare @dataSize Float

Select @dataSize= (SUM(used_pages) * 8) / (1024.00) From sys.allocation_units -- Run it in YourDatabase

-- Log Size

Create Table #Log_info ( FieldId Int, FileSize Bigint, Startoff Bigint, FseqNo int, Status smallint,

Parity Bigint, CreateLsn numeric)

Declare @sql nvarchar(100)

Set @sql = 'dbcc loginfo(YourDatabase)'

Insert into #log_info

Exec sp_executesql @sql

Declare @LogSize Float

Select @LogSize= FileSize/ (1024.00 * 1024.00) From #Log_info where [Status] = 2 -- Only Active VLFs

Select @dataSize DataSize_inMB, @LogSize LogSize_inMB, @dataSize+ @LogSize As 'BackupSize_Approx[90%]'

Drop Table #log_info

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 5

Just know that the backup size is not just the data size. It also includes some header information (very small) and open transactions that are rolled forward or rolled back at the conclusion of the backup/restore process. The size of these transactions is completely dependent on their size and number within your system. So you can plan for the size of a backup being roughly, but not exactly the size of the data. Just remember this if you're truly operating right at the edge of available space.

"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