Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBA Morning Check List


DBA Morning Check List

Author
Message
chuck.beach
chuck.beach
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 27
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.[size] 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.[size] 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
alen teplitsky
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 4621
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
COOL_ICE
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 431
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
COOL_ICE
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 431
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 :-)
msilver
msilver
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 34
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search