I had an interesting problem recently. A database was stuck in single user mode. How exactly was it stuck you ask? Well, 4-5 system sessions were holding locks on the database (and blocking each other). Which meant I wasn’t able to get exclusive use of the database which is required… Read more
Sometimes you have a requirement to grant permissions to every database on an instance. Historically this has required creating a user (database level principal) on each database and granting it the correct permission. And making sure to grant those permissions to the model database so that future databases have the… Read more
Everyone deals with deadlocks from time to time. But sometimes we need to control who’s the deadlock victim and who isn’t. For example, I’m doing a big delete on a table in a 24×7 environment, I can’t afford downtime to do it so I’m doing my delete in small… Read more
The SQL Tiger Team (t) has recently put out a guided walkthrough for SQL Connectivity issues.
If you look at the bottom of the announcement you’ll also notice links to walkthroughs for
I’m… Read more
When you open SSMS a connection window automatically comes up. Then once you pick your server, connection type, username and password (if needed) you get an object explorer connection.
But what if that isn’t what you want?
Throughout the Azure portal you will find buttons called Quick Start. You won’t find it everywhere but I’ve found it in the Resource… Read more
My recent post on why SQL logins aren’t working has generated a couple of follow up posts. The first one was on how to create a connect item and then this one. It was pointed out to me that the errors in the log have quite a bit more information… Read more
I recently wrote a blog about how to tell why your SQL login isn’t working. There were a lot of good comments and several of them suggested that I create a connect entry to make the error easier to understand. There was also a question of how to create… Read more
Blocking is just part of life I’m afraid. Because we have locks (and yes we have to have them, and no, NOLOCK doesn’t avoid them) we will have blocking. Typically it’s going to be very brief and you won’t even notice it. But sometimes you get a query or… Read more
You’ll frequently hear that you should add the COPY_ONLY clause when taking an ad-hoc full backup to avoid messing up the backup chain. Just in case you haven’t: You should add the COPY_ONLY clause when taking an ad-hoc full backup to avoid messing up the backup chain.
But you may… Read more
I recently had an interesting time getting the SQL Auth logins on a new instance working. I was doing a side by side upgrade and the only server principals used by the application were SQL Server authenticated logins. I tried over and over again but kept getting the same error. Read more
I’m a big fan of the sp_help system stored procedures, which is why I’ve been a bit disappointed that the sp_helpindex stored procedure is rather badly flawed. It doesn’t have the included columns from the index. This seems a bit strange to me since included columns first appeared in… Read more
I’ve been studying Azure recently and came across a really interesting Azure site.
To give you an idea why I find this view so interesting I’m going to drill down to a SQL Database that I have.
Up front, you can see some interesting information. The location, status, service… Read more
Honestly, I’m only posting this because I had a hard time finding anything posted on this error and I found it somewhat unclear. Here is a quick walk through to produce the error:
-- Create server principal. CREATE LOGIN SQLTest WITH PASSWORD = 'SQLTest'; GO -- Create a database principal…
I was thrilled to host TSQL Tuesday #85, the last TSQL Tuesday of 2016. My subject was backup and recovery and I was pleased to see this line of my invitation quoted several times.
Backups are one of the most common things DBAs discuss, and they are at once one…
I’ve been using sp_helptext for years. Honestly I probably end up using it at least once a day. But I was recently exposed to object_definition() which has a similar output. There are, however, a few differences.
This system function returns the text of any (T-SQL) code based object. I.e.… Read more
Years ago I worked for a company that had a rather cool way of handling requests. When we ran the request the query we used and the output were saved to a directory on the network. This way if there were any questions about what had been done we had… Read more
The other day I received an email asking for some help with a school project. Would I please answer some questions about my career and industry. Well after some due diligence I answered the questions. But the questions were rather interesting and the answers turned out pretty well (IMHO) so… Read more
The basic steps when restoring backups are
- The most recent full backup.
- The most recent differential backup.
- All log backups after the most recent differential.
I’ve been really excited about the new Data Migration Assistant (DMA) since I first heard about it. One of the things I like best about it is that unlike the old Upgrade Advisor it doesn’t have to be run on the server being upgraded. You can run it against… Read more