Chris_Garrahy@msn.com.au (1) [Avatar] Offline
#1
The latest MEAP is excellent. A small point - listing 9.2 only works for the default instance of SQL Server. The following will work for any instance:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT isnull(cast(SERVERPROPERTY('InstanceName') as varchar(12smilie), '') AS [Instance_name]
, instance_name as [Database_name]
, counter_name AS [File_type]
, cntr_value / 1024.0 AS [Size(MB)]
FROM sys.dm_os_performance_counters
WHERE object_name =
CASE isnull(cast(SERVERPROPERTY('InstanceName') as varchar(12smilie), '')
WHEN '' THEN 'SQLServersmilieatabases'
ELSE 'MSSQL$' + cast(SERVERPROPERTY('InstanceName') as varchar(12smilie) + 'smilieatabases'
END
AND counter_name IN (
'Data File(s) Size (KB)'
, 'Log File(s) Size (KB)'
, 'Log File(s) Used Size (KB)')
ORDER BY Database_name, counter_name
ian.stirk (27) [Avatar] Offline
#2
Re: Listing 9.2
Hi Chris,

Thanks for your feedback, and modified code.

You are right of course.

The dilemma I have is in balancing the additional benefits these factors (e.g. server instance) bring, to their accompanying complexity. In the interests of clarity, I’ve tried to keep to as simple a system as possible. That said, I will give it some more thought.

Glad you’re enjoying the MEAP!

Thanks again
Ian