SQL Server Management Studio - Enhancements in Object Explorer Details Window with MS SQL 2008
Microsoft is improving SQL Server with every new version and with every service pack.
Now with the MS SQL Server 2008 there exists many enhancements for sql server administrators as well as sql developers.
I want to share with you what I have realized for a short time ago. I mean the Object Explorer Details window for the Databases node of a SQL Server database instance in Object Explorer window.
Do you see the list about the properties of databases created on the related SQL Server.
By the way, this view is not limited for only MS SQL2008 instances, if you are using SQL2008 SSMS you will get the same list for a SQL Server 2005 database instance.
What is important and what makes this list valuable is that you can get such a list by opening the Properties screen of each database and collect data fragments from different tabs on the database properties screen.
I run the SQL Server Profiler while refreshing the window while the databases are listed and get a huge list of sql queries.
I have copied the sql scripts that are executed by the SSMS at the bottom of this article.
You can compare the improvement in Object Explorer Details window by comparing it with a MS SQL Server 2005 SQL Server Management Studio screen.
Below I have copied down a view from SSMS 2005 Object Explorer Details window.
There is only a list of database names when you click on the Databases node of the MS SQL Server instance on the Object Explorer window.
I believe the Object Explorer Detail screen was useless before the SSMS 2008 because why you should want to get the same list that you can easily see on the Object Explorer window by expanding the Databases node.
But after the enhancements in Microsoft SQL Server 2008 for the SQL Server Management Studio (SSMS), you might want to get a clear shot of the databases on a SQL Server instance. The Object Explorer Details window now grants a summary view of the existing sql databases created.
Another point that makes SSMS Object Explorer Detail window a simple tool for monitoring databases is you can add a numerous features and properties of each database and compare them in one management window.
Let's look at what this tool grants us for monitoring from a high level and take a snapshot of the SQL Server database instance.
|
Name Policy Health State Recovery Model Compatibility Level Collation Owner Date Created ID Last Backup Date Last Log Backup Date Size (MB) System Database Data Space Used (KB) Index Space Used (KB) Space Available ANSI NULL Default Case Sensitive Default File Group GUID Mail Host Mirroring Status Ownership Chaining Primary File Path Read Only ANSI NULL Enabled ANSI Padding Enabled ANSI Warnings Enabled Arithabort Enabled Auto Close Enabled Auto Create Statistics Enabled Auto Shrink Enabled Auto Update Statistics Enabled Broker Enabled Full Text Enabled Mirroring Enabled Quoted Identifiers Trustworthy Enabled |
I have set the databases node Object Explorer Details window to include the ID, Last Backup Date, Size, Space Available, Mirroring Status as well as the default Policy Health State, Recovery Model, Compatibility Level and Collation.
I use the ID of the database to set filter for the related sql database in the SQL Server Profiler.
Last backup dates are important if you need to keep your backup strategy live.
Size, space used and available space values are important criterias to keep under control. So I added them also.
The SQL Server Management Studio for MS SQL Server 2008 keeps these settings in your profile data. So when you open the Object Explorer Details window later, you will get the same design of sql databases list.
But I think there is a small bug in the SSMS window. When you add a new column the list resets itself and reloads but when you remove an item / a column from the list you have to refresh the list to get the desired view.
Any way, actually this is not a bug to talk about.
SQL Server Profiler Output
I traced the MS SQL Server Profiler output while the SSMS is loading the databases properties list into the Object Explorer Details window. And you can find below the queries executed by the SQL Server engine.
exec sp_executesql N'
SELECT
dtb.name AS [Database_Name],
''Server[@Name='' + quotename(CAST(
serverproperty(N''Servername'')
AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(dtb.name,'''''''') + '']'' AS [Database_Urn],
case
when DATABASEPROPERTY(dtb.name,''IsShutDown'') is null then 0x200
else 0
end |
case
when 1 = dtb.is_in_standby then 0x40
else 0
end |
case
when 1 = dtb.is_cleanly_shutdown then 0x80
else 0
end |
case dtb.state
when 1 then 0x2
when 2 then 0x8
when 3 then 0x4
when 4 then 0x10
when 5 then 0x100
when 6 then 0x20
else 1
end
AS [Database_Status],
dtb.compatibility_level AS [Database_CompatibilityLevel],
dtb.recovery_model AS [RecoveryModel],
dtb.user_access AS [UserAccess],
dtb.is_read_only AS [ReadOnly],
dtb.name AS [Database_DatabaseName2]
FROM
master.sys.databases AS dtb
WHERE
(CAST(case when dtb.name in (''master'',''model'',''msdb'',''tempdb'') then 1 else dtb.is_distributor end AS bit)=@_msparam_0 and CAST(isnull(dtb.source_database_id, 0) AS bit)=@_msparam_1)
ORDER BY
[Database_Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0'
select convert(float,low/1024.) from master.dbo.spt_values where number = 1 and type = 'E'
By the way, the below script is executed for each database.
use [AutoCompleteSampleDB]
SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
SUM(a.total_pages) AS [SpaceUsed],
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in (1, 3)) AS [LogSize]
FROM
sys.allocation_units AS a INNER JOIN sys.partitions AS p ON (a.type = 2 AND p.partition_id = a.container_id) OR (a.type IN (1,3) AND p.hobt_id = a.container_id)