Finding The Last Queries Run On A SQL Instance

Hey everyone! Last SQL Something post for the year 2013! :-)

Pretty happy with how the year went blog-wise (not as many posts per month as I'd hoped but still I managed to stick with it).

Anyhoo, enough about that and on to the post! Today we are looking at how to view the last few queries that ran on your databases.

Mini Post: SQL Server Configuration Manager "The Request Failed..." Error

Happy Holidays! And here is a Mini Post for you!

It's a simple error to run into. You open SQL Server Configuration Manager to start a service and this pops up: The request failed or the service did not respond in a timely fashion.

Fig. 1: Failure...

Hmmm.

Getting the Time Difference Between Inserted Rows (Given a DateTime Column) - SQL Server 2008

Good day and welcome to SQL Something!

I'm particularly pleased to write this one as it's the first non-MiniPost I've written in a while. Huzzah!

Today we are going to look at how to get the time difference between the insertion of two rows, given a datetime column.

Mini Post: Removing all Whitespace/NewLine Characters From a String

Hey and hello! Welcome back or welcome new!

Today really quickly we'll look at something that is very helpful in my day-to-day stuff, that is, stripping all spaces and any new line characters from a string.

You can use the below:

REPLACE(REPLACE(REPLACE(REPLACE(YourString, CHAR(9), ''), CHAR(10), ''), CHAR(13), ''), ' ', '');

This strips Tab spaces ( represented by CHAR(9) ), Line Feed spaces ( represented by CHAR(10) ), Carriage Returns ( represented by CHAR(13) ) and plain old spaces (represented by ' ').

Please see here for CHAR types.


DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Getting distinct random values using 'TOP' and 'NEWID()'

Heya everyone out there, Mini Post time!

...Too many of these don't you think? I really have to buckle down and write a longer post sometime.

Anyway let's focus on something I had to use 5 minutes ago (I have used it before, but 5 minutes ago was the most recent so I figured I'd jot this down quickly while its in my head).

Sometimes we wish to get back random rows from our table. For this we can use 'NEWID()' as follows:

SELECT top 10 Col1
FROM Table1
-- WHERE SomePredicate
ORDER BY NEWID();

The above will pull 10 random Col1 values, regardless if the same value can appear multiple times in Col1.

Now sometimes we wish to pull not only random values, but random distinct values.

It'd be nice if we could do something like:

--Incorrect
SELECT top DISTINCT 10 Col1
FROM Table1
-- WHERE SomePredicate
ORDER BY NEWID();

But alas, we'll get an error. So instead please use (the key point is the 'GROUP BY'):

--Correct
SELECT top 10 Col1
FROM Table1
-- WHERE SomePredicate
GROUP BY Col1
ORDER BY NEWID();

DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Adding a Column to an Existing Table With a Default Value

Hey again everyone and welcome to SQL Something.

Just thought I'd throw this in as well because I needed to do this earlier this week: Adding a column with a default value.

Initial query was found here (click this, it's a very cool blog).

See my slight variant below and explaination:

ALTER TABLE YourTable
ADD YourNewColumn INT NOT NULL DEFAULT(42)
GO

So as far as an explanation goes, what the above does is alter your existing table (named 'YourTable') by adding a new column (named 'YourNewColumn') of type INT with a default value of 42. Each row in the table will then have a new column filled with a value of 42.

You can change the column type and the default value in the query to suit your needs.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Error Specified Cast is Not Valid

Hey everybody! Me again with another Mini Post.

Today we are going to look at a SQL restore error: Specified Cast is Not Valid.

You may see this error when you are attempting to restore a SQL Server Backup via the SQL Server Management Studio. This error could be the result of a couple of different things:

• Corrupt backup
• Attempting to restore a backup of a newer version of SQL Server on a older version of SQL Server
• SQL Server Management Studio GUI bug

Solutions (in order of the problems above):

• Redo the backup and then try to restore it (if this still doesn't work it may indicate a corrupt DB; check the DB with DBCC CHECKDB)
• Verify that you are not attempting to restore a backup on a older version of SQL Server (example don't try to restore a 2008 DB backup on a 2005 instance)
• Try restoring from the command line instead of the GUI

DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: The Operating System Returned Error 21

Hey everybody and welcome back to SQL Something!

Today we're gonna take a quick look at a very generic error: Error 21.

You will usually get a message along the lines of "The operating system returned error 21" along with something like "The device is not ready". The second part of the error is the important bit as it specifies that, for whatever reason, the 'device' (usually your drive or the actual files on it) is not ready.

This could be due to a number of reasons (hence the generic nature of the error):

• Drive disk space issue
• Hard drive failure
• SAN failure
• Corrupt database files (MDF/LDF etc)

I would say after you check out what might be causing the issue as well as rectify it, you run a DBCC CHECKDB to ensure that you do not have any consistency issues with you database(s).


DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Remote Procedure Call Failed. [0x800706be] (SQL Server 2008 / 2008 R2)

Hey everyone! Yes, it's Mini Post time.

After you have installed SQL Server 2008 or 2008 R2 (on possibly a Windows 7 or 8 machine) and you attempt to go into the Configuration Manager you may see the following:

Fig. 1: The error in question.

This seems to be a bug that a few people run into (including myself recently). The solution(s) are as follows:

SQL Server 2008

• Upgrade to Service Pack 3. Found here (worked for me!).
• If the above does not work, use the steps to recreate the WMI Repository found here.

SQL Server 2008 R2

• Upgrade to Service Pack 1 or 2. Found here and here respectively. 
• If the above does not work, use the steps to recreate the WMI Repository found here.

If anyone knows the exact cause of this bug, please feel free to share in the comments below. It will benefit everyone. :-)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Reseed Identity Column

Hey all, when in doubt: Mini Post!

In this post we look at reseeding the identity column via T-SQL.

Please note that the rules change depending on if there are already values in your table when you attempt the reseeding.

1) With Values in Table

Suppose you want the next value in your table (Table1) to be 12. We will use the following:

DBCC CHECKIDENT (Table1, reseed, 11)

Note that your reseed value will be one less than the value you want to appear (e.g. 11 is one less than 12).

2) Without Values in Table (Empty Table)

Suppose you have an empty table and you want the next value to be 12 (for whatever reason). We will use the following:

DBCC CHECKIDENT (Table1, reseed, 12)

Note that your reseed value will be exactly the value you want to appear (e.g. we use 12).

3) Reseed via TRUNCATE

If you are going to delete all the values in a table anyway before you reseed, you can use the TRUNCATE statement instead of DELETE, and the table will automatically reseed from whatever it's initial starting value was.

TRUNCATE TABLE Table1

(Please read up on TRUNCATE before you decide that it's a viable solution for your needs)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Counting Duplicate Records

Hey all, just trying to sneak in a couple posts before the month ends (can't have a month without posts; just feels wrong).

Alright this was a useful something that I used way back whenever and recently had to reuse:

SELECT Col1, COUNT(*) AS Total
FROM Table1
--WHERE SomeCondition
GROUP BY Col1
HAVING COUNT(*) > 1
ORDER BY COUNT(*) ASC

This query will list the number of times a value in Col1 is duplicated (i.e. if appears more than one time).

Handy for finding copies of a value that is only supposed to appear once in a table.
You can, of course, adjust the "HAVING" section to suit your needs (e.g. HAVING COUNT > 3, HAVING COUNT = 1, etc) thereby creating a more generalized search query.


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Useful - Colour Coding Your SQL Instances/Queries

Hey everyone just a quick useful something that my colleague reminded me of.

So if you're like me, you may have many queries windows open but they could be from multiple instances of SQL Server, over multiple servers.

It may become a little confusing as to what query is being run on what instance.

Luckily SQL Server gives you the ability to colour code your query windows! You can assign a colour to a particular instance and then all queries that run against that instance will have a band of colour, indicating the instance. Lets take a look after the jump.

Recreate a 2008 DB on a 2005 Instance

Hey everyone, just a quick note on one way to get data from a 2008 DB to a 2005 DB. (EDIT: This initial "Mini Post" turn out a bit longer than I expected...)

As most of you would know you cannot restore a backup from a newer version of SQL Server on an older instance, so this limits the options you have if you do end up having to this for whatever reason.

Your best option is to generate a script of the DB on the newer instance and execute the script on the older instance.

You can choose to either script the data as well, or you can export the data to either a text file or Excel document and import the data into the 2005 DB afterwards.

Mini Post: Change SQL Server Instance Min/Max Memory Settings

Hey everyone, just a quick note on how to change the min/max memory settings on your SQL Server instance.

By design, your SQL Server service will attempt to use as much memory as it sees fit in order to avoid going back to the disk to store the data it wants to. It will instead attempt to store as much data as possible in memory.
Not setting a max limit on the amount of memory SQL Server should use can greatly negatively impact the service's performance as well as the server's performance and you may start getting memory errors such as:

Error 802
There is insufficient memory available in the buffer pool.

Mini Post: Change Column Collation

Hey guys, this is how you go about changing the collation of a column.

Run the following, putting in your database, column and collation names where appropriate:


ALTER TABLE dbo.YourTable ALTER COLUMN [YourColumn]
            varchar(20)COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL;
GO

Double check that your column collation was change by running the following on your database:

SELECT name, collation_name 
FROM sys.columns 
WHERE name = N'YourColumn';

Mini Post: Change Database Collation

So just to get this one out of the way (was bugging me that I didn't write it as yet), here's how to change the collation of a database. (See bottom of post for links to other related collation posts)

Use only one of the following solutions: EITHER Management Studio OR TSQL.

SQL Server 2008: Change Instance Collation Via Rebuilding The System Databases

Hey everyone, welcome back to the first proper length SQL Something in a while.

In an earlier post I outlined how to get queries to ignore collation. In a future mini post I will show how to change a database's collation.

Today's focus is the instance's collation.

Changing the collation of a SQL Instance is not something to be taken lightly (which is why it's good practtice to choose the correct collation the first time you set up the instance). In order to change it you have (as far as I know) two options:

• Reinstall the instance.
• Rebuild The System Databases.

So without further ado, let's look at the second option.

Mini Post: Get Sizes Of Tables In A Database

Hey all, here's something I've been meaning to put up for a while.

Its a very common question that pops up from time to time so I thought I'd share the answer in one more place on the internet.

Well two answers actually: The Report Solution and The Query Solution.

Mini Post: View Queries Currently Running On Your Instance

Hey all! Below is a quick and easy way you can see what queries are currently running on your instance.


SELECT QueryText.TEXT AS Query,
DB_Name(Requests.database_id) AS DBName,
Requests.session_id AS Session_ID,
Requests.status AS Status,
Requests.command AS Command_Type,
Requests.cpu_time AS CPU_Time,
Requests.total_elapsed_time/1000 AS Time_In_Seconds,
Requests.total_elapsed_time AS Time_In_MilliSeconds
FROM sys.dm_exec_requests Requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS QueryText

The query makes use of the sys.dm_exec_requests and sys.dm_exec_sql_text Dynamic Management Objects (DMOs). Check the links for additional columns that may be useful to you.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Adjust Queries to Ignore Collation

Heya people, gonna quickly run through how to adjust queries to ignore collation. I will return with another Mini Post to show how to actually change the collation of an instance and a DB.

You may have run into an scenario where your database has a different collation from that of your instance
(for example, the database could have been taken from an instance with a different collation than that of your current instance).

When you attempt to compare two columns, one with collation A and one with collation B, you will get the following error:
"Cannot resolve the collation conflict"
along with the names of collation A and B as well as the comparison operation that was attempted (whether it was '=', BETWEEN', 'IN' etc)

To resolve this, you can set the collation of A (the previously foreign DB) to be the collation of the current instance. In your WHERE clause where you are doing your comparisons place "COLLATE DATABASE_DEFAULT" immediately after the name of the column used for comparison like so:

...WHERE A.Col1 COLLATE DATABASE_DEFAULT in (SELECT B.Col1 FROM B)

No more error. :-)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Recovering A Database With Only The MDF File: These Are Your Options

Hello out there and welcome back to SQL Something!

This is one of a few blog posts I had time to complete and post (busy times). Without further ado, let's get started.

This post was inspired by a recent event where we lost the drive that our log files were on (not gonna go into the gory details about that).
Thankfully, prior to this we knew hardware weirdness was going on with the server so we moved all our production DBs to our secondary server. Whew.
Thus, losing the drive on what was formerly the production server wasn't that bad. And it then gave me some MDF files to attempt to restore at my leisure, just for trying sake.

Mini Post: Using sys.dm_exec_connections to Get Info About Current Connections to SQL Server

Hey guys! Another bite sized mini post here!

Today we take a quick look at sys.dm_exec_connections (2012 equivelent here) to get a little info on what/how sessions are connected to our instance.

As stated in the links sys.dm_exec_connections provides server level info on SQL Server connections. Using the below for example:

SELECT top 50 session_id, auth_scheme, connect_time, client_net_address
FROM sys.dm_exec_connections
order by session_id

Would result in the below:

Fig. 1: Results of querying sys.dm_exec_connections

The client_net_address, I find particularly useful as it shows the host address of the client that is connected to instance. Nice. Auth_scheme is also nice as it shows the 'how' of the connection (is it a SQL login etc).


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Mini Post: Taking a DB out of 'RESTORING' State

Hey guys, just a quick post here on how to take your database out 'RESTORING' state.

So maybe you stopped Mirroring, maybe you were restoring transaction logs, maybe your database has an issue and curled up into itself or maybe you accidently restored a DB with the "WITH NORECOVERY" option (or heck, maybe you did it on purpose).

The bottom line is your DB is currently in restoring.

To remove it from this state, please run the following:
RESTORE DATABASE YourDBName WITH RECOVERY

Ta-da! Its back online. :-)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

SQL Server 2012: OFFSET-FETCH

Hello all and welcome back (finally!) to SQL Something. :-)

Been a while I know, but sometimes life steps in your way a little bit. But hey even in those times, life teaches you something new.

Recently I’ve studying for the Microsoft 70-461 Exam (Querying Microsoft SQL Server 2012) so if you find posts in the future seem to be query related then you know why. :-p

A lot of the stuff I’m reading so far seems interchangeable with stuff you can use in SQL Server 2008 queries but I did run into something early that seemed kind of interesting. That thing is OFFSET-FETCH.

Mini Post: Setting Line Numbers In the Query Window

1st Mini Post!!!

I think I’m gonna do these every now and again, in addition to the weekly posts. Stuff that’s a little too small to be a normal post, but stuff that’s still useful.

Alrighty, let’s set line numbers shall we? Honestly I don’t know why a) it’s not on as default or b) it’s so roundabout to set up a simple thing but hey, that’s just me.

SQL Server: Setting Up Transactional Backups

Heya guys, it’s been awhile. Welcome back to the first SQL Something in 2 weeks. I sincerely apologise for this. The first week I came down with an awful fever and then the second week was really busy workwise. 

Sigh, still feeling pretty bad about not updating though. Bleh.

Anyhoo let’s begin without further ado. This week, we’re gonna take a look at setting up Transactional Backups.

SQL Server Email Notifications for Back Ups

Heya everyone and, as usual, a big welcome back to SQL Something. :-)

Today’s post is going to illustrate how to set up Email Notifications for SQL Server jobs, specifically for a simple backup job in this case. A friend of mine indicated that they didn’t know how and that inspired this week’s post. 

It’s going to be divided up into three main parts:

• Setting up the Email Account(s) to Send Notifications
• Setting up the Email Account(s) to Receive Notifications
• Setting up the Job with Email Alerts

This blog post is also going to look like it was hit by a picture nuke.

Remove a Database from Single User Mode

Hey everybody! Welcome back to SQL Something.

It’s been a long week but still trying to stick to my self-imposed rule of having one post per week. Here’s a simple one on how to remove Single User Mode from a DB.

Fig. 1: What a DB looks like in Single User Mode in Management Studio.

 

From my readings and my experience it seems a DB could go into single user mode in order to prevent corruption after some form of incorrect action was taken, whether purposefully or not. This recently happened to me when I tried to purposefully break replication. I tried to delete a DB while it was publishing, it then threw an error and curled up in a fetal position in single user mode. Poor little guy.

So how do we take a DB out of Single User Mode? We can try the following:

SQL Server Error: Cannot drop database because it is being used for replication.

Hey everyone! Welcome (or also hopefully "Welcome back") to SQL Something. :-)

This week’s instalment is going to be a little short and focused on fixing the above error that I ran into. 

The general consensus is that this error occurs after Replication was removed from a database and then you attempt to delete the DB. Apparently there might be some replication metadata that was left behind that would cause SQL Server to believe that replication is still taking place. If you attempt to delete the DB, you’ll get the error and it may go into "single user mode" (Access to the DB is restricted to one user; I may make a post on how to get out of it in the future).

As usual I’m gonna post everything I read on how to fix it as well as what I actually did.

SQL Server Configuration Manager Error: Cannot Connect to WMI Provider

Good day everyone and welcome back again to another instalment of SQL Something (now with pictures!).

Today we are going to look at something that really surprised me during work last week, because frankly I never heard about nor saw anything like it. But, once you tackle an issue with a cool head and your good friend Google, everything will be alright. 

Using SQL Server Table Hints: NOLOCK

Hey everybody, Geon here again with another exciting instalment of “SQL Something”.

Today we are going to be looking at querying using NOLOCK.

When I first started working where I am, I used to run my SELECT queries ‘normally’, that is, without using the NOLOCK feature. I remember one day I put a large query to run (one that normally took about 30 minutes) with the mind-set that I’ll “check back on it in a bit”. Ten minutes later one of our sys admins pointing out that one of our applications started throwing a bunch of errors, all database related. This app almost continuously wrote data to its database. Suddenly, it could not insert anything into one of its database tables and was kicking up a massive fuss because of it.

What You Can Do With SQL Server Replication

Hey again everybody. This is me trying to stick to my self-imposed rule of writing something (re: anything) once a week that deals with SQL Server, so without further ado, here we go.

A very, very basic rundown of Replication goes a little something like this:

• Replication allows you to copy specific data from a database (known as a 'Publisher') to another database (known as a 'Subscriber'). 
• A 'Distributor' determines how the data is received by the Subscriber.
• The replicated data consists of items specified by the admin. For example: specific tables and specific columns within those tables.
• Data is synched between the Publisher and the Subscriber at an admin specified time interval.
• You can have one or more Subscribers for one Publisher.

How to Check Database File and Log Sizes Using SQL.

I thought a bit as to what would be a nice simple first post for my blog when, as luck would have it, I was inspired by my job.

We had noticed a database growing a bit faster than it should have so I decided I'd like to know a couple things:

• What was the growth rate of the .MDF file per day,
• What was the growth rate of the .LDF file per day,
• What was the growth rate of each table per day.

So to answer those questions we can turn to sys.sysfiles and the columns it provides. The ones that interested me at the point in time were the 'name' and 'size' columns (you can look at the link and see what other columns might be helpful for your particular situation).