| Author |
 Topic  |
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
 Posted - 04/19/2007 : 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 - 04/19/2007 : 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
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 04/19/2007 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 04/19/2007 : 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
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 04/19/2007 : 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 62
The 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
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 04/19/2007 : 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
Flowing Fount of Yak Knowledge
USA
2507 Posts |
Posted - 04/19/2007 : 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
END
END
GO
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/ |
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 04/19/2007 : 12:51:28
|
quote:
Originally posted by pootle_flump
exec MyDbName.dbo.sp_spaceused
Thank you. I was using it in a diffrent way ;-)
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 04/19/2007 : 12:52:15
|
Yeah - the parameter you used is for the object (table).
Glad to help
|
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 04/19/2007 : 12:55:30
|
Thnaks anyway!
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 04/19/2007 : 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
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 04/19/2007 : 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)
USA
6997 Posts |
Posted - 04/19/2007 : 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 a
Results:
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 |
Edited by - Michael Valentine Jones on 04/19/2007 20:49:58 |
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 04/19/2007 : 19:44:47
|
Thank you MVJ! Thats great!
I am trying to understand how it works.
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 04/19/2007 : 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)
USA
6997 Posts |
Posted - 04/19/2007 : 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
) a
Results:
DB_PAGES_PER_MEGABYTE BYTES_PER_MEGABYTE BYTES_PER_DB_PAGE
--------------------- ------------------ -----------------
128 1048576 8192
(1 row(s) affected)
CODO ERGO SUM |
|
|
|
ravilobo
Flowing Fount of Yak Knowledge
India
1183 Posts |
Posted - 04/19/2007 : 20:58:46
|
Got it size is in 8KB pages!!!! Never knew this....
------------------------
I think, therefore I am - Rene Descartes
|
|
|
|
Jagadeshs
Starting Member
Australia
1 Posts |
Posted - 04/13/2010 : 19:17:57
|
Hi Michael Valentine Jones
It is a excellet Query that you Posted on 04/19/2007 at 19:29:37.
Cheers,
Jagi. |
|
|
|
k3nnyg
Starting Member
USA
2 Posts |
Posted - 06/06/2012 : 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
USA
2 Posts |
Posted - 06/06/2012 : 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. |
|
|
| |
Topic |
|
|
|
Free space in DB file.
