Saturday, March 31, 2012

New DMV’s in SQL 2012

-- Windows Information

-- Returns one row that displays Windows operating system version information

select * from sys.dm_os_windows_info

windows_release

windows_service_pack_level

windows_sku

os_language_version

6.1

Service Pack 1

3

1033

---------------------------------------------------------------------------------------------------------------------------

/*

Returns information about the SQL Server, Full-Text, and SQL Server Agent services

in the current instance of SQL Server.

Use this dynamic management view to report status information about these services

*/

select * from sys.dm_server_services

 

servicename

startup_type

startup_type_desc

status

status_desc

process_id

last_startup_time

service_account

filename

is_clustered

cluster_nodename

SQL Server (SQL2012RTM)

2

Automatic

4

Running

1308

2012-04-01 09:07:42.5782389 +05:30

.\xyz

"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Binn\sqlservr.exe" -sSQL2012RTM

N

NULL

SQL Server Agent (SQL2012RTM)

2

Automatic

4

Running

5044

2012-04-01 09:08:25.6187145 +05:30

.\xyz

"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Binn\SQLAGENT.EXE" -i SQL2012RTM

N

NULL

SQL Full-text Filter Daemon Launcher (SQL2012RTM)

3

Manual

4

Running

5392

2012-04-01 09:08:53.9649644 +05:30

NT Service\MSSQLFDLauncher$SQL2012RTM

"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Binn\fdlauncher.exe" -s MSSQL11.SQL2012RTM

N

NULL

-----------------------------------------------------------------------------------------------------------------------------------------

/*

Returns configuration and installation information that is stored in the

Windows registry for the current instance of SQL Server. Returns one row per registry key.

Use this dynamic management view to return information such as the SQL Server services

that are available on the host machine or network configuration values for the instance of SQL Server.

*/

select * from sys.dm_server_registry

registry_key

value_name

value_data

HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$SQL2012RTM

ObjectName

.\xyz

HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$SQL2012RTM

ImagePath

"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Binn\sqlservr.exe" -sSQL2012RTM

HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$SQL2012RTM

Start

2

HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$SQL2012RTM

ObjectName

.\srinivasma

HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$SQL2012RTM

ImagePath

"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Binn\SQLAGENT.EXE" -i SQL2012RTM

HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$SQL2012RTM

Start

2

HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$SQL2012RTM

DependOnService

MSSQL$SQL2012RTM

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\CurrentVersion

CurrentVersion

11.0.2100.60

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\Parameters

SQLArg0

-dC:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\DATA\master.mdf

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\Parameters

SQLArg1

-eC:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Log\ERRORLOG

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\Parameters

SQLArg2

-lC:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\DATA\mastlog.ldf

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

TcpDynamicPorts

49517

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

DisplayName

TCP/IP

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Np

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Np

PipeName

\\.\pipe\MSSQL$SQL2012RTM\sql\query

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Np

DisplayName

Named Pipes

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Sm

Enabled

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Sm

DisplayName

Shared Memory

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Via

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Via

DefaultServerPort

0:1433

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Via

ListenInfo

0:1433

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Via

DisplayName

VIA

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp

Enabled

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp

ListenOnAllIPs

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp

KeepAlive

30000

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp

DisplayName

TCP/IP

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

IpAddress

fe80::947e:f986:d4a7:4b5c%11

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

IpAddress

169.254.75.92

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

IpAddress

fe80::50a9:44cc:af27:364d%10

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

IpAddress

169.254.54.77

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

IpAddress

::1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

IpAddress

127.0.0.1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

IpAddress

fe80::100:7f:fffe%12

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

TcpDynamicPorts

49516

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

DisplayName

Any IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\SQLServerAgent

ErrorLoggingLevel

3

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\SQLServerAgent

JobHistoryMaxRows

1000

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\SQLServerAgent

JobHistoryMaxRowsPerJob

100

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\SQLServerAgent

WorkingDirectory

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\JOBS

-------------------------------------------------------------------------------------------------------------------------------------------

/*

Returns information about the operating system volume (directory) on which the

specified databases and files are stored in SQL Server 2012. Use this dynamic management function

to check the attributes of the physical disk drive or return available free space information

about the directory.

*/

select database_id, f.file_id, volume_mount_point, cast(total_bytes/1024.00/1024.00 as int) Total_MB_Drive,

cast(available_bytes/1024.00/1024.00 as int) Available_MB_Drive

from sys.database_files as f

cross apply sys.dm_os_volume_stats(db_id(f.name), f.file_id);

 

database_id

file_id

volume_mount_point

Total_MB_Drive

Available_MB_Drive

1

1

C:\

455123

325097

------------------------------------------------------------------------------------------------------------------------------------

/*

Returns one row for each memory dump file generated by the SQL Server Database Engine.

Use this dynamic management view to troubleshoot potential issues.

The dump type may be a minidump, all-thread dump, or a full dump. The files have an extension of .mdmp

*/

 

select * from sys.dm_server_memory_dumps

 

 

file_name

creation_time

size_in_bytes

 

 

 

---------------------------------------------------------------------------------------------------------------------------

Posted by at No comments:
Subscribe to: Posts (Atom)