Thursday, February 21, 2008

SQL Server 2000 - Health check scripts

Below is the health check script that shows the following information : -

SQL Server Daily Health Check Script will report the following:-

1) The SQL Server Name
2) The SQL Server IP Address
3) The SQL Server version
4) The Disk Space available
5) Scheduled jobs that has failed.
6) Any critical errors in SQL Server error Logs.
7) All Database and the backups status.
8) Transactions log sizes




/******************************************************************
*
* DAILY HEALTH CHECK SCRIPT - SQL Server
*
*
******************************************************************/
/*
declare @rc int
set @rc=0
exec sp_emailDailyHealthCheck
@charfrom = N'DELL@r.com',
@charemailserver = N'1.1.1.1',
@charsubject = N'SQL Health Check Report: (Server: "DELL")',
@intresults = @rc
*/
/*****************************************
* Create temp tables for storing the health check output
*****************************************/
--drop table #script_output

CREATE TABLE #script_output (msg varchar(1000),row [int] IDENTITY (1, 1) NOT NULL )
Declare @output varchar(1000)

insert into #script_output values ('< HTML>< title>Daily Health Check Report - ' + @@servername + ' - '+ cast(getdate() as varchar) + '')
insert into #script_output values ('< p>

Daily Health Check Report - ' + @@servername + ' - '+ cast(getdate() as varchar) + '

< /p>')

set @output = 'SERVER NAME: ' + @@servername + '< br>< br>'
insert into #script_output values (@output)

set @output = 'VERSION: ' + @@version + '< br>< br>'
insert into #script_output values (@output )

--- Get the ip address of the server
CREATE TABLE #temp1(t varchar(3000))
insert into #temp1 exec xp_cmdshell 'ipconfig'
DECLARE @t1 varchar(300)-- subject to localisation

SET @t1 = (
SELECT top 1 #temp1.t from #temp1
where t like '%IP Address%'
order by t DESC
)


insert into #script_output
SELECT 'IP Address:' +LTRIM(RTRIM(SUBSTRING(t,CHARINDEX(':',t)+1,len(@t1)))) + '< br>' from #temp1
where t like '%IP Address%'


drop table #temp1
-- end of getting ip address


/******************************************************************
*
* Get Total Disk Space Availaible
*
* This script displays Drive and total sql size used and free size
*
*
*****************************************************************/
Begin
Declare @a Varchar(50)
,@b Varchar(50)
,@c Varchar(50)
,@TSize1 VARCHAR(255)

Create Table #Ops_Drives
(
Drive Varchar(1),
Size int
)
Declare @a1 int
,@b1 int
,@c1 int
,@d1 int

Create Table #Ops_DriveTmp
(
DBName Varchar(25)
,Location Varchar(255)
,Size Varchar(8)
,MaxSize int
,Growth Int
,Device Varchar(30)
)

Exec SP_MSForEachDB
'Use [?] Insert into #Ops_DriveTmp
Select Upper(Convert(Varchar(25),DB_Name())) as ''Database'',
Convert(Varchar(255),FileName),Convert(Varchar(8),Size/128)''Size in MB'',
MaxSize,Growth,
Convert(Varchar(30),Name)
from SysFiles'


Select @a1=Sum(Convert(Int,Size)) from #Ops_DriveTmp

Insert Into #Ops_Drives
Exec Master..XP_Fixeddrives
Create Table #Ops_TmpDriveInfo
(
Drive Varchar(1),
SQL Varchar(8),
FreeSpace Varchar(255),
TotalSpace varchar(255),
Util varchar(4)
)

DECLARE @oFSO1 INT,@oDrive1 INT, @ret1 INT
EXEC @ret1 = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO1 OUT

Declare CheckDrives Cursor For Select Drive,size from #Ops_Drives Order by Drive
Open CheckDrives
Fetch Next from CheckDrives into @c,@c1
While (@@Fetch_Status=0)
Begin
Select @b1=sum(convert(Int,Size))
from #Ops_DriveTmp
where substring(Location,1,1)=@c

Set @a =@c + ':\'

EXEC @ret1 = master.dbo.sp_OAMethod @oFSO1, 'GetDrive', @oDrive1 OUT, @a
EXEC @ret1 = master.dbo.sp_OAMethod @oDrive1, 'TotalSize', @TSize1 OUT

set @d1 = CAST(ROUND((CAST(isnull(@TSize1,1) AS FLOAT) / 1024) /1024,0) AS INT)

Insert into #Ops_TmpDriveInfo values(@c,isnull(@b1,0),@c1,@d1 ,
(100 -(round(( @c1)/ ROUND((CAST(isnull(@TSize1,1) AS FLOAT) / 1024) /1024 ,0),3) * 100)) )

Fetch Next from CheckDrives into @c,@c1
End



--- set the output
insert into #script_output values ('< br>')
insert into #script_output values ('< p>< h3>Disk Space< table border=1>')
insert into #script_output values ('< tr>< td>Drive< /td>< td>SQL Server Data(MB)< /td>< td>Free Space (MB)< /td>< td>Total Space (MB)< /td>< td>Utilisation(%)< /td>< /tr>')


insert into #script_output
select '< tr>< td>' + Drive + '< /td>< td>' + [SQL]+ '< /td>< td>' +
FreeSpace + '< /td>< td>' + TotalSpace + '< /td>< td>' + Util + '< /td>< /tr>'
from #Ops_TmpDriveINFO
insert into #script_output values ('< /table>< /p>')

drop table #Ops_TmpDriveINFO
drop table #Ops_DriveTmp
drop table #Ops_Drives

exec @ret1 = sp_OADestroy @oFSO1
exec @ret1 = sp_OADestroy @oDrive1

Close CheckDrives

Deallocate CheckDrives

End
/******************************************************************
*
* End Of Get Total Disk Space Availaible
******************************************************************/

/******************************************************************
*
* Get Summary Database Details
*
* This script displays Summary Database Details, % used
*
*
*****************************************************************/
SET nocount ON

DECLARE @dbname SYSNAME

CREATE TABLE #datafilestats (
dbname VARCHAR(200),
flag BIT DEFAULT 0 ,
fileid TINYINT,
[filegroup] TINYINT,
totalextents DEC(20,1),
usedextents DEC(20,1),
[name] VARCHAR(200),
[filename] VARCHAR(200))

DECLARE @string SYSNAME

SET @string = ''

SET @dbname = ''

WHILE 1 = 1
BEGIN
SELECT top 1 @dbname = name
FROM master..sysdatabases
WHERE name > @dbname
IF @@ROWCOUNT = 0
BREAK
IF NOT (SELECT Databasepropertyex(@dbname,'Status')) = 'ONLINE'
BREAK
SET @string = 'use [' + @dbname + '] DBCC SHOWFILESTATS with no_infomsgs'
INSERT INTO #datafilestats
(fileid,
[filegroup],
totalextents,
usedextents,
[name],
[filename])
EXEC( @string)
UPDATE #datafilestats
SET dbname = @dbname,
flag = 1
WHERE flag = 0
UPDATE #datafilestats
SET totalextents = (SELECT SUM(totalextents) * 8 * 8192.0 / 1048576.0
FROM #datafilestats
WHERE dbname = @dbname)
WHERE flag = 1
AND fileid = 1
AND filegroup = 1
AND dbname = @dbname
UPDATE #datafilestats
SET usedextents = (SELECT SUM(usedextents) * 8 * 8192.0 / 1048576.0
FROM #datafilestats
WHERE dbname = @dbname)
WHERE flag = 1
AND fileid = 1
AND filegroup = 1
AND dbname = @dbname
END

CREATE TABLE #sizeinfo (
db_name VARCHAR(200) NOT NULL PRIMARY KEY CLUSTERED,
total DEC(20,1),
data DEC(20,1),
data_used DEC(20,1),
[data (%)] DEC(20,1),
data_free DEC(20,1),
[data_free (%)] DEC(20,1),
LOG DEC(20,1),
log_used DEC(20,1),
[log (%)] DEC(20,1),
log_free DEC(20,1),
[log_free (%)] DEC(20,1),
status DEC(20,1))

INSERT #sizeinfo
(db_name,
[log],
[log (%)],
status)
EXEC( 'dbcc sqlperf(logspace) with no_infomsgs')

UPDATE #sizeinfo
SET data = d.totalextents
FROM #datafilestats d
JOIN #sizeinfo s
ON d.dbname = s.db_name
WHERE d.flag = 1
AND d.fileid = 1
AND d.filegroup = 1

UPDATE #sizeinfo
SET data_used = d.usedextents
FROM #datafilestats d
JOIN #sizeinfo s
ON d.dbname = s.db_name
WHERE d.flag = 1
AND d.fileid = 1
AND d.filegroup = 1

UPDATE #sizeinfo
SET total = (data + LOG)

UPDATE #sizeinfo
SET [data (%)] = (data_used * 100.0 / data)

UPDATE #sizeinfo
SET data_free = (data - data_used)

UPDATE #sizeinfo
SET [data_free (%)] = (100 - [data (%)])

UPDATE #sizeinfo
SET log_used = (LOG * [log (%)] / 100.0)

UPDATE #sizeinfo
SET log_free = (LOG - log_used)

UPDATE #sizeinfo
SET [log_free (%)] = (log_free * 100.0 / LOG)

BEGIN

--- set the output
insert into #script_output values ('< br>')
insert into #script_output values ('< p>< h3>Database Summary< table border=1>')
insert into #script_output values ('< tr>< td>Database Name< /td>< td>Total(MB)< /td>< td>Data (MB)< /td>< td>Data Used (MB)< /td>< td>Data Free (MB)< /td>' +
'< td>Data Used(%)< /td>< td>Log(MB)< /td>< td>Log Used(MB)< /td>< td>Log Free(MB)< /td>< td>Log Used(%)< /td>< /tr>')

Insert into #script_output (msg)
SELECT '< tr>< td>' + db_name + '< /td>< td>' +
cast(total as varchar) + '< /td>< td>' +
cast(data as varchar) + '< /td>< td>' +
cast(data_used as varchar) + '< /td>< td>' +
cast(data_free as varchar) + '< /td>< td>' +
''+ cast([data (%)]as varchar)+ '< /td>< td>' +
cast([log] as varchar) + '< /td>< td>' +
cast(log_used as varchar)+ '< /td>< td>' +
cast(log_free as varchar)+ '< /td>< td>' +
''+ cast([log (%)] as varchar)+ '< /td>< /tr>'
FROM #sizeinfo WITH (nolock)
ORDER BY total desc

insert into #script_output values ('< /table>< /p>')

END

DROP TABLE #datafilestats

DROP TABLE #sizeinfo

/******************************************************************
*
* End Of Summary Database Details
*
*****************************************************************/





/******************************************************************
*
* SQL Server Disk Space Check
*
* This script displays database and file size, compared to total
* and free disk space. It also displays the number of Kb that each
* file will grow by on the next extend.
*
******************************************************************/

BEGIN
/*****************************************
* Create temp tables for disk space info
*****************************************/
CREATE TABLE #space (dletter varchar(2), fspace int, tspace BIGINT)
CREATE TABLE #dbsize (dbname varchar(50), dbsize int, remarks varchar(255))
CREATE TABLE #fdata ([name] VARCHAR(255), [filename] VARCHAR(255),
[filegroup] VARCHAR(10), [size] VARCHAR(50),
[maxsize] VARCHAR(50), growth VARCHAR(20), usage VARCHAR(20))
CREATE TABLE #growth (dbname VARCHAR(50), fname VARCHAR(255), next_exp INT, gtype VARCHAR(2))

/*****************************************
* populate temp tables
*****************************************/
INSERT INTO #space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
INSERT INTO #dbsize EXEC master.dbo.sp_databases

-- Create cursor for files
DECLARE c_files CURSOR FOR
SELECT RTRIM(af.fileid), RTRIM(af.[name]), RTRIM(af.[filename]),
RTRIM(af.[size]), RTRIM(db.[name])
FROM master.dbo.sysaltfiles af, master.dbo.sysdatabases db
WHERE af.dbid = db.dbid AND db.version <> 0
DECLARE @tfileid INT, @tname VARCHAR(255), @tfilename VARCHAR(255)
DECLARE @tsize INT, @tdbname VARCHAR(50)
DECLARE @SQL NVARCHAR(255)
DECLARE @growth VARCHAR(20), @next_exp INT, @gtype VARCHAR(2)

-- Open cursor
OPEN c_files
FETCH NEXT FROM c_files
INTO @tfileid, @tname, @tfilename, @tsize, @tdbname

-- Populate #growth table with file growth details
WHILE @@fetch_status = 0
BEGIN
TRUNCATE TABLE #fdata
-- Get file data
SET @SQL = 'INSERT INTO #fdata EXEC '
SET @SQL = @SQL + @tdbname + '.dbo.sp_helpfile ''' + @tname + ''''
EXEC sp_executesql @SQL
SELECT @growth = growth FROM #fdata
-- Determine if growth is % or Mbytes
IF RIGHT(@growth,1) = '%'
BEGIN
SET @next_exp = CAST(LEFT(@growth, LEN(@growth) - 1) AS INT)
SET @next_exp = CAST(ROUND(((CAST(@tsize AS FLOAT) * 8) / 100) * @next_exp,0) AS INT)
SET @gtype = '%'
END
ELSE
BEGIN
SET @next_exp = CAST(LEFT(@growth, CHARINDEX(' ',@growth)) AS INT)
SET @gtype = 'MB'
END
-- Create record for file in #growth table
INSERT INTO #growth VALUES (@tdbname, @tname, @next_exp, @gtype)
FETCH NEXT FROM c_files
INTO @tfileid, @tname, @tfilename, @tsize, @tdbname
END

-- Close cursor
CLOSE c_files
DEALLOCATE c_files

/*****************************************
* Update temp table info with total disk sizes
*****************************************/
-- Create cursor for disk space table
DECLARE c_disks CURSOR FOR
SELECT dletter, fspace, tspace FROM #space
FOR UPDATE
DECLARE @dletter VARCHAR(2), @fspace INT, @tspace BIGINT

-- Create FileSystemObject
DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT



-- Open cursor and fetch first row
OPEN c_disks
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace

-- Loop through all records in the cursor
WHILE @@fetch_status = 0
BEGIN
-- Get disk size
SET @dletter = @dletter + ':\'
EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
-- Update table
UPDATE #space
SET tspace = CAST(@drsize AS BIGINT)
WHERE CURRENT OF c_disks
-- Destory oDrive
EXEC master.dbo.sp_OADestroy @oDrive
-- Fetch next row
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace


END

-- Close cursor
CLOSE c_disks
DEALLOCATE c_disks

-- Destroy FSO
EXEC master.dbo.sp_OADestroy @oFSO


/*****************************************
* Return disk space info
*****************************************/
insert into #script_output values ('< br>')
insert into #script_output values ('< p>< h3>Database Detail Space Usage Info')
insert into #script_output values ('< table border=1>')
insert into #script_output values
('< tr>< td>Database Name< /td>< td>File Name< /td>< td>Database Size(MB)< /td>< td>File Size(Mb)< /td>< td>Free DiskSpace(Mb)< /td>< td>Total DiskSpace(Mb)< /td>< td>Next Extension< /td>< td>Next Expansion< /td>< td>File Path< /td>< /tr>')


insert into #script_output (msg)
SELECT '< tr>< td>' + db.[name] + '< /td>< td>' + af.[name] + '< /td>< td>' +
CAST(ROUND(CAST(ds.dbsize AS FLOAT) / 1024,0) AS varchar) + '< /td>< td>' +
CAST(ROUND((CAST(af.[size] AS FLOAT) * 8) / 1024,0) AS varchar) + '< /td>< td>'+
CAST(s.fspace AS varchar)+ '< /td>< td>' +
CAST(ROUND((CAST(s.tspace AS FLOAT) / 1024) /1024,0) AS varchar) + '< /td>< td>' +
STR(g.next_exp) + ' KB< /td>< td>' +
CAST(ROUND(CAST(g.next_exp AS FLOAT) / 1024,0) AS varchar) + '< /td>< td>' +
af.[filename] + '< /td>< /tr>'
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]
ORDER BY ds.dbsize desc ,db.[name], af.[name]

insert into #script_output values ('< /table>< /p>')

/*****************************************
* Drop temporary tables
*****************************************/
DROP TABLE #space
DROP TABLE #dbsize
DROP TABLE #fdata
DROP TABLE #growth

END

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/



/******************************************************************
*
*Check for Last Backup Done.
******************************************************************/
--- set the output
insert into #script_output values ('< br>')
insert into #script_output values ('< p>< h3>Last Backup< table border=1>')
insert into #script_output values ('< tr>< td>Database Name< /td>< td>Days Since LastBackup< /td>< td>LastBackup< /td>< /tr>')

insert into #script_output (msg)
SELECT '< td>' + B.name + '< /td>< td>' + ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER')
+ '< /td>< td>' + ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') + '< /td>< /tr>'
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

insert into #script_output values ('< /table>< /p>')

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/


/******************************************************************
*
* Lists user and permission
******************************************************************/


-- ***************************************************************************
-- Declare local variables
DECLARE @DBName1 VARCHAR(32);
DECLARE @SQLCmd VARCHAR(1024);
-- ***************************************************************************

-- ***************************************************************************
-- Get the SQL Server logins
-- Create Temp User table
CREATE TABLE ##Users (
[sid] varbinary(85) NULL,
[Login Name] varchar(24) NULL,
[Default Database] varchar(18) NULL,
[Login Type] varchar(9),
[AD Login Type] varchar(8),
[sysadmin] varchar(3),
[securityadmin] varchar(3),
[serveradmin] varchar(3),
[setupadmin] varchar(3),
[processadmin] varchar(3),
[diskadmin] varchar(3),
[dbcreator] varchar(3),
[bulkadmin] varchar(3));
---------------------------------------------------------
INSERT INTO ##Users SELECT sid,
loginname AS [Login Name],
dbname AS [Default Database],
CASE isntname
WHEN 1 THEN 'AD Login'
ELSE 'SQL Login'
END AS [Login Type],
CASE
WHEN isntgroup = 1 THEN 'AD Group'
WHEN isntuser = 1 THEN 'AD User'
ELSE ''
END AS [AD Login Type],
CASE sysadmin
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [sysadmin],
CASE [securityadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [securityadmin],
CASE [serveradmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [serveradmin],
CASE [setupadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [setupadmin],
CASE [processadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [processadmin],
CASE [diskadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [diskadmin],
CASE [dbcreator]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [dbcreator],
CASE [bulkadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [bulkadmin]
FROM master.dbo.syslogins
Where sysadmin =1 or securityadmin =1 or serveradmin =1 or setupadmin=1 or processadmin = 1
or diskadmin=1 or dbcreator =1 or bulkadmin=1;

---------------------------------------------------------


--- set the output
insert into #script_output values ('< br>')
insert into #script_output values ('< p>< h3>List Of Users That Has Server Roles< table border=1>')
insert into #script_output values ('< tr>< td>Login Name< /td>< td>Default Database< /td>< td>Login Type< /td>' +
'< td>sysadmin< /td>< td>securityadmin< /td>< td>serveradmin< /td>< td>setupadmin< /td>< td>processadmin< /td>' +
'< td>diskadmin< /td>< td>dbcreator< /td>< td>bulkadmin< /td>< /tr>')

insert into #script_output (msg)
SELECT '< tr>< td>' + [Login Name] + '< /td>< td>' +
[Default Database]+ '< /td>< td>' +
[Login Type] + [AD Login Type] + '< /td>< td>' +
[sysadmin] + '< /td>< td>' +
[securityadmin] + '< /td>< td>' +
[serveradmin] + '< /td>< td>' +
[setupadmin] + '< /td>< td>' +
[processadmin] + '< /td>< td>' +
[diskadmin] + '< /td>< td>' +
[dbcreator] + '< /td>< td>' +
[bulkadmin] + '< /td>< /tr>'
FROM ##Users
ORDER BY [Login Type], [AD Login Type], [Login Name]


insert into #script_output values ('< /table>< /p>')

-- ***************************************************************************
-- ***************************************************************************

-- ***************************************************************************
-- Create the output table for the Database User ID's
CREATE TABLE ##DBUsers (
[Database] VARCHAR(64),
[Database User ID] VARCHAR(64),
[Server Login] VARCHAR(64),
[Database Role] VARCHAR(64))
-- ***************************************************************************


-- Declare a cursor to loop through all the databases on the server
DECLARE csrDB CURSOR FOR
SELECT name
FROM master..sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');
-- ***************************************************************************
-- ***************************************************************************
-- Open the cursor and get the first database name
OPEN csrDB
FETCH NEXT
FROM csrDB
INTO @DBName1
-- ***************************************************************************
-- ***************************************************************************
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- ***************************************************************************
-- ***************************************************************************
--
SELECT @SQLCmd = 'INSERT ##DBUsers ' +
' SELECT ''' + @DBName1 + ''' AS [Database],' +
' su.[name] AS [Database User ID], ' +
' COALESCE (u.LoginName, ''** Orphaned **'') AS [Server Login], ' +
' COALESCE (sug.name, ''Public'') AS [Database Role] ' +
' FROM [' + @DBName1 + '].[dbo].[sysusers] su' +
' LEFT OUTER JOIN master.dbo.syslogins u' +
' ON su.sid = u.sid' +
' LEFT OUTER JOIN ([' + @DBName1 + '].[dbo].[sysmembers] sm ' +
' INNER JOIN [' + @DBName1 + '].[dbo].[sysusers] sug ' +
' ON sm.groupuid = sug.uid)' +
' ON su.uid = sm.memberuid ' +
' WHERE su.hasdbaccess = 1' +
' AND su.[name] != ''dbo'' AND u.LoginName is null '
EXEC (@SQLCmd)
-- ***************************************************************************
-- ***************************************************************************
-- Get the next database name
FETCH NEXT
FROM csrDB
INTO @DBName1
-- ***************************************************************************
-- ***************************************************************************
-- End of the cursor loop
END
-- ***************************************************************************
-- ***************************************************************************
-- Close and deallocate the CURSOR
CLOSE csrDB
DEALLOCATE csrDB
-- ***************************************************************************
-- ***************************************************************************

--- set the output
insert into #script_output values ('< br>')
insert into #script_output values ('< p>< h3>List Of Orphan Users< table border=1>')
insert into #script_output values ('< tr>< td>Database User ID< /td>< td>Server Login< /td>' +
'< td>Database Role< /td>< td>Database< /td>< /tr>')


-- Return the Database User data
insert into #script_output (msg)
SELECT '< tr>< td>' + [Database User ID] + '< /td>< td>' +
[Server Login] + '< /td>< td>' +
[Database Role] + '< /td>< td>' +
[Database] + '< /td>< /tr>'
FROM ##DBUsers
ORDER BY [Database User ID],[Database];


insert into #script_output values ('< /table>< /p>')

-- ***************************************************************************
-- ***************************************************************************
DROP TABLE ##Users;

DROP TABLE ##DBUsers;
-- ***************************************************************************



/******************************************************************
*
* End of Lists user and permission
*
******************************************************************/

/******************************************************************
*
* Failed Scheduled alert jobs logs
*
******************************************************************/

--- set the output
insert into #script_output values ('< br>')
insert into #script_output values ('< p>< h3>Failed Scheduled SQL Job< table border=1>')
insert into #script_output values ('< tr>< td>Job Name< /td>< td>Run Date & Time< /td>< /tr>')

insert into #script_output
SELECT '< td>' + j.name + '< /td>< td>' + cast(DATEADD
( SECOND, jh.run_duration, CAST
(CONVERT(VARCHAR, jh.run_date)
+ ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')
AS DATETIME
) ) as varchar) + '< /td>< /tr>'
FROM
msdb..sysjobhistory jh
INNER JOIN
msdb..sysjobs j
ON
j.job_id = jh.job_id
WHERE
jh.step_name = '(Job outcome)'
and jh.run_date >= cast(year(getdate() - 1)as varchar) + right('0'+ cast(month(getdate() -1 )as varchar),2) + right('0'+ cast(day(getdate() -1) as varchar),2)
and jh.run_status =0 -- failure

insert into #script_output values ('< /table>< /p>')

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/



/******************************************************************
*
* Lists out the Last 20 Sql error logs with filtering on unimportant mesg
*
******************************************************************/


CREATE TABLE #Errors (vchMessage varchar(500), ID int)
CREATE INDEX idx_msg ON #Errors(ID, vchMessage)

INSERT #Errors EXEC sp_readerrorlog

--This will remove the header from the errolog
SET ROWCOUNT 4
DELETE #Errors
SET ROWCOUNT 0

--- set the output
insert into #script_output values ('< br>')
insert into #script_output values ('< p>< h3>Last 20 SQL Servers log< table border=1>')

insert into #script_output (msg)
SELECT top 20 '< tr>< td>' + vchMessage + '< /td>< /tr>'
FROM #Errors
WHERE vchMessage NOT LIKE '%Copyright (c)%'
AND (isdate(substring(vchMessage,1,10)) = 1)
AND substring(vchMessage,1,10) > convert(varchar(10),(dateadd(dd,-1,getdate()-1)),120)
ORDER BY ID DESC


insert into #script_output values ('< /table>< /p>')

DROP TABLE #Errors


/******************************************************************
*
*End of Lists out the Last 20 Sql error logs with filtering on unimportant mesg
*
******************************************************************/



/******************************************************************
*
* Writing the script output to html file
*
******************************************************************/

insert into #script_output values ('')


DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80),
@objFile int


set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT



Declare @Filename varchar(30)
--set @Filename = 'DailyHealthcheck_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') + '.html'
set @Filename = 'c:\DailyHealthcheck.html'

Select @FileAndPath=@filename


if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,1,True


if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'

Declare msgcursor CURSOR FOR
select msg from #script_output order by row

Declare @String1 varchar(2000)

Open msgcursor
fetch next from msgcursor
into @String1

-- Loop through all records in the cursor
WHILE @@fetch_status = 0
BEGIN
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'WriteLine', Null, @String1

fetch next from msgcursor
into @String1

END

Close msgcursor
Deallocate msgcursor

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream

drop table #script_output

/******************************************************************
*
* End Of Writing the script output to html file
******************************************************************/


/******************************************************************
*
* Sending the health check report to email
******************************************************************/
---- get all the operator email
Declare c2 scroll cursor
for
SELECT email_address FROM msdb.dbo.sysoperators
where enabled=1

Declare @email varchar(3000)
Declare @tmpemail varchar(500)
set @email =''

open c2
fetch next from c2 into @tmpemail
While (@@Fetch_Status <> - 1)
begin

set @email= @email + @tmpemail + ','
fetch next from c2 into @tmpemail
end
close c2
deallocate c2

declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@from = @charfrom,
@to = @email,
@server = @charemailserver,
@subject = @charsubject ,
@attachment = N'c:\DailyHealthcheck.html',
@message = N'See Attached report '


/******************************************************************
*
* End Of Sending the health check report to email
******************************************************************/





1 comment:

msasi236 said...

How to exclude the databases which are offline. The script was showing error, as some databases are offline.