January 26, 2010 at 9:21 am
Hi,
Am working on scripts to help me in daily monitoring tasks..
I want to get the database names, its file names, total size and available space..till now am able to get all the bits except for the last one
—————————————————————————
SELECT a.name as [DBname], b.name AS ‘File Name’ , case when b.type=0 then (select ‘Data’) else ‘Log’ end as [File_type] ,b.physical_name AS ‘Physical Name’, b.size/128 AS ‘Total Size in MB’,
b.size/128.0 – CAST(FILEPROPERTY(b.name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’
FROM sys.databases a left outer join sys.master_files b
on a.database_id = b.database_id
—————————————————————————
This gives me list of everything what i want on that server but regarding “available space” it can only retrieve for the current database files..I want that for all the database files on that server..
i tried adding where clause to it " where name = 'DB name' but this won't return available space for that database files unless this query is run in that DB..
Could anyone plz help me in this..
Thanks
Shree
January 26, 2010 at 9:29 am
There are a lot of good scripts around, but here is a script that I use frequently (comment out/ remove the drive letter if that is not of interest to you):
If Object_ID('tempdb.dbo.#UsedSpace') Is Not Null
Drop Table #UsedSpace
Create Table #UsedSpace (DBName VarChar(150), FileID SmallInt, FileGroup SmallInt, TotalExtents Int, UsedExtents Int,
Name NVarChar(256), FileName NVarChar(512))
exec sp_msforeachdb 'use ?
Declare @SQL VarChar(8000)
Set @SQL = ''DBCC SHOWFILESTATS WITH TABLERESULTS''
Insert Into #UsedSpace (FileID, FileGroup, TotalExtents, UsedExtents, Name, FileName)
Exec(@SQL);
Update #UsedSpace Set DBName = ''?'' Where DBName Is Null;
'
Select DBName, Name, (TotalExtents * 64) / 1024 As TotalSpace,
(UsedExtents * 64) / 1024 As UsedSpace,
((TotalExtents * 64) / 1024) - ((UsedExtents * 64) / 1024) As FreeSpace
From #UsedSpace
--Where Left(FileName, 1) = 'E'
Order By ((TotalExtents * 64) / 1024) - ((UsedExtents * 64) / 1024) Desc
If Object_ID('tempdb.dbo.#UsedSpace') Is Not Null
Drop Table #UsedSpace
January 26, 2010 at 9:47 am
Hey Thanks David!!
Just tried the script you gave..it works great!..however it returns only the data files for all the databases..
Can we do something to get the log files as well..??
Thanks
Shree
January 26, 2010 at 9:55 am
I forgot that we can use " DBCC SQLPERF(logspace)" to get the details for the log files!!
Thanks once again David!
- Shree 🙂
January 26, 2010 at 9:57 am
Cool, I was just replying when I saw your last post. Glad it will work for you.
January 27, 2010 at 6:27 am
To monitor databases size, am using " sp_helpdb" - this gives size allocated to all the databases on a server but not the available space!
i tried using " EXEC sp_msforeachdb @command1="use ? exec sp_spaceused" " this gives me all the details but it gets on to 2 lines for each database 🙁
Could anyone plzz help in how to find available space for all databases on a server..
Thanks
Shree
January 28, 2010 at 7:08 am
Hi There,
I had just completed a similar task.Yes, the sp_spaceused returns 2 result set.I ended up reviewing the sp_spaceused and using part of the code to return the results that I want and manage to get it into a single result set.
Now I have an SSIS package created with the modified sp_spaceused in the script task that would execute daily as a job and take care of reading the information from each database and populating my staging table.
I suggest that you examine the sp_spaceused and break it part by part to what you want.You can even add on to the script.
Link to my post http://www.sqlservercentral.com/Forums/Topic840084-147-1.aspx
January 28, 2010 at 3:18 pm
Here is a stored proc I have used to collect space data on multiple db's. it might provide some ideas for you?
I collect total allocated and total used(reserved). Should be able to get available from that...;-)
THe table I am inserting into:
CREATE TABLE [dbo].[dbSpaceDist](
[DB] [char](40) NULL,
[DB_Tot] [numeric](9, 2) NULL,
[DB_Used] [numeric](9, 2) NULL,
[Log_Tot] [numeric](9, 2) NULL,
[Log_Used] [numeric](9, 2) NULL,
[EntryDt] [datetime] NULL
) ON [PRIMARY]
The stored proc:
CREATE procedure [dbo].[dbspCalcdbaSpaceDist]
@days int = -732
as
/*
*************************************************************
Name: dbspCalcdbaSpaceDist
Description:
Gather the data and log space for all databases on the system
and insert the information into DBSpaceDist. The following databases
are not added to DBSpaceDist:
pubs
Northwind
model
tempdb
Usage:exec dbspCalcdbaSpaceDist -90
Author:
Input Params:
-------------
@days int. Number of days to keep in DBASpaceDist.
Locals:
--------
@errHolds error value
*************************************************************
*/
set nocount on
declare @err int
select @err = 0
/*
Create the temp tables to hold the results of DBCC
commands until the information is entered into
DBASpaceDist.
*/
DECLARE @tbl_logspace table(
DBName char( 40),
LogSize float,
PrcntUsed float,
status int
)
DECLARE @tbl_dataspace table(
FileID int,
FileGrp int,
TotExtint,
UsdExt int,
LFileNmvarchar( 100),
PFileNmvarchar( 100)
)
/*
Get the log space
*/
INSERT INTO @tbl_logspace
EXEC ('dbcc sqlperf( logspace)')
/*
Get the data space
Use a cursor to loop through the results from DBCC
since you have to run this command from each database
with a USE command.
for each database
insert the sum of the size columns and the log info from
the cursor into a row in dbaSpaceDist table
*/
declare @db char( 40), @cmd char( 500)
, @logsize numeric(10,4), @prcntused numeric(10,4)
declare dbname cursor for
select DBName, LogSize, PrcntUsed from @tbl_logspace
FOR READ ONLY
open dbname
fetch next from dbname into @db, @logsize, @prcntused
while @@fetch_status = 0
begin
select @cmd = 'use ' + rtrim( @db) + ' dbcc showfilestats'
insert @tbl_dataspace
exec( @cmd)
if @db in
('db1',
'db2',
'db3',
'db4',
'db5',
'db6',
'tempdb'
)
insert [dbo].[dbSpaceDist]
([DB]
,[DB_Tot]
,[DB_Used]
,[Log_Tot]
,[Log_Used]
,[EntryDt]
)
select
@db,
((cast( sum(TotExt) as numeric( 9, 2))* 32) / 512),
((cast( sum(UsdExt) as numeric( 9, 2))* 32) / 512),
@logsize,
(@logsize * (@prcntused/100)),
getdate()
from @tbl_dataspace d
fetch next from dbname into @db, @logsize, @prcntused
delete @tbl_dataspace
end
close dbname
deallocate dbname
/*
Remove old information from the DBASpaceDist table.
*/
delete dbSpaceDist
where entrydt < dateadd( day, @days, getdate())
return @err
jg
January 28, 2010 at 5:31 pm
Hello,
This is what you are looking for :
USE MASTER
GO
CREATE TABLE #TMPFIXEDDRIVES (
DRIVE CHAR(1),
MBFREE INT)
INSERT INTO #TMPFIXEDDRIVES
EXEC xp_FIXEDDRIVES
CREATE TABLE #TMPSPACEUSED (
DBNAME VARCHAR(50),
FILENME VARCHAR(50),
SPACEUSED FLOAT)
INSERT INTO #TMPSPACEUSED
EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')
SELECT C.DRIVE,
CASE
WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
END AS DISKSPACEFREE,
A.NAME AS DATABASENAME,
B.NAME AS FILENAME,
CASE B.TYPE
WHEN 0 THEN 'DATA'
ELSE TYPE_DESC
END AS FILETYPE,
CASE
WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
END AS FILESIZE,
CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,
B.PHYSICAL_NAME
FROM SYS.DATABASES A
JOIN SYS.MASTER_FILES B
ON A.DATABASE_ID = B.DATABASE_ID
JOIN #TMPFIXEDDRIVES C
ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
JOIN #TMPSPACEUSED D
ON A.NAME = D.DBNAME
AND B.NAME = D.FILENME
ORDER BY DISKSPACEFREE,
SPACEFREE DESC
DROP TABLE #TMPFIXEDDRIVES
DROP TABLE #TMPSPACEUSED
I use this for space monitoring.
Hope this helps !
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 29, 2010 at 1:46 am
Thanks a lott for the replies..:)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply