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

 

 

Advertisements

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

 

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

 

 

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

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

 

 

SQL Server – Query/Command to check the nodes of SQL Cluster Instance

To check the current node/Active Node where the SQL Instance is running, you can execute the below query.

select serverproperty(‘ComputerNamePhysicalNetBIOS’)

1

 

To find out all the nodes of the SQL Cluster, you can use the below DMV.

select * from sys.dm_os_cluster_nodes

2

 

To find out if the instance is clustered, you can use below query, if it returns 1, then the instance is clustered

select ‘IsClustered’, serverproperty(‘IsClustered’)

3

 

SQL Server – How to attach database without LDF file

There can be a situation where you don’t have full database backup and you have corrupted your ldf file due to power shutdown (or some one intentionally deleted the ldf file). And you are left with only the data file (i.e mdf file).  You can still attach your mdf file with database and recover it.

Below is an example of how, you can attach a database without ldf file.

Database Name : Northwind

Data File:              C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\northwnd.mdf

Log File:                Not Available

 

Using SSMS : Attaching database without LDF File

  • Connect to the SQL server instance using SSMS
  • From the Object Explorer, right click the Databases node and a drop-down menu will be appeared.
  • Now click on the Attach tab.

 

2

 

 

  • Click on the Add button from appeared dialog box.

3

 

 

  • Now a Locate Database File, select it and click ok. Now you will see your mdf file is selected but SQL server is unable to select your transaction log file because transaction log file was deleted.

 

 

4

 

  • Now if you click OK, it will fail with the operating system error ( unable to open the physical file).

 

 

5

 

 

6

 

  • To attach .mdf file without transaction log file; select the log file and click on the remove button to remove it.
  • Now click on the OK button to attach the database file. SQL server will create a transaction log file for you while attaching the database.

 

7

 

 

 

9

 

 

Hope this was helpful !!!

ERROR!!! “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”

Issue:

Sometimes you may get into issues while connecting to SQL Server with below error message

1

“A network-related error or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

 

Solution:

There could be several reasons for you getting this error.  Below are few things you need to check to troubleshoot and resolve this issue.

1. Make sure the server you are trying to connect is up and running.  Ping the Server IP (where SQL is installed) from the client machine.

ping

 

 

2. If the Server is up, then make sure the SQL Server is up and running. To check that, go to SQL Server configuration manager (Start > All Programs > SQL Server 2005/2008/R2> Configuration Tools > SQL Server Configuration Manager)

Click on SQL Server Serivices on the left hand side and ensure the SQL Server (MSSQLSERVER) Service is running.

 

2

 

 

3. Check that the SQL Browser service is running. If you have installed a SQL Server named instance and not configured a specific TCP/IP port, incoming requests will be listening on a dynamic port. To resolve this you will need to have the SQL Browser service enabled and running.

 

3

 

4.  Check for TCP/IP and Named Pipes protocols and port. Open SQL Server Configuration Manager and check the SQL Server Network Configuration protocols. You should enable Named Pipes (if it is applicable to your environment) and TCP/IP protocol.

 

4

 

5.  For the TCP/IP protocol, right click and select properties to check the TCP/IP communication port as well. The default port is 1433, which can be changed for security purposes if needed.

 

5

 

6.  Try to telnet the SQL Server from Client machine with SQL port. Ensure that SQL port is open in Windows Firewall.

 

7

 

7. Make sure Remote connection is allowed in SQL Server. Open SSMS, connect to server, right-click server and select Properties. Click the Connections node and then Under Remote server connections, ensure Allow remote connections to this server is checked

 

6

 

 

Hope this was helpful !!!