Thursday, February 15, 2018
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
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).
I was requested by someone to rename a database on a SQL Server this morning. Me: "When do you need it done?" Them: "...
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...