“Database is out of space” – SQL Server or SharePoint database runs out of space.

If your SQL Server or SharePoint database is running out of space, you need to take immediate action to avoid downtime and data corruption. Below is a detailed step-by-step guide on how to identify, troubleshoot, and resolve the issue in a structured way.

---

Step 1: Identify the Issue

Before taking any corrective actions, confirm that the database is actually out of space.

Check for Errors

1. SQL Server Logs:
   • Open SQL Server Management Studio (SSMS).
   • Run the following command to check for space-related errors in the error logs: EXEC xp_readerrorlog 0, 1, 'disk space';
   • Look for messages like “Database is out of space” or “The transaction log for database is full”.
2. SharePoint ULS Logs:
   • Open SharePoint ULS Logs (located in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\LOGS for SharePoint 2016+).
   • Look for messages related to SQL database out of space.
3. Event Viewer:
   • Open Event Viewer (eventvwr.msc).
   • Check under Windows Logs → Application for any critical SQL Server-related errors.

---

Step 2: Check Database and Disk Space

Check Database Size in SQL Server

1. Open SSMS.
2. Run the following command to check the current database size: EXEC sp_spaceused;
3. To check size for a specific database: USE [YourDatabaseName]; EXEC sp_spaceused;
4. Check free space available: SELECT DB_NAME(database_id) AS DatabaseName, name AS Logical_Name, size/128.0 AS TotalSize_MB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpace_MB FROM sys.master_files WHERE DB_NAME(database_id) = 'YourDatabaseName';

Check Disk Space

1. Open Command Prompt (cmd) and run: fsutil volume diskfree C: Replace C: with the drive where SQL Server database files are stored.
2. Open File Explorer → Right-click on the drive → Properties → Check available space.

---

Step 3: Free Up Space

If the disk is full, follow these methods to reclaim space.

Method 1: Delete Unused Database Files

1. Identify old backup files in C:\Program Files\Microsoft SQL Server\MSSQL\Backup or your backup directory.
2. Move them to another disk or delete unnecessary backups.
3. If your SharePoint database has old logs, clear them using: USE WSS_Content; DBCC SHRINKFILE('WSS_Content_log', 1);

Method 2: Shrink Database (Use with Caution)

If necessary, shrink the database to reclaim unused space:

USE YourDatabaseName;
DBCC SHRINKDATABASE (YourDatabaseName);

To shrink the log file:

DBCC SHRINKFILE (YourDatabase_log, 1);

Note: Shrinking a database is not recommended for normal use, as it can cause fragmentation.

Method 3: Increase Database File Size (If Possible)

If there’s free space on the disk but the database has reached its allocated limit, manually increase the size.

1. Open SSMS.
2. Right-click on the database → Properties → Files.
3. Under Autogrowth, increase the size limit (set to UNLIMITED if necessary).

To do this via SQL:

ALTER DATABASE YourDatabaseName 
MODIFY FILE (NAME = 'YourDatabaseFile', SIZE = 5000MB);

Method 4: Move Database Files to Another Drive

If your database is out of space but another disk has free space, move the database files:

1. Get Current File Location: SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('YourDatabaseName');
2. Detach Database: USE master; ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE;
3. Move .mdf and .ldf files to a new location.
4. Reattach Database: CREATE DATABASE YourDatabaseName ON (FILENAME = 'D:\NewPath\YourDatabase.mdf'), (FILENAME = 'D:\NewPath\YourDatabase.ldf') FOR ATTACH;

---

Step 4: Prevent Future Space Issues

Enable Auto Growth

1. Open SSMS → Right-click database → Properties.
2. Go to Files → Set Autogrowth settings to “By Percentage” instead of a fixed MB value.
3. Set maximum file size to Unlimited (if disk space allows).

Monitor Database Growth

1. Set up a SQL Agent Job to monitor disk space daily: EXEC xp_fixeddrives;
2. Use Performance Monitor (perfmon) to track SQL Server disk usage.

Implement Database Maintenance

1. Schedule index rebuilding weekly: ALTER INDEX ALL ON YourTable REBUILD;
2. Enable database auto-shrink only if necessary: ALTER DATABASE YourDatabaseName SET AUTO_SHRINK ON;
3. Backup Strategy:
   • Store backups on another disk or cloud.
   • Clean up old backups automatically with: EXECUTE sp_delete_backuphistory 'YYYY-MM-DD';

---

Step 5: Verify Everything is Working

After resolving space issues:

1. Check Database Status: SELECT name, state_desc FROM sys.databases;
2. Restart SQL Server Services:
   • Open Services.msc.
   • Restart SQL Server (MSSQLSERVER).
3. Run Database Integrity Check: DBCC CHECKDB('YourDatabaseName');
4. Monitor Logs: EXEC xp_readerrorlog;

---