Category: SQL Server

  • Extended Stored Procedure xp_msver

    xp_msver returns information about the SQL Server version, actual build number of the server and information about the server environment.

    You can also pass the parameter to get the specific information.

  • Script to Enable/Disable Database for Replication

    You can enable the database for replication using below script.

    use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'true'
go

    If you have restore the database on test environment and you are getting the error that “Database is part of Replication”, you can clear/disable it by executing below query.

    use master
exec sp_replicationdboption @dbname = 'sqldbpool',
@optname = 'publish',
@value = 'false'
go

  • Dedicated Administrator Connection

    DAC: Dedicated Administrator Connection feature is available from the SQL Server 2005. It is available in all the higher editions by default except express edition. DAC will be useful when SQL Server is not responding any connections; in such kind of situation DBA will connect through the DAC and troubleshoot/fix the issue.

    You can execute below kind of command for the initial troubleshooting.

    -- Locking Info
SELECT * FROM sys.dm_tran_locks
GO
-- Running Sessions
SELECT * FROM sys.dm_exec_sessions
GO
-- Requests Status
SELECT * FROM sys.dm_exec_requests
GO
--Open Sessions
SP_WHO2
--To get the SQL Text
DBCC OPENTRAN (SPID)
--To terminate the curlprit process
KILL SPID

    DAC is disabled by default, it is a good practice to enable the DAC. You can enable the DAC using by executing below query.

    Use master
GO
--0 = Allow Local Connection,
--1 = Allow Remote Connections*/
sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

    You can connect using DAC on of the following method.
    Command Prompt
    SQL Server Management Studio

    Using Command Prompt: Use SQLCMD utility to connect to SQL Server as below.
    -A argument is to specify the DAC connection.
    -S argument is to specify the server name.
    -d argument is to specify the database name.
    -E argument is for windows connection with integrated security true

    Using Management Studio: Write ADMIN: before the server name in management studio connection window. It will give you the DAC connection.

    To enable the DAC connection in SQL Server express edition add ;-T7806 trace flag as startup parameter.

    Go into configuration manager — right click on SQL Server Service and select properties — go into advanced tab and add the trace flag ;-T7806. Once done restart the SQL Server Services.

  • Common cause of the performance issues

    Below are the most common reasons for the performance issues.

    • SQL Server Configuration Issues
    • Database/table/schema Design and Configuration Issues
    • CPU/IO/Memory Bottleneck
    • Blocking
    • Network Bottleneck
    • Poor Indexing Strategy (Missing Index, Fragmented Index, Un-Used Index)
    • Out-of-date/missing statistics
    • T-SQL Code
    • Application Code

  • Script to find out Heap Table/Table without clustered Index

    Heap Table: Table without a clustered index is called Heap Table. You can find out the Heap Table by querying the sys.indexes or sys.partitions against the index_id column.

    select OBJECT_NAME(object_id),* 
from sys.indexes where index_id = 0

Select OBJECT_NAME(object_id),*  
from sys.partitions where index_id = 0