Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

DBA Morning Check List Expand / Collapse
Author
Message
Posted Monday, August 30, 2010 11:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 22, 2011 1:45 PM
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 "


Post #977487
Posted Monday, August 30, 2010 1:04 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #977537
Posted Tuesday, August 31, 2010 1:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 6:10 AM
Points: 350, Visits: 429
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
Post #977726
Posted Tuesday, August 31, 2010 1:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 6:10 AM
Points: 350, Visits: 429
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
Post #977736
Posted Tuesday, August 31, 2010 11:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 09, 2013 12:32 PM
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.

Post #978244
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse