Thursday, March 1, 2018

Level Up your SQL Server Architecture Knowledge

Read these articles about SQL Server files and their structure. These can help you work with your SAN administrators to improve I/O through proper disk configuration, as well as helping you deal with corruption in the data and log files through a better understanding of the file layouts.

Files and Filegroups:

Understanding Pages and Extents:

Tables and Index Data Structures Architecture

Enjoy, and you're welcome.

Thursday, February 15, 2018

Renaming databases in SQL Server.

I was requested by someone to rename a database on a SQL Server this morning.

Me: "When do you need it done?"

Them: "Now. You've just got to press F2 and give it a new name!"

Me: "It doesn't work like that."

Them: "Yeah it does... the other guy does it like that all the time!"

Me: "He's been extremely lucky then. There's always a session blocking the rename procedure from getting an exclusive lock."

Them: "Well, it works!"

So I tried using F2 to rename a database (even after they told me that they'd restarted the app & web servers connecting to this database. Predictably, it didn't work, and also predictably, they were shocked by this.


So, inspired by the previous exchange, I have written out the 6 super-steps you need to perform to rename a database in SQL Server. This will work in 2008+.

1. Make sure you have an outage window for this change.
2. Make sure you have modify rights for the Data and Log file locations.
3. Update the logical file names using ALTER DATABASE.
4. Update the physical file names using ALTER DATABASE.
5. Detach the database.
6. Attach the database with the new name, setting the owner appropriately.

Thursday, November 23, 2017

Using Blogger, or something else, and want to post awesome looking code snippets? Use GIST.

I just found this...

From this...

And if you look at the previous post I've just made (as of 24th November, 2017), you can see it's awesome.

Go on - upvote the answer on StackOverflow.

Typing is for those who don't know any better

Sometimes, I need to copy/paste a lot of lines and modify just one portion of it to make each line unique - like creating large numbers of temp tables to test an obfuscation DLL I wrote that I'm calling through the CLR. Yeah, I do that sort of stuff. Better than using T-SQL to obfuscate Personally Identifiable Information.

Sure, I could use Shift+ALT to select just a particular section of text, but some of the statements I write go over multiple lines that all need to be replicated and modified. Who has time for that? Well, I probably do, but it looks cooler if I don't write it at all.

So I do things like this:

Or... if I want to get a little fancier, then I might do something like this:

For those times when I want to generate statements to make updates to a list of tables and columns, rather than do something less than set-based (you know - cursors/while loops).

Monday, July 3, 2017

CHARINDEX Performance affected by a lack of a starting position

Yes. It really is. If you have large queries that use CHARINDEX in a WHERE clause, and that SELECT is used as part of a UNION, omitting a start position will drastically affect performance. I've no idea WHY that is, but I saw it and measured performance this morning. True story.

Thursday, June 1, 2017

Killing Linked Server Connections

Have you ever tried to cancel or rollback a transaction, particularly to a linked server, more particularly to an Oracle linked server, and that "Uh oh" feeling has come over you when you've realised that it's stuck, and the only way to fix it is to restart the SQL Server service?

Fret not! Using TCPView will save you!


Having said that, copy TCPView to a file location accessible by the machine in question, and RDP to the machine. Open TCPView, Accept the agreement, and it will then present you with a list of all the processes currently running (it may take a while if there's a lot of connections). You'll be able to determine which connection(s) is/are the one(s) you're looking for by checking the remote address and port columns. As an example, if you have a stuck transaction to an Oracle server, the port will be 1533, and the State, Sent and Received packets columns will be blank.

Once you've found your transactions, right-click on the first one and select Close Connection. Check your favourite SQL Server monitoring tool to make sure the transaction has now disappeared. Repeat for any other stuck transactions.

Complete list of SQL Server Messages

Yes, you can query master.sys.messages to get a list of SQL Server messages, but Microsoft has provided the full list in an easy to read list.


Wednesday, May 31, 2017

Can't remove filegroups and their associated data files?

If you've ever tested the implementation of filegroups and partitioning in SQL Server Developer or Enterprise Edition (Sorry Standard Edition), and decided you need to remove those empty files and filegroups, but couldn't, there's a reason for that. You need to drop the partition scheme, then the partition function, then the data files, then the filegroups. In that order. Specifically.

If you don't do that, you'll have 99 problems, and filegroups will be one of them.

Tuesday, May 30, 2017

T-SQL FizzBuzz

Yes... this is my own code. Yes... I also go by Stephen Falken elsewhere.

set statistics time on;

 @numbers int = 10000
 ,@rangelow int = 1
 ,@rangehigh int = 100;

create table #Numbers (Number int identity(1, 1), Col1 int);

insert into #Numbers (Col1) select top (@numbers)(1) from sys.messages;

  when (n.Number % 15 = 0) then 'FizzBuzz'
  when (n.Number % 3 = 0) then 'Fizz'
  when (n.Number % 5 = 0) then 'Buzz'  
  else cast(n.Number as varchar(6))
 #Numbers n
 n.Number between @rangelow and @rangehigh;

drop table #Numbers;

Thursday, May 18, 2017

Get out of disk space jail free.

We've all had those times where we've been running something on a Dev or Test server, and even though the database is in simple mode, the log file still grows (remember to CHECKPOINT people!). It keeps growing, and growing, and then you run xp_fixeddrives and you've only got a few GB left. Uh oh, spaghetti-ohs!

In a pinch, you can add another log file, located on another drive, to allow the transaction(s) in progress to complete without hitting "Transaction Log is full due to ACTIVE_TRANSACTION". THe NKOTB Step-By-Step guide is:

  1. Find a drive with lots of space that is attached to the server.
  2. If no drive exists, attach one.
  3. Add the new file, setting the appropriate growth setting.
  4. Set the max size of the existing log file to allow enough space for the log files of other databases to have some sort of growth.
  5. Issue a CHECKPOINT to truncate the logs of data not required.
  6. Breathe.

Of course - this is a massive NO-NO on Production systems because you should have correctly sized your drives before the server was created, and have been tracking file growth and additional space requirements proactively so you never run into this issue.

Unless someone runs a terrible query in Production that fills your log files. Then you're in a pickle.

Monday, February 1, 2016

The Mystery Revealed: Why the sp_ prefix is so special and reserved...

The answer: You can run stored procedures prefixed with sp_ without exec (just like extended procedures prefixed with xp_). All the other answers are correct as well, but this is an additional one. One of those "a long time ago in a Microsoft office in Redmond" things where a developer thought "Hey, lets do this because they're special and we can just run them". That's what makes them special.

Okay, so it's probably not a big mystery, but I've never seen anyone talk about this facet of it, so I just thought I'd put it out there.

Okay, scratch all that. I just went back to test a few different scenarios, and now I don't need to supply EXEC for any stored procedures on any server versions. I'm scratching my head over this one.

Level Up your SQL Server Architecture Knowledge

Read these articles about SQL Server files and their structure. These can help you work with your SAN administrators to improve I/O through ...