dbWarden - A Free SQL Server Monitoring Package

  • Blitz index report will be a great addition to this report.

    http://www.brentozar.com/blitzindex/

  • I was just curious to know how it sends text alerts. Can you please explain? Thanks in advance.

  • It sends text alerts by setting adding your phones "email address" to the text alert. For example, if you are on Sprint, it would be <phonenumber>@messaging.sprintpcs.com. ATT's format is <phonenumber>@txt.att.net. I don't recall the others, but you can search for them.

  • We have xp_cmdshell enabled on our servers (yeah, I know, that's not best practice). To support this you need to have gen_GetHealthReportToFile check for xp_cmdshell already being enabled. Here's some code to use:

    -- Determine if xp_cmdshell is already enabled

    DECLARE @xpCmdShellEnabledAtStart int = 0

    SELECT @xpCmdShellEnabledAtStart = CONVERT(INT, ISNULL(value_in_use, value))

    FROM sys.configurations

    WHERE name = 'xp_cmdshell' ;

    If @xpCmdShellEnabledAtStart = 0

    Begin

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE

    EXEC sp_configure 'xp_cmdshell', 1

    RECONFIGURE

    EXEC master..xp_cmdshell @SQL

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE

    EXEC sp_configure 'xp_cmdshell', 0

    RECONFIGURE

    End

    Else

    EXEC master..xp_cmdshell @SQL

  • HI,

    Thank you for the tool, you guys are awesome :-). I need some assistance though, when executing the jobs dba_checkfiles and dba_healthreport the job is failing with error Error converting data type int to nvarchar. [SQLSTATE 42000] (Error 8114). The step failed. The other jobs all work well.

    I managed to dbo.usp_FileStats, when i try to execute the procedure manually it gives me the error. Could you please assist me. I am using SQL 2008 R2

    Any help would be appreciated 🙂

  • Jevond (4/15/2013)


    HI,

    Thank you for the tool, you guys are awesome :-). I need some assistance though, when executing the jobs dba_checkfiles and dba_healthreport the job is failing with error Error converting data type int to nvarchar. [SQLSTATE 42000] (Error 8114). The step failed. The other jobs all work well.

    I managed to dbo.usp_FileStats, when i try to execute the procedure manually it gives me the error. Could you please assist me. I am using SQL 2008 R2

    Any help would be appreciated 🙂

    Hi Jevond,

    I'm afraid I'm not encountering that error on my 2k8 R2 box. I'll keep looking for any potential failures. If your database names aren't private information, I'd be interested in knowing the names and sizes of your databases so I can help troubleshoot. You can shoot me an email if you want.

    Regards,

    Michael

  • alchemistmatt (4/13/2013)


    We have xp_cmdshell enabled on our servers (yeah, I know, that's not best practice). To support this you need to have gen_GetHealthReportToFile check for xp_cmdshell already being enabled. Here's some code to use:

    -- Determine if xp_cmdshell is already enabled

    DECLARE @xpCmdShellEnabledAtStart int = 0

    SELECT @xpCmdShellEnabledAtStart = CONVERT(INT, ISNULL(value_in_use, value))

    FROM sys.configurations

    WHERE name = 'xp_cmdshell' ;

    If @xpCmdShellEnabledAtStart = 0

    Begin

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE

    EXEC sp_configure 'xp_cmdshell', 1

    RECONFIGURE

    EXEC master..xp_cmdshell @SQL

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE

    EXEC sp_configure 'xp_cmdshell', 0

    RECONFIGURE

    End

    Else

    EXEC master..xp_cmdshell @SQL

    Thanks, we added logic to check the current setting. I am going to update the script sometime today and the change will be in that release.

  • On the subject of the database name: rather than have to individually edit about 140 occurrences of DBA to <db-name-of-our-choice>, would you consider using the SSMS Template-style <Database_Name, sysname, Database_Name> substitution mechanism (Ctrl+M), please?

    Each time you release an updated file, the same edit-db-to-local-name process must be performed and, as noted in a prior post, because the operator definitions have DBA in them, a global edit is impossible.

    Please consider for increased usability, especially as it is very common for sites to already have a DBA database for specific functions and tables (numbers, dates, et al), thus warranting a DBWarden database name instead.

    Thanks.

  • You said:

    > The output HTML report looks so professional

    I'd love to see it but so far I have not. I have got as far as enabling dba_HealthReport and I see it run every day but the HTML is not generated.

    HealthReportIDDateStampGeneratedHTML

    142013-04-15 06:05:05.170NULL

    Do you have any suggestion how to get past this? Once it is generated, how do I view it? Or is it automatically dropped into a file in htdocs?

    Sorry if these are trivial questions....

    Gabor

  • gkiss 73362 (4/15/2013)


    You said:

    > The output HTML report looks so professional

    I'd love to see it but so far I have not. I have got as far as enabling dba_HealthReport and I see it run every day but the HTML is not generated.

    HealthReportIDDateStampGeneratedHTML

    142013-04-15 06:05:05.170NULL

    Do you have any suggestion how to get past this? Once it is generated, how do I view it? Or is it automatically dropped into a file in htdocs?

    Sorry if these are trivial questions....

    Gabor

    Hello Gabor,

    Could you tell me which version of SQL Server you are running?

    When the HealthReport table shows GeneratedHTML as NULL, that usually means there was an issue generating the HTML caused by a NULL value (missing a COALESCE somewhere).

    You will also want to make sure you have the latest file from Sourceforge. We released an update last week to fix problems generating HTML in SQL Server 2012.

  • select @@version

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

  • This is awesome.

    Thank you.

    Alex S
  • Small enhancement requests re Jobs:

    1. If a Job is disabled, don't color the success/failed Last Outcome cell.

    2. If a job hasn't been executed in the last MONTH, ignore the status, too. Case in point, having inherited three production servers where a number, on one of them more than 20 one-timers, haven't been run since June 2008, the big red FAILED is a huge distraction. We don't have time or anyone with that length of tenure, to fill in the blanks as to what they are, etc.

    Thanks, again, for what is proving to be a very useful tool.

  • SAinCA (4/15/2013)


    On the subject of the database name: rather than have to individually edit about 140 occurrences of DBA to <db-name-of-our-choice>, would you consider using the SSMS Template-style <Database_Name, sysname, Database_Name> substitution mechanism (Ctrl+M), please?

    Each time you release an updated file, the same edit-db-to-local-name process must be performed and, as noted in a prior post, because the operator definitions have DBA in them, a global edit is impossible.

    Please consider for increased usability, especially as it is very common for sites to already have a DBA database for specific functions and tables (numbers, dates, et al), thus warranting a DBWarden database name instead.

    Thanks.

    SAinCA (4/15/2013)


    Small enhancement requests re Jobs:

    1. If a Job is disabled, don't color the success/failed Last Outcome cell.

    2. If a job hasn't been executed in the last MONTH, ignore the status, too. Case in point, having inherited three production servers where a number, on one of them more than 20 one-timers, haven't been run since June 2008, the big red FAILED is a huge distraction. We don't have time or anyone with that length of tenure, to fill in the blanks as to what they are, etc.

    Thanks, again, for what is proving to be a very useful tool.

    Thank you very much for the suggestions! I'm keeping track of what everyone has suggested and we're going through them as we get time.

  • Hi Michael

    Nice work. If you want a reliable method of getting Disk volume info here is a sproc I have written for SQL 2005+.

    USE [master]

    GO

    IF IS_SRVROLEMEMBER('sysadmin') = 0

    RAISERROR( 'This procedure must be deployed by a Database Administrator', 18, 1);

    go

    EXEC sp_configure 'show advanced options', 1

    RECONFIGURE WITH OVERRIDE

    GO

    EXEC sp_configure 'Ole Automation Procedures', 1

    RECONFIGURE WITH OVERRIDE

    GO

    USE msdb

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF OBJECT_ID('[dbo].[usp_GET_DISK_INFO]') IS NOT NULL

    DROP PROCEDURE [dbo].[usp_GET_DISK_INFO]

    go

    CREATE PROCEDURE [dbo].[usp_GET_DISK_INFO]

    WITH EXECUTE AS SELF

    AS

    BEGIN

    DECLARE @hr int

    DECLARE @fso int

    DECLARE @drive char(1)

    DECLARE @drive_obj int

    DECLARE @drive_type int

    DECLARE @total_size bigint

    DECLARE @bytes_per_MB bigint

    DECLARE @prop_tot_size varchar(150)

    DECLARE @prop_vol_name varchar(150)

    DECLARE @prop_drv_type varchar(150)

    DECLARE @drive_type_desc varchar(30)

    SET @bytes_per_MB = 1048576;

    DECLARE @drive_info TABLE

    (

    DRIVE_INFO_ID INT IDENTITY(1,1) NOT NULL,

    SERVER_NAME SYSNAME DEFAULT (@@SERVERNAME) NULL,

    DRIVE CHAR(1) NULL,

    FREE_SPACE_MB BIGINT NULL,

    USED_SPACE_MB BIGINT NULL,

    TOTAL_SPACE_MB BIGINT NULL,

    DRIVE_TYPE INT NULL,

    DRIVE_TYPE_DESC VARCHAR(30) NULL,

    VOLUME_NAME SYSNAME NULL

    );

    INSERT INTO @drive_info

    ( DRIVE, FREE_SPACE_MB )

    EXEC master..xp_fixeddrives;

    BEGIN TRY

    EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',@fso OUT

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    DECLARE drv_cursor CURSOR LOCAL FAST_FORWARD FOR

    SELECT DRIVE from @drive_info ORDER by DRIVE

    OPEN drv_cursor

    FETCH NEXT FROM drv_cursor INTO @drive

    WHILE @@FETCH_STATUS=0

    BEGIN

    EXEC @hr = sp_OAMethod @fso,'GetDrive', @drive_obj OUT, @drive

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    EXEC @hr = sp_OAGetProperty @drive_obj,'TotalSize', @prop_tot_size OUT

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @drive_obj

    EXEC @hr = sp_OAGetProperty @drive_obj,'DriveType', @prop_drv_type OUT

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @drive_obj

    EXEC @hr = sp_OAGetProperty @drive_obj,'VolumeName', @prop_vol_name OUT

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @drive_obj

    IF ISNUMERIC(@prop_tot_size) = 1

    SET @total_size = CONVERT(BIGINT, @prop_tot_size) / @bytes_per_MB;

    ELSE

    SET @total_size = NULL;

    IF ISNUMERIC(@prop_drv_type) = 1

    BEGIN

    SET @drive_type = CONVERT(INT, @prop_drv_type) ;

    SET @drive_type_desc =

    CASE @drive_type

    WHEN 1 THEN 'Removable'

    WHEN 2 THEN 'Fixed'

    WHEN 3 THEN 'Network'

    WHEN 4 THEN 'CD-ROM'

    WHEN 5 THEN 'RAM Disk'

    ELSE 'Unknown'

    END;

    END

    ELSE

    BEGIN

    SET @drive_type = NULL;

    SET @drive_type_desc = 'Unknown';

    END

    SET @prop_vol_name = NULLIF(LTRIM(RTRIM(@prop_vol_name)),'');

    UPDATE @drive_info

    SET DRIVE_TYPE = @drive_type,

    DRIVE_TYPE_DESC = @drive_type_desc,

    VOLUME_NAME = @prop_vol_name,

    TOTAL_SPACE_MB = @total_size,

    USED_SPACE_MB = @total_size - FREE_SPACE_MB

    WHERE drive = @drive;

    FETCH NEXT FROM drv_cursor INTO @drive

    END

    CLOSE drv_cursor

    DEALLOCATE drv_cursor

    EXEC @hr = sp_OADestroy @fso

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    END TRY

    BEGIN CATCH

    PRINT ERROR_MESSAGE()

    END CATCH

    SELECT

    SERVER_NAME,

    DRIVE,

    FREE_SPACE_MB,

    USED_SPACE_MB,

    TOTAL_SPACE_MB,

    DRIVE_TYPE,

    DRIVE_TYPE_DESC,

    VOLUME_NAME

    FROM @drive_info

    ORDER BY drive

    END;

    GrayB

Viewing 15 posts - 31 through 45 (of 187 total)

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