is this script completed or any modification required..
SET NOCOUNT ON
declare @a int
select @a=count(*) from sys.databases where state > 0
if @a=0 
 Select 'GREEN' as Status, 'All Databases are Online!' as Description
else
begin
  create table #dbstate
(
id int identity(1,1),
databaseid int,
dbstate int,
name varchar(50)
)
create table #output
(
dbname varchar(50),
dbstate varchar(50)
)
insert into #dbstate(databaseid,dbstate,name)
select db.database_id,db.state,db.name from sys.databases db
declare @dbcount int
select @dbcount=count(*) from #dbstate
while @dbcount>0
begin
declare @state int
declare @name varchar(50)
select @state=dbstate,@name=name from #dbstate where id=@dbcount
if @state=1
begin
declare @temp1 int
set @temp1=0
select @temp1=count(*) from msdb.dbo.log_shipping_monitor_secondary a 
join sys.databases b on a.secondary_database=b.name
join #dbstate c on b.database_id=c.databaseid where c.id=@dbcount
if @temp1=0 
insert into #output values(@name,'Restoring')
end
else if @state=2
begin
declare @temp2 int
set @temp2=0
select @temp2=count(*) from msdb.dbo.log_shipping_monitor_secondary a 
join sys.databases b on a.secondary_database=b.name
join #dbstate c on b.database_id=c.databaseid where c.id=@dbcount
if @temp2=0 
insert into #output values(@name,'recovering')
end
else if @state=3
begin
insert into #output values(@name,'recovery_pending')
end
else if @state=4
begin
insert into #output values(@name,'suspect')
end
else if @state=5
begin
insert into #output values(@name,'emergency')
end
else if @state=6
begin
insert into #output values(@name,'offline')
end
set @dbcount=@dbcount-1
end

select @dbcount=count(*) from #output
if @dbcount>0
begin
Select 'RED' as Status, 'Below databases are not in Online state' as Description
select * from #output
end
else
Select 'GREEN' as Status, 'All Databases are Online!' as Description

drop table #dbstate
drop table #output
end
September 15th, 2013 2:16am

the query looks fine. Are you trying to the get the state of DBs ?

At the end try this query to get summarized state for all DBs,

select a.*,ISNULL(b.dbstate,'ONLINE') from #dbstate a
left join #output b
ON a.name=b.dbname

Free Windows Admin Tool Kit Click here and download it now
September 15th, 2013 4:39am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics