Different Methods to Know COMPATIBILITY LEVEL of a Database

There are many methods to know the compatibility levels of a database.

1) Use system stored procedure sp_helpdb
   
EXEC sp_helpdb TEST

How to Change Compatibility of Database

There are two different ways how we can change the compatibility of database to SQL Server.

1) Using Management Studio

For this method first to go database and right click over it. Now select properties.

2) Using T-SQL Script.

You can execute following script and change the compatibility settings to 120.

USE [master] GO
ALTER DATABASE [TEST] SET COMPATIBILITY_LEVEL = 120
GO

Ms SQL Useful Stored Procedures

DBCC CHECKDB ('DB_Name')
DBCC CHECKALLOC ('DB_Name')
DBCC updateusage ('DB_Name')
DBCC CHECKIDENT ('dbo.tablename',reseed,0) 
- '0' by default,Seed Value Last Incremented value can be added

EXEC SP_help

EXEC SP_helptext

EXEC sp_renamedb 'oldname' , 'newname';

xp_fixeddrives  - To get HD available in the Server / Disk Space

To Get List of Jobs running in SQL Agent Job

How to find the SQL Agent Jobs  running in your DB Instance and its Steps, query used for the results

 SELECT J.NAME AS 'JOB NAME',

S.STEP_ID AS 'STEP',

S.STEP_NAME AS 'STEP NAME',

S.COMMAND AS 'QUERY',

DATABASE_NAME AS 'DATABASE'

FROM MSDB.DBO.SYSJOBS J

INNER JOIN MSDB.DBO.SYSJOBSTEPS S ON S.JOB_ID = J.JOB_ID

WHERE J.ENABLED = 1

ORDER BY J.NAME,S.STEP_ID

 

How to Enable Automatic Statistic Update on Database

After putting good indexes and optimizing the query, I could not achieve an optimized performance and I still received an unoptimized response from the SQL Server. On examination, I figured out that the culprit was statistics. The database that I was trying to optimize had auto update of the statistics was disabled.

Once I enabled the auto update of statistics, the database started to respond as expected. If you ever face situation like this, please do the following:

1) Enable Auto Creation of Statistics

2) Enable Auto Update of Statistics

3) Update Statistics for whole database

Display All IP address used in a LAN

A single line of a command and its arguments that can help display all computer IP addresses (those that are being used) on a LAN,

arp -a

ipconfig /all

net view /all

nmap -sn 192.168.0.0/24 (replacing the sub-net with the appropriate one for your LAN)

MS SQL Server Main Replication Models

Replication Publisher and Subscriber :

To understand how SQL replication works, we’ll have to define two replication terms – Publisher and Subscriber. In a SQL replication the Publisher is the part that offers the data for distribution. The Subscriber is the part that consumes (gets updates) the data from the Publisher.