Estimate Backup Size before backup strat

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Estimate Backup Size before backup strat

Estimate Backup Size before backup strat

Rate Topic

Display Mode

Topic Options

Author

Message

SQLServer2012

Posted Sunday, October 21, 2012 9:18 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, October 28, 2012 10:12 PM
Points: 2, Visits: 9

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.

Post #1375242

saggi.sql

Posted Sunday, October 21, 2012 9:40 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, March 23, 2013 5:55 PM
Points: 2, Visits: 159

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

Post #1375243

Bhuvnesh

Posted Monday, October 22, 2012 12:18 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

thirudwh (10/21/2012)

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

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1375259

cooljagadeesh

Posted Monday, October 22, 2012 12:25 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:39 PM
Points: 23, Visits: 122

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

Post #1375261

Posted Monday, October 22, 2012 1:45 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 4:26 AM
Points: 300, Visits: 485

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

Post #1375291

Grant Fritchey

Posted Monday, October 22, 2012 7:05 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 13,380, Visits: 25,164

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1375404

« Prev Topic | Next Topic »

Permissions