To create the sql server health check

Question

To create the sql server health check

bala2

SSCertifiable

Points: 5547
More actions
April 2, 2012 at 11:35 pm

#255874

Can any one help to create a script to find out sql server health check i.e (We want to create automated SP to find
the Sql server state ,current server node i.e is running,no database and
the status of databases (offline/online/recoverymode...etc), jobs that are running on particular instance.
Please help .
Many Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Start with the Policy-based management policies that ship with SQL. You'll have to load them into the list of policies, they're somewhere in the install directory of SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bala2 SSCertifiable Points: 5547 More actions · Answer 2

Thanks for reply,

I am not having experience on development part.can you please help me out

Divine Flame SSCoach Points: 15943 More actions · Answer 3

bala2 (4/2/2012)
Can any one help to create a script to find out sql server health check i.e (We want to create automated SP to find
the Sql server state ,current server node i.e is running,no database and
the status of databases (offline/online/recoverymode...etc), jobs that are running on particular instance.
Please help .
Many Thanks.

You may need to create more than 1 stored procedure for all the status that you want. I think you need to write vb/powershell script to get the status of the SQL Server Service (running/stopped) as it can't be identified from within the stored procedure if the SQL Server itself is stopped.

However, you can write the stored procedure to get the status of rest all the things such as status of databases, jobs etc. You can create a maintenace database on some test server then you can configure a SSIS package to get the data from all other servers & insert it in the maintenance database.

Sujeet Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

bala2 (4/3/2012)
Thanks for reply,
I am not having experience on development part.can you please help me out

There's no development required in what I suggested.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Divine Flame SSCoach Points: 15943 More actions · Answer 5

GilaMonster (4/3/2012)
bala2 (4/3/2012)
Thanks for reply,
I am not having experience on development part.can you please help me out
There's no development required in what I suggested.

Do we have the option to get the notifications on whether the SQL Server service is running or not or to check whether any particular job is running or not by using Policy Based Management?

Sujeet Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Divine Flame (4/3/2012)
GilaMonster (4/3/2012)
bala2 (4/3/2012)
Thanks for reply,
I am not having experience on development part.can you please help me out
There's no development required in what I suggested.
Do we have the option to get the notifications on whether the SQL Server service is running or not or to check whether any particular job is running or not by using Policy Based Management?

No, but I did say 'Start with Policy-based management'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Divine Flame SSCoach Points: 15943 More actions · Answer 7

GilaMonster (4/3/2012)
Divine Flame (4/3/2012)
GilaMonster (4/3/2012)
bala2 (4/3/2012)
Thanks for reply,
I am not having experience on development part.can you please help me out
There's no development required in what I suggested.
Do we have the option to get the notifications on whether the SQL Server service is running or not or to check whether any particular job is running or not by using Policy Based Management?
No, but I did say 'Start with Policy-based management'

Got it 🙂 I thought I missed something.

Sujeet Singh

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Search the web for Brent Ozar blitz (or sql server take over) and also for Glenn Berry diagnostic. They both have great stuff.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service