Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1011121314»»

Automating SQL Server Health Check (SQL Server 2005) Expand / Collapse
Author
Message
Posted Tuesday, April 24, 2012 2:15 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:46 AM
Points: 95, Visits: 1,136
Look at the very 1st post.
There is an attachment with the script.
It has an .txt ending.

Regarding the code:
Inside the whole SP, there´s one variable called @TableHTML which will be used to generate the HTML code for the Email Body.
-- Variable declaration   
DECLARE @TableHTML VARCHAR(MAX),
@StrSubject VARCHAR(100),
@Oriserver VARCHAR(100),
@Version VARCHAR(250),
@Edition VARCHAR(100),
@ISClustered VARCHAR(100),
@SP VARCHAR(100),
@ServerCollation VARCHAR(100),
@SingleUser VARCHAR(5),
@LicenseType VARCHAR(100),
@StartDate DATETIME,
@EndDate DATETIME,
@Cnt int,
@URL varchar(1000),
@Str varchar(1000)

Regards
Dirk


--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Post #1288714
Posted Thursday, May 10, 2012 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:08 AM
Points: 8, Visits: 117
Hi All,

Your the scripts is awesome..!!
Can you guide how this can be modified to get the same information for multiple servers i.e running this script in one server that will provide information for all server of the same domain...

I will be glad to get guidance on this...

Thanks.!
Post #1297868
Posted Friday, May 11, 2012 12:48 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:46 AM
Points: 95, Visits: 1,136
I never tried it in this way...

What I did to run the solution:
Implemented the proc and a corresponding sql srv job on each server.
Now I get from every server a report in the morning. With the help of a filter rule I highlight those emails which contain a failed or false error message

If you need to gather the informations first, then I would recommend that you take a look at Rodney Landrum's Tacklebox.
There's an example how to build a repository, get information from multiple servers via a SSIS package and afterwards run reports against these information.



--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Post #1298362
Posted Friday, May 11, 2012 5:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:08 AM
Points: 8, Visits: 117
Thanks a lot Dirk for your quick response..!!
Info provided is really good but am looking for a such a automating scripts that should be running on one server and retrieves the SQL service's status for all servers in domain.

Benefit of it would be that one server will act something like "Monitor server". Am a core SQL server DBA and do not have much knowledge on t-sql programming part...

Let me know if you get some info on the same.

Thanks,
Post #1298484
Posted Saturday, May 12, 2012 10:29 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:46 AM
Points: 95, Visits: 1,136
Hi there,

it´s the same on my side.
I look out for useful scripts, tools etc and adapt them.
The repository mentioned in the tacklebox is running on one server in the company I work for and gathers information from more than 200 productive instances.

So the only thing I did was to enter all servers in on of the tables of the repository. That´s the "source" for the ssis package which then connects to each server indiviudally and gathers the information.
Quite easy if you can setup up some kind of service account which has access to all your servers/instances.
This account is also the proxy account on the server which is running the ssis package.
To set it up properly and let it run in my environment it took me about 3-4 hours and now works like a charm.

Regards and have a great weekend
Dirk


--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Post #1299129
Posted Saturday, May 12, 2012 9:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 1:07 AM
Points: 21, Visits: 72
Dirk.Hondong (4/24/2012)
Look at the very 1st post.
There is an attachment with the script.
It has an .txt ending.

Regarding the code:
Inside the whole SP, there´s one variable called @TableHTML which will be used to generate the HTML code for the Email Body.
-- Variable declaration   
DECLARE @TableHTML VARCHAR(MAX),
@StrSubject VARCHAR(100),
@Oriserver VARCHAR(100),
@Version VARCHAR(250),
@Edition VARCHAR(100),
@ISClustered VARCHAR(100),
@SP VARCHAR(100),
@ServerCollation VARCHAR(100),
@SingleUser VARCHAR(5),
@LicenseType VARCHAR(100),
@StartDate DATETIME,
@EndDate DATETIME,
@Cnt int,
@URL varchar(1000),
@Str varchar(1000)

Regards
Dirk



Dear Dirk

I cant find any txt file attached in this post,

but i copy and paste your code there then i got the following error msg:


Must declare the scalar variable "@ServerIP".
Msg 105, Level 15, State 1, Line 16
Unclosed quotation mark after the character string '</font></td>
</tr>
</table>
Post #1299206
Posted Saturday, May 12, 2012 11:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 1:07 AM
Points: 21, Visits: 72
and this is the code which i use:


-- Variable declaration
DECLARE @TableHTML VARCHAR(MAX),
@StrSubject VARCHAR(100),
@Oriserver VARCHAR(100),
@Version VARCHAR(250),
@Edition VARCHAR(100),
@ISClustered VARCHAR(100),
@SP VARCHAR(100),
@ServerCollation VARCHAR(100),
@SingleUser VARCHAR(5),
@LicenseType VARCHAR(100),
@StartDate DATETIME,
@EndDate DATETIME,
@Cnt int,
@URL varchar(1000),
@Str varchar(1000),

---------------------------- Section A ------------------------------
SET @TableHTML =
'<font face="Verdana" size="4">Server Info</font>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">
<tr>
<td width="27%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server IP</font></b></td>
<td width="39%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>
<td width="90%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Project/Client</font></b></td>
</tr>
---------------------------- Section B ------------------------------
<tr>
<td width="27%" height="27"><font face="Verdana" size="2">'+@ServerIP+'</font></td>
<td width="39%" height="27"><font face="Verdana" size="2">' + @OriServer +'</font></td>
<td width="90%" height="27"><font face="Verdana" size="2">'+@Project+'</font></td>
</tr>
</table>
Post #1299221
Posted Monday, May 14, 2012 5:36 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:46 AM
Points: 95, Visits: 1,136
The very first post has a link in it´s footer:
http://www.sqlservercentral.com/Forums/Attachment5016.aspx





--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Post #1299477
Posted Tuesday, September 04, 2012 5:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 16, 2014 6:57 PM
Points: 60, Visits: 552
Thanks for the script...

I did some modifications as per my requirement and it works like a charm.

But, when i scheduled this as a job i am getting a blank report. but if i run the same code in the job in query window, i get the html output.
I tried running the job myself manually, still same result.

Any hints?

Thanks
MvM
Post #1354268
Posted Monday, October 08, 2012 3:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 6:46 AM
Points: 2, Visits: 192
Hi This is krupanandh.N working as a sql dataabase Administrator in spi mysore.
i have scheduled sp "dbo.uspEmailSQLServerHealth" daily triggered at @9 am .
But The job is failing but iam getting reports.

when iam executed this job iam geting the following error.

Message
Executed as user: domain\user An INSERT EXEC statement cannot be nested. [SQLSTATE 42000] (Error 8164). The step failed.

any one help me on this.


Thanks
krupanandh.N
Post #1369683
« Prev Topic | Next Topic »

Add to briefcase «««1011121314»»

Permissions Expand / Collapse