Month: August 2016

No comments

SQL Server – View Recovery Model using T-SQL

  By ,

Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

 

Use the script below to view the database recovery model.

 

SELECT​​ name​​ AS​​ [Database Name],

recovery_model_desc​​ AS​​ [Recovery Model]

FROM​​ sys.databases

GO

 

The following table summarizes the three recovery models.

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL SERVER – LCK_M_XXX – Wait Type

  By ,

Locking is a mechanism used by the SQL Server Database Engine to synchronize access by multiple users to the same piece of data, at the same time. In simpler words, it maintains the​​ integrity of data by protecting (or preventing) access to the database object.

 

LCK_M_BU
Occurs when a task is waiting to acquire a Bulk Update (BU) lock.

 

LCK_M_IS
Occurs when a task is waiting to acquire an Intent Shared (IS) lock.

 

LCK_M_IU
Occurs when a​​ task is waiting to acquire an Intent Update (IU) lock.

 

LCK_M_IX
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.

 

LCK_M_S
Occurs when a task is waiting to acquire a Shared lock.

 

LCK_M_SCH_M
Occurs when a task is waiting to acquire a Schema Modify lock.

 

LCK_M_SCH_S
Occurs when a task is waiting to acquire a Schema Share lock.

 

LCK_M_SIU
Occurs when a task is waiting to acquire a Shared With Intent Update lock.

 

LCK_M_SIX
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock.

 

LCK_M_U
Occurs when a task is waiting to acquire an Update lock.

 

LCK_M_UIX
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock.

 

LCK_M_X
Occurs when a task is waiting to acquire an Exclusive lock.

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
2 Comments

SQL Server – Resolve Error 5030 to Rename Database

  By ,

When you want to rename your database and you hit the error below, you need to set the database to​​ Single User Mode. After you rename your database, then you set the database back to Multi-User mode.​​

Msg 5030, Level 16, State 2, Line 1

The database could not be​​ exclusively locked to perform the operation

Follow the steps​​ below to rename your database.​

  1. Set the database to single-mode
ALTER DATABASE OLD_DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

2. Rename the Database

ALTER DATABASE OLD_DBName MODIFY NAME = NEW_DBNAME;

3. Set the database to Multi-user mode

ALTER DATABASE NEW_DBNAME SET MULTI_USER WITH ROLLBACK IMMEDIATE;

You may check out this blog post where I show a better way to rename a database.

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Change Database Owner

  By , ,

Below is the T-SQL script to change DB owner.​​ 

 

EXEC​​ sp_changedbowner​​ 'owner_name';

 

No comments

SQL Server – Enabling Service Broker

  By ,

Service Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions between instances. The basic functions of sending and receiving messages​​ forms a part of a “conversation”. Each conversation is considered to be a complete channel of communication. Each Service Broker conversation is considered to be a dialog where two participants are involved.

 

--Enable Service Broker

ALTER​​ DATABASE​​ DB_Name​​ SET​​ ENABLE_BROKER;

 

--Verify status of Service Broker. A status of 1 means that it is enabled (0 means it is disabled)

 

SELECT​​ is_broker_enabled​​ 

FROM​​ sys.databases

WHERE​​ name​​ =​​ 'DB_Name'