August 5, 2010 at 9:03 am
I am posting this on the 2008 forum, but actually would love to have something for at least 2005 if not also 2000 as we have all three environments where I work...
I work in an environment where DBAs are locked out of servers at an RDP/Admin level, even on most of the database servers we administer. We are sysadmin within SQL but we can't log on to the boxes and we don't have any local access outside of SQL (the SQL Service account is local admin, but we aren't given any account passwords in the DBA group). We have a very rigorous definition of who does what.
I have to document servers we just set up and so I am taking it as a challenge to use only T-SQL solutions using best practices to get the information I need. Memory and CPU detail is a snap. Where I am hitting a brick wall is disk. I can get file size of the SQL files, I can use xp_fixeddrives to get the drive letters and their free spaces, but what I am not getting is total space. I don't want to assume that total space is simply all files in SQL + available space, since other things may be out there for who knows what reason. Also, none of these solutions deal with mount points, which we use frequently.
So here's my question: using only T-SQL, without xp_cmdshell, is there any way to get total disk size of all volumes on a SQL Server, including mount points?
August 5, 2010 at 9:16 am
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[sp_diskspace]')
AND TYPE IN ( N'P', N'PC' ))
DROP PROCEDURE [dbo].[sp_diskspace]
GO
CREATE PROCEDURE Sp_diskspace
AS
SET nocount ON
DECLARE @hr INT
DECLARE @fso INT
DECLARE @drive CHAR(1)
DECLARE @odrive INT
DECLARE @TotalSize VARCHAR(20)
DECLARE @MB BIGINT;
SET @MB = 1048576
CREATE TABLE #drives
(
servername VARCHAR(15),
drive CHAR(1) PRIMARY KEY,
freespace INT NULL,
totalsize INT NULL,
freespacetimestamp DATETIME NULL
)
INSERT #drives
(drive,
freespace)
EXEC MASTER.dbo.Xp_fixeddrives
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 #drives
ORDER BY drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @hr = Sp_oamethod
@fso,
'GetDrive',
@odrive OUT,
IF @hr <> 0
EXEC Sp_oageterrorinfo @fso
EXEC @hr = Sp_oagetproperty
@odrive,
'TotalSize',
@TotalSize OUT
IF @hr <> 0
EXEC Sp_oageterrorinfo @odrive
UPDATE #drives
SET totalsize = @TotalSize / @MB,
servername = Host_name(),
freespacetimestamp = ( Getdate() )
WHERE drive = @drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=Sp_oadestroy @fso
IF @hr <> 0
EXEC Sp_oageterrorinfo @fso
SELECT servername,
drive,
totalsize AS 'Total(MB)',
freespace AS 'Free(MB)',
CAST(( freespace / ( totalsize * 1.0 ) ) * 100.0 AS INT) AS 'Free(%)',
freespacetimestamp
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
GO
DECLARE @rc INT
EXECUTE @rc = [master].[dbo].[Sp_diskspace]
August 5, 2010 at 9:19 am
We did look at that. But there are two problems with it -- 1) we'd like to not enable OLE access if we could avoid it (we see it as risky as xp_cmdshell) and 2) mount points didn't seem to be available to the FileSystemObject, at least where we were trying it. Thanks for the input, though.
August 5, 2010 at 9:21 am
Not sure if this will get the mount points. This method requires ole automation be on. It turns it on, and then returns it to it's original setting when finished.
IF EXISTS
(SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[HoldDiskSpace]')
)
DROP TABLE [tempdb].[dbo].[HoldDiskSpace]
GO
CREATE TABLE [tempdb].[dbo].[HoldDiskSpace] (
[Server] nvarchar(128),
Drive char(1) PRIMARY KEY,
[Free(MB)] int NULL,
[Total(MB)] int NULL,
[Free(%)] tinyint NULL,
[DateChecked] [datetime] NOT NULL CONSTRAINT [DF_HoldDiskSpace_Date Checked] DEFAULT (getdate()),
)
SET NOCOUNT ON
if object_id('tempdb..#config') IS NOT NULL DROP TABLE #config
CREATE TABLE #config(name nvarchar(35), minimum int, maximum int, config int, run int)
insert into #config exec sp_configure
declare @show int, @ole int
select @show = run from #config where name like 'show%'
if @show = 0 begin
exec sp_configure 'show',1
reconfigure with override
delete from #config
insert into #config exec sp_configure
end
select @ole = run from #config where name like 'ole%'
if @ole = 0 begin
exec sp_configure 'ole',1
reconfigure with override
end
DECLARE @hr INT
DECLARE @fso INT
DECLARE @drive VARCHAR(3)
DECLARE @odrive INT
DECLARE @TotalSize VARCHAR(20)
DECLARE @MB BIGINT ; SET @MB = 1048576
INSERT [tempdb].[dbo].[HoldDiskSpace](Drive,[Free(MB)])
EXEC master.dbo.xp_fixeddrives
-- This is a VB method
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 [tempdb].[dbo].[HoldDiskSpace]
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 [tempdb].[dbo].[HoldDiskSpace]
SET [Total(MB)]=@TotalSize/@MB,
[Server] = CONVERT(nvarchar(128),Serverproperty('Servername')) ,
[Free(%)] = CAST(([Free(MB)]/(@TotalSize/@MB*1.0))*100.0 as int)
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
if @ole = 0 begin
exec sp_configure 'ole',0
reconfigure with override
end
if @show = 0 begin
exec sp_configure 'show',0
reconfigure with override
end
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 5, 2010 at 9:23 am
jeff.mason (8/5/2010)
We did look at that. But there are two problems with it -- 1) we'd like to not enable OLE access if we could avoid it (we see it as risky as xp_cmdshell) and 2) mount points didn't seem to be available to the FileSystemObject, at least where we were trying it. Thanks for the input, though.
OK. How about a CLR?
August 5, 2010 at 10:43 am
skcadavre (8/5/2010)
jeff.mason (8/5/2010)
We did look at that. But there are two problems with it -- 1) we'd like to not enable OLE access if we could avoid it (we see it as risky as xp_cmdshell) and 2) mount points didn't seem to be available to the FileSystemObject, at least where we were trying it. Thanks for the input, though.OK. How about a CLR?
No, that's definitely out as well.
So I guess our gut instinct was right, then -- as of now, SQL Server doesn't expose the disk details to any of the views or XPs. I can probably modify the OLE code so that it meets the immediate need (since the particular box that I am auditing doesn't have mount points) and turn off the OLE option immediately, but long term we have to find a solution.
I assume that this has been suggested to Microsoft for future features...?
August 5, 2010 at 10:57 am
Looks like I can use Wayne's script as is with just a SELECT * and a DROP TABLE added and no impact on the server, so that's what I will use short term. I like the way that the options are turned off and on within the script. Works great. However, still no solution for mount points....
August 5, 2010 at 11:30 am
Can you get the mount point information from VBSCRIPT? If so, then you should be able to just expand upon what is already there with the sp_OA calls. I've never worked with mount points, so I have no idea of how to proceed with it. Since you have it, you're in the best position to play around with it to get the information that you need.
If you can remote in to the server, I'd do that and experiment with the VBSCRIPT code directly. Once you get that working, you can easily adapt it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 5, 2010 at 11:34 am
WayneS (8/5/2010)
If you can remote in to the server,
Ah, if only I could, then I wouldn't need to do what I am doing.....
Actually, I don't know if mount points are exposed to VBScript. That seems to be a recent update to infrastructure options. It would be helpful, and it probably is in more recent things like .Net scripting, but not sure on that.
You guys have been helpful, though, and I already have my immediate need filled. Thanks!
August 5, 2010 at 11:46 am
Don't have a ton of experience mapping COM object to VBScripts, so at first I am not sure how to do this with the OA procs, but you don't use the usual COM generation to get at this. In VBScript, this is how you initialize what you need to get out mount points:
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Is there a way of writing that line with the OA procedures? I have enough to get it from there I think.
August 5, 2010 at 12:27 pm
jeff.mason (8/5/2010)
Don't have a ton of experience mapping COM object to VBScripts, so at first I am not sure how to do this with the OA procs, but you don't use the usual COM generation to get at this. In VBScript, this is how you initialize what you need to get out mount points:Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Is there a way of writing that line with the OA procedures? I have enough to get it from there I think.
Did you try:
declare @VBstr varchar(1000), @strComputer varchar(100);
set @strComputer = '.';
set @VBstr = winmgmts:{impersonationLevel=impersonate}!\\' + @strComputer + '\root\cimv2';
EXEC @hr=sp_OACreate @VBstr, @OutputObjectVariableThatYouWillReference OUT
No idea if this will work, but I think you can get the general idea of how to convert one to the other from it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 5, 2010 at 12:30 pm
No, I did not, but that was because some long unused COM knowledge came back to me, about the difference between CreateObject versus GetObject and how they are not equivalent. I think, if I am remembering this right, that VBScript depends upon CreateObject, at least the way that the OA procs are calling it, and that GetObject may not work there. Maybe someone who remembers this better can sort those two out for me.
August 5, 2010 at 12:56 pm
Been doing some research and found that using OA_Create and WMI together doesn't work. At some point you have to resort to OS-level activity that is blocked to me, even if you use WMI through SSIS (so it would seem). But WMI is the way to get at mount points, and you can't use the OLE procs to query it. Drat.
August 5, 2010 at 5:07 pm
Thanks for the update Jeff.
In 2008, you should be able to use PowerShell to get the data you need.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 5, 2010 at 5:31 pm
WayneS (8/5/2010)
Thanks for the update Jeff.In 2008, you should be able to use PowerShell to get the data you need.
Only if you have access to the OS to run it - which it does not sound like he has. And, you can't run it remotely because they are not granted access to WMI remotely either.
I guess the server team is going to have to provide you with the data. Tell them you need it daily in a delimited file so you can import it into SQL Server. About the only thing you can do at this point.
If they don't want to do that, ask them if they are willing to setup a proxy account for powershell. Then, you could create a powershell script and run that to get the information.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply