Reporting Services Makes Server Support Easier

  • 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[/url]

  • 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!

  • 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[/url]

  • 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.

  • 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!

     

     

  • The user would need probably need execute permissions on xp_fixeddrives.

     

     

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • 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 bob

    WITH 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