Estimate Backup Size before backup strat

  • 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.

  • 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

  • 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;-)

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

    then u proceed further

  • 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

  • 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

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

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