Any Extended Stored Procedure is there to find out the number of drives in the server and their capacity
-
Hi,
Any Extended/Undocumented Stored Procedure is there to find out the drive name and their capacity in the server .(we know xp_fixeddrives gives only the free space in each drive i want to know the capacity of the each drive)
Any Stored proc/Extended Stored proc /Undocumented DBCC Command to find out size/max size of the datafile and data size used % (we know that dbcc sqlperf(logspace) gives log space utilization of each database. lll ly let me know the command to find out the data file size of each database in a server)
Thanks in anticipation
-
Here's a stored procedure that will get all local drives, capacity, and free space. I didn't write this, and I'm not sure who to give credit to. Chances are that it came from this site, though.
I also have a procedure for database file usage. I'll post it in a separate post.
Steve
CREATE PROCEDURE af_DiskSpace
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @DateTime datetime
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #af_disk_space
([#Drive] [char] (1) NOT NULL ,
[#TotalSizeMB] [int] NULL ,
[#FreeSpaceMB] [int] NULL ,
[#FreeSpacePCT] [int] NULL ,
[#FSDateStamp] [datetime] NULL)
set @DateTime = convert(varchar(10), getdate(),101)
INSERT #af_Disk_Space(#Drive,#FreeSpaceMB)
EXEC master.dbo.xp_fixeddrives
UPDATE #af_Disk_Space
SET #FSDateStamp = @DateTime
-- WHERE #FSDateStamp is null
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT #Drive from #af_Disk_Space
--WHERE #FSDateStamp = @DateTime
ORDER by #Drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #af_Disk_Space
SET #TotalSizeMB=@TotalSize/@MB, #FreeSpacePCT = ((#FreeSpaceMB/((@TotalSize/@MB)*1.0))*100.0)
WHERE #Drive=@drive --and #FSDateStamp = @DateTime
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT
#Drive as 'Drive',
#TotalSizeMB as 'Total(MB)',
#FreeSpaceMB as 'Free(MB)',
#FreeSpacePCT as 'Free(%)',
#FSDateStamp as 'Date Checked'
FROM #af_Disk_Space
ORDER BY #Drive
--RETURN
drop table #af_Disk_Space
GO
-
You also can create your stored procedure from this VB script (result is in file C:\disk_log.txt) or use this script in DTS package to fill your report table:
Const HARD_DISK = 3
Const ForAppending = 2
strComputer = "."
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
("C:\disk_log.txt", ForAppending, True)
objTextFile.WriteLine("DeviceID, FreeSpace, Size ")
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colDisks = objWMIService.ExecQuery _
("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")
For Each objDisk in colDisks
objTextFile.WriteLine(objDisk.DeviceID & ", " & objDisk.FreeSpace & ", " & objDisk.Size)
Next
objTextFile.Close
--------------------------
Result file looks like this:
DeviceID, FreeSpace, Size
C:, 22657998848, 30725742592
D:, 80170778624, 160039239680
-
Here's the procedure that checks database file space. It does do log files too, though. I didn't write this one either, but I do know who did. Some guy by the name of Steve Jones posted it on this site (
). I have made quite a few changes to Steve's procedure. It all should be documented.Steve (not Jones)
if object_Id('af_FileSpace') Is Not Null
drop table af_FileSpace
go
CREATE TABLE af_FileSpace (
DBName varchar(128),
LogicalName varchar(128),
FileType char(4),
TotalMB numeric(10,4),
UsedMB numeric(10,4),
PercentUsed numeric(10,4),
RunDate datetime
 
GO
if object_id( 'af_FileSpace') Is Null
select ' af_FileSpace Not Created'
else
select ' af_FileSpace Created'
go
if object_Id( 'af_getFileSpace') Is Not Null
drop procedure af_getFileSpace
go
CREATE procedure af_getFileSpace
@days int = 90
as
/*
*************************************************************
Name: af_getFileSpace
Description:
Gather the data and log space for all databases on the system
and insert the information into af_FileSpace. The following databases
are not added to af_FileSpace:
pubs
Northwind
model
tempdb
Usage:exec af_getFileSpace 90
Author: Steve Jones
Input Params:
-------------
@days int. Number of days to keep in af_FileSpace. defaults to
90 days (1 quarter).
Output Params:
--------------
Return:
Results:
---------
Locals:
--------
@err Holds error value
Modifications:
--------------
8/10/2004 Steve Phelps - modified table structure, corrected logical names,
corrected percent calculations (also changed to percent used instead of
percent free), modified @days parameter to use a positive value(by simply muliplying by -1).
*************************************************************
*/
set nocount on
declare
@err int,
@run_date datetime
select @err = 0
select @run_date = getdate()
select @days = @days * -1
/*
Create the temp tables to hold the results of DBCC
commands until the information is entered into
af_FileSpace.
*/
CREATE TABLE #logspace (
DBName varchar( 100),
LogSize float,
PrcntUsed float,
status int
)
CREATE TABLE #dataspace
( FileID int,
FileGrp int,
TotExt int,
UsdExt int,
LFileNm varchar( 100),
PFileNm varchar( 100)
)
/*
Get the log space
*/
INSERT INTO #logspace
EXEC ('dbcc sqlperf( logspace)')
-- problem: this is ok for dbs with 1 log file. However, if there are multiple
-- log files, it will aggregate the data rather than providing stats for each file.
insert af_FileSpace
select dbname,
' ',
'Log',
logsize,
(logsize * (PrcntUsed/100)),
PrcntUsed,
@run_date
from #logspace
where dbname not in('Pubs', 'Northwind', 'model', 'tempdb')
-- SQL Server treats multiple log files as a single file. Details for individual files is not available,
-- so in the case of a database with multiple log files, the statistics for the log are aggregated, and the
-- logical filename is reported as 'n/a, multiple files'.
update afs
set LogicalName = 'n/a, multiple files'
from af_FileSpace afs
join (select db_name(dbid) as dbname-- count(*) as [count]
from master..sysaltfiles where status &0x40 = 0x40
group by dbid
having count(dbid) > 1) sa
on sa.dbname = afs.DBName
where afs.FileType = 'Log'
-- Logs with multiple files have been identified. Now retrieve logical file names for single file logs.
update afs
set LogicalName = sa.name
from af_FileSpace afs
join master..sysaltfiles sa
on afs.DBName = db_name(sa.dbid)
where sa.status &0x40 = 0x40
and (afs.LogicalName is null or afs.LogicalName = ' ')
/*
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.
*/
declare @db char( 40), @cmd char( 500)
declare db_cur cursor
for select DBName from #logspace where dbname not in('Pubs', 'Northwind', 'model', 'tempdb')
open db_cur
fetch next from db_cur into @db
while @@fetch_status = 0
begin
select @cmd = 'use ' + rtrim(@db) + ' dbcc showfilestats'
insert #dataspace
exec( @cmd)
insert af_FileSpace
select
@db,
LfileNM,
'Data',
((cast( TotExt as numeric(10,4))* 64) / 1024),
((cast( UsdExt as numeric(10,4))* 64) / 1024),
(cast(cast(UsdExt as numeric(10,4)) / cast(TotExt as numeric(10,4)) as numeric (10,4)) * 100),
@run_date
from #dataspace
fetch next from db_cur into @db
delete #dataspace
end
close db_cur
deallocate db_cur
/*
Drop the temporary tables
*/
drop table #logspace
drop table #dataspace
/*
Remove old information from the af_FileSpace table.
*/
delete af_FileSpace
where RunDate < dateadd( day, @days, @run_date)
return @err
GO
if object_id( 'af_getFileSpace') Is Null
select 'af_getFileSpace Not Created'
else
select 'af_getFileSpace Created'
go
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply