Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-04-19 : 10:26:52
|
How to check the free space in a DB file using TSQL?------------------------I think, therefore I am - Rene Descartes |
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-04-19 : 10:32:41
|
You can use the FILEPROPERTY system function, or the sp_spaceused system procedure. sp_spaceused gives only an estimate (at least it did through SQL 2000. That may have changed). |
 |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-04-19 : 10:44:35
|
Thank you mcrowley!1. File property gives the total space, not the FREE SPACE in a file. 2. didn't work sp_spaceused for file------------------------I think, therefore I am - Rene Descartes |
 |
|
pootle_flump
1064 Posts |
Posted - 2007-04-19 : 11:09:59
|
quote: Originally posted by ravilobo 2. didn't work sp_spaceused for file
Could you expand on that?Undocumented DBCC command - treat with caution:DBCC showfilestats |
 |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-04-19 : 11:39:12
|
DBCC showfilestats-- Doesn't gives me the free space sp_spaceused 'DB_file_Name'Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62The object 'DB_file_Name' does not exist in database 'DB_file_Name' or is invalid for this operation.------------------------I think, therefore I am - Rene Descartes |
 |
|
pootle_flump
1064 Posts |
Posted - 2007-04-19 : 11:59:43
|
quote: Originally posted by ravilobo DBCC showfilestats-- Doesn't gives me the free space
Come on - it tells you the size and the used space. I think therefore I am.... exec MyDbName.dbo.sp_spaceused |
 |
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-19 : 12:13:32
|
Here's some code I stole from somewhere on the net:----------------BEGIN SET NOCOUNT ON IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DBFileInfo')) BEGIN DROP TABLE #DBFileInfo END IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#LogSizeStats')) BEGIN DROP TABLE #LogSizeStats END IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DataFileStats')) BEGIN DROP TABLE #DataFileStats END IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#FixedDrives')) BEGIN DROP TABLE #FixedDrives END CREATE TABLE #FixedDrives (DriveLetter VARCHAR(10), MB_Free DEC(20,2)) CREATE TABLE #DataFileStats (DBName VARCHAR(255), DBId INT, FileId TINYINT, [FileGroup] TINYINT, TotalExtents DEC(20,2), UsedExtents DEC(20,2), [Name] VARCHAR(255), [FileName] VARCHAR(400)) CREATE TABLE #LogSizeStats (DBName VARCHAR(255) NOT NULL PRIMARY KEY CLUSTERED, DBId INT, LogFile REAL, LogFileUsed REAL, Status BIT) CREATE TABLE #DBFileInfo ([ServerName] VARCHAR(255), [DBName] VARCHAR(65), [LogicalFileName] VARCHAR(400), [UsageType] VARCHAR (30), [Size_MB] DEC(20,2), [SpaceUsed_MB] DEC(20,2), [MaxSize_MB] DEC(20,2), [NextAllocation_MB] DEC(20,2), [GrowthType] VARCHAR(65), [FileId] SMALLINT, [GroupId] SMALLINT, [PhysicalFileName] VARCHAR(400), [DateChecked] DATETIME) DECLARE @SQLString VARCHAR(3000) DECLARE @MinId INT DECLARE @MaxId INT DECLARE @DBName VARCHAR(255) DECLARE @tblDBName TABLE (RowId INT IDENTITY(1,1), DBName VARCHAR(255), DBId INT) INSERT INTO @tblDBName (DBName,DBId) SELECT [Name],DBId FROM Master..sysdatabases WHERE [name] = 'FASTPROD'--(Status & 512) = 0 /*NOT IN (536,528,540,2584,1536,512,4194841)*/ ORDER BY [Name] INSERT INTO #LogSizeStats (DBName,LogFile,LogFileUsed,Status) EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS') UPDATE #LogSizeStats SET DBId = DB_ID(DBName) INSERT INTO #FixedDrives EXEC Master..XP_FixedDrives SELECT @MinId = MIN(RowId), @MaxId = MAX(RowId) FROM @tblDBName WHILE (@MinId <= @MaxId) BEGIN SELECT @DBName = [DBName] FROM @tblDBName WHERE RowId = @MinId SELECT @SQLString = 'SELECT ServerName = @@SERVERNAME,'+ ' DBName = '''+@DBName+''','+ ' LogicalFileName = [name],'+ ' UsageType = CASE WHEN (64&[status])=64 THEN ''Log'' ELSE ''Data'' END,'+ ' Size_MB = [size]*8/1024.00,'+ ' SpaceUsed_MB = NULL,'+ ' MaxSize_MB = CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN [size]*8/1024.00 ELSE maxsize*8/1024.00 END,'+ ' NextExtent_MB = CASE WHEN (1048576&[status])=1048576 THEN ([growth]/100.00)*([size]*8/1024.00) WHEN [growth]=0 THEN 0 ELSE [growth]*8/1024.00 END,'+ ' GrowthType = CASE WHEN (1048576&[status])=1048576 THEN ''%'' ELSE ''Pages'' END,'+ ' FileId = [fileid],'+ ' GroupId = [groupid],'+ ' PhysicalFileName= [filename],'+ ' CurTimeStamp = GETDATE()'+ 'FROM '+@DBName+'..sysfiles' PRINT @SQLString INSERT INTO #DBFileInfo EXEC (@SQLString) UPDATE #DBFileInfo SET SpaceUsed_MB = (SELECT LogFileUsed FROM #LogSizeStats WHERE DBName = @DBName) WHERE UsageType = 'Log' AND DBName = @DBName SELECT @SQLString = 'USE ' + @DBName + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS' INSERT #DataFileStats (FileId,[FileGroup],TotalExtents,UsedExtents,[Name],[FileName]) EXECUTE(@SQLString) UPDATE #DBFileInfo SET [SpaceUsed_MB] = S.[UsedExtents]*64/1024.00 FROM #DBFileInfo AS F INNER JOIN #DataFileStats AS S ON F.[FileId] = S.[FileId] AND F.[GroupId] = S.[FileGroup] AND F.[DBName] = @DBName TRUNCATE TABLE #DataFileStats SELECT @MinId = @MInId + 1 END SELECT [ServerName], [DBName], [LogicalFileName], [UsageType] AS SegmentName, B.MB_Free AS FreeSpaceInDrive, [Size_MB], [SpaceUsed_MB], [MaxSize_MB], [NextAllocation_MB], CASE MaxSize_MB WHEN -1 THEN CAST(CAST(([NextAllocation_MB]/[Size_MB])*100 AS INT) AS VARCHAR(10))+' %' ELSE 'Pages' END AS [GrowthType], [FileId], [GroupId], [PhysicalFileName], [DateChecked] FROM #DBFileInfo AS A LEFT JOIN #FixedDrives AS B ON SUBSTRING(A.PhysicalFileName,1,1) = B.DriveLetter ORDER BY DBName,GroupId,FileId IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DBFileInfo')) BEGIN DROP TABLE #DBFileInfo END IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#LogSizeStats')) BEGIN DROP TABLE #LogSizeStats END IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DataFileStats')) BEGIN DROP TABLE #DataFileStats END IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#FixedDrives')) BEGIN DROP TABLE #FixedDrives ENDENDGO************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-04-19 : 12:51:28
|
quote: Originally posted by pootle_flumpexec MyDbName.dbo.sp_spaceused
Thank you. I was using it in a diffrent way ;-)------------------------I think, therefore I am - Rene Descartes |
 |
