Best Practices to Increase Restore Speed

Everyone has a list of best practices for backups, including me. But, you know what doesn’t get talked about much? Restores. Yeah, there are things you can do to make your restores better, faster, stronger. Why would you want to improve restore speed? Think about it like this, backups are something that you automate, tweak,... » read more

Ways to improve native SQL database backup and restore

Ways to improve native SQL database backup and restore times: Backup your databases with compression: I/O is typically the bottleneck, this shifts some of the work to CPU. Change your storage configuration: Store backups locally or on higher-performance SAN Backup your databases to multiple files (stripes): This might depends on your specific storage and network... » read more

Instant File Initialization and Database Recovery

When SQL Server reserves new space on a disk, it initializes the space with zeros. This behavior can be switched off thereby reducingthe execution time of some operations and the load on the disk subsystem. Disk space reservation without initialization is called Instant File Initialization. This feature is not widely known, although its use has become possible starting with SQL Server 2005. The benefits of the Instant File Initialization are the following: 1. Accelerating the creation... » read more

Setting Max Memory for SQL Server

By default, SQL Server’s max memory is 2147483647 – a heck of a lot more than you actually have.  Trivia time – that’s the max number for a signed 32-bit integer.  SQL Server will just keep using more and more memory until there’s none left on the system. If the operating system has no memory... » read more

Estimating the Size of Backup Files

How to estimate the next backup size? When we want to estimate the next backup size we can use the following stored procedure which gives some information about the backup size. The reserved column value approximately gives information about size of uncompressed backup size. Reserved column defines the database size which consists of data and... » read more

.BAK File Size vs Actual Database File Size

The space was allocated to the database files, but not used. You can create a new database, make it 10GB in size, and see the files allocate that amount of space on disk. However, until you put data in the database, the file is essentially empty, and your backup file size will be minimal. For... » read more

Check and Reset Table Identity

Checks the current identity value for the specified table and, if it’s needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column. Examples A. Resetting the current identity value, if it’s needed The following example resets the current identity value, if it’s... » read more