Reporting Services Makes Server Support Easier
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/crichardson/2898.asp
-
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.
-
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.
-
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
-
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!
-
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
Consultant - Straight Path Solutions
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 -
Great article, very impressed!
-
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!
-
Yes I thought so too.
However:
select user_name(grantee_principal_id), permission_name, state_desc from sys.database_permissions where major_id = object_id('xp_fixeddrives')
tells me that public has exec granted.
The following:
create login bobWITH PASSWORD = 'b0b',
CHECK_POLICY = OFF
master..sp_adduser bob
grant exec on xp_fixeddrives to bob
Then tells me bob has the rights on it.But if you log in as bob, and run xp_fixeddrives it runs fine, but doesn't give you any (ok, me and I think you) results. Which is odd quite frankly.I haven't managed to figure out yet exactly what it does need, but I don't want to be connecting reporting services as sa...
I am hoping someone may be able to point at a setting/permission perhaps that handles this.Cheers.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply