Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Reporting Services Makes Server Support Easier Expand / Collapse
Author
Message
Posted Friday, March 02, 2007 3:42 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:05 AM
Points: 1,451, Visits: 2,911
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
Post #348925
Posted Monday, March 12, 2007 6:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 1:24 PM
Points: 5, Visits: 31
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.


Post #350664
Posted Monday, March 12, 2007 8:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2008 7:32 AM
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.

Post #350743
Posted Monday, March 12, 2007 9:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:16 AM
Points: 130, Visits: 530

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




Post #350755
Posted Monday, March 12, 2007 9:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, March 22, 2014 12:31 PM
Points: 236, Visits: 405

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!

Post #350757
Posted Monday, March 12, 2007 9:54 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:05 AM
Points: 1,451, Visits: 2,911
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
Post #350776
Posted Monday, March 12, 2007 10:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 10,910, Visits: 12,548
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
)
*/
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)
)

Create Table #sql_version
(
[index] int,
[name] varchar(100),
internal_value Int,
character_value varchar(200)
)

Create Table #stats
(
stat_skey Int Identity (1,1),
variable varchar(25),
value varchar(50)
)

Insert Into #drive_stats
Exec master..xp_fixeddrives

Insert Into #sql_version
Exec master..xp_msver


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

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

Insert Into #stats
(
variable,
value
)
Select
'MB Free Space on ' + drive_letter,
free_space
From
#drive_stats

Insert Into #stats
(
variable,
value
)
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

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
Post #350793
Posted Wednesday, March 28, 2007 3:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:19 AM
Points: 52, Visits: 643
Great article, very impressed!
Post #354411
Posted Tuesday, April 17, 2007 4:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:22 AM
Points: 1,052, Visits: 862

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!

 

 




Post #358892
Posted Tuesday, April 17, 2007 1:30 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:05 AM
Points: 1,451, Visits: 2,911

The user would need probably need execute permissions on xp_fixeddrives.

 

 



Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Post #359071
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse