Exclusive access could not be obtained because the database is in use

Well, after a few months of actually did some projects to complete at my deadline was successes, here I am back to blogging!

Actually This time i'm going to talk about the database restoring is not happening, it is because of the "database is in use".

Here's a common way of doing that:

1. Set the database in SINGLE_USER mode and forcibly terminate other connections:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

The above command will set the database in SINGLE_USER MODE and additionally the ROLLBABK IMMEDIATE termination option will roll back all the incomplete transactions. It will also disconnect any other connections to the database.

*Note: After issuing the above command, you will only be able to use a single connection to the database. So, if you are already using a query window connected to the target database, either restore the database by using the RESTORE T-SQL command or close the query window and restore the target database by using the Restore Database Wizard from SQL Server Management Studio.

2. Restore the database.

3. Set the database back to MULTI_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET MULTI_USER;

That's it! You have successfully restored the database!


And here's an alternative way (a little bit more complex):

1. If there are any SQL Server logins granted any access on the target database then disable them:

alter login [LOGIN_NAME] disable

*Note: Even though now you have disabled the login(s) there still might exist active connections to the database.

2. To find and terminate existing connections on the target database perform the following:

-- Query that returns all the SPIDs (process IDs)/sessions established to a given database:
SELECT spid,loginame,login_time,program_name
FROM [master]..sysprocesses
WHERE DBID=DB_ID('DATABASE_NAME')

By collecting the above SPIDs you get the sessions that must be forcibly terminated (be careful not to forcibly terminate your own session! :) By including the columns loginame, login_time and program_name in the above query, you are able to identify and exclude the spid that belongs to your session (that is, the DBA session).

You can terminate a session by executing the T-SQL Statement:

KILL @SPID

*Note 1: Replace @SPID with the appropriate SPID number.
*Note 2: You can even create a user-defined function undertaking this task.

3. Set the database in SINGLE_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET SINGLE_USER;

*Note: After issuing the above command, you will only be able to use a single connection to the database. So, if you are already using a query window connected to the target database, either
restore the database by using the RESTORE T-SQL command or close the query window and restore the target database by using the Restore Database Wizard from SQL Server Management Studio.

4. Restore the database.

5. Set the database back to MULTI_USER mode:

USE [master]
ALTER DATABASE [DATABASE_NAME]
SET MULTI_USER;


Remarks and Considerations
------------------------------
In the case of a RESTORE operation I would personally prefer the first method as it is simpler. With a single T-SQL Statement you set the database in SINGLE_USER MODE and also terminate all the active connections immediately and roll back all the incomplete transactions.

The second method is more preferable in cases where more "elegant" session control is required. This method allows the DBA to terminate sessions one-by-one explicitly instead of the first method which massively terminates all connections.

Choosing one of the two ways I guess that relies to the urgency of the RESTORE operation, the judgment of the DBA and also any Business Policies that might stand for such cases.

Because both the abovementioned methodologies have to do with forcibly terminating connections to a database in SQL Server, you must be extremely careful when using them as you might accidentally cause severe data loss when applying them inappropriately and without the necessary authorization from the Mansgement.

Source: http://aartemiou.blogspot.com/2009/03/exclusive-access-could-not-be-obtained.html

Deadlock in SQL Server

Deadlocks can be a pain to debug since they're so rare and unpredictable. The problem lies in repeating them in your dev environment. That's why it's crucial to have as much information about them from the production environment as possible.

Deadlocks occur when two (or more) processes are holding locks on resources and are waiting for locks on resources in such a way that they will never resolve.

Here I'm discussing a one of the way to monitor the Deadlocks.

How to Trace Deadlocks?
Get Info By ID

DBCC INPUTBUFFER(55)

Deadlocks can be traced by turning on two specific flags:

dbcc traceon (1204, 3605, -1)

go

dbcc tracestatus(-1)

Go

Deadlocks trace output can be examined in SQL Server log.

How to Avoid Deadlocks?

Here are some tips on how to avoid deadlocking on your SQL Server:

• Ensure the database design is properly normalized.
• Have the application access server objects in the same order each time.
• During transactions, don’t allow any user input. Collect it before the transaction begins.
• Avoid cursors.
• Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.
• Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
• If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
• Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
• If appropriate, use as low of an isolation level as possible for the user connection running the transaction.  
• Consider using bound connections.

Concatenation in SELECT and SPLIT functions with Transact SQL

Scalar UDF with variable concatenation in SELECT 

CREATE FUNCTION dbo.udf_select_concat ( @c INT )
    RETURNS VARCHAR(MAX) AS BEGIN
    DECLARE @p VARCHAR(MAX) ;
           SET @p = '' ;
        SELECT @p = @p + ProductName + ','
          FROM Products
         WHERE CategoryId = @c ;
    RETURN @p
    END

And, as for its usage:

    SELECT CategoryId, dbo.udf_select_concat( CategoryId )
      FROM Products
     GROUP BY CategoryId ;

SPLIT Function

CREATE FUNCTION [dbo].[Split]
(
@RowData NVARCHAR(MAX),
@Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE
(
ID INT IDENTITY(1,1),
Data NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Iterator INT
SET @Iterator = 1
DECLARE @FoundIndex INT
SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)
WHILE (@FoundIndex>0)
BEGIN
INSERT INTO @RtnValue (data)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))
SET @RowData = SUBSTRING(@RowData,
@FoundIndex + DATALENGTH(@Delimeter) / 2,
LEN(@RowData))
SET @Iterator = @Iterator + 1
SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
END

INSERT INTO @RtnValue (Data)
SELECT Data = LTRIM(RTRIM(@RowData))
RETURN
END

GO

Sources : http://balavardhanreddy.over-blog.com/15-categorie-10814096.html
               http://www.projectdmx.com/tsql/rowconcatenate.aspx

How to Restore SQL Server 2008 to SQL Server 2005

This is the only way I found we can downgrade database versions.

Creating Scripts to Move SQL Server 2008 Database to SQL Server 2005
These steps show you how to generate the necessary scripts.

• Use the scripting wizard in SQL Server 2008 to script data as well as schemas into SQL Server 2005 compatibility mode.
• Run "Generate SQL Server Scripts" wizard in SQL Server Management Studio (once Object Explorer is connected to the appropriate instance) by right clicking on database and selecting "Tasks –> Generate Scripts."
• Click "Script all objects in selected database" & then click "Next."
• Change script options: Specifically, set "Script for Server Version" to "SQL Server 2005" and set "Script Data" to "True". (SQL Server 2000 is also supported.) If you are putting the database on a new instance for the first time, make sure the "Script Database Create" option is set to "True." Click "Next" when you are happy with options.
• Finish the wizard.

After creating the file with these changes, you can run the script in SQL Server 2005 Management Studio to recreate the database in your development environment. You can now test data against SQL Server 2008 and SQL Server 2005.

Sources :

1.  http://www.nwds-ak.com/WebResources/Database/RestoreSQLServer2008to2005/CreatingSQLScriptstoRestore2008.aspx
2. http://www.sqlservermart.com/HowTo/MSSQL_Convert_2008_To_2005.aspx

How to get count with some filtering condition in select command using groups as well

count(case when IsNull(G.EmpCode,'X')='X' then null else 1 end) as Fullfilled
count(case when A.Selected = 'Y' then 1 else null end) as NoOfGaps


Eg:


SELECT A.EmpCode
,F.EMPLOYEENAME as EmployeeName
,E.DESCSHORT
,count(case when IsNull(G.EmpCode,'X')='X' then null else 1 end) as Fullfilled
,count(case when IsNull(G.EmpCode,'X')='X' then 1 else null end) as NotFullfilled
,count(case when A.Selected = 'Y' then 1 else null end) as NoOfGaps
,sum(case when A.Status = 'T' then G.TrainingHrs else 0 end) as NoOfHrs
,sum(case when A.Status = 'T' then G.TrainingFees else 0 end) as Costs
,count(case when A.Status = 'A' then 1 else null end) as Attending
from tb_Employee as A
left outer join HR_Competency D on A.CompetencyGroup = D.CompetencyGroup and A.Competency = D.Competency and D.CompanyCode = A.CompanyCode
left outer join HR_EmployeeMaster F on A.CompanyCode = F.CompanyCode and A.EmpCode = F.EmpCode
left outer join HR01_CODEREF E on F.JobGrade = E.Code and E.CodeType = 'JOB' and E.CompanyCode = A.CompanyCode
left outer join TR_EmployeeTraining G on A.CompanyCode = G.CompanyCode and A.EmpCode = G.EmpCode and A.TrainingYear = G.TrainingYear
and A.CompetencyGroup = G.CompetencyGroup and A.Competency = G.Competency and G.Status not in ('S', 'P', 'R', 'C', 'W','Z')

where A.CompanyCode = 'LSH' and rtrim(A.TrainingYear) = year(getdate())
and  A.EmpCode IN (SELECT  X.EMPCODE FROM HR_EMPLOYEEMASTER X, HR_PAYROLL Y WHERE X.COMPANYCODE = 'LSH'
AND X.COMPANYCODE = Y.COMPANYCODE AND X.EMPCODE = Y.EMPCODE AND Y.CPFSTATUS <> '4'  AND X.EMPCODE='IQ001' )
group by A.CompanyCode,A.EmpCode,F.EMPLOYEENAME,E.DESCSHORT

DB Backup, Restore, Shrink

Backup Database to Disk

USE TESTDB;
GO
BACKUP DATABASE TESTDB
TO DISK = 'D:\SQLServerBackups\TESTDB201101241156.Bak'
GO

Restore Database

----Make Database to single user Mode

ALTER DATABASE YourDB

SET SINGLE_USER WITH

ROLLBACK IMMEDIATE

----Restore Database

RESTORE DATABASE YourDB

FROM DISK = 'D:BackUpYourBaackUpFile.bak'

WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',

MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser

mode.

If error occurs please execute following command it will convert

database in multi user.*/

ALTER DATABASE YourDB SET MULTI_USER

GO

Shrinking a database and specifying a percentage of free space

DBCC SHRINKDATABASE (TESTDB, 10);
GO

Truncating a database

DBCC SHRINKDATABASE (TESTDB, TRUNCATEONLY);

Get all the object definitions: SPROC, TRIGGER, VIEW & FUNCTION

SELECT SchemaName=schema_name(schema_id),

       ObjectName=object_Name(m.object_ID),

       ObjectDefinition=definition

FROM   sys.SQL_Modules m

  INNER JOIN sys.objects o

    ON m.object_id=o.object_id

ORDER BY SchemaName, ObjectName

How to Change Schema for Tables, Views and Stored Procedures

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name

FROM sys.Objects o

INNER JOIN sys.Schemas s on o.schema_id = s.schema_id

WHERE s.Name = 'yourschema'    And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')

How to Change DB Owner for Tables , Views and Stored Procedures

declare @OldOwner varchar(100)

declare @NewOwner varchar(100)

set @OldOwner = 'ABC'

set @NewOwner = 'dbo'

--Tables--

select 'EXEC sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + ''' '

from information_schema.TABLES

where Table_schema = @OldOwner

--Views--

select 'EXEC sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + ''' '

from information_schema.VIEWS

where Table_schema = @OldOwner

--Stored Procedures--

select 'EXEC sp_changeobjectowner ''[' + @OldOwner + '].[' + obj.name + ']'', ''' + @NewOwner + ''' '

from Sys.objects obj

Inner join Sys.schemas sch ON obj.schema_id=sch.schema_id

where [type]='P' AND is_ms_shipped=0 AND sch.[name] = @OldOwner