Programmatically determine the available free space in the database?

Question

Ok, I tried looking for this answer, but no luck. I have about 50 databases on our SQL Server at work that I have to check almost every day to find out if any space can be freed up by shrinking. Our discs tend to fill up a lot, which is why this is almost an everyday task.

Is there a quick way to check which databases actually have free space? Does anyone know of a system / user stored procedure or system view that can generate a list of each database and how much free space is available for shrinking in that database?

By the way, using SQL Server 2005.

Answer 1

---

Run sp_spacedused for each database:

--temporary table to hold database names CREATE TABLE #Databases (name varchar(255)) INSERT INTO #Databases SELECT name FROM master..sysdatabases DECLARE abc CURSOR FOR SELECT name FROM #Databases DECLARE @name varchar(255) DECLARE @sql nvarchar(1024) OPEN abc FETCH NEXT FROM abc INTO @name WHILE @@FETCH_STATUS = 0 BEGIN --build sql to switch to that database... SET @sql = 'USE ' +@name PRINT @sql EXECUTE (@sql) --and run spaceused on it SET @sql = 'EXECUTE sp_spaceused @UpdateUsage=True' PRINT @sql EXECUTE (@sql) FETCH NEXT FROM ABC INTO @name END CLOSE abc DEALLOCATE abc DROP TABLE #Databases 

---

Examples of special results

Run T-SQL:

 USE Contoso EXECUTE sp_spaceused @UpdateUsage=True 

Results:

 database_name: Contoso database_size: 85.13 MB unallocated_space: 15.41 MB reserved: 70,368 KB (68.7 MB) data: 42,944 KB (41.9 MB) index_size: 24,200 KB (23.6 MB) unused: 3,224 KB ( 3.1 MB) 

Squeeze:

 DBCC SHRINKDATABASE (Contoso) 

Check the space again:

 EXECUTE sp_spaceused @UpdateUsage=True 

Results:

 database_name: Contoso database_size: 69.81 MB unallocated_space: 0.20 MB reserved: 70,256 KB (68.6 MB) data: 43,024 KB (42.0 MB) index_size: 24,200 KB (23.6 MB) unused: 3,032 KB ( 3.0 MB) 

Answer 2

The answer is not to worry about how much free space is in the databases, but instead perform the night or weekly maintenance task that performs the compaction. Most likely, you do not compress your log files (this is where I usually had the greatest growth).

Run a full backup, then start the transaction log backup with TRUNCATE_ONLY, and as soon as it is done, run DBCC SHRINKFILE () in your log files and databases.

Then you only need to control the total available disk space, and not the individual growth of the database.

SQL Server abbreviation database . You must fully back up and back up the transaction log before this works.

You can get information about individual files loaded when you run 'select * from master.dbo.sysdatabases' and run 'select * from [dbname] .. sysfiles'. I’m not 100%, I almost never touch an instance of SQL Server for almost a year, sysfiles can be in the "master" database.

Answer 3

Maybe something in the WMI or SQL views. But I have to ask - how does the cost of your time lamenting this every day compare to buying some disk drives?

Answer 4

You can use sp_spaceused and use its result, or you can look at yourself in the sys.allocations_units table and calculate the total pages used. You should not look at the “database” level, but at the file level, because some may have multiple NDFs. Start with sys.database_files to view all the files in the database files. The size column will contain the total number of pages in the database. Summarize the common pages from sys.allocation_units to get the pages used:

 with sum_au as ( select data_space_id , sum(total_pages) as total_pages from sys.allocation_units au group by data_space_id) select ds.name , df.size , total_pages as in_use from sys.database_files df join sys.data_spaces ds on df.data_space_id = ds.data_space_id join sum_au au on au.data_space_id = ds.data_space_id