Reduce DB Space

SpreadsheetGear: ASP.NET Excel Reporting

Easily create richly formatted Excel reports without Excel using the new generation of spreadsheet technology built from the ground up for scalability and reliability. Recently I have delete 2 million unwanted records from my sql server database table, what i realise is even after deleting records, space used by database is not reducing.

1) Whenever we delete records from table, sql server doesn't reduce size of database immediately.

2) Even after deleting table , sql server doesn't reduce size of database.

3) Instead of Freeing space for deleted records, sql server marks pages containing deleted records as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages.

So In order to claim database space after deleting records in Table, go through following steps:

1)      Check what is Size of your Database using following command?

Exec sp_spaceused

       2) Delete Records from table, If you have already did that skip this step.

       3) Run below command to claim unused database space.

DBCC SHRINKDATABASE(0)

DBCC SHRINKDATABASE command - Shrinks the size of the data and log files in the specified database.

Best Practise to use this command

A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

If you have Created, Alter or Drop any Database table recently then run below command.

DBCC UPDATEUSAGE(0)

DBCC UPDATEUSAGE(0) - Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

Example showing how this command helps me to reduce size of my database after deleting records from table.

1) Take Backup of your Production Database.

2) Take Backup of Table Scripts of your Production Database.

3) Create Test Database in Local Environment.

4) Run Tables creation script.

5) Restore Production Database to Test Database in local environment

6) Check Size of your Database

    Exec sp_spaceused

7) Run Update Usage command
DBCC UPDATEUSAGE(0)

8) Check Size of your Database
Exec sp_spaceused

9) Run Shrink Database command
DBCC SHRINKDATABASE(0)

10) Check Size of your Database
Exec sp_spaceused

If everything goes smooth then you would see that your database size is reduced.