Sql-server – Reduce pre-allocated disk space

I have written reports, and actually alerting with it, for clients to monitor multiple servers within their test and production environments. I basically used a SQL Agent Job with a PowerShell step to pull in disk information (win32_volume) into some tables.

I then decided to create another PowerShell step that went back and checked if the free space was within a configured threshold (stored in a table). We went with this approach because it was just easier to setup and write fairly quickly. It was also cleaner code to write for a HTML report to be sent via email, than trying to do it in T-SQL.

You have DMVs in SQL Server to get disk space usage (e.g. sys.dm_os_volume_stats) but the PowerShell option above allowed us to monitor all drives found on a given server. Now caveat you will have to create a proxy account for the PowerShell execution and that account should have appropriate permissions.

An example of just getting a HTML report on free disk space.

EDIT

Just realized from Mike's comment that I had a function in my profile to, it is written a bit different than his but this is just what worked for me:

function Get-DiskSpace($server,[switch]$all)
{
        $servers = gc C:\Users\smelton\Documents\WindowsPowerShell\OSList.txt
        if ($all)
        {
            foreach ($s in $servers)
            {
                Get-WmiObject -Class Win32_Volume -ComputerName $s |
                    Select-Object @{Label='ServerName';Expression={$s}}, 
                        DriveLetter, 
                        Label, 
                        @{Label='GB Capacity';Expression={"{0:N2}" -f($_.Capacity/1GB)}},
                        @{Label='GB FreeSpace';Expression={"{0:N2}" -f($_.freespace/1GB)}},
                        @{Label='% Free';Expression={"{0:N2}" -f($_.freespace/$_.Capacity)}} |
                    Sort-Object -Property DriveLetter
            } #end foreach
        }
        else
        {
            Get-WmiObject -Class Win32_Volume -ComputerName $server | Where {$_.DriveType -eq 3} |
                Select-Object DriveLetter, Label, @{Label='GB Capacity';Expression={"{0:N2}" -f($_.Capacity/1GB)}},
                    @{Label='GB FreeSpace';Expression={"{0:N2}" -f($_.freespace/1GB)}},
                    @{Label='% Free';Expression={"{0:N2}" -f($_.freespace/$_.Capacity)}} |
                Sort-Object -Property DriveLetter
        } #end if/else
} #end Get-DiskSpace

I will be deleting approx 400 million records from this table.

Hopefully, you are doing it in chunks - to avoid bloating transaction log.

notice that i am not see any drop in my hard drive space

You wont, as you have to explicitly shrink the database file to release space. Just deleting the records, SQL server wont release the space back to the OS.

1. Since the database should not be this size. Is it still bad for me to shrink it?

It is generally a bad practice as databases should be presized properly. If you are 100% sure that your database is NOT going to be again this size, its OK for you to shrink the database (in your scenario).

2. This is a production Database. If I shrink it will it cause downtime or lock the database?

Shrinking a database is a very IO intensive. Its advisable to shrink using DBCC SHRINKFILE during maintenance window or when there is minimal activity.

3. In management studio you have two options, Database or Files. Given my situation what would be the best option?

You should use TSQL to do the shrink. (since you asked, shrinking FILE is the way to go instead of database). You can use shrink database in chunks - tsql script to do the shrink in chunks.

Remember that when you shrink, you are fragmenting your indexes.

4. is there a rule on the percentage of free space a DB should have?

You can refer to Estimating Disk Space Requirements for Databases article. There is no fix rule, you have to take into account of how your database is expected to grow. You also have to take into account, autogrowth of your databases.

References : Why Does the Transaction Log Keep Growing or Run Out of Space?