|
pootle_flump
1064 Posts |
Posted - 2007-04-19 : 12:52:15
|
Yeah - the parameter you used is for the object (table).Glad to help |
 |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-04-19 : 12:55:30
|
Thnaks anyway!------------------------I think, therefore I am - Rene Descartes |
 |
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-04-19 : 14:13:39
|
quote: Originally posted by ravilobo Thank you mcrowley!1. File property gives the total space, not the FREE SPACE in a file. 2. didn't work sp_spaceused for file------------------------I think, therefore I am - Rene Descartes
Have a look at the FILEPROPERTY ( file_name , property ) function in BOL. I do not believe it gives you total space at all. |
 |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-04-19 : 16:08:14
|
Thats right! Today is a bad day..;-)------------------------I think, therefore I am - Rene Descartes |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-04-19 : 19:29:37
|
This should do what you want:select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) , NAME = left(a.NAME,15), FILENAME = left(a.FILENAME,30)from dbo.sysfiles aResults:FILEID FILE_SIZE_MB SPACE_USED_MB FREE_SPACE_MB NAME FILENAME ------ -------------- -------------- -------------- --------------- ------------------------------ 1 2.94 2.88 .06 Northwind D:\MSSQL\DATA\northwnd.mdf 2 1.00 .46 .54 Northwind_log D:\MSSQL\DATA\northwnd.ldf (2 row(s) affected) Edit: Changed to show 2 decimal places and simplify calculation of free space.CODO ERGO SUM |
 |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-04-19 : 19:44:47
|
Thank you MVJ! Thats great! I am trying to understand how it works.------------------------I think, therefore I am - Rene Descartes |
 |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-04-19 : 19:49:18
|
Why you are dividing by 128 and not by 1024?------------------------I think, therefore I am - Rene Descartes |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-04-19 : 20:48:24
|
quote: Originally posted by ravilobo Why you are dividing by 128 and not by 1024?...
select DB_PAGES_PER_MEGABYTE = BYTES_PER_MEGABYTE/BYTES_PER_DB_PAGE , *from ( select BYTES_PER_MEGABYTE = 1024*1024 , BYTES_PER_DB_PAGE = 1024*8 ) aResults:DB_PAGES_PER_MEGABYTE BYTES_PER_MEGABYTE BYTES_PER_DB_PAGE --------------------- ------------------ ----------------- 128 1048576 8192(1 row(s) affected) CODO ERGO SUM |
 |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-04-19 : 20:58:46
|
Got it size is in 8KB pages!!!! Never knew this....------------------------I think, therefore I am - Rene Descartes |
 |
|
Jagadeshs
Starting Member
1 Post |
Posted - 2010-04-13 : 19:17:57
|
Hi Michael Valentine JonesIt is a excellet Query that you Posted on 04/19/2007 at 19:29:37.Cheers,Jagi. |
 |
|
k3nnyg
Starting Member
3 Posts |
Posted - 2012-06-06 : 08:20:34
|
Here is the above query modified to give you the free space for each DB on the server. (Note that sp_msforeachdb is an undocumented command)Well done on the query btw :)Exec sp_msforeachdb 'use select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) , NAME = left(a.NAME,15), FILENAME = left(a.FILENAME,30)from dbo.sysfiles a' |
 |
|
k3nnyg
Starting Member
3 Posts |
Posted - 2012-06-06 : 08:24:54
|
In the post above in front of "use" should be [ ? ] (Left bracket, Question mark, right bracket)no spaces. I dont know if it shows up differently for others but for me it just shows up as a circle with a question mark in it. |
 |
|
airbiker
Starting Member
1 Post |
Posted - 2013-05-27 : 11:18:38
|
I really like that thread and find it usefull.Here's my little improvement:use mastergoselect NAME = left(a.NAME,15),FILENAME = left(a.FILENAME,30),a.FILEID,[FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),[SPACE_USED_MB] =convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),[FREE_SPACE_MB] =convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) into #temp_db_free_spacefromdbo.sysfiles awhere 0=1Exec sp_msforeachdb 'use insert into #temp_db_free_spaceselectNAME = left(a.NAME,15),FILENAME = left(a.FILENAME,30),a.FILEID,[FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),[SPACE_USED_MB] =convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),[FREE_SPACE_MB] =convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) fromdbo.sysfiles a'select * from #temp_db_free_spaceorder by 1, 2drop table #temp_db_free_space (replace question mark by left-bracket question-mark right-bracket)This will give you a nice list where you can sort it afterward.Guy |
 |
|
Next Page
|
|
|
|
|