What Database Facts to Periodically Collect and Monitor? 2

[JohnBates]

hi experts,

Do you collect information about your SQL Server databases to later review so you can get ahead of any upcoming problems?

** I'd be interested in knowing what criteria you collect and watch. **

On a daily basis, I want to insert database sizes into a table so I can detect growth trends. Also the amount of free space on the database drive.

Do you collect and store performance stats? Anything else?

Thanks for your suggestions. John

 

[Mich]

There are hundreds, if not thousands, of counters you can monitor. You can do a lot outside of SQL with tools like System Monitor or within SQL with tools such as the supplied DMVs. There are books written on this subject. I highly suggest buying Microsoft's self paced training book called Optimizing and Maintaining a Database Administration Solution (exam 70-444). This book gives TONS of useful examples.

This is a question you'll get a hundred different opinions on. Instead of just throwing mine in, here is a quote from the book.

Deciding What to Monitor
Two schools of thought exist about what you should monitor. Some experienced
DBAs are selective about the counters and instances they recommend. Too much
information can be a disadvantage, and logging can use resources and generate large
files. Others, equally experienced, take the view that you should select the objects of
interest and capture all counters and instances of these objects. If you save the
information to a database, you can then use database queries or write your own
application to access whatever information you need. In the latter case, you need to
ensure you have enough disk space available and archive your data as needed. If the
overhead is unacceptable, you can reduce your time interval instead of reducing
the number of counters tracked. This philosophy argues that monitoring is an important
function (it certainly is) and if you are still having resource problems after you have
increased your time interval (say, to more than five minutes), you should buy more
hardware. If you agree with the latter philosophy, the objects you should monitor are
as follows:
? Memory
? Physical disk
? Process
? Processor
? Network interface
? SQLServer:Access Methods
? SQLServer:Buffer Manager
? SQLServeratabases
? SQLServer:General Statistics
? SQLServer:Latches
? SQLServer:Locks
? SQLServer:Memory Manager
? SQLServer:SQL Statistics
? SQLServer:User Settable
SQL Server 2005 Books Online recommends that you perform your monitoring on a
monitoring server rather than on the SQL Server 2005 production server that you
want to monitor. In practice, this philosophy is debatable. If the Performance tools
run on a remote system, Distributed Component Object Model (DCOM) calls have
(arguably) more impact on network resources than does logging all counters directly
on the production server.

-If it ain't broke, break it and make it better.

 

[mutley1]

John,

2000 or 2005? The below works on 2000 (not tested on 2005) and gives a history of the backup sizes since the date in the script. I'f you don't clear out the backup history, this is a measure of the data growth (i.e. backup doesn't obviously include white space).

If you let me know if it's 2000 or 2005 I have one that takes file sizes, % free / used and space in MB left on the drive where the file in question sits.

CREATE      procedure usp_dbbackupsize


AS
truncate table db_backupsize

INSERT INTO DB_backupsize
SELECT
@@servername as ServerName,
a.database_name,
--a.backup_size,
--convert (decimal(30,2), (a.backup_size + SUM(1536)) / 1024) As SizeInKB,
convert (decimal(30,2), (a.backup_size + SUM(1536)) / 1024 / 1024) As SizeInMBs,
convert (decimal(7,3), (a.backup_size + SUM(1536)) /1024 / 1024 / 1024) As SizeInGBs,
a.backup_finish_date
--b.physical_device_name
	    
	    
FROM (SELECT database_name, backup_finish_date
FROM msdb..backupset
--WHERE database_name = 'XYZ'
WHERE CAST(backup_finish_date as char(12)) >  {d '2005-12-31'}

AND media_set_id IN
(SELECT media_set_id FROM msdb..backupmediafamily
WHERE type = 'D')
GROUP BY database_name, backup_finish_date) z

JOIN

msdb..backupset a on z.database_name = a.database_name
and z.backup_finish_date = a.backup_finish_date

JOIN
msdb..backupmediafamily b on a.media_set_id = b.media_set_id

GROUP BY a.database_name, a.backup_finish_date, a.backup_size--, b.physical_device_name
ORDER BY a.database_name ASC

/*select * from db_backupsize



SELECT distinct database_name, count (*)
FROM msdb..backupset
GROUP BY database_name*/
--order by database_name
GO

 

[JohnBates]

Thanks mich and mutley1.

This is 2005 Standard Edition.

John