Metadata Collection - SQL Server Integration Services Design Patterns - page 22

Tip The sys.dm_os_performance_counters DMV is very useful for data-
base monitoring and contains much more information than just data and log file sizes.
You can easily modify the preceding code to include additional performance counters.
However, you should be aware that there are three types of cntr_type values (value/
base, per second, and point-in-time), and the preceding code only works for the point-
in-time counter type ( cntr_type = 65792 ). Refer to SQL Server Books Online for
more information on the types of information available in this DMV and how to work
with each counter type.

Now that you understand the expected output of the query, you need a table to store
the results. From within SSMS, execute the T-SQL statement in Listing 1-6 within the
dbaCentralLogging database.

Listing 1-6 . Example of T-SQL Code to Create a Table to Store Data and Log File Size
Information

USE dbaCentralLogging;
GO

CREATE TABLE dbo.dba_monitor_databaseGrowth
(

log_id INT IDENTITY(1,1)
, captureDate DATETIME
, serverName NVARCHAR(128)
, databaseName SYSNAME
, dataSizeInKB BIGINT
, logSizeInKB BIGINT

CONSTRAINT PK_dba_monitor_databaseGrowth
PRIMARY KEY NONCLUSTERED(log_id)

);

CREATE CLUSTERED INDEX CIX_dba_monitor_databaseGrowth
ON
dbo.dba_monitor_databaseGrowth(captureDate,serverName,databaseName);