Estimate Backup Size In Sql Server

Facebooktwitterredditpinterestlinkedinmail

When you’re creating your database backup maintenance plans, you need to choose a drive that has enough space for your backup. So how much space will you actually need to backup your database? Figuring this out is actually a lot easier than you would expect.

Although it won’t be 100% accurate, SQL Server has a built in stored procedure that will get you pretty close. You can use the sp_spaceused stored procedure to show you the amount of space used in your database. Now keep in mind that a full database backup only stores the actual data/objects in the database. The unused space is not stored in the backup.

USE zoobase
GO
EXEC sp_spaceused @updateusage = N'TRUE'
GO

Estimate Backup Size In SQL Server

In the example above we call the sp_spaceused stored procedure from the database that we want to get the backup size for. This stored procedure will return a couple datasets with multiple columns. The column that you will look at is named reserved. This will show you a good estimate of how large your full backup file will be.

 
More Info
The @updateusage parameter, in the example above, tells the stored procedure to update the space usage statistics before returning the database size information. Passing TRUE in to this parameter will give us the most accurate size estimate.

List Of Data Types And Their Sizes

Facebooktwitterredditpinterestlinkedinmail

The below table shows the storage sizes of the different data types in SQL Server.

[table caption=”DateTime Data Types” th=”0″ width=”500″ colwidth=”150|150″]
date,3 bytes
datetime,8 bytes
datetime2,6 – 8 bytes (depending on precision)
datetimeoffset,10 bytes
smalldatetime,4 bytes
time,5 bytes
[/table]

[table caption=”Numeric Data Types” th=”0″ width=”500″ colwidth=”150|150″]
decimal,5 – 17 bytes
numeric,5 – 17 bytes
float,4 or 8 bytes
real,4 bytes
bigint,8 bytes
int,4 bytes
smallint,2 bytes
tinyint,1 byte
money,8 bytes
smallmoney,4 bytes
[/table]

[table caption=”String” th=”0″ width=”500″ colwidth=”150|150″]
char,size defined in table
varchar,2 bytes + data size
text,data size
nchar,2 times size defined in table
nvarchar,2 bytes + 2 times data size
ntext,2 times data size
[/table]

[table caption=”Binary” th=”0″ width=”500″ colwidth=”150|150″]
binary,size defined in table
varbinary,2 bytes + data size
image,2 bytes + data size
[/table]

[table caption=”Other” th=”0″ width=”500″ colwidth=”150|150″]
bit,1 byte
hierarchyid,5 bytes
uniqueidentifier,16 bytes
[/table]

Reference: http://msdn.microsoft.com/en-us/library/ff848794.aspx

SQL Server 2014 Feature – Timeout For Online Index Rebuilding

Facebooktwitterredditpinterestlinkedinmail

SQL Server 2014 comes with a new argument for how to handle the blocking issues that come from online index rebuilds.

 
Before SQL Server 2014

Before the existence of SQL Server 2014, if you were doing an online index rebuild… SQL Server would wait until the index rebuild could get the locks that it needs for rebuilding the index.  This can cause long running maintenance plans on very active tables.

 
SQL Server 2014 Online Index Rebuild Argument

The new functionality available in SQL Server 2014 tags on to the original index/table rebuild syntax.  It allows you to customize how you want to handle the blocking that occurs on the indexes/tables that are being rebuilt.  Here is a sample of the new syntax for managing the blocking on the index rebuild.

ALTER	INDEX ALL
ON		Animal
REBUILD	WITH
(
	SORT_IN_TEMPDB = ON,
	STATISTICS_NORECOMPUTE = OFF,
	ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES,
									     ABORT_AFTER_WAIT = BLOCKERS ) )
)

Notice in the statement above, the new WAIT_AT_LOW_PRIORITY flag that gets added to the ONLINE argument.  When you rebuild an index in SQL Server with the WAIT_AT_LOW_PRIORITY flag turned on, it will allow other operations to proceed while the index build waits for low priority locks.  This argument comes with 2 parameters.

 
MAX_DURATION

The MAX_DURATION parameter is how long you want to wait in minutes.  It must always be in minutes.

 
ABORT_AFTER_WAIT

This parameter comes with 3 options.  This is basically who loses if the rebuild process cannot get the locks that it needs.  The connection that you choose will be terminated after the timeout period.

  • NONE – Continue waiting for the lock
  • SELF – The rebuild operation will fail
  • BLOCKERS – All transactions that are blocking the rebuild will be killed

 
If you do not specify WAIT_AT_LOW_PRIORITY with you index rebuild, then SQL Server will automatically set it to the default of 0 minutes and NONE.

 
 
Index Reference:  http://msdn.microsoft.com/en-us/library/ms188388.aspx

Table Reference:  http://msdn.microsoft.com/en-us/library/ms190273.aspx