DBA Morning Check List

  • 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

  • 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.

  • 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

  • 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

  • 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

  • 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 "

  • 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

  • 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 🙂

  • 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 🙂

  • I found the article to be very interesting as well but I have a similar situation where I have several servers. I would like the names of some 3rd party vendors to automate this process.

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

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