Thursday, November 29, 2007 10:14 AMHello
Am trying to create a script that will determine the amount of space is been used on both my file server and sql server for all jobs residing on an instance. Once this result has been determined, I will like to populate them into another table, which will be used to develop a report model, using SSRS.
Am using the xp_fixeddrives query, but it only returns the drive letter and the amount of free space.
Can anyone provide me with any ideas, pls?
Friday, November 30, 2007 12:55 AM
See the script and article below:
SELECT getdate() AS Date,
name AS NameOfFile,
size/128.0 AS SizeInMB,
CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS SpaceUsedInMB,
size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB,
-- Spaceused by Tables
SELECT object_name(id) AS name,
rowcnt AS rows,
reserved * 8 AS reserved_kb,
dpages * 8 AS data_kb,
(sum(used) * 8) - (dpages * 8) AS index_size_kb,
(sum(reserved) * 8) - (sum(used) * 8) AS unused_kb
WHERE indid IN (0,1) -- cluster e não cluster
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
GROUP BY id, indid, rowcnt, reserved, dpages
ORDER BY rowcnt DESC