SQL Server Performance Forum – Threads Archive
Free space in a fileIs 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
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
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
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]
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