Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Estimate Backup Size before backup strat Expand / Collapse
Author
Message
Posted Sunday, October 21, 2012 9:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, October 21, 2012 9:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:19 AM
Points: 2, Visits: 169
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
Posted Monday, October 22, 2012 12:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1375259
Posted Monday, October 22, 2012 12:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 20, 2014 10:08 PM
Points: 30, Visits: 158
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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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
Posted Monday, October 22, 2012 7:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:49 AM
Points: 15,713, Visits: 28,120
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 »

Add to briefcase

Permissions Expand / Collapse