April 3, 2008 at 11:59 am
I am trying to come up with a script that will provide me with the remaining space on a server as one number, but only for the drives where the data and log files are stored (in other words, I don't care about free space remaining on the C: drive, only D:, F:, G:, etc). Those drive letters may or may not exist, depending on whether the server is clustered and/or san-attached.
So far, I've come up with the following:
Step1:
SELECT DISTINCT left(filename, 1) from dbo.sysdatabases --> This will return to me the list of drives where datafiles are stored
Step2:
Create table #DrvSpace (
DriveLetter char(02) null,
MB_Free float null)
INSERT into #DrvSpace EXECUTE master..xp_fixeddrives
SELECT 'Total Remaining Disk Space' = SUM(MB_Free)
FROM #DrvSpace
WHERE DriveLetter = 'D'
Drop Table #DrvSpace
GO
Now, what I'm interested in is the following:
- How do I find the drive letter where the log files are stored, similar to the method I used in querying dbo.sysdatabases?
- How can I make it so that SELECT statement in the Step2 can dynamically check for WHERE DriveLetter IN (List of results from the SELECT LEFT... portion, so the individual user doesn't have manually perform 2 steps and plug in the drive letters themselves?
Since the question may come up, I'm doing this because I have to document a capacity planning process we use, and one of the columns in our planning spreadsheet that we have to fill in is "Total Free Space Remaining" (on drives where data and log files are stored - not always the same drive, naturally), and I'm trying to come up with a solution the user can just cut-and-paste into management studio and voila - Total Free Space remaining on the pertinent drives, which can then be cut and pasted right into the spreadsheet.
As I'm very new to SQL Server, there is probably a much easier way to do this, and if so, please tell me. I'm not hung up on getting this specific query to work, just the end result.
"Got no time for the jibba jabba!"
-B.A. Baracus
April 3, 2008 at 1:02 pm
Now, what I'm interested in is the following:
- How do I find the drive letter where the log files are stored, similar to the method I used in querying dbo.sysdatabases?
- How can I make it so that SELECT statement in the Step2 can dynamically check for WHERE DriveLetter IN (List of results from the SELECT LEFT... portion, so the individual user doesn't have manually perform 2 steps and plug in the drive letters themselves?
1. something like this?
CREATE TABLE #DBLoc
(
Fileid SMALLINT,
groupid SMALLINT,
size INT,
maxsize INT,
growth INT,
status INT,
perf INT,
NAME sysname,
filename NVARCHAR(260)
)
INSERT INTO [#DBLoc]
EXEC MASTER..sp_msforeachdb "SELECT * FROM ?..sysfiles"
SELECT SUBSTRING(filename, 1, 1) + ' contains the log files ' AS Drive
FROM [#DBLoc]
WHERE filename LIKE '%.ldf'
GROUP BY SUBSTRING(filename, 1, 1)
2 Something like this as well?
SELECT 'Total Remaining Disk Space' = SUM(MB_Free)
FROM #DrvSpace
WHERE DriveLetter <> 'C'
Be careful, I had a Quorum drive as well (for the clustered server), make sure you do or do not want this. We standardized the Quorum drive here to be Q, so I could exclude it if I desired.
-- Cory
April 3, 2008 at 1:50 pm
Cory Ellingson (4/3/2008)
2 Something like this as well?
SELECT 'Total Remaining Disk Space' = SUM(MB_Free)
FROM #DrvSpace
WHERE DriveLetter <> 'C'
Be careful, I had a Quorum drive as well (for the clustered server), make sure you do or do not want this. We standardized the Quorum drive here to be Q, so I could exclude it if I desired.
LOL, it's been a long week - I guess <> 'C' is certainly simple enough.
This won't give me exactly what I need everytime, but enough of the time for me not to worry about it anymore. 🙂
"Got no time for the jibba jabba!"
-B.A. Baracus
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply