April 12, 2013 at 8:09 pm
Blitz index report will be a great addition to this report.
April 12, 2013 at 10:20 pm
I was just curious to know how it sends text alerts. Can you please explain? Thanks in advance.
April 13, 2013 at 9:27 am
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.
April 13, 2013 at 11:28 am
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
April 15, 2013 at 9:47 am
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 🙂
April 15, 2013 at 10:25 am
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
April 15, 2013 at 11:05 am
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.
April 15, 2013 at 11:55 am
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.
April 15, 2013 at 12:24 pm
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.
HealthReportID DateStamp GeneratedHTML
14 2013-04-15 06:05:05.170 NULL
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
April 15, 2013 at 12:30 pm
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.
HealthReportID DateStamp GeneratedHTML
14 2013-04-15 06:05:05.170 NULL
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.
April 15, 2013 at 1:06 pm
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)
April 15, 2013 at 3:04 pm
This is awesome.
Thank you.
April 15, 2013 at 3:39 pm
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.
April 15, 2013 at 3:45 pm
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.
April 15, 2013 at 3:48 pm
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 186 total)
You must be logged in to reply to this topic. Login to reply