Thursday, November 23, 2017
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.
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
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
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.
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
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
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
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:
- Find a drive with lots of space that is attached to the server.
- If no drive exists, attach one.
- Add the new file, setting the appropriate growth setting.
- 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.
- Issue a CHECKPOINT to truncate the logs of data not required.
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 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
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).
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.
I just found this... https://gist.github.com/ From this... https://stackoverflow.com/a/5615444 And if you look at the previous post I&...
September 2017 Update: HOLY JEEZ. I can't believe I forgot to add a step in to modify the format file so you only have the column you w...
When SQL Server Service Broker is being used in a database, any record it creates in the hidden system tables sys.sysconvgroup, sys.sysdesen...