July 21, 2009 at 2:38 pm
I just threw together a maintenance summary query for 2005 that I thought others might find useful.
It's by no means complete, but it's a good start.
Let me know if you have any suggestions/comments/questions on it:
declare @Server sysname;
select @Server = 'Dev';
;with
Files (DB, AllocatedSizeMB) as
(select dbid, sum(size) * 8192.0 / 1048576
from master.sys.sysaltfiles
group by dbid),
TraceFiles (FName) as
(select cast(value as nvarchar(256))
from fn_trace_getinfo(1)
where property = 2),
DataGrowths (DB, Qty, FirstGrow, LastGrow) as
(select DatabaseID,
count(*) as Events,
min(StartTime),
max(StartTime)
from TraceFiles
cross apply fn_trace_gettable(FName, default)
where eventclass = 92
group by DatabaseID),
LogGrowths (DB, Qty, FirstGrow, LastGrow) as
(select DatabaseID,
count(*) as Events,
min(StartTime),
max(StartTime)
from TraceFiles
cross apply fn_trace_gettable(FName, default)
where eventclass = 93
group by DatabaseID),
TraceStart (FirstTraceDate) as
(select min(StartTime)
from TraceFiles
cross apply fn_trace_gettable(FName, default)),
FullBackup (DBName, Latest, Row, Size) as
(select database_name,
backup_start_date,
row_number() over (partition by database_name order by backup_start_date desc) as Row,
backup_size/1024.0 as Size
from msdb.dbo.backupset
where type = 'D'
and is_copy_only = 0),
LogBackup (DBName, Latest, Row, Size) as
(select database_name,
backup_start_date,
row_number() over (partition by database_name order by backup_start_date desc) as Row,
backup_size/1024.0 as Size
from msdb.dbo.backupset
where type = 'L'
and is_copy_only = 0)
select
@Server as [Server],
name as [Database],
compatibility_level as Compatibility,
case is_auto_create_stats_on
when 0 then 'No'
else 'Yes'
end as AutoStats,
page_verify_option_desc as PageVerify,
AllocatedSizeMB,
FirstTraceDate,
DataGrowths.Qty as DataFileGrowEvents,
DataGrowths.FirstGrow as DataFileFirstGrow,
DataGrowths.LastGrow as DataFileLastGrow,
LogGrowths.Qty as LogFileGrowEvents,
LogGrowths.FirstGrow as LogFileFirstGrow,
LogGrowths.LastGrow as LogFileLastGrow,
recovery_model_desc as [Recovery],
FullBackup.Latest as LastFullBackup,
LogBackup.Latest as LastLogBackup
from sys.databases
left outer join Files
on databases.database_id = Files.DB
left outer join DataGrowths
on databases.database_id = DataGrowths.DB
left outer join LogGrowths
on databases.database_id = LogGrowths.DB
left outer join TraceStart
on 1 = 1
left outer join FullBackup
on databases.name = FullBackup.DBName
and FullBackup.Row = 1
left outer join LogBackup
on databases.name = LogBackup.DBName
and LogBackup.Row = 1;
Would be easy enough to add @@Version to it, or a variety of other things like that.
It uses the default 2005 trace for a lot of its functionality, so if that's not running, it won't do you much good.
I ran it to summarize stuff for a manager. Opened my eyes when I saw that one of the databases had 350 autogrow events in the last month and a half. Hadn't realized that one was set up incorrectly, but sure fixed it fast!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 21, 2009 at 2:53 pm
Looks very good - I like it. One small change you might consider:
Remove @Server variable - replace with @@servername global variable. That is, if what you want to show for the server name is the actual server name and not just a description.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 21, 2009 at 2:53 pm
Pretty good bit of summary info. I like it. Just sent it out to the other DBAs at work.
You need to add it to the scripts section so I can put it in my briefcase. 🙂
July 22, 2009 at 6:35 am
Jeffrey Williams (7/21/2009)
Looks very good - I like it. One small change you might consider:Remove @Server variable - replace with @@servername global variable. That is, if what you want to show for the server name is the actual server name and not just a description.
I was using that for "Dev", "Test", "Production1", "Production2", that kind of thing. More meaningful than "FL0018100D01289" (which is an actual server name here). But if server names are human-meaningful in your environment, then that would definitely be better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2009 at 11:22 am
Yeah, that's what I thought you were doing - and at least for my immediate manager reporting on the actual server name would be better. But, either way works just fine.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply