Tag Archives: T-SQL Script

Different ways to check the SQL Server Instance Port number

Problem: If there are multiple SQL instances running on the same computer, it is difficult to identify the instance port number. You can use the below solution to find the instance specific port numbers.

Solution: You can check the list of port number used by the SQL Server instances using one of the below way.

Soln 1# Using SQL Server Configuration Manager

  • Go to SQL Server Configuration Manager
  • Select Protocols for SQL2005/2008 under SQL server Network Configuration
  • Right click on TCP/IP and select Properties
  • Select the IP Addresses-tab
  • In the section IP ALL, you can see the ports

Soln 2#From Registry Values
SQL Server 2005
Type the regedit command in Run window and check the below registry values.HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL.#\ MSSQLServer\ SuperSocketNetLib\TCP\IPAll

SQL Server 2008
Default instance

Named instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll

Soln 3# Error Log
Query the error log as below to get the port number.

EXEC xp_readerrorlog 0,1,”Server is listening on”,Null

Soln 4# Command Prompts
Execute the below command from the command prompt.

Netstat -abn

Basics of Replication Troubleshooting

There are many scenarios where you have been alerted for the replication failure and you have to troubleshoot the issue. In this article I will guide you what should be your approach to get the detailed error message and transaction details in replication.

First check the replication monitor and click on the failed publisher. Next step is double click on the failed subscriber from All Subscriptions list.

Now next step is click on the error and check its description.
Error :
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0000044100002D93000100000000, Command ID: 1)

From the above error message we have to identify which command is failed to execute on the subscriber.

To get the exact command, find out the distributer server and distribution database for the failed publisher.

Once you get the distribution database server, execute the below query against the distribution DB.

use distribution
SELECT * FROM msrepl_commands
WHERE xact_seqno = 0x0000044100002D93000100000000
AND command_id = 1

Once you execute the above query against the distribution database, you will get the more information about the error, for example Publisher database ID, Article ID and much more…

We have to use the above details, to get the exact command using either SP_BROWSEREPLCMDS (If CLR is enabled) or you can cast the command column in msrepl_commands table.

We will check both the alternatives.

Please note CLR must be enabled for to use this procedure.

@xact_seqno_start = '0x0000044100002D930001',
@xact_seqno_end = '0x0000044100002D930001',
@publisher_database_id = 1033,
@article_id = 12,
@command_id= 1

By casting command column in msrepl_commands table

Please note if you want to see the better output use the Result to Text as output in SSMS (CTRL + T)

FROM msrepl_commands
WHERE xact_seqno = 0x0000044100002D930001
AND command_id = 1

Now you got the exact SQL Command. As a next step check the objects from both the publisher and the subscriber to see the violation of the keys or do the data comparisons etc.

Script to Monitor the Running Job Status

Think of environment where multiple jobs are running at the same time and you want to monitor the current status of each job. One option is to use the job activity monitor.


And another way is you can use the below system stored procedures by running it against the MSDB database.


exec msdb..sp_get_composite_job_info

exec msdb.dbo.sp_help_job
@execution_status = 1


You can check the job execution status parameter values as below.

0 = Not idle or suspended

1 = Executing

2 = Waiting For Thread

3 = Between Retries

4 = Idle

5 = Suspended

6 = Waiting For Step To Finish

7 = Performing Completion Actions


Different ways to monitor Log Shipping for SQL Server databases

Log shipping has been an option for creating a fail over server for SQL Server for quite some time. In this tip, we look at different ways that you can monitor the status of your log shipped databases.


Steps to Attach a SQL Server database without transaction log file

Problem: There could be situation where you missed the database transaction log file(.LDF) and you have only data file (.MDF). You can attach the database using below solution.

Solution: In the below script I have created the database,dropped its log file and created the database with the .mdf file.

--created database with .mdf and .ldf file
( NAME = N'singleFileDemo', FILENAME = N'L:\singleFileDemo.mdf' , SIZE = 2048KB , FILEGROWTH = 10240KB )
( NAME = N'singleFileDemo_log', FILENAME = N'F:\singleFileDemo_log.ldf' , SIZE = 1024KB , FILEGROWTH = 5120KB )

--inserting data into database
use singleFileDemo
create table tb1 (name varchar(10))

--inserting records
insert into tb1 values('Jugal')
go 10;

--deleting the log file
--detaching the database file
USE [master]
EXEC master.dbo.sp_detach_db @dbname = N'singleFileDemo'

-- now next step is delete the file manually or you can do it from command prompt
EXEC xp_cmdshell 'del F:\singleFileDemo_log.ldf'

-- script to attach the database 
USE [master]
CREATE DATABASE [singleFileDemo] ON 
( FILENAME = N'L:\singleFileDemo.mdf' )

When you will execute the CREATE DATABASE FOR Attach script you will get the below warning message.

File activation failure. The physical file name "F:\singleFileDemo_log.ldf" may be incorrect.
New log file 'F:\singleFileDemo_log.LDF' was created.

Once the database is ready execute the DBCC CHECKDB for any error.