Category Archives: SQL Server

Make SQL Server DB Read Only/Read-Write Only

5 Replies

Read Only
You can add the database in readonly mode by using below query. You can not add, update or delete any records once database is in read only mode.

ALTER DATABASE SQLDBPOOL set READ_ONLY
GO

READ_WRITE
You can set the database again in to Read/Write mode using below query.

ALTER DATABASE SQLDBPOOL SET READ_WRITE WITH NO_WAIT
GO

MSQL_XP Wait type

3 Replies

MSQL_XP
This kind of wait type occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends. In simple word SQL Server lost the control of extended stored procedure.

Few days back LiteSpeed backup job are running longer than normal, when I checked the process status using Activity Monitor, it shows processes are running but in MSQL_XIP wait state.

Activity Monitor

I tried to KILL the backup sessions and it gone into KILLED\RollBack state forever.

You can either reboot the server to free/terminate all the session or there is another way that you can find the SQL Server Backup Process associated Operating System Id.

You can follow the below steps to find out SPID related KPID

Step 1: Execute SP_WHO2 active command, you will SPID related KPID which is OS Process ID.
OR
Step 1: Check for DMVs to get the OS Process ID

Step 2: To get the more information regarding the OS Process download the Process Explorer from the below link.

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

Step 3: Once confirmed you can kill OS process using Task Manager.

Error: The INSERT statement conflicted with the FOREIGN KEY constraint

Leave a reply

Error Description
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint “foreignKey_child”. The conflict occurred in database “jugal_1”, table “dbo.parent”, column ‘parentid’.
The statement has been terminated.

Solution: Error message is pretty clear, it means the value you are inserting into child table is not exist in the parent table. There could be number of reason for i.e value not exist in parnet table, you have added value in the table but yet it is not commited, you are updating the foreign key column.

Script to reproduce issue
create table parent
(
parentid int constraint primaryKey_parent primary key,
name varchar(10)
)

create table child
(
childid int constraint foreignKey_child foreign key references parent(parentid),
name varchar(10)
)

insert into child values(10,’jugal’)