DBA Morning Check List

Question

DBA Morning Check List

Viewing 10 posts - 46 through 54 (of 54 total)

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

Steve Vassallo SSCommitted Points: 1635 More actions · Answer 1

Great article and something i need to put in place in my environment. We are using Idera SQL Safe to perform the backups so I know at a moments notice if backups or trans logs backups fail. I do need to get a monitoring tool for SQL diags. Anyone have any recommendations? Idera's is good but wondering if there are any other alternatives out there. That would make my checklist even easier with a good monitoring tool

amarG Valued Member Points: 50 More actions · Answer 2

Of all the morning checkings most stress full is "Space Checking"

we are monitoring over 50 production server..

so space checking of each server is tough..

carrying it in a week .. dividing in set to be carried in a day..

is a good idea. //it will give u a stats to analyse and estimate the risk,

there should be some automation for space checking.

Divine Flame SSCoach Points: 15943 More actions · Answer 3

Hi,

I think most of the DBAs are already having this kind of everyday checklist. The point is, we need the automation of it. Its literaly annoying to do these tasks manually on more than 50 servers everyday. So if you have some idea about automating the same, that will be great 🙂

Anyways, nice article 😉

Sujeet Singh

Bill Richards-377350 SSChasing Mays Points: 660 More actions · Answer 4

Thank you for taking time to read my article. I appreciate the insight of each poster. I posted my initial comments on 4/14/2008. However, let me reiterate that I wrote this article to challenge those who do not yet have a checklist to develop one. To the shops that do have a checklist, I desired to offer my ideas to improve their shops even more. I also appreciate your comments, so that I may improve my process.

Thanks,

Bill Richards, MCSE, MCDBA

Senior Database Analyst

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

looking on writing a report on #1, disk space

can someone point me to some sample code to do the following. grab the database info on a server. the size of the db, the free space, all db files and free space on them. SSMS has reports for it, but i was looking for the code to write my own consolidated version for all our servers and databases

chuck.beach Grasshopper Points: 14 More actions · Answer 6

Here is some queries extracted from an Access program to collect stats on SQL data bases

FOR SQL 7:

SQL = "SELECT DB.name AS DBname, DB.crdate as DBCrDate, DB.Status as Status,DB.Status2 as Status2,DB.dbid," & _

"AF. AS DBsize,ISNULL(SUSER_SNAME(DB.sid), '') AS Owner_FullNetworkID " & _

"FROM master.dbo.sysaltfiles AF RIGHT OUTER JOIN master.dbo.sysdatabases DB ON AF.dbid = DB.dbid " & _

"WHERE AF.groupid = 1"

FOR SQL 2000:

SQL = "SELECT DB.name AS DBname, DB.crdate as DBCrDate, DB.Status as Status,DB.Status as Status2,DB.dbid," & _

"AF. AS DBsize,ISNULL(SUSER_SNAME(DB.sid), '') AS Owner_FullNetworkID " & _

"FROM master.dbo.sysaltfiles AF RIGHT OUTER JOIN master.dbo.sysdatabases DB ON AF.dbid = DB.dbid " & _

"WHERE AF.groupid = 1"

For SQL 2005

SQL = "SELECT DB.name AS DBname, DB.status as Status, DB.status2 as Status2, DB.crdate AS DBCrDate,DB.dbid," & _

"AF.size AS DBsize,ISNULL(SUSER_SNAME(DB.sid), '') AS Owner_FullNetworkID " & _

"FROM msdb.sys.sysdatabases AS DB " & _

" LEFT OUTER JOIN " & _

" (SELECT * FROM msdb.sys.sysaltfiles WHERE groupid=1) AS AF " & _

" ON DB.dbid = AF.dbid "

For SQL 2008:

SQL = "SELECT DB.name AS DBname, DB.status as Status, DB.status2 as Status2, DB.crdate AS DBCrDate,DB.dbid," & _

"AF.size AS DBsize,ISNULL(SUSER_SNAME(DB.sid), '') AS Owner_FullNetworkID " & _

"FROM msdb.sys.sysdatabases AS DB " & _

" LEFT OUTER JOIN " & _

" (SELECT * FROM msdb.sys.sysaltfiles WHERE groupid=1) AS AF " & _

" ON DB.dbid = AF.dbid "

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 7

thx

this is what i have so far from what you pointed me to

select @@servername, a.database_id as Database_ID ,a.name as Database_Name, b.name as FileName, substring(b.physical_name, 1,1) as Drive_Letter, b.physical_name as Path,
b.size as File_Size, b.max_size, b.growth, a.create_date,
a.recovery_model_desc, a.page_verify_option_desc
from sys.databases a
inner join sys.master_files b
on a.database_id = b.database_id

my perfmon query is

declare @dayofthemonth int;
declare @month int;
declare @hour int;
declare @minute int;
set @dayofthemonth = (select DATEPART(day, getdate()));
set @month = (select DATEPART(month, getdate()));
set @hour = (select DATEPART(hour, getdate()));
set @minute = (select DATEPART(minute, getdate()));
with free_hd_space_cte
as
(
select a.counterid, substring(a.machinename,3,20) as machinename, a.objectname, a.countername, a.instancename, convert(int, b.countervalue) as CounterValue, max(convert(datetime, substring(b.counterdatetime,1, 16))) as TimeRead
from counterdetails a inner join counterdata b
on a.counterid = b.counterid
where b.counterid in (select CounterID
from counterdetails
where objectname = 'logicaldisk'
and countername in ('Free Megabytes')
and instancename != '_Total')
and convert(datetime, substring(b.counterdatetime,1, 16)) > getdate() -1
group by a.machinename, a.instancename, a.counterid, a.objectname, a.countername,b.countervalue, b.counterdatetime
--order by a.machinename, a.instancename, a.counterid
)
select distinct MachineName, ObjectName, CounterName, InstanceName, Countervalue, TimeRead
from free_hd_space_cte
where datepart(day, timeread) = @dayofthemonth
and datepart(month, timeread) = @month
and datepart(hour, timeread) = @hour
and datepart(minute, timeread) between 0 and 9
and countervalue < 30
--and (select countername from free_hd_space_cte where countervalue < 30 and countername = '% Free Space')
group by MachineName, ObjectName, CounterName, InstanceName, CounterValue, timeread
--having datepart(minute, timeread) between 0 and 9
order by machinename, countername, countervalue asc, instancename

plan is to dump the result of the first querry into a database every day and then change the second to join on that table to return more data. since a lot of our database files stay big with a lot of white space in them i don't want to check them when a drive is low on space. i want a single report with free space on each drive, the database files on there and the free space in each one

next is to find where SQL stores the data to see how much free space is in each file

found it, going to look in sp_spaceused and take the code from there to use for this report

COOL_ICE SSCommitted Points: 1706 More actions · Answer 8

Out of my experience I have couple of more things that can be added to monitoring checklist.

Backups

It is very important to check integrity of the backup files. Once in a while try to restore .bak files on some dummy database just to confirm the backed up databases are good enough to restore in times emergencies

Disk Space

Keep track on size of database and how fast is it growing. These records can be used for future analysis of capacity planning

- SAMJI
If you marry one they will fight with you, If you marry 2 they will fight for you 🙂

COOL_ICE SSCommitted Points: 1706 More actions · Answer 9