Archive
SQL Server – Finding TCP Port Number SQL Instance is Listening on
By default SQL Server listens on TCP port number 1433, and for named instances TCP port is dynamically configured. There are several options available to get the listening port for SQL Server Instance.
Here are a few methods which we can use to get this information.
Method 1: SQL Server Configuration Manager
Method 2: Windows Event Viewer
Method 3: SQL Server Error Logs
Method 4: sys.dm_exec_connections DMV
Method 5: Reading registry using xp_instance_regread
Let's see how you can use each of these methods in detail:
Method 1: SQL Server Configuration Manager:
Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager
Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <Instance Name>
Step 3. Right Click on TCP/IP and select Properties
Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group.
If SQL Server if configured to run on a static port it will be available in TCP Port textbox, and if it is configured on dynamic port then current port will be available in TCP Dynamic Ports textbox. Here my instance is listening on port number 61499.
Method 2: Windows Event Viewer:
When SQL Server is started it logs an event message as 'Server is listening on [ 'any' <ipv4> <port number>' in windows event logs. Here <port number> will be actual port number on which SQL Server is listening.
To view this using Event Viewer:
Step 1. Click Start > Administrative Tools > Event Viewer.
Note: If Administrative Tools are not available on Start menu, go to Start > Control Panel > System and Maintenance > Administrative Tools > View event logs
Step 2. Navigate to Event Viewer > Windows Logs > Application
Step 3. Since huge amount of event are logged, you need to use filtering to locate the required logs. Right click on Application and select Filter Current Log...
Step 4. You can filter the events by Event ID and Event source. The event we are interested in has Event ID of 26022, and it's source is SQL Server Instance. You need to filter by both Event ID and SQL Server Instance if you have multiple instances installed, for a single instance you can filter by Event ID only. Click on OK to apply the filter.
Step 5. Once the filter is applied, Locate message 'Server is listening on [ 'any' <ipv4> …'. As we can see from below screenshot that SQL Server Instance is running on TCP Port 61499.
Method 3: SQL Server Error Logs:
When SQL Server is started it also logs an message to SQL Server Error Logs. You can search for port number in SQL Server Error Logs by opening SQL Server Error Log in notepad or via T-SQL using extended stored procedure xp_ReadErrorLog as below:
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO
Result Set:
LogDate ProcessInfo Text
2013-03-21 13:34:40.610 spid18s Server is listening on [ 'any' <ipv4> 61499].
2013-03-21 13:34:40.610 spid18s Server is listening on [ 'any' <ipv6> 61499].
(2 row(s) affected)
As we can see from the output that SQL Server Instance is listening on 61499.
Note: This method does not work if SQL Server Error Logs have been cycled. See sp_Cycle_ErrorLog for more information.
Method 4: sys.dm_exec_connections DMV:
DMVs return server state that can be used to monitor SQL Server Instance. We can use sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:
SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
GO
Result Set:
local_tcp_port
61499
(1 row(s) affected)
As we can see from the output… same as above 
Method 5: Reading registry using xp_instance_regread:
Port number can also be retrieved from Windows Registry database.
We can use extended stored procedure xp_instance_regread to get port number information using below T-SQL code:
DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value = @portNumber OUTPUT
SELECT [Port Number] = @portNumber
GO
Result Set:
Port Number
61499
(1 row(s) affected)
As we can see … same as above
Note: The above code will only work if SQL Server is configured to use dynamic port number. If SQL Server is configured on a static port, we need to use @value_name = 'TcpPort' as opposed to @value_name = 'TcpDynamicPorts'.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – When will my backup finish?
The sys.dm_exec_requests is a great way to find out how long the BACKUP will take to complete. If you are doing a backup WITH STATS or by GUI, you will not need to use the DMV. But, if someone else is running a backup (or a Job), you can find the percent of backup completed and estimated completion time using this DMV. Another easy way is to ask the person who is taking the backup 
, but it’s not always possible.
The same thing happened to me recently (yesterday in fact!). I had to deploy a CR for an Application, and a Full database backup was to be taken in case a rollback is required. Now, if things were simple, I will take the backup and then proceed with CR deployment, but the database server is maintained by a different team about 4772 miles away! (managed by customer), and it’s not easy for people like me to keep staring at Outlook while waiting for backup completion notification from customer’s team.
When a backup is running, you can use the below query to check the progress, total_elapsed_time and estimated_completion_time returns milliseconds:
SELECT command, percent_complete,
'elapsed' = total_elapsed_time / 60000.0,
'remaining' = estimated_completion_time / 60000.0
FROM sys.dm_exec_requests
WHERE command like 'BACKUP%'
Result Set:
command percent_complete elapsed remaining
BACKUP DATABASE 50.75982 44.594500 41.207166
For a complete list of operation that reports percent_complete, check BOL for sys.dm_exec_requests.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – sys.dm_db_persisted_sku_features tells you about edition-specific features
sys.dm_db_persisted_sku_features lists all features which are utilized by the database. Features specific to Enterprise/Developer edition are:
- Compression,
- Partitioning,
- TDE and CDC
These features are available only on Enterprise/Developer editions of SQL Server. You cannot attach or restore databases utilizing these features to a "lower" edition, such as Standard or Express edition.
For example, You can check features utilized by your database using:
USE SqlAndMe
SELECT *
FROM sys.dm_db_persisted_sku_features
Result Set:
feature_name feature_id
Compression 100
Partitioning 200
(2 row(s) affected)
Since this database uses partition and compression it cannot be attached to an instance of "lower" edition,
If you try to attach database to a "lower" edition, you will run into below error:
Msg 909, Level 21, State 1, Line 1
Database 'SqlAndMe' cannot be started in this edition of SQL Server because part or all of object 'PageCompressionTest' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 905, Level 21, State 1, Line 1
Database 'SqlAndMe' cannot be started in this edition of SQL Server because it contains a partition function 'PartFunc01'. Only Enterprise edition of SQL Server supports partitioning.
Msg 933, Level 21, State 1, Line 1
Database 'SqlAndMe' cannot be started because some of the database functionality is not available in the current edition of SQL Server.
These error messages tell you exactly why the database cannot be started. 1st error is due to compression feature, and 2nd is for partitioning feature. Notice the last message, it says, "Database cannot be started.", the database does attach to server, but it cannot be started.
In case of restore also, you will receive same error messages, the database will be restored but, it cannot be started:
20 percent processed.
40 percent processed.
60 percent processed.
80 percent processed.
100 percent processed.
Processed 29976 pages for database 'SqlAndMe', file 'SqlAndMe' on file 1.
Processed 3 pages for database 'SqlAndMe', file 'SqlAndMe_log' on file 1.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database 'SqlAndMe'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 1
Database 'SqlAndMe' cannot be started in this edition of SQL Server because part or all of object 'PageCompressionTest' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 905, Level 21, State 1, Line 1
Database 'SqlAndMe' cannot be started in this edition of SQL Server because it contains a partition function 'PartFunc01'. Only Enterprise edition of SQL Server supports partitioning.
Msg 933, Level 21, State 1, Line 1
Database 'SqlAndMe' cannot be started because some of the database functionality is not available in the current edition of SQL Server.
In order to avoid this issue, it must be planned ahead. Before using any edition-specific features ask – "Will the database need to be moved between editions?"
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – New OS Related DMF – sys.dm_os_volume_stats
sys.dm_os_volume_stats is a new dynamic management function introduced in SQL Server 2008 R2 SP1 and Denali CTP3. It returns information about partitions on which database files reside.
You can now check for free space on a particular partition, instead of using xp_fixeddrives which does not support this.
sys.dm_os_volume_stats takes two parameters databae_id and file_id,
sys.dm_os_volums_stats(@Database_ID, @File_ID)
For example, you can use per-database view sys.database_files to get the File_ID, and pass the same:
SELECT File_ID, Name, Physical_Name
FROM sys.database_files
Result Set:
File_ID Name Physical_Name
———- ———– ———————
1 AdventureWorks2008R2_Data C:\…08R2_Data.mdf
2 AdventureWorks2008R2_Log C:\…08R2_Log.ldf
65537 FileStreamDocuments2008R2 C:\…ents2008R2
(3 row(s) affected)
You can pass this to sys.dm_os_volume_state to get information for a particular partition as follows:
SELECT STATS.Volume_Mount_Point AS 'Drive',
DB_NAME(STATS.Database_ID) AS 'Database Name',
FILES.[Name] AS 'File Name',
FILES.[Size] / 128 AS 'File Size',
((STATS.Total_Bytes / 1024) / 1024)
AS 'Drive Capacity (MB)',
((STATS.Available_Bytes / 1024) / 1024)
AS 'Drive Free Space (MB)'
FROM sys.database_files FILES
CROSS APPLY sys.dm_os_volume_stats
(DB_ID(N'AdventureWorks2008R2'), FILES.file_id) STATS
Result Set:
Drive Database Name File Name File Size Drive Capacity (MB) Drive Free Space (MB)
—— ————- ——— ———— ——————- ———————
C:\ AdventureWorks2008R2 AdventureWorks2008R2_Data 196 145456 57513
C:\ AdventureWorks2008R2 AdventureWorks2008R2_Log 18 145456 57513
C:\ AdventureWorks2008R2 FileStreamDocuments2008R2 0 145456 57513
(3 row(s) affected)
Above example returns the information for a single database, to get the information for all databases on the server, you need to apply sys.db_os_volume_stats to output from sys.master_files which is a system-wide view:
– For All Databases
SELECT STATS.Volume_Mount_Point AS 'Drive',
DB_NAME(STATS.Database_ID) AS 'Database Name',
FILES.[Name] AS 'File Name',
FILES.[Size] / 128 AS 'File Size',
((STATS.Total_Bytes / 1024) / 1024)
AS 'Drive Capacity (MB)',
((STATS.Available_Bytes / 1024) / 1024)
AS 'Drive Free Space (MB)'
FROM sys.master_files FILES
CROSS APPLY
sys.dm_os_volume_stats
(FILES.database_id, FILES.file_id) STATS
Partial Result Set:
Drive Database Name File Name File Size Drive Capacity (MB) Drive Free Space (MB)
—— ————- ———- ———– ——————- ———————
C:\ master master 4 145456 57201
C:\ master mastlog 0 145456 57201
C:\ model modeldev 2 145456 57201
C:\ model modellog 9 145456 57201
C:\ SqlAndMe SqlAndMe 3 145456 57201
C:\ SqlAndMe SqlAndMe_log 10 145456 57201
C:\ TempDatabase TempDatabase 6 145456 57201
C:\ PartitionDB PartitionDB 3 145456 57201
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Using SQL DMVs – sys.dm_db_task_space_usage
sys.dm_db_task_space_usage contains details for allocation/de-allocation pages for activity tasks for database.
It returns below columns:
1. session_id – session id
2. request_id – request id within the session, i.e. execution batch within a session
3. database_id – will always be 2, id of tempdb
4. user_objects_alloc_page_count – number pages allocated for the request
5. user_objects_dealloc_page_count – number of pages de-allocated for the request.
for other columns returned, refer BOL…
sys.dm_db_task_space_usage returns only active tasks:
– © 2011 – Vishal (http://SqlAndMe.com)
SELECT session_id, request_id,
user_objects_alloc_page_count,
user_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
Partial Result Set:
session_id request_id user_objects_alloc_page_count user_objects_dealloc_page_count
———- ———– —————————– ——————————-
1 0 0 0
2 0 0 0
3 0 0 0
4 0 0 0
5 0 0 0
6 0 0 0
7 0 1 0
8 0 0 0
When a query is executed which requires storage in tempdb, this DMV will return the number of pages allocated for the request.
For example, below query will require tempdb storage:
– Session ID = 56
USE AdventureWorks2008R2
SELECT A.*
INTO #TempTable
FROM HumanResources.Employee A
CROSS APPLY HumanResources.Employee B
CROSS APPLY HumanResources.Employee C
You can check sys.dm_db_task_space_usage while the above query is running to check the pages allocated to the request.
SELECT session_id, request_id,
user_objects_alloc_page_count,
user_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
WHERE session_id = 56
Result Set:
session_id request_id user_objects_alloc_page_count user_objects_dealloc_page_count
———- ———– —————————– ——————————-
56 0 8193 0
(1 row(s) affected)
Once the request is completed this row will no longer be available.
To check pages allocated to a session you can use sys.dm_db_session_space_usage.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Using SQL DMVs – sys.dm_db_session_space_usage
While you can use sys.dm_db_file_space_usage to get the free space available in tempdb. you can use sys.dm_db_session_space_usage to get the used space by each session.
sys.dm_db_session_space_usage contains following columns:
1. session_id – session_id
2. database_id – will always be 2, id of tempdb
3. user_objects_alloc_page_count – number of pages allocated/reserved by the session
4. user_objects_dealloc_page_count – number of pages that are de-allocated by user objects for the session
for other columns returned by sys.dm_db_session_space_usage, refer BOL…
sys.dm_db_session_space_usage returns each row per session as below:
– © 2011 – Vishal (http://SqlAndMe.com)
SELECT session_id, user_objects_alloc_page_count,
user_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage
Partial ResultSet:
session_id user_objects_alloc_page_count user_objects_dealloc_page_count
———- —————————– ——————————-
1 0 0
2 0 0
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0
8 0 0
Now, if I create a new object I should be able to see the same in the view:
USE AdventureWorks2008R2
SELECT *
INTO #TempTable
FROM HumanResources.Employee
SELECT @@spid
Result Set:
(290 row(s) affected)
——
52
(1 row(s) affected)
Now, if we check the DMV for session id = 52, we should be able to see the usage for that session:
SELECT session_id, user_objects_alloc_page_count,
user_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = 52
Result Set:
session_id user_objects_alloc_page_count user_objects_dealloc_page_count
———- —————————– ——————————-
52 99 90
(1 row(s) affected)
(1 row(s) affected)
After dropping the #TempTable the counts will change as below:
session_id user_objects_alloc_page_count user_objects_dealloc_page_count
———- —————————– ——————————-
52 99 99
(1 row(s) affected)
(1 row(s) affected)
You can see here that de-allocated page count changed from 90 to 99.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Using SQL DMVs – sys.dm_db_file_space_usage
You can use the sys.dm_db_file_space_usage dynamic management view to monitor the spaced used by tempdb for storing user/internal objects and version store.
sys.dm_db_file_space_usage has following columns:
1. database_id – will always be 2, database id of tempdb.
2. unallocated_extent_page_count – total number of free extents in the database file
3. version_store_reserved_page_count – pages allocated for version store
4. user_object_reserved_page_count – pages allocated for user objects, these are the permanent object you create explicitly in tempdb, system tables, temporary tables, table variables…
5. internal_object_reserved_page_count – pages allocated for internal objects
for details about other columns returned refer BOL.
Let’s see it in action:
– © 2011 – Vishal (http://SqlAndMe.com)
SELECT *
FROM sys.dm_db_file_space_usage
SELECT *
INTO #TempTable2
FROM AdventureWorks2008R2.HumanResources.Employee
SELECT *
FROM sys.dm_db_file_space_usage
Result Set:
database_id file_id unallocated_extent_page_count version_store_reserved_page_count user_object_reserved_page_count internal_object_reserved_page_count mixed_extent_page_count
———– ——- —————————– ——————————— ——————————- ———————————– ———————–
2 1 134832 0 64 0 136
(1 row(s) affected)
(290 row(s) affected)
database_id file_id unallocated_extent_page_count version_store_reserved_page_count user_object_reserved_page_count internal_object_reserved_page_count mixed_extent_page_count
———– ——- —————————– ——————————— ——————————- ———————————– ———————–
2 1 134824 0 72 0 136
(1 row(s) affected)
The user_object_reserved_page_count changed to 72 from 64 after creating a temporary table.
And you can also see that the free extents available decreased from 134832 to 134824.
If you need to check currently available free space in tempdb, you can use below query:
SELECT (unallocated_extent_page_count * 1.0) / 128
AS [FreeSpcae (MB)]
FROM sys.dm_db_file_space_usage
Result Set:
FreeSpcae (MB)
—————————————
1053.312500
(1 row(s) affected)
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Recently Executed Queries – Using DMVs/DMFs
To find the queries that were executed recently using the sys.dm_exec_query_stats DMV.
SELECT sql_handle, creation_time, last_execution_time
FROM sys.dm_exec_query_stats
WHERE last_execution_time > '2011-05-06 06:45:00.000'
The above statement will return the performance statistics for cached query plans. sys.dm_exec_query_stats contains the sql handle of the statement which was executed. To retrieve the actual query text, you need to use a DMF – sys.dm_exec_sql_text as follows:
sys.dm_exec_sql_text(sql_handle)
This functions takes the sql/plan hadle as input and returns the sql text. CROSS APPLY can be utilized add query text to the output:
SELECT SQLTEXT.text, STATS.last_execution_time
FROM sys.dm_exec_query_stats STATS
CROSS APPLY sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT
WHERE STATS.last_execution_time > '2011-05-06 19:15:00.000'
ORDER BY STATS.last_execution_time DESC
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
