June 24, 2004 at 1:54 pm
I'm trying to consolidate server/DB info into one database. I will then do health and welfare checks from the consolidated SRVR_Stats DB. My concept is that I gather all the data (file sizes, free space, backup date/times, etc.) I will then read them and send a consolidated e-mail to an operator instead of 50 individual ones that all really relate to single issue. I want to drop the Signal-to-Noise ratio.
Anyway, this is the procedure that I've written to discover the what recovery model the databases are set to from the named server and write it to one table. Some of it comes from other stuff on this site. Thank you all contributing authors. Anyone see anyway to do it better?
-------------------------
CREATE PROCEDURE CP_Get_Recovery_Model
@SvrName VARCHAR(35)
AS
DECLARE @dbid INT
DECLARE @MaxId INT
DECLARE @RecMod varchar(45)
DECLARE @dbName varchar(45)
DECLARE @SvrName1 VARCHAR(35)
DECLARE @sQuery VARCHAR(4000)
IF NOT EXISTS (SELECT NAME FROM SRVR_STATS.dbo.SYSOBJECTS WHERE NAME ='Recovery_Model')
BEGIN
CREATE TABLE SRVR_STATS.dbo.Recovery_Model
(DBName Varchar(35),
SRVR_Name varchar(15),
Recovery_Model varchar(45),
DB_ID_Num smallint,
AsOfDate datetime DEFAULT (getdate()),
Index_Num bigint IDENTITY (1 ,1 ) NOT FOR REPLICATION )
END
ELSE
BEGIN
DELETE FROM SRVR_STATS.dbo.Recovery_Model WHERE SRVR_Name = @SvrName
END
IF NOT EXISTS (SELECT NAME FROM SRVR_STATS.dbo.SYSOBJECTS WHERE NAME ='DBID_TABLE')
BEGIN
CREATE TABLE SRVR_STATS.dbo.DBID_TABLE
(MAX_DBID int NULL )
END
IF NOT EXISTS (SELECT NAME FROM SRVR_STATS.dbo.SYSOBJECTS WHERE NAME ='DBLIST')
BEGIN
CREATE TABLE SRVR_STATS.dbo.DBLIST
(DBNAME VARCHAR(50))
END
IF NOT EXISTS (SELECT NAME FROM SRVR_STATS.dbo.SYSOBJECTS WHERE NAME ='RECMOD')
BEGIN
CREATE TABLE SRVR_STATS.dbo.RECMOD
(RECMOD VARCHAR(50))
END
BEGIN
SELECT @sQuery = ( 'INSERT INTO SRVR_STATS.dbo.DBID_TABLE SELECT CONVERT(BIGINT,MAX(dbid)) AS MAX_DBID FROM ' + @SvrName + '.MASTER.dbo.sysdatabases')
EXECUTE(@sQuery )
SELECT @MaxId = (SELECT MAX_DBID FROM SRVR_STATS.dbo.DBID_TABLE )
TRUNCATE TABLE SRVR_STATS.dbo.DBID_TABLE
SET @dbid = 1
WHILE @dbid <= @MaxId
BEGIN
TRUNCATE TABLE SRVR_STATS.dbo.DBLIST
SELECT @sQuery = ('INSERT INTO SRVR_STATS.dbo.DBLIST SELECT name FROM ' + @SvrName + '.MASTER.dbo.sysdatabases WHERE dbid = ' + CONVERT(VARCHAR(10), @dbid))
EXECUTE ( @sQuery )
SELECT @dbname = (SELECT dbname FROM SRVR_STATS.dbo.DBLIST)
IF (@dbname IS NOT NULL)
BEGIN
TRUNCATE TABLE SRVR_STATS.dbo.RECMOD
SELECT @sQuery = ('INSERT INTO SRVR_STATS.dbo.RecMod SELECT Case Status & (4+8) ' +
' When 0 Then ' + CHAR(39)+ 'FULL' + CHAR(39)+
' When 4 Then ' + CHAR(39)+ 'BULK_LOGGED' + CHAR(39)+
' When 8 Then ' + CHAR(39)+ 'SIMPLE' + CHAR(39) +
' When 12 Then ' + CHAR(39)+ 'SIMPLE' + CHAR(39) + ' End from ' + @SvrName + '.MASTER.dbo.sysdatabases WHERE NAME = ' + CHAR(39)+ @dbname + CHAR(39) )
EXECUTE ( @sQuery )
select @RecMod = (select RECMOD FROM SRVR_STATS.dbo.RECMOD)
EXEC ('SET QUOTED_IDENTIFIER OFF
insert into SRVR_STATS.dbo.Recovery_Model ( SRVR_Name, DBName,
Recovery_Model, DB_ID_Num, AsOfDate)
VALUES ( "' + @SvrName + '" , "' + @dbname +'" , ' +
' "' + @RecMod + '" , ' + @dbid + ' , CURRENT_TIMESTAMP )' )
SET @dbid = @dbid + 1
END
ELSE SET @dbid = @dbid + 1
END
select * FROM SRVR_STATS.dbo.Recovery_Model
END
-------------------------------
Note that I did hardcode the db name ("SRVR_STATS") in my procedure.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
June 25, 2004 at 8:11 am
This is what I use...
if (select databaseproperty(@DB, 'istrunclog')) = '1'
begin
print ' * recovery model is SIMPLE. *'
set @flag = @flag + 1
end
if (select databaseproperty(@DB, 'isbulkcopy')) = '1'
begin
print ' * recovery model is BULKLOGGED. *'
set @flag = @flag + 1
end
else
begin
print ' recovery model is full.'
end
-- Steve
June 25, 2004 at 11:13 am
I used same statement with one more ELSE after first IF:
if (select databaseproperty('cav2dev', 'istrunclog')) = '1'
begin
print ' * recovery model is SIMPLE. *'
--set @flag = @flag + 1
end
else if (select databaseproperty('cav2dev', 'isbulkcopy')) = '1'
begin
print ' * recovery model is BULKLOGGED. *'
--set @flag = @flag + 1
end
else
begin
print ' recovery model is full.'
end
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply