SQL Server Performance Forum – Threads Archive

Free space in a file

Is there a way to tell how much free space there is in a file besides using the EM task pad view?

---

Yes run SP_SPACEUSED @updateusage=’true’ to get optimum values. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.

---

Let me re-phrase the question. The Taskpad view in EM shows a list of files in the database and how much space is used in each file. I am wondering if there is a SP or table that can be used to get the same info without having to use EM. SP_SPACEUSED shows how much total space is available in the database.

---

Take help from Nigel’s scripthttp://www.nigelrivett.net/SQLAdmin/SpaceUsedAllTables.html andhttp://www.databasejournal.com/features/mssql/article.php/3386661 for further fun. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

–Use below script to find how much data is there in data file….
declare @SQLstring as nvarchar(50) set @SQLstring = N’DBCC SHOWFILESTATS’
create table #DBSpace(FielId tinyint, Filegroup tinyint, TotalSpace int, Used_Space int, name1 varchar(25),NameofFile Varchar(200) ) use [DatabaseName]
Go
insert into #DBSpace execute sp_executesql @SQLstring select Name1,TotalSpace/16 as ‘Total Database Size (MB)’,Used_Space/16 as ‘Total Used Space (MB)’ from #DBSpace Drop table #DBSpace Deepak Kumar

---

]]>