Low disk space alert

Question

Low disk space alert

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Jeff Moden (6/6/2012)
As always, you're a gentleman and a scholar, Orlando. Thanks for talking about this. There will always be some disagreement between thee and me on these types of things and, yes, I have to agree to disagree about some of it. I do absolutely agree that people should be warned of what could happen if they have users/apps that have "SA" privs when some of this "SA" functionality is turned on. I have to say it again that having those things turned off will do no good if an attacker get's in as "SA" because some folks just don't understand that particular problem.

It's my pleasure. I am excited to have these types of exchanges with you. Agreed on your last point. If someone knows what they're doing, wants to hurt a system and can get in as SA then all security measures taken to that point become moot.

Just so you know, my stance on the OA procs is not unfounded. My exploration of the sp_OA procs dates to the early 2000's when working with a homegrown column-level encryption solution that leveraged capicom.dll. It was a hairpulling affair to be sure. I am pretty sure it is what has scarred me into swearing off sp_OA forever, and motivates me to save others the headaches and eminent dangers of a mis-implemented object being used via the OA procs.

Shifting gears and having a great amount of fun, you'll be very pleased that I've at least met you half-way by discovering some of the wonders of PowerShell. I say "half way" because... well... you'll figure that out when you see the following code. 😀
IF OBJECT_ID('tempdb..#CmdOutput','U') IS NOT NULL
DROP TABLE #CmdOutput
;
CREATE TABLE #CmdOutput (RowNum INT IDENTITY(1,1), OutputText VARCHAR(512))
;
INSERT INTO #CmdOutput
(OutputText)
EXEC xp_CmdShell 'MODE CON: COLS=200 && PowerShell "$Item = @(''SystemName'',''VolumeName'',''DeviceID'',''Description'',''Compressed'',''DriveType'',''FileSystem'',''FreeSpace'',''MediaType'',''Size'',''VolumeDirty''); Get-WMIObject -query ''Select * From Win32_LogicalDisk'' | Format-Table $item -auto; exit;"'
;
SELECT *
FROM #CmdOutput
;

I wish I could test drive your code but I get this weird error when I run it:

[font="Courier New"]Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.[/font]

Sorry, I couldn't resist 😀

Seriously, I enabled xp_cmdshell for a moment and liked what I saw. A splitter to make your result into a tabular resultset is only a short bit of code away.

I could see a little PowerShell (via xp_cmdshell of course) creeping into your toolkit to replace things that are not easy to do with Windows Batch, or available command line tools. PowerShell via xp_cmdshell could easily serve as a full, safer replacement for most of the things I see people trying to do with the sp_OA procs. Examples that come to mind:

- the task at hand in this post to test for free disk space %

- emailing using the built-in .net email classes instead of CDOSYS (should Database Mail not be an option)

- scripting out database objects using PowerShell+SMO from within T-SQL

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Now THAT was funny! I laughed out loud when I saw the error you got. Great way to start the morning. 🙂

I'd likely not use the "horizontal" format as a return. That was just me experimenting with some of the different techniques. Sure, I could build an intelligent splitter to automatically figure out where the columns are (and they can change width according to content), but it would seem that using the "vertical format" would be a lot easier to "parse" and then pivot back into a table.

I will admit that writting sp_OA* to do the same thing is a bit of a pain but, once done, it's done. By the same token, Powershell does make it awefully easy to use WMI. With such easy power also comes great responsibility. Once again, we are talking about SQL Server doing something that most folks wouldn't have it do at a Windows Server/System level, especially with having xp_CmdShell enabled. The key is having a good lockdown and code reviews. And, yeah... even though I'm the Sr. DBA in most places I work, I do have people review my code before I put it into production. It works two ways... I have another set of eyes checking for mistakes and we both (me and whoever is doing the code review) might learn something during the review.

Sorry about your previous experience with sp_OA*. I can definitely see how that might turn someone off to it. I had a similar experience where someone wrote such code that would always create a new connection and then leave it open. Think of it as a "connection leak". It didn't take long to render the server pretty useless and to be in need of a reboot. That particular code had gone in just before I arrived at that particular company. It took a fair bit to find the problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

opc.three (6/6/2012)
Win32_Volume is more appropriate in some server environments. I use Win32_LogicalDisk in this environment as Win32_Volume is not a class on all the servers that I support.
Here is how you would do the same in PowerShell using the Win32_Volume class. It only took a couple minor tweaks.
Get-WMIObject Win32_Volume -Computer COMPUTERNAME | Select-Object -Property Volume,Capacity,FreeSpace,Name,@{Name="FreeSpace(GB)";Expression={"{0:N1}" -f($_.FreeSpace/1gb)}},@{Name="Capacity(GB)";Expression={"{0:N1}" -f($_.Capacity/1gb)}},@{Name="freespace(%)";Expression={"{0:N1}" -f(($_.FreeSpace/$_.Capacity)*100)}}
Edit: use quote instead of code window

What Namespace do you use? I'm getting an error that the query could not be executed within the namespace provided.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Welsh Corgi (2/13/2013)
opc.three (6/6/2012)
Win32_Volume is more appropriate in some server environments. I use Win32_LogicalDisk in this environment as Win32_Volume is not a class on all the servers that I support.
Here is how you would do the same in PowerShell using the Win32_Volume class. It only took a couple minor tweaks.
Get-WMIObject Win32_Volume -Computer COMPUTERNAME | Select-Object -Property Volume,Capacity,FreeSpace,Name,@{Name="FreeSpace(GB)";Expression={"{0:N1}" -f($_.FreeSpace/1gb)}},@{Name="Capacity(GB)";Expression={"{0:N1}" -f($_.Capacity/1gb)}},@{Name="freespace(%)";Expression={"{0:N1}" -f(($_.FreeSpace/$_.Capacity)*100)}}
Edit: use quote instead of code window
What Namespace do you use? I'm getting an error that the query could not be executed within the namespace provided.

Which version of Windows? version of PowerShell?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I'm using Windows Server 2008 and I believe that it is Powershell 2.0.

I'm open to various options.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Get-WMIObject Win32_Volume -Computer COMPUTERNAME | Select-Object -Property Volume,Capacity,FreeSpace,Name,@{Name="FreeSpace(GB)";Expression={"{0:N1}" -f($_.FreeSpace/1gb)}},@{Name="Capacity(GB)";Expression={"{0:N1}" -f($_.Capacity/1gb)}},@{Name="freespace(%)";Expression={"{0:N1}" -f(($_.FreeSpace/$_.Capacity)*100)}}

You changed COMPUTERNAME in the command line, right? Namespace is not explicitly set (should be using root\cimv2 under the covers iirc) as you can see it is not provided anywhere on the command line. I just tested the command manually on 2008 R2 PowerShell 2.0 to make sure and it works as expected...not sure what the issue is on your end.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

amorgan2099 SSC Rookie Points: 37 More actions · Answer 7

I have been getting email alerts from sql server that will not stop. I have disabled the sql mail in sql server agent, operator, notification job itself in sql server agent. I cannot get the email alerts to stop sending from the server. How do I get the low disk space alerts to stop sending?