Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting Services Makes Server Support Easier


Reporting Services Makes Server Support Easier

Author
Message
Carolyn Richardson
Carolyn Richardson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1553 Visits: 3443
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/crichardson/2898.asp

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Rodan
Rodan
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 44
love this for our sql machines - it would be nice to be able to figure out how to incorporate this type of solution into all of our other servers... One place for us to review space would be very nice... just a thought.



Jeff Steinweber
Jeff Steinweber
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 29

I have recently created a solution for this for all servers (not just SQL).  All that it requires is that you have WMI running on the servers that you wish to monitor.  It makes use of the winmgmts object to gather the disk size and free space for all available logical disks.

STEP 1.

Pick a server where you want to record the monitoring and create a database called Monitor with tables and user name as follows:

Use Monitor
Go
Create table logtable (id int identity(1,1), notes varchar(1000), date datetime default getdate())
go

Create Table Servers(ServerName varchar(128))
go

Create Table FreeSpace(Computer varchar(128),
Drive varchar(2),DiskSize decimal(28,5)
,FreeSpace decimal(28,5),Percentage decimal (10,5), Date datetime)
go

use master
go
sp_addlogin 'diskuser','disk','Monitor'
go
use Monitor
go
sp_adduser 'diskuser'
go
sp_addrolemember 'db_datawriter','Diskuser'
go
sp_addrolemember 'db_datareader','Diskuser'
go

STEP 2 - insert the names of the servers you would like to monitor.  For example:

Insert into Servers select 'SERVER1'

Insert into Servers select 'SERVER2'  etc, etc....

Note:  I have also added some columns to the server table for things like a server category and a primary and secondary support person.

STEP 3
Create a VBS script to get the data and insert into the tables.  Here is a copy of my script, this will do some word wrapping so you will have to fix it in your script.

'Objective: Find Disk Free Space in all the listed servers in a table and write to a database table
on error resume next
Const MBCONVERSION= 1048576 
Dim AdCn
Dim ErrorSQL
Dim AdRec
Dim i, SQL
Set AdCn = CreateObject("ADODB.Connection")
Set AdRec = CreateObject("ADODB.Recordset")
Set AdRec1 = CreateObject("ADODB.Recordset")
 
AdCn.Open = "Provider=SQLOLEDB.1;Data Source=YOURSERVER;Initial Catalog=Monitor;user id = 'diskuser';password='disk' "
SQL1 = "Select ServerName from Servers"
 
AdRec1.Open SQL1, AdCn,1,1
 
ErrorSQL="insert into logtable(notes) values ('Disk Monitoring Started')"
AdRec.Open ErrorSQL, AdCn,1,1
 
while not Adrec1.EOF
Computer = Adrec1("ServerName")
 
Set objWMIService = GetObject("winmgmts://" & Computer)
'wscript.echo err.number
If err.number <> 0 then
 ErrorSQL="insert into logtable(notes) values ('" + Computer + ":  Error-- " + Err.description+ "')"
 AdRec.Open ErrorSQL, AdCn,1,1
else
 Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk") 
  If err.number <> 0 then
   ErrorSQL="insert into logtable(notes) values ('" + Computer + ":   Error-- " + Err.description+ "')"
  else
   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_
    &",'" &now() &"')"
    AdRec.Open SQL, AdCn,1,1
   end if
 
   Next 
  end if
end if
 
err.Clear
Adrec1.movenext
 
Wend
 
AdRec.Open "insert into logtable(notes) values ('Disk Monitoring - Completed')", AdCn,1,1

STEP 4

Save the above into a VBS file on your server where you created the database and schedule it to run using Windows Schedule.  Make sure that if you are crossing domains that you pick a "run as" login for the Schedule that would have Admin rights to all the servers in the list.

I schedule mine to run once per day and have written a few ReportingService reports out of it.  You could easily create a subscription to email a list of server results that drop below a certain percentage of free space.


DavidL
DavidL
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 756

Hello Carolyn:

I had a question about one item in the article.  In a textbox on the report there is the following snippet of 'code' visible:

=First(Fields!......

What is that "First" all about?  Thx.  D Lewis





F. Dwarf
F. Dwarf
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 559

DavidL:

The First() function returns the first value found in a field of the dataset. Refer to the Books Online for more information.

I hope this helps.

Regards!


Carolyn Richardson
Carolyn Richardson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1553 Visits: 3443
If you just drag the field over from the dataset window it will poplulate with the First syntax ie:

=First(Fields!Started.Value, "ServerNameUptime")



Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
Good Article that gave me some ideas. Here is an SP I created after reading the article that combines it all in 1 place, plus additions that you can put in the master DB on all SQL Servers (7-2005).

ALTER PROCEDURE dbo.sp_server_statistics
/*
   (
      @parameter1 datatype = default value,
      @parameter2 datatype OUTPUT
   Wink
*/
AS
   
/* Jack D. Corbett
**
** Returns basic server statistics
**
** Example
** ----------
** Exec master.dbo.sp_server_statistics
**
** History
** ----------
** 03-12-2007 Created
**
*/

SET NOCOUNT ON

Create Table #drive_stats
   (
   drive_letter varchar(10),
   free_space Decimal(20, 2)
   Wink
   
Create Table #sql_version
   (
   [index] int,
   [name] varchar(100),
   internal_value Int,
   character_value varchar(200)
   Wink
   
Create Table #stats
   (
   stat_skey Int Identity (1,1),
   variable varchar(25),
   value varchar(50)
   Wink
   
Insert Into #drive_stats
   Exec master..xp_fixeddrives

Insert Into #sql_version
   Exec master..xp_msver

   
Insert Into #stats
   (
   variable,
   value
   Wink
   SELECT
      'Last Start Time',
      login_time
   FROM
      master..sysprocesses
   WHERE
      spid = 1

Insert Into #stats
   (
   variable,
   value
   Wink   
   SELECT
      'Uptime',
      CONVERT(CHAR(25), DATEDIFF(DAY, login_time, GETDATE())) as 'Uptime'
   FROM
      master..sysprocesses
   WHERE
      spid = 1

Insert Into #stats
   (
   variable,
   value
   Wink
   Select
      'MB Free Space on ' + drive_letter,
      free_space
   From
      #drive_stats
      
Insert Into #stats
   (
   variable,
   value
   Wink
   Select
      Case
         When [name] = 'ProductVersion' then 'SQL Server Version'
         When [name] = 'WindowsVersion' then 'Windows Version'
         When [name] = 'ProcessorCount' then 'Processors'
         When [name] = 'PhysicalMemory' then 'MB RAM'
         Else [name]
      End,
      character_value
   From
      #sql_version
   Where
      [name] in ('ProductVersion', 'WindowsVersion', 'ProcessorCount', 'PhysicalMemory')
   Order By
      [index]   
      
Select
   *
From
   #stats
ORder By
   stat_skey
   
RETURN

Also, the SQLH2 tool that microsoft provided awhile ago provides some of this functionality and there are some RS reports you can download that report on the data collected by SQLH2.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
kate-395233
kate-395233
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 762
Great article, very impressed!
RichB
RichB
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1102 Visits: 1015

what permissions do you need to successfully run xp_fixeddrives on 2k5?  I find that a basic user ends up with an empty result set but no error messages!

 

 





Carolyn Richardson
Carolyn Richardson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1553 Visits: 3443

The user would need probably need execute permissions on xp_fixeddrives.

 

 



Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search