How to enable service broker on a database

Problem:

My production database has service broker enabled. When I refreshed my development database from production database backup, my development application started giving below error

“Exception while starting message processor. System.InvalidOperationException: The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications.”

Solution:

When you restore a database from backup, the service broker is disabled by default.

To enable the server broker on the server use the below command.

 

Command:

SELECT is_broker_enabled FROM sys.databases WHERE name = ‘[DATABASE NAME]’;

ALTER DATABASE [DATABASE NAME] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

 

Note: replace [DATABASE NAME] with your database name.

 

Every database has a unique ID used by Service Broker. This ID must be unique across all databases in a SQL instance. If you already have another database with the same unique ID used by service broker then you can create a new unique ID for service broker with below command.

 

ALTER DATABASE [DATABASE NAME] SET NEW_BROKER

 

Hope this was helpful !!!!

 

 

How to find out SQL Version and Edition

There are many ways to find out SQL Server Version and Edition. I am sharing some of the most common ones.

 

1. SQL Commands

SELECT @@VERSION

Another Script:

SELECT SERVERPROPERTY (‘Edition’) as Edition,
SERVERPROPERTY(‘ProductLevel’) as SQLServicePackLevel,
SERVERPROPERTY(‘ProductVersion’) as SQLVersion

 

 

2. Using GUI – SSMS ->Object Explorer

Open SSMS and connect to SQL Instance, Right click sql instance and select properties.

here in the properties windows you can find out the SQL Version and Edition along with a lot of other useful details as well.

 

 

You can also find out the SQL Version (along with SP) when you connect NEW QUERY (in SSMS)

In the status bar below in New Query window (it show the SQL Version and Product Level)

 

 

SQL Server Version List

this list is taken from https://sqlserverbuilds.blogspot.ae/. This is one of the best places to refer for latest SQL releases and version details.

 

Hope this was helpful. !!!

 

 

 

 

How to find last start time of SQL Server

There are many ways to check when sql server was last started. Below are two of the options.

 

1. Using DMV

select sqlserver_start_time, * from sys.dm_os_sys_info

 

2. Using Server Dashboard Report

Connect to SQL Instance in Object explorer of SSMS. Right Click SQL Instance Select Reports -> Standard Reports -> Server Dashboard

 

 

Hope this was helpful !!!

 

Script to Check List of SQL Jobs, their Status and owner

Problem:   SQL Script to get list of SQL Jobs running under SQL Agent, their Status and Job Owner Details

 

Solution:   Below is the script.

 

SQL Script:

_______________________________________

USE msdb

SELECT j.[name] AS ‘JobName’,
Enabled = CASE
WHEN j.enabled = 0 THEN ‘No’
ELSE ‘Yes’
END,
l.[name] AS ‘OwnerName’
FROM msdb.dbo.sysjobs j
INNER JOIN master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]

go

_______________________________________

 

ScreenShot:

 

jobdetails

SQL Server – Some commonly used DMV/DMFs by most of the DBAs

There are more than 150 DMV/DMFs spread across 20 some categories in 2012, and no one remember all these by heart.  Below are some commonly used DMV/DMFs by most the DBAs.

DMV /DMFs

— Execution related DMV and DMF

— Current Connections (User Processes only)
SELECT * FROM sys.dm_exec_connections

— Active Sessions
SELECT * FROM sys.dm_exec_sessions

— Currently executing requests
SELECT * FROM sys.dm_exec_requests

— Very Important: Query Statistics
SELECT * FROM sys.dm_exec_query_stats

— Cached Plans
SELECT * FROM sys.dm_exec_cached_plans

— Very Important: Query Optimizer details
SELECT * FROM sys.dm_exec_query_optimizer_info

— Very Important: Query Plan, This is DMF
SELECT TOP 1 * FROM sys.dm_exec_query_stats T1
CROSS APPLY sys.dm_exec_query_plan (T1.plan_handle )

— Very Important: SQL Statement, this is DMF
SELECT TOP 1 * FROM sys.dm_exec_query_stats T1
CROSS APPLY sys.dm_exec_SQL_text (T1.SQL_handle )

— Index related Dynamic Management Objects
— Index Usability
SELECT * FROM sys.dm_db_index_usage_stats

— Missing Indexes
SELECT * FROM sys.dm_db_missing_index_details

— Physical Stats, this is DMF, requires DBID, ObjectID, IndexID, PartitionID and Mode
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(‘TESTDB‘), OBJECT_ID(‘EMPLOYEE‘), NULL, NULL , NULL) WHERE avg_fragmentation_in_percent >30

— SQL Server Operating system related Dynamic Management Objects

— Wait Stats
SELECT * FROM sys.dm_os_wait_stats

