Archive

Archive for the ‘Management Views and Functions’ Category

SQL Server – Kill all sessions using database

November 25, 2013 2 comments

Before an existing database can be restored, there should be connections using the database in question. If the database is currently in use the RESTORE command fails with below error:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

To avoid this, we need to kill all sessions using the database. All sessions using the database can be queries using system stored procedure sp_who2 or using sys.dm_exec_sessions DMV:


SELECT   session_id
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = 'SqlAndMe'

You need to terminate each of the sessions returned individually by using KILL command.

If there are large number of sessions to kill, or you need to do this on a routine basis it gets boring to do it this way. You can *automate* this using below script, which takes database name as input, and kills all sessions connecting to it.


-- Kill all sessions using a database
-- Vishal - http://SqlAndMe.com

USE [master]
GO

DECLARE @dbName SYSNAME
DECLARE @sqlCmd VARCHAR(MAX)

SET @sqlCmd = ''
SET @dbName = 'SqlAndMe' -- Change database name here

SELECT   @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) +
         CHAR(13)
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = @dbName

PRINT @sqlCmd

--Uncomment below line to kill
--EXEC (@sqlCmd)

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 – Finding TCP Port Number SQL Instance is Listening on

May 1, 2013 5 comments

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

image

Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group.

image

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…

image

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.

image

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.

image

 


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 Smile

 


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 Smile Smile

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?

November 18, 2011 5 comments

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 – Error Msg 102, Level 15, State 1, Line x when using sys.dm_db_index_physical_stats

November 14, 2011 2 comments

You run into this error when using sys.dm_db_index_physical_stats DMV on a database which is running under SQL Server 2000 (80) compatibility mode.

USE [SqlAndMe]

GO

 

SELECT      *

FROM        sys.dm_db_index_physical_stats

            (DB_ID('SqlAndMe'),

            OBJECT_ID('dbo.ProductList'),

            1,

            NULL,

            'SAMPLED')

GO

Result Set:

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near ‘SqlAndMe’.

There is nothing wrong with the syntax at all, yet you might spend time scratching your head!!! DMVs do not support a function as a parameter when compatibility mode is set to SQL Server 2000 (80). You can check the current compatibility mode using sys.databases:

USE [master]

GO

 

SELECT      name, compatibility_level

FROM        sys.databases

WHERE       name = 'SqlAndMe'

GO

Result Set:

name          compatibility_level

SqlAndMe      80

To use sys.dm_db_index_physical_stats without changing the database compatibility mode you can try one of the below solutions:

Solution 1: Run the statement in the context of a different database which has compatibility mode of SQL Server 2005 (90) or higher:

USE [master]

GO

 

SELECT      name, compatibility_level

FROM        sys.databases

WHERE       name = 'master'

GO

 

SELECT      *

FROM        sys.dm_db_index_physical_stats

            (DB_ID('SqlAndMe'),

            OBJECT_ID('SqlAndMe.dbo.ProductList'),

            1,

            NULL,

            'SAMPLED')

GO

Since the compatibility mode of [master] is set to SQL Server 2008 (100) this will work fine. We need to use three-part name for object here as we are running the statement in the context of different database.

Result Set:

name          compatibility_level

master        100

 

database_id object_id   index_id    partition_number

6           1906105831  1           1               

Solution 2: Remove functions calls from arguments of sys.dm_db_index_physical_stats. Pass constants or variables instead. This solution does not require context switching and can be done from the context of the database itself:

USE [SqlAndMe]

GO

 

DECLARE @dbid VARCHAR(20)

DECLARE @objid VARCHAR(20)

 

SET @dbid   = DB_ID('SqlAndMe')

SET @objid  = OBJECT_ID('dbo.ProductList')

 

SELECT      *

FROM        sys.dm_db_index_physical_stats

            (@dbid, @objid, 1, NULL, 'SAMPLED')

GO

Result Set:

database_id object_id   index_id    partition_number

6           1906105831  1           1               

 

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

August 22, 2011 Leave a comment

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

August 1, 2011 Leave a comment

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

SQL Server – Server-related dynamic management views in SQL Server 2008 SP1 and “Denali”

July 29, 2011 3 comments

There are three new server-related DMVs introduced in SQL Server 2008 R2 Service Pack 1 and SQL Server Codename "Denali".

 

1. sys.dm_server_services:

- For SQL Server 2008 R2 SP1, it contains information about SQL Server and SQL Server Agent services for the current instance.

- For "Denali", it returns information about SQL Server, Full-Text Search and SQL Server Agent services for the current instance. Let’s use below query on both:

SELECT servicename          AS [Name],

       startup_type_desc    AS [Type],

       status_desc          AS [Status],

       process_id           AS [ProcessID],

       service_account      AS [Startup Account]

FROM   sys.dm_server_services

Result Set for SQL Server 2008 R2 SP1:

Name                           Type       Status         ProcessID   Startup Account

————————–     ——-    ——         ———   —————

SQL Server (MSSQLSERVER)       Automatic  Running        2492        .\sqladmin

SQL Server Agent (MSSQLSERVER) Manual     Stopped        NULL        .\sqladmin

 

(2 row(s) affected)

Result Set for "Denali":

Name                       Type    Status   ProcessID  Startup Account

————————– ——  ——   ———  —————

SQL Server (DENALI)        Manual  Running  5176       .\sqladmin

SQL Server Agent (DENALI)  Manual  Stopped  NULL       .\sqladmin

SQL Server Agent (DENALI)  Manual  Stopped  NULL       .\sqladmin

 

(3 row(s) affected)

Ah!, for "Denali" it’s returning two entries for SQL Server Agent, and not returning the Full-Text Search service, (it is installed), a possible "Denali" bug (Anyone else got the same issue?).

 

2. sys.dm_server_registry:

- For both SQL Server 2008 R2 SP1 and "Denali" – this view returns configuration and installation information, such as host machine or network configuration for the current instance, i.e. information we used to fetch using xp_instance_regred for an instance, Let’s execute below query on both:

SELECT registry_key, value_name, value_data

FROM   sys.dm_server_registry

WHERE  value_name = N'CurrentVersion'

Result Set for SQL Server 2008 R2 SP1:

registry_key                                    value_name    value_data

——————————-                 ———–   ————

HKLM\Software\…\MSSQLServer\CurrentVersion   CurrentVersion       10.50.2500.0

 

(1 row(s) affected)

Result Set for "Denali":

registry_key                                    value_name    value_data

——————————-                 ———–   ————

HKLM\Software\…\MSSQLServer\CurrentVersion    CurrentVersion       11.0.1440.19

 

(1 row(s) affected)

 

3. sys.dm_server_memory_dumps:

This view returns a list of memory dump files generated by the SQL Server Database Engine. It returns below information:

i) filename – File name and physical path of the memory dump file,

ii) creation_time – date and time the memory dump file creation,

iii) size_in_bytes – memory dump file size in bytes.

 

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

Follow

Get every new post delivered to your Inbox.

Join 279 other followers