SharePoint 2013 – Drives are running out of free space

Query all database sizes on SQL Server

On any SharePoint Environment, the SQL Server databases can take a lot of space. The space can be used by Usage Database or even the transaction logs. The below query returns the Database name and the file size used by data and log files receptively.

Shrink all databases on SQL Server

You can use the following query to basically shrink all databases on your SQL Server. However, read the blog post here to understand why you should not be doing this.

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

Change the recovery model for all databases on SQL Server

Disable Configure usage and health data collection

On a development environment, you might consider to completely turn off the ‘Configure usage and health data collection’.

Change Set-SPUsageDefinition for all events

Or use the following script to retain the events in usage to database for minimum time (for e.g. 1 day)

Get-SPUsageDefinition | ForEach-Object {Set-SPUsageDefinition -Identity $_.name -DaysRetained 1}