— Scheduler
SELECT * FROM sys.dm_os_schedulers

— Waiting/ Suspended Tasks
SELECT * FROM sys.dm_os_waiting_tasks WHERE Session_ID > 50

— Performance Counters
SELECT * FROM sys.dm_os_performance_counters

— Cluster Nodes Details
SELECT * FROM sys.dm_os_cluster_nodes

 

 

SQL Server – How to display line number in Query Editor- SSMS

Displaying line numbers in Query Editor is very useful, when you are working with large queries/module. As it is really annoying to find line number when a syntax error occurs [line numbers in Query Editor is not enabled].

How to enable or disable the display of line numbers in SSMS Query Editor

1. Open SQL Server Management Studio. Go to Tools > Options

1

2. In the Options dialog box on the left side panel expand the Text Editor option and expand Transact-SQL. Next select General as shown in the snippet below.

2.1

3. Tick the Line numbers checkbox and click OK.

2

 

4. Now, when a query window is opened Line Numbers will be displayed as below.

3

 

Hope this is helpful !!!!

SQL Server- Move TempDB to new location

Why and How to move TempDB to new location

Why:

There are mainly two reasons why you would like to move TempDB to a new location:

1. TempDB has grown big and existing drive doesn’t have enough disk space.

2. Moving TempDB to a new disk which is faster to improve the performance.

 

How:  Follow the steps to move TempDB to new location

First of all, check out the current location of TempDB files by executing the below query.

use tempdb

go

exec sp_helpfile

 

1

 

This will provide you the current location of tempdb.

now to move the tempdb to new location, execute the below command

 
USE master
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = ‘C:\Tempdb\tempdb.mdf‘)
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = ‘C:\Tempdb\templog.ldf‘)
GO

 

2

 

I am moving tempdb from (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\) to (C:\Tempdb\) as i have only one drive in my machine. You can change it accordingly.

To make changes come in effect, restart SQL Service.

 

3

After restarting the sql services, check the location of tempdb now by running the below query.

use tempdb

go

exec sp_helpfile

4

 

 

Tempdb has been moved to a new location.

 

Hope this was helpful !!!!!

ERROR !! SQL Server Configuration Manager Cannot Connect to WMI Provider

Problem:

Error Message – “Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with the SQL Server Configuration Manager. Invalid namespace[0x80041010]” when trying to launch SQL Server Configuration Manager.

 

1

 

Cause:

This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.

 

Solution:

To work around this problem, open a command prompt, type the following command, and then press ENTER:

Command:

mofcomp “%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof”

Note: For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

2

3

 

 

Hope this was helpful !!!!

How to backup Table in SQL

Taking backup of SQL Server table is possible in SQL Server. Below are some options to backup a table in SQL Server.

1. Generate Table Script with data
2. Make a copy of table using SELECT INTO
3. Export Data using SSIS to any destination

 

Method 1 : Generate Table Script with data

1 2 3 4 5 6

 

 

Method 2: Make a copy of table using SELECT INTO

Script:

 

7

SELECT * INTO DataBase.dbo.BackUpTable FROM SourceDataBase.dbo.SourceTable

 

Method 3: Export Data using SSIS to any destination [IMPORT / EXPORT Wizard]

The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available. The list of available providers includes the following data sources:

  • SQL Server
  • Flat files
  • Microsoft Office Access®
  • Microsoft Office Excel®

 

 

 

 

 

SQL Server – Restrict snapshot generation to only newly added article to SQL Server Replication

Problem:

I have Transactional Replication configured and when I add new article into the replication and generate new snapshot, it gets generated for all the articles.  As generating snapshot for all the articles is a waste of time and resources [Disk, CPU etc], I want to generate snapshot only for newly added articles.

Solution:

This is a common request to add a new article to a publication.  To save resources we will walk through how to only create the snapshot for the new article(s).

Assuming Transactional Replication is already configured.

 

The publication has 4 objects/tables to be published.

1

Now, we are adding a new object [BillOFMaterials(Production)] to the publication.  When we add new article to the publication, the publication becomes invalid and we need to create a new snapshot.

 

 

2

When we start the snapshot, it generates snapshot for all 5 articles in the publication.

 

 

 

3

 

 

To achieve what we want, we need to disable options [allow_anonymous and immediate_sync].

Run the below commands on your publisher database for your publication to make the changes.

4

 

 

–Run on your publisher database
EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘allow_anonymous’ ,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘your publication name’,
@property = ‘immediate_sync’ ,
@value = ‘false’
GO

 

Now, lets add another article [BusinessEntityAddress[Person] to the publication and generate a new snapshot.

 

5

 

This time it gets generated for only the newly added article.

 

6

 

7

 

 

Hope it was helpful !!!!