It is a well-known fact that sooner or later each business dependent on IT-infrastructure faces with “Low disk memory” problem. There are plenty of ways to clean the disk’s space, using standard OS services. But neither of them can be used for cleaning or shrinking data on SQL Server. The problem can stuck the overall business processes though and lead to unplanned delay and high costs.

In this article we will show how to shrink a database using standard SQL Server options.

First of all it is needed to detect what exactly allocates the space and how much memory is used. The most popular way is performing a sql-command: execsp_helpdbmydb This command will return the following parameters: files names, files groups, size, etc.

Example:

Another useful command:

exec master.sys.sp_MSForEachDB 'exec ?.sys.sp_spaceused'

This command will return the following parameters for each db: database_name, database_size, unallocated space.If GUI using is preferable, analyze the disk usage is MS SQL Server Management Studio using standard reports. To invoke the report, the database should be selected, then Reports->Standard reports->Disk usage.

After that a database backup should be created.

Once the backup is done, use the following command:

-- USE mydbGODBCC SHRINKDATABASE(N'mydb' )GO

The command will shrink the size of the data and log files in the specified database.

Unused indexes can also cause the problem. The best way to see the allocated memory for indexes storing is sp_indexes procedure.

Example:
This example returns all index information from the Employees table of the Victoria database on the MELBOURNE database server.

EXEC sp_indexes @table_server = ‘MELBOURNE’,@table_name = 'Employees',@table_catalog = 'Victoria',@is_unique = 0

Drop all the unused indexes after the very careful analyze.

To sum up, the general methodology is following:

- Analyze the physical and logical model base

- Check the distribution of physical objects for files

- Investigate the growth of files

- Identify unused space, and make a prediction of growth

Next:

- Make a decision of troubleshooting and cleaning of old unnecessary data

- After that rectify the issue, clean the data and shrink the files if free space in these files cannot be used by the server

Please note, that this task is very risky and we strongly recommend to use order DB Administrator’s services for this purpose.

‍

‍