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 !!!!

 

 

SQL Server Setup Installation taking very long time

I had to install  a standalone instance of SQL Server 2012 [developers edition] on one of the machine. Usually the whole installation finishes within an hour or two.  I started the installation process after passing all the required parameters and switched to another work [multitasking 😉 ].  It wasn’t a high priority task, so I went for lunch also. When I came back, it was almost 2 hours since the setup installation was running. To my surprise it was still running [Status bar was showing it stuck at around 30% It was stuck at Install_SqlSupport_KatmaiRTM_Cpu64_Action:CreateShortcuts. Creating shortcut].  I went through the sql server setup logs to check if there is something useful pointing to the reason behind SQL Server taking forever to finish.  I checked event logs as well but to no avail.

 

 

I started my hunt for a solution to this problem on internet .

I am sharing the trick that solved the issue for me:  🙂

 

I cancelled the currently running installation setup.

Followed the below steps.

  • Open Internet Explorer
  • Go to Setting -> Internet Options
  • Open the “Advanced” Tab
  • Go to security sections below and uncheck the “Check for server certificate revocation” and “check for publisher certificate revocation options”
  • Click Apply and then OK
  • Close the browser and reboot the system.

After this when I re-initiated the Installation. It finished in normal time. 🙂

 

 

Hope it 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 update owner for all SQL Agent Jobs to SA

Problem:

I have many SQL Agent Jobs in my SQL Server and they are running under different accounts, I want to change the owner for all this jobs to SA in one go. I don’t want to manually change owner through ssms for each job.  Is there a script for this.

 

Solution:

Below is the SQL Script to update owner for all SQL Agent Jobs to SA.

First: Script to find out SQL Agent Job details (Name, Status and Owner)

https://exploresqlserver.wordpress.com/2016/11/15/script-to-check-list-of-sql-jobs-their-status-and-owner/

 

USE msdb

go

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


 

jobdetails1

 

Below is the script to generate job owner update commands for all SQL Agent Jobs to sa

Script:

SET nocount ON

SELECT ‘EXEC MSDB.dbo.sp_update_job ‘ + Char(13)
+ ‘@job_name = ‘ + Char(39) + j.[name] + Char(39)
+ ‘,’ + Char(13) + ‘@owner_login_name = ‘
+ Char(39) + ‘sa’ + Char(39) + Char(13) + Char(13)
FROM   msdb.dbo.sysjobs j
INNER JOIN master.dbo.syslogins l
ON j.owner_sid = l.sid
WHERE  l.[name] <> ‘sa’
ORDER  BY j.[name]

 

jobdetails2

Copy the generated script ,paste in New Query window and execute. This will change all SQL Jobs owner to SA.

 

jobdetails3

 

You can check Job owner details :

USE msdb

go

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

jobdetails4

 

 

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 !!!!