Finding the Port Number for a particular SQL Server Instance

DECLARE @tcp_port NVARCHAR(5)

EXEC xp_regread @rootkey = ‘HKEY_LOCAL_MACHINE’
 ,@key = ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’
 ,@value_name = ‘TcpPort’
 ,@value = @tcp_port OUTPUT

SELECT @tcp_port

Free ebook: Introducing Microsoft SQL Server 2012

Free ebook: Introducing Microsoft SQL Server 2012

http://blogs.msdn.com/b/microsoft_press/archive/2012/03/15/free-ebook-introducing-microsoft-sql-server-2012.aspx

SSIS Package in SQL Server Agent Job Fails

Error:

Argument "xyz" for option "connection" is not valid.  The command line parameters are invalid.  The step failed.

Solution:

·         If your SQL Server is 64 bit

·         In SQL Server JOB-->Properties-->Steps--->

·         In the Execution Option Tab -- Check "Use 32 bit runtime"

·         In the Data Sources Tab -- Uncheck the checkboxes of the connection managers if already checked.

·         Schedule the Job and run it.

Script to find DB_ID in SQL Server 2000

USE master

SELECT NAME
 ,DB_ID(NAME) AS DB_ID
FROM sysdatabases
ORDER BY dbid

T-SQL Script to load the trace file to a database table

T-SQL to load the trace file to a database table:

USE pubs
GO

SELECT *
INTO trace_table
FROM::fn_trace_gettable('c:\my_trace.trc', DEFAULT)

Script to remove SQL Server database from single-user mode.

Remove SQL Server database from single-user mode.

execute sp_dboption

1> exec sp_dboption 'testdb01', 'single user', 'FALSE';
2> go
Msg 5064, Level 16, State 1, Server REMIDIAN01, Line 1
Changes to the state or options of database 'testdb01' cannot be made at this
time. The database is in single-user mode, and a user is currently connected to
it.
Msg 5069, Level 16, State 1, Server REMIDIAN01, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.

Now I can use TSQL below to find all the database sesions connected to this database.

TSQL script to retrieve client sessions per database.

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'testdb01'
go

Kill the particular session(s) with the ;kill’ command.

1> kill 51
2> go
1>

Now I can ‘remove’ the database from Sinlge user mode.

1> exec sp_dboption 'testdb01', 'single user', 'FALSE'
2> go
1>

Reference:http://remidian.com/2008/01/remove-sql-server-database-from-single-user-mode/