There are couple of new DMV’s which came to my attention today and I would like to share them with you all.
sys.dm_os_windows_info: This DMV will get you OS related information of the Server.
Sample result is –
windows_release windows_service_pack_level windows_sku os_language_version 5.1 Service Pack 3 NULL 1033
sys.dm_server_registry: This DMV will get you information on registry keys which are used by the server.
Sample result is(I have trimmed the data) –
registry_key value_name value_data HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$DASANUP ObjectName LocalSystem HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$DASANUP ImagePath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\sqlservr.exe" -sDASANUP
sys.dm_server_memory_dumps : This DMV will get you information on dumps which are generated by the server.
Sample result is –
filename creation_time size_in_bytes C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0001.mdmp 2011-08-29 14:39:31.2129902 -07:00 6015847 C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0002.mdmp 2011-08-29 14:44:06.7232483 -07:00 6526667 C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0003.mdmp 2011-08-29 14:47:11.7133910 -07:00 6429912
sys.dm_server_services: This DMV will get you information on services and startup’s.
Sample result is –
servicename startup_type startup_type_desc status status_desc process_id last_startup_time service_account filename is_clustered cluster_nodename SQL Server (DASANUP) 3 Manual 4 Running 2252 2011-10-27 15:53:26.3520221 -07:00 LocalSystem "C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\sqlservr.exe" -sDASANUP N NULL SQL Server Agent (DASANUP) 2 Automatic 4 Running 4308 2011-10-27 15:54:01.0705499 -07:00 LocalSystem "C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\SQLAGENT.EXE" -i DASANUP N NULL
SQLSailor 