Database down alert/notification and other reports.
-
Hi,
I am a beginning DBA and would like to set up a system of alerts and notifications. I know there are a lot of options with these in SQL Server 2005, as well as many DMV's and DMF's. I am having trouble finding a solution for what I need though - I would like to be notified/alerted every time any database becomes corrupted/goes offline/even purposefully detached. In other words, any time a db loses connection with an application because it is not available. Is there any view/function/alert in 2005 that would permit me to do that?
I am also trying to set up a report that I could subscribe to to get daily to show the status of the databases, as well as some physical stats like "Database A: Online, MDF size: 1GB, LDF size: 100MB". Does anyone have a solution/TSQL that would let me accomplish this.
I would appreciate any help with this, as I am sure there is a lot of functionality that I have not found. I am looking for a SQL Server 2005 solution, without additional coding.
Thanks again!
-
I don't think there is any build in solution for this...
You have write your own...
You set the database alerts to get the notification when db corrupted...see bol topic "How to: Create an Alert Using Severity Level"
For db size... you can use the following...
set nocount on
declare @cmd varchar(500)
declare @db varchar(128)
create table #results(FileType varchar(4) NOT NULL,
[Name] sysname NOT NULL,
Total numeric(9,2) NOT NULL,
Used numeric(9,2) NOT NULL,
[Free] numeric(9,2) NOT NULL,
dbname sysname NULL)
create table #data(Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL)
create table #log(dbname sysname NOT NULL,
LogSize numeric(15,7) NOT NULL,
LogUsed numeric(9,5) NOT NULL,
Status int NOT NULL)
begin
/* Get data file(s) size */
declare dcur cursor local fast_forward
for
select NAME from sys.databases
open dcur
fetch next from dcur into @db
while @@fetch_status=0
begin
set @cmd = 'use ' + @db + ' DBCC showfilestats'
insert #data
exec(@cmd)
insert #results(FileType,[Name],Total,Used,[Free],dbname)
select 'Data',
left(right([FileName],charindex('\',reverse([FileName]))-1),
charindex('.',right([FileName],
charindex('\',reverse([FileName]))-1))-1),
CAST(((TotalExtents*64)/1024.00) as numeric(9,2)),
CAST(((UsedExtents*64)/1024.00) as numeric(9,2)),
(CAST(((TotalExtents*64)/1024.00) as numeric(9,2))
-CAST(((UsedExtents*64)/1024.00) as numeric(9,2))),
@db
from #data
delete #data
fetch next from dcur into @db
end
close dcur
deallocate dcur
/* Get log file(s) size */
insert #log
exec('dbcc sqlperf(logspace)')
insert #results(FileType,[Name],Total,Used,[Free],dbname)
select 'Log',dbname+'_log',LogSize,
((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize),
dbname
from #log
select dbname,FileType,[Name],Total,Used,[Free]
from #results order by dbname,FileType
select @@servername as servername, dbname, sum(total) Total, sum(used) Used, sum(free) Free
from #results
group by dbname
order by Total desc
drop table #data
drop table #log
drop table #results
return
end
/**********************************
**********************/
MohammedU
Microsoft SQL Server MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply