to find the SQL Port nunber by t-sql from registries

DECLARE @InstanceName nvarchar(50)
DECLARE @value VARCHAR(100)
DECLARE @RegKey_InstanceName nvarchar(500)
DECLARE @RegKey nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER'))

--For SQL Server 2000
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8
BEGIN
if @InstanceName='MSSQLSERVER'
Begin
SET @RegKey='SOFTWARE\Microsoft\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\'
END
ELSE
BEGIN
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\'
END

EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @RegKey,
@value_name = 'TcpPort',
@value = @value OUTPUT

Select @@SERVERNAME as ServerName,@value as PortNumber
END


--For SQL Server 2005 and up
if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'


EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @RegKey_InstanceName,
@value_name = @InstanceName,
@value = @value OUTPUT
SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP\IPAll'

EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @RegKey,
@value_name = 'TcpPort',
@value = @value OUTPUT

Select @@SERVERNAME as ServerName,@value as PortNumber
END

Query for knowing SQL Server Uptime.

SET NOCOUNT ON

DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)

SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60

IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0

SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))

ELSE

SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60

PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name like N'%Agent%')

BEGIN

PRINT 'SQL Server is running but SQL Server Agent <> running'

END

ELSE BEGIN

PRINT 'SQL Server and SQL Server Agent both are running'

END

Enabling page level locking

Sometimes index rebuild task will be failed due to the following issue:

Description: Executing the query "ALTER INDEX [IDX_XXX_OF_DATE] ON [dbo].[DI..." failed with the following error: "The index "IDX_XXX_OF_DATE" (partition 1) on table "XXXX_OF_CF" cannot be reorganized because page level locking is disabled.".

Solution

Enable page level locking…

To find the disabled page level locking indexes, please execute the given query:

SELECT * FROM sys.indexes WHERE allow_page_locks = 0

Copy the index names from the above output and enable locking by executing the below query:

Alter Index <index name> On <table name> Set (ALLOW_PAGE_LOCKS = ON)

If we want to do the same for multiple indexes in single database at a time, please execute the following:

Use <dbname>

GO

select 'alter index ['+s.[name]+'] on ['+t.[name]+'] Set (ALLOW_PAGE_LOCKS = ON);' from sys.indexes as s inner join sysobjects as t on s.[object_id] = t.[id] where s.[allow_page_locks] = 0 and is_unique = 0

Copy the given OUTPUT and paste it into new NEW QUERY WINDOW and execute it.

Check for disabled indexes and re-run the job.

TSQL - Code to find used columns in database objects in MS SQL Server

Sometimes, you find that you have columns that are not being used. The data no longer is collected or relevant. In this case, you need to remove some columns from your database. Please use the following code to find used columns in database objects.

SELECT TOP 100 PERCENT
so1.name AS ObjectName,
CASE so1.type
WHEN 'P' THEN 'Procedure'
WHEN 'V' THEN 'View'
WHEN 'FN' THEN 'Function'
WHEN 'U' THEN 'Table'
WHEN 'TR' THEN 'Trigger'
WHEN 'IF' THEN 'Inline Function'
WHEN 'TF' THEN 'Table-Valed Function'
ELSE so1.type
END AS ObjectType,
DB_NAME() AS DatabaseName,
USER_NAME(so.uid) AS OwnerName,
so.name AS TableName,
c.name AS FieldName
FROM
sysobjects so,
sysobjects so1,
sysdepends d,
syscolumns c
WHERE
so1.xtype IN ('P','FN','IF','TF','V','TR') --('P', 'V', 'FN', 'TR')
AND d.id = so1.id
AND d.depid = so.id
AND so.id = c.id
AND d.depnumber = c.colid
ORDER BY
so.name
-----------------------------
Your comments are always accpeted.

Thanks,
Satish Kumar
+91 9966 710 244
Hyderabad.