Calculating resource usage and capacity planning for SQL server databases can be a time consuming business and there are many factors to take into consideration. Over time we plan to provide some tips and resources that we’ve come across over the years to help out in this area.

We’re often asked by clients who are using our shared database servers to provide them with information related to the performance of their database. We provide a number of statistics by default but a fairly common request is for a breakdown on the storage usage of their database. The script below is an example of how you can use T-SQL to query the database itself to provide a breakdown on the space used for each table in the database. It also provides the total allocated space to each table and an approximate row count.

T-SQL script to display table size summary:

SELECT LEFT(OBJECT_NAME(id), 30) AS [Table],
CAST( CAST(reserved * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS ‘Allocated (MB)’,
CAST(CAST(dpages * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS ‘Used (MB)’,
CAST(CAST((reserved – dpages) * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS ‘Unused (MB)’,
rowcnt AS ‘Row Count (approx.)’
FROM sysindexes WHERE indid IN (0, 1) AND OBJECT_NAME(id) NOT LIKE ‘sys%’ AND OBJECT_NAME(id) NOT LIKE ‘dt%’
ORDER BY reserved DESC, LEFT(OBJECT_NAME(id), 30)

The script above, along with others that we will post in the future, can be really useful in helping to gain a clearer picture about how your database is being used and can provide pointers to what you might want to look at when attempting to optimise performance.

If you have a dedicated SQL Server you will be able to backup your databases to your server. If you use a shared database you will need to contact the Secura Hosting Support Department and ask them to take a backup for you.

Open SQL Server so you can see a list of all your databases. Right-click on the database you wish to backup and choose “Tasks” then “Back Up…”.

We will have already set the backup paths for you so all you need to do from here is click “Options” and check the radio button next to “Overwrite all existing backup sets”. Once you have done that click OK to start the backup.

To restore a backup right-click on the database you wish to restore to and choose “Tasks”, “Restore” then “Database”. Click the “From Device” radio button then click the “…” button. Click “Add” browse to the backup file you just created or another backup you wish to use and click OK and OK. Make sure you have a tick in the box under “Restore” then click on “Options” and check the box next to “Overwrite the existing database”. Make sure that the “Restore As” paths are correct and are pointing the the new database files. Click OK to start the restore.

Please note Secura Hosting cannot take responsibility for any loss of data while following these steps. If you are unsure of any parts of these processes then please stop and contact our support department who will be able to guide you through the process.