Classic issue – a database has (for whatever reason) been set to single-user and the one session available is being used . . .But now you need to free that session and/or make the database multi-user again.

Trying to free the session using the database properties in the SSMS GUI won’t work, you don’t have access to that session. Make life easier by making use of T-SQL.

First, run SP_Who. This will produce a list all the active connections to all databases.  Note the session ID of the active connection to the database you’re interested in.

Second, run the  KILL <<SESSION ID>> to kill the connection made by that pesky user.

The session for the database is now free, another user can access the database.

If you wish to put the database  in multi-user mode (so more than one person can access the database at a time), run the following

ALTER DATABASE <<DATABASE NAME>>
SET MULTI_USER

The following will roll back and transactions which the user may have made immediately.

ALTER DATABASE <<DATABASE NAME>>
WITH ROLLBACK IMMEDIATE 
SET MULTI_USER

To put the database in to single user mode replace the MULTI_USER with SINGLE_USER.

 

Yes you could probably use the import/output wizard  that comes with MS SQL but if all you want to do is import a simple CSV file quickly into T-SQL, why not just use an easy bit of T-SQL.

USE <<DATABASE_NAME>>
BULK
INSERT <<TABLE_NAME>>
FROM 'FULL_FILE_PATH.csv'
WITH ( FIELDTERMINATOR= ',' , ROWTERMINATOR= '\n' )

This simple script could be modified to suit importing  data with a variety of field and row terminators.

It is surprising that within T-SQL there is a TOP() but no BOTTOM(). So how can we find the bottom of a set?

Lets first recap on what TOP() is meant to achieve. Assume that there is a table called Ducks which has information on Award Winning ducks (Let us never question how they or what they have won these awards for). There are 3 columns, Name, NumberOfAwards and Category. If we win a simple SELECT on the table we get,

Name              NumberOfAwards             Category
-------            -------------------       -----------
Barry               10                       1
Ivan                4                        1
Steve               15                       3
Mandy               12                       2

If we run the following

SELECT TOP(1) NumberOfAwards
FROM Ducks

the data return is thus,

NumberOfAwards             Name
-------------------       -----------
15                        Steve
Steve is returned because he the duck the highest score,15.

Using TOP(2) would return,

NumberOfAwards             Name
-------------------       -----------
15                        Steve
10                        Barry

Steve and Barry are returned because they are the top 2 award winners. You get the idea.

Now, what if you want to get the bottom Award winner. There is no BOTTOM() so how can we grab this data. TOP() is affected by the ordering of data. The following T-SQL will return the bottom award winners

SELECT TOP(1) NumberOfAwards
FROM Ducks
ORDER BY NumberOfAwards asc -- sorting the data in ascending

NumberOfAwards             Name
-------------------       -----------
4                          Ivan

This works because the data is ordered in the following fashion using ASC

NumberOfAwards             Name
-------------------       -----------
4                          Ivan
10                         Barry
12                         Mandy
15                         Steve

Using the ASC order puts Ivan at the top. When TOP(1) is used it will grab this top value in the list.

If you data was ordered using DESC then the top value would be retrieved. Try it!

 

At some point you might want to present different content in query results based on particular conditions.

For example, you might have a table containing information on speciality, Award winning ducks (you never know). Lets say that there is a Ducks table (obviously) with the following columns Name, NumberOfAwards and Category (let us never question what awards they’ve won).

Lets say we run the following T-SQL

SELECT Name, NumberOfAwards, Category
FROM Ducks

This will return something like


Name              NumberOfAwards             Category
-------            -------------------       -----------
Barry               10                       1
Ivan                4                        1
Steve               15                       3
Mandy               12                       2

Lets say that we want to add some additional information to the category. The number code is ok but a textual translation would be useful. The numbered categories are as follows.

Number          Category

1                          Racing

2                         Fancy

3                        Fighting

We want to achieve what is shown below,

Name              NumberOfAwards             Category
-------            -------------------       -----------
Barry               10                       Racing
Ivan                4                        Racing
Steve               15                       Fighting
Mandy               12                       Fancy

To can use a CASE to achieve this very simply. If you’ve used CASE in other programming languages, T-SQL use is not dissimilar – its a ‘switch’ used to perform behaviour based on certain conditions.

The syntax of CASE is simple – there are two ways of doing it,

METHOD ONE –

SELECT Name,
NumberOfAwards,
CASE Category  -- the column we're 'testing'
WHEN 1 THEN ‘Racing’ -- if the Category column is 1 then print Racing
WHEN 2 THEN ‘Fancy’ -- if the category is 2 then print Fancy
WHEN 3 THEN ‘Fighting’ -- if the category is 3 then print Fighting
ELSE ‘N/A’ -- is the category isn't 1, 2 or 3 print N/A
END as ‘DuckCategory’ -- end the case
FROM Ducks

METHOD TWO –

SELECT Name,
NumberOfAwards,
‘DuckCategory’  as
CASE  -- look no column name!
WHEN category = 1 THEN ‘Racing’ -- if the Category column is 1 then print Racing
WHEN category = 2 THEN ‘Fancy’ -- if the category is 2 then print Fancy
WHEN category = 3 THEN ‘Fighting’ -- if the category is 3 then print Fighting
ELSE ‘N/A’ -- is the category isn't 1, 2 or 3 print N/A
END -- end the case
FROM Ducks

Both methods achieve the same result -where there is a category number a textual description is printed.

Ducktastic.

There are  a variety of reasons why a DBA might want to perform log restores, maybe as part of recovery or maybe to set up mirroring.

The basic syntax for a log restore is as follows

RESTORE LOG database_name FROM DISK = 'Location' (with norecovery)

This is fine for restoring one log backup but if you’ve got maybe 12 hours worth of log backups that’s a pain.

Below is a quick and dirty script which will generate the T-SQL which when run will restore the logs from a point in time. Think of this like quick and dirty T-SQL script generation. You could even plug this into dynamic SQL.

declare @database_name as nvarchar(20)
set @database_name  ='Ivan' -- name of the database

declare @lastFullTime as datetime
set @lastFullorDiffTime =  '2012-09-03 19:30:00.000' -- a date and time after the last full or diff backup, remember, log backups are every 15 minutes

select 'RESTORE LOG' + @database_name FROM DISK = ''' + physical_device_name + ''' with norecovery, stats = 10'
from backupmediafamily bmf
inner join backupset as bs
on bmf.media_set_id = bs.media_set_id
where bs.type = 'L'
and bs.database_name = @database_name and bs.backup_finish_date > @lastFullorDiffTime
order by backup_finish_date

go

This will grab the required information from system tables containing information about the log backups and generate restore statements which, when copied, pasted and run in a new query window, will restore logs from a specific point in time.

I will grant you that more sophisticated methods exist for multiple restores (please feel free to post them here below) but when you need something which will  quickly and simply restore logs, this can be useful.