October 12, 2008 at 9:51 am
I'm trying to write a T-SQL script that will go through all my databases that are being mirrored and setup warning thresholds. My problem is that I'm not a DBA nor am I familiar with programing enough to connect all the dots.
I'm assuming I need to create a temp table that pulls the database names I need from sys.databases and put that into my temp table. Then take that information and replace the DBName field below with each of the databases that I have in my temp table.
exec sys.sp_dbmmonitorchangealert @database_name=N'DBName', @alert_id='1', @threshold='15', @enabled='1'
exec sys.sp_dbmmonitorchangealert @database_name=N'DBName', @alert_id='2', @threshold='10240', @enabled='1'
exec sys.sp_dbmmonitorchangealert @database_name=N'DBName', @alert_id='3', @threshold='10240', @enabled='1'
I've been searching online for similar scripts but I'm not knowledgeable enough to see where my logic is incorrect.
I know I need to have a DECLARE Statement for the database name. As well as I may have to use curDBName to pull the dbname info into memory and then write it into my temp table. Then from there use the temp table to pull the dbname and put it into the above exec statements. However, I'm not sure if this is correct or if there is a better way to do this.
October 14, 2008 at 9:08 am
The simplest way to run a particular script in each and every database is:
exec sp_MSForEachDB 'use ?;exec sp_spaceused;'
However, this would run the script for all databases, not just your mirrored ones.
September 9, 2009 at 11:36 pm
Try this 🙂
declare @sql nvarchar(4000)
declare @dbname varchar(100)
declare @db_id int
declare db_cursor cursor forward_only for
select db_name(database_id) from sys.database_mirroring
where mirroring_role = 1
open db_cursor
fetch next from db_cursor into @dbname
while @@FETCH_STATUS = 0
begin
set @sql = ' exec sys.sp_dbmmonitorchangealert @database_name= '''
set @sql = @sql + @dbname
set @sql = @sql + ''', @alert_id=''1'', @threshold=''15'', @enabled=''1'' '
-- print @sql
execute sp_executesql @sql
fetch next from db_cursor into @dbname
end
close db_cursor
deallocate db_cursor
September 9, 2009 at 11:38 pm
Try this 🙂
declare @sql nvarchar(4000)
declare @dbname varchar(100)
declare @db_id int
declare db_cursor cursor forward_only for
select db_name(database_id) from sys.database_mirroring
where mirroring_role = 1
open db_cursor
fetch next from db_cursor into @dbname
while @@FETCH_STATUS = 0
begin
set @sql = ' exec sys.sp_dbmmonitorchangealert @database_name= '''
set @sql = @sql + @dbname
set @sql = @sql + ''', @alert_id=''1'', @threshold=''15'', @enabled=''1'' '
-- print @sql
execute sp_executesql @sql
fetch next from db_cursor into @dbname
end
close db_cursor
deallocate db_cursor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply