Query to pull the users created in a database

SELECT
SU.Name AS UserName,
SU.createdate As createdate,
SR.Name AS RoleName
FROM sysUsers AS SU
INNER JOIN
sysUsers AS SR ON SU.GID = SR.UID WHERE
SU.GID <> SU.UID
ORDER BY RoleName, UserName

Enabling Service Broker on MSDB

Sometimes SQL Server jobs will be failed due to the following issue:

Msg 14650, Level 16, State 1, Procedure sp_send_dbmail, Line 72
Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery.

Solution
Enable Service Broker on MSDB …
To check Service Broker is enabled on MSDB, please execute the given query:
select is_broker_enabled,* from sys.databases where name = 'MSDB'

If the output value of “is_broker_enabled” is 0 -> Service Broker NOT enabled
Note: A value of 0 indicates that Service Broker message delivery is not activated in the MSDB database

If the output value of is_broker_enabled is 1 -> Service Broker IS enabled

To enable the Service Broker on MSDB database is as follows:

1) Connect to SQL Server
2) Stop the SQL Agent Services
3) Run the query "ALTER DATABASE MSDB SET ENABLE_BROKER"
4) Check the service broker is enabled or not by executing the below query:
4.1) select is_broker_enabled from sys.databases where name = 'MSDB'
4.2) The output value “1” means service broker is successfully enabled
5) Start the SQL Agent Services

Reason to stop SQL Agent services: Enabling Service Broker is not allowed when the database is in USE. Obviously SQL Agent will use MSDB database.

After the execution of the above, please exeute the below scripts to confirm that we are receving mails:

EXEC msdb.dbo.sp_send_dbmail
@recipients = user_name@mail.com',
@body = 'TEST.',
@subject = 'TEST' ;

SELECT @@SERVERNAME, it may return NULL / Wrong Server name

when you run SELECT @@SERVERNAME, it may return NULL / Wrong Server name.

Whenever you change the Network Name of Server Name, @@SERVERNAME does not report such changes. @@SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.

To confirm do the following:
SELECT @@SERVERNAME
select SERVERPROPERTY('MACHINENAME')
If the two above outputs shown different name means do the following:

To make SELECT @@SERVERNAME reflect the new changed Network Name perform the below task.

USE master
GO
EXEC sp_dropserver 'OLDSERVERNAME'
GO
EXEC SP_addserver 'NEWNETWORKNAME','LOCAL'
GO
Once done please restart the SQL Services and then Query SELECT @@SERVERNAME.

enabling DeadLoack trace on SQL Server

BEGIN
DBCC TRACEON (1204, 3605, -1)
WAITFOR TIME '23:30';
DBCC TRACEOFF (1204, 3605, -1)
END

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.