Out of Disk Space on the SQL Server

In certain circumstances manual maintenance steps may be required to free up disk space.

Log File Full

If using the full recovery model for the database, ensure that automatic backups are enabled for log files.

It is possible to manually shrink the log file of either the datamart or staging databases using the following steps:

Start SQL Server Management Studio (SSMS)

Connect to the SQL Server

On the left in "Object Explorer" expand "Databases"

Right click on the database name (e.g. BigDataInsightUC)

Select "Task", then "Shrink", then "Files"

Next to "File Type" select Log

Under Shrink Action, select "Reorganize pages before releasing unused space

Enter the size to shrink the file to (a minimum size is listed in this dialogue as a guideline as to how small you can shrink it)

Press OK

For more on resolving issues with a full transaction log see https://docs.microsoft.com/en-us/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002 

Full Drive

Re-evaluate if the amount of storage the system has is sufficient to to store the quantity of data required for the desired retention period.

If the drive that datamart database is on is out of disks space, there are a number of steps one can do to resolve this:

  • Reduce the retention period
  • Increase the size of the drive
  • Shrink files:

Shrinking files to increase the amount of storage space, follow the steps above, however in Step 6, select a filegroup (names starting with "FG_").

Repeat this process for other file groups.


Provide feedback on this article