Database Health

Question

Database Health

Viewing 6 posts - 16 through 20 (of 20 total)

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

Steve,

I've sent you pretty much the same, but if anyone needs any more detail or assistance please feel free to drop me a line or post a reply here-

i'll be able to answer short ish questions.

MVDBA

mnorton SSC Veteran Points: 258 More actions · Answer 2

Hey Michael,

Thanks so much. I can't wait to unzip the files and look into how you performed your polling. I appreciate this site and people like you who share how you tackled an issue. It helps people like me who haven't been DBA's for too long. Thanks again.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

Whoa- hold on there don't get too excited - it's fairly simple stuff!!

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

For reference I'll post some of the procs I created previously - as and when i get time - i'd welcome suggestions from others.....

here is one to check log sizes and fill% (in case anyone left a database in the wrong recovery mode or t-log backups/SQLAgent are disabled)

CREATE procedure [dbo].[usp_logspace] as

--author:michael vessey

--create date:13-jul-2007

--version:1.00

--description:execute DBCC sqlperflogspace query at each server

--change history:

set nocount on

set transaction isolation level read committed

DECLARE @strsql NVARCHAR(1500)

DECLARE @servername varchar(200)

declare @rc int

IF NOT EXISTS (SELECT NAME FROM sysobjects were NAME='tbl_logspace')

BEGIN

CREATE TABLE tbl_logspace (servername VARCHAR(100) NULL ,connectionstatus VARCHAR(100) NULL,dbname VARCHAR(100) NULL,logsize_mb VARCHAR(100) NULL,logused_percent varchar(100) null, statuscode varchar(100) null)

END

ELSE

BEGIN

TRUNCATE TABLE tbl_logspace

end

DECLARE curs1 CURSOR FOR

SELECT servername FROM tblservers WHERE active=1

OPEN curs1

FETCH NEXT FROM curs1 INTO @servername

WHILE @@FETCH_STATUS=0

BEGIN

insert into tbl_logspace exec @rc=Rotator2 @servername+'MASTER','exec DBCC SQLPERF(LOGSPACE)'

if @rc=0 --failure

begin

insert into tbl_logspace (servername,status) select @servername,'FAILED TO CONNECT'

end

FETCH NEXT FROM curs1 INTO @servername

END

CLOSE curs1

DEALLOCATE curs1

GO

--

MVDBA

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 5

It's old and needs to be updated, but for the DBA, I have a few articles on this topic:

http://www.sqlservercentral.com/articles/Monitoring/20010422115752/84/

http://www.sqlservercentral.com/articles/Administering/20010422115754/85/

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

LAST backup time

SELECT d.NAME,MAX(b.backup_finish_date) FROM

MASTER.dbo.sysdatabases d LEFT OUTER join

msdb.dbo.backupset b ON d.NAME=database_name AND b.server_name=@@servername

WHERE backup_finish_date>'01 oct 2008' AND TYPE='D'

GROUP BY d.name

MVDBA