September 7, 2008 at 7:10 pm
rbarryyoung (9/7/2008)
I like the powershell idea though, but I'm not proficient enough to evaluate it yet.
Yeah - I know what you mean, since I am only proficient enough to have cobbled this together. There is so much more that could be done, but I have not had to time to work on it. And, of course - it really needs to have better documentation 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 8, 2008 at 3:25 pm
WMI, much easier 😎
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM Win32_LogicalDisk where drivetype=3",,48)
For Each objItem in colItems
Wscript.Echo "-----------------------------------"
Wscript.Echo "Win32_LogicalDisk instance"
Wscript.Echo "-----------------------------------"
Wscript.Echo "DeviceID: " & objItem.DeviceID
Wscript.Echo "DriveType: " & objItem.DriveType
Wscript.Echo "Size: " & objItem.Size / 1073741824 & " GB's"
Wscript.Echo "FreeSpace: " & objItem.FreeSpace /1073741824 & " GB's"
Wscript.Echo "FileSystem: " & objItem.FileSystem
Wscript.Echo "Name: " & objItem.Name
Wscript.Echo "VolumeName: " & objItem.VolumeName
Wscript.Echo "VolumeSerialNumber: " & objItem.VolumeSerialNumber
Next
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 9, 2008 at 5:46 am
There are a lot of really good artices on SQLServerCentral.com that can point you in the right direction. One of the ones that I think might be a good fit is:
http://www.sqlservercentral.com/articles/Monitoring/monitoringdriveanddatabasefreespace/1415/
... Jerry
September 9, 2008 at 6:11 am
Good article, but you do have to audit all the drives beforehand to use it. And, what if a new drive is made available by the SysOps? The code is another source of required maintenance. HOWEVER, it does solve the requirement of not having to make a trip to xp_CmdShell or sp_OA* sprocs. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2008 at 8:27 am
This is a VBScript I've been running for a while. It takes each server name listed in a txt file, connects, dynamically grabs the drive info, and then puts a record for each drive into a table. I run it hourly to check on space. At the end of the month I trim the results down to 1 a day for an archive.
'Objective: Find Disk Free Space in all the listed servers and write to a database
Dim AdCn
Dim AdRec
Dim i, SQL
Set AdCn = CreateObject("ADODB.Connection")
Set AdRec = CreateObject("ADODB.Recordset")
' NOTE: Change the connection string according to your environment.
AdCn.Open = "Provider=SQLOLEDB.1;Data Source=ServerName;Initial Catalog=DiskMonitor;user id = 'username';password='Password' "
Set iFSO = CreateObject("Scripting.FilesyStemObject")
'Input file for server names
InputFile="c:\computerlist.txt"
Set ifile = iFSO.OpenTextFile(inputfile)
Const MBCONVERSION= 1048576
Do until ifile.AtEndOfLine
Computer= ifile.ReadLine
Set objWMIService = GetObject("winmgmts://" & Computer)
Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk")
For Each objLogicalDisk In colLogicalDisk
if objLogicalDisk.drivetype=3 then
SQL = "Insert into FreeSpace (Computer,Drive,DiskSize,FreeSpace,Percentage,date) values('"_
&Computer&"','" & objLogicalDisk.DeviceID &"',"& objLogicalDisk.size/MBCONVERSION &_
"," & objLogicalDisk.freespace/MBCONVERSION & "," &_
((objLogicalDisk.freespace/MBCONVERSION)/(objLogicalDisk.size/MBCONVERSION))*100_
&", GETDATE() )"
AdRec.Open SQL, AdCn,1,1
end if
Next
Loop
September 11, 2008 at 3:42 pm
Grant Hoerz (9/11/2008)
This is a VBScript I've been running for a while. It takes each server name listed in a txt file, connects, dynamically grabs the drive info, and then puts a record for each drive into a table. I run it hourly to check on space. At the end of the month I trim the results down to 1 a day for an archive.
'Objective: Find Disk Free Space in all the listed servers and write to a databaseDim AdCn
Dim AdRec
Dim i, SQL
Set AdCn = CreateObject("ADODB.Connection")
Set AdRec = CreateObject("ADODB.Recordset")
' NOTE: Change the connection string according to your environment.
AdCn.Open = "Provider=SQLOLEDB.1;Data Source=ServerName;Initial Catalog=DiskMonitor;user id = 'username';password='Password' "
Set iFSO = CreateObject("Scripting.FilesyStemObject")
'Input file for server names
InputFile="c:\computerlist.txt"
Set ifile = iFSO.OpenTextFile(inputfile)
Const MBCONVERSION= 1048576
Do until ifile.AtEndOfLine
Computer= ifile.ReadLine
Set objWMIService = GetObject("winmgmts://" & Computer)
Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk")
For Each objLogicalDisk In colLogicalDisk
if objLogicalDisk.drivetype=3 then
SQL = "Insert into FreeSpace (Computer,Drive,DiskSize,FreeSpace,Percentage,date) values('"_
&Computer&"','" & objLogicalDisk.DeviceID &"',"& objLogicalDisk.size/MBCONVERSION &_
"," & objLogicalDisk.freespace/MBCONVERSION & "," &_
((objLogicalDisk.freespace/MBCONVERSION)/(objLogicalDisk.size/MBCONVERSION))*100_
&", GETDATE() )"
AdRec.Open SQL, AdCn,1,1
end if
Next
Loop
using the WMI class WIN32_Logicaldisk i see 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 11, 2008 at 6:34 pm
Those are all good, but let's revisit the OP's restrictions...
xp_fixeddrives returns FREE disk space, but not TOTAL disk space.
xp_cmdshell will not work because this is a high security place.
sp_OACreate, sp_OAMethod, etc. will not work for the same security reasons.
Line 2 and 3 imply that it has to be done within T-SQL and without using the items listed in 2 and 3.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2008 at 9:37 am
rbarryyoung (8/26/2008)
Okay:do the following:
1. Start Performance Monitor.
2. Select "Counter Logs" under "Performance Logs and Alerts"
3. From the Action Menu, click "New Log Settings..."
4. Enter a name like "Disk Stats"
5. Click "Add Objects..." on the Dialog that pops-up
6. On the Add Objects dialog, Select the Server Name (use local if you are actually on the server, which is better), then select the "Logical Disk" performance object and click Add, then Close..
7. Change the interval to 3600 seconds (once an hour).
8. Change the Run As to either the local Admin or your username and set the password.
9. Go to the Log Files tab, change the Log File Type to SQL Database
10. Click the Configure... button and select the System DSN that points to your Server & Database.
11. Go to the Schedule tab and set it up to run immediately and to stop Manually.
12. click OK.
This worked!!
Although I tweaked it slightly.
- I add Counters instead of Objects - I don't need every item under the sun for Logical Disk, just the % Free Space and Free Megabytes.
- I added for each individual disk, except for _Total, for each of the 25 servers.
- I scheduled it for once per day, starting at 1:30 AM.
It created two tables - CounterData and CounterDetails.
I'm keeping historical records so I can do trend analysis (for example, based on the past 4 months, how long before this disk fills?).
I think that's it....THANK YOU, Darth Barry!!!
:hehe:
September 18, 2008 at 9:40 am
Also, I can run Reporting Services reports on this, set up a subscription to email them out, etc.
Very useful stuff.
September 18, 2008 at 9:42 am
This is the stored proc I wrote to analyze the PerfMon counter stats:
/****** Object: StoredProcedure [dbo].[spSelectFreeDiskSpace] Script Date: 09/18/2008 11:41:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Jason Wisdom
-- Create date: 2008-09-17
-- Description:Generates the Free Disk Space report.
-- =============================================
ALTER PROCEDURE [dbo].[spSelectFreeDiskSpace]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Counter TABLE
(
CounterDateTimeCHAR(24),
ServerIDINT,
IsProductionTINYINT,
EnvironmentVARCHAR(100),
ServerNameVARCHAR(100),
InstanceNameVARCHAR(100),
CounterNameVARCHAR(100),
CounterValueDECIMAL(28,10)
)
DECLARE @MostRecentYearINT
DECLARE @MostRecentMonthINT
DECLARE @MostRecentDayINT
SELECT @MostRecentYear=SUBSTRING(MAX(CounterDateTime), 1, 4) FROM counterdata
SELECT @MostRecentMonth=SUBSTRING(MAX(CounterDateTime), 6, 2) FROM counterdata
SELECT @MostRecentDay=SUBSTRING(MAX(CounterDateTime), 9, 2) FROM counterdata
INSERT INTO @Counter
SELECT da.CounterDateTime, NULL, NULL, NULL, REPLACE(de.MachineName, '\', ''), de.InstanceName, CASE WHEN CounterName='% Free Space' THEN 'FreePct' ELSE 'FreeMegabytes' END AS Counter, da.CounterValue --, '!!!!!', *
FROM counterdata da
JOIN counterdetails de ON da.CounterID=de.CounterID
WHERE SUBSTRING(da.CounterDateTime, 1, 4)=@MostRecentYear
AND SUBSTRING(da.CounterDateTime, 6, 2)=@MostRecentMonth
AND SUBSTRING(da.CounterDateTime, 9, 2)=@MostRecentDay
UPDATE @Counter
SET ServerID=s.ServerID,
IsProduction=s.IsProduction,
Environment=CASE WHEN s.IsProduction=3 THEN 'Production' WHEN s.IsProduction=1 THEN 'Staging' ELSE 'Development' END
FROM dbo.[Server] s JOIN @Counter c ON s.ServerName=c.ServerName
SELECT * FROM @Counter
pivot
(
sum(CounterValue)
for CounterName
in (FreePct, FreeMegabytes)
) as p
END
(the tabbing does look better in SQL Query window...)
(were I to tweak this further, I would probably create a UDF to convert the inflexible char(24) to a valid datetime. CAST and CONVERT don't work on char-formatted dates unless they are in MSFT's very specific very anal datetime format, which this CHAR(24) is not)
September 18, 2008 at 10:01 am
Thank you others, as well.
I've never used the WMI before.....so many technologies......
thanks again.
September 18, 2008 at 10:03 am
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 17, 2011 at 3:39 am
I'm a bit late at this party, I know, but still:
There is NO way then to get total drive space from tsql alone? We need SOME info from outside SQL Server?
Greetz,
Hans Brouwer
November 10, 2011 at 12:21 am
This calculation is not accurate regarding Total size.
But it is close, so it depends on how accurate you want to be
November 10, 2011 at 5:55 am
FreeHansje (10/17/2011)
I'm a bit late at this party, I know, but still:There is NO way then to get total drive space from tsql alone? We need SOME info from outside SQL Server?
Unfortunately, I believe that's still the case.
One of the things you could try is a scheduled job with a script task to populate a table on a regular basis if you didn't want to go through the rigors of properly locking down a system to use xp_CmdShell. You could even call the job and do a "WAITFOR DELAY" of a couple of seconds to let it finish and then read from the table (or file using BULK INSERT) in T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply