Thursday, November 23, 2017

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

I just found this... https://gist.github.com/

From this... https://stackoverflow.com/a/5615444

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!

PLEASE NOTE: DON'T DO THIS ON A PRODUCTION SERVER. TEST IT ON A TEST SERVER. NO WARRANTY OR SUPPORT OR ANYTHING ELSE AT ALL, EVER, IS PROVIDED BY ME FOR ANYONE ELSE. IF YOU BREAK IT, YOU BOUGHT IT.

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.

https://technet.microsoft.com/en-us/library/cc645603(v=sql.105).aspx

Enjoy.

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;

declare
 @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;

select
 case
  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))
 end
from
 #Numbers n
where
 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.

Wednesday, January 27, 2016

Unaccounted Database Bloat? Check your hidden system tables.

When SQL Server Service Broker is being used in a database, any record it creates in the hidden system tables sys.sysconvgroup, sys.sysdesend and sys.sysdercv won't be included in any of the standard SQL Reports regarding Disk Space by Table (or any other report that looks at tables). Normally, this is okay, and from experience, there's usually little variance between the total data stored in tables and indexes, and the database size.

Large variances will start to appear if Service Broker Conversations aren't being completed, either due to END CONVERSATION not being called in code, or another issue. To see how many conversations are still recorded, run this query (it might take a few minutes):

SELECT
 count(1)
FROM
 sys.conversation_endpoints ep
 INNER JOIN sys.services s on ep.service_id = s.service_id
WHERE
 -- Conversations that won't be closed automatically by Service Broker...
 security_timestamp = '1900-01-01'
 -- ...and if you want to look at a particular service...
 and s.name = N'http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService'

One database had well over 1,000,000 conversations that weren't closed properly. This article talks about using WITH CLEANUP to close conversations that cannot be completed normally (in this instance, the state was staying at CONVERSING).

Token-based server access validation failed

TL;DR: Check that the user has been granted "connect to SQL Server" before looking for a needle in a haystack.

The task seemed simple enough - restore a prod database to a dev server, set recovery mode, assign permissions, create data source in SQL Reporting Services, close ticket. Until this error appeared when trying to test the data source:

Date  7/08/2015 10:37:18 AM
Log  SQL Server (Current - 7/08/2015 11:37:00 AM)

Source  Logon

Message
Login failed for user 'DOMAIN\User'. Reason: Token-based server access validation failed with an
infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]

Error: 18456, Severity: 14, State: 11.

Hmm. Strange - the user exists in other databases. After a little DuckDuckGo-ing, I came across this reasonably comprehensive page: Troubleshooting specific login failed error messages.

I read through it, considered the message, and checked what the author said to check. Nothing worked. I tried to open SSMS as the troubled account and connect to the SQL Server myself. No good. So going back to the instance of SSMS running under my account, I checked the permissions for the (previously working) login. What did I find? Connect to SQL didn't have a tick in the GRANT column. Put the tick in there, clicked OK, everything works.

The takeaway from this is that while the error does look like it could be related to NTLM/Kerberos/AD troubles, a slight shift in perspective when reading the description of Error Code 0x139F (from the above link) is required. For those who haven't clicked the link, the description is ""The group or resource is not in the correct state to perform the requested operation". The requested operation is to connect to the server, and the correct state would be to have the necessary permissions to connect to the server. So check the Connect permissions for the login on the server.

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

I just found this...  https://gist.github.com/ From this...  https://stackoverflow.com/a/5615444 And if you look at the previous post I&...