-->
Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Creating a list of random numbers

I was working on a blog post this weekend that required a list of random numbers. Now, this isn’t exactly an uncommon task, but it’s not as easy as it would seem. The random number function RAND() has a few inherent flaws. And from what I can remember the random… Read more

0 comments, 52 reads

Posted in SQLStudies on 22 March 2017

The parts of an object’s name

Any object within a database in SQL Server has what is called a four part name. Which rather implies four pieces right? Simple enough

Server.Database.Schema.ObjectName

And you can find the lists of each of those pieces in

 
The breakdown is nice but how about usage?… Read more

0 comments, 89 reads

Posted in SQLStudies on 20 March 2017

Compressed backup errors and TF 3042

Compressing your backups has very few downsides. It’s usually faster (the additional time for compression is less than the time saved by reduced IO) and of course, the backups are smaller. I have run into a few issues, however.

On one occasion there was 75gb free on a drive, the… Read more

4 comments, 102 reads

Posted in SQLStudies on 16 March 2017

Limits vs Goals

It’s that time again. Kennie Nybo Pontoppidan (b/t) is the host this month and has a fun topic for us.

The daily (database-related) WTF

Database horror stories. Things that make your eyes pop and not in a good way. What a fun topic! But not an… Read more

0 comments, 104 reads

Posted in SQLStudies on 14 March 2017

Review of Azure Fundamentals class on Openedx

My company has recently adopted a Cloud first attitude. Now that’s different from Microsoft’s Cloud first. Microsoft is putting new features in the Azure first, then at regular intervals (the releases) those changes get pushed to on-premises versions of the applications. Our version means, in any new development check… Read more

0 comments, 155 reads

Posted in SQLStudies on 8 March 2017

Using GROUP BY instead of DISTINCT

Recently, Aaron Bertrand (b/t) posted Performance Surprises and Assumptions : GROUP BY vs. DISTINCT. In it he says he prefers GROUP BY over DISTINCT. He discusses the fact that GROUP BY will, in fact, under certain circumstances, produce a faster query plan. I highly recommend… Read more

6 comments, 1,478 reads

Posted in SQLStudies on 6 March 2017

Event storming! It’s raining emails!

One of the most common ways to get an event notification is by email. So what happens when you get 500 emails in a day and only one or two are actionable? Do you read every single email? Spending quite literally hours to find those one or two gems? Or… Read more

3 comments, 100 reads

Posted in SQLStudies on 1 March 2017

Queries with optional parameters

These are those queries where you are pulling for, let’s say, a first name, a last name, a state, and/or a city. Simple enough, until you notice that or. We might only get a first name, or a state and the query still needs to work. These queries are… Read more

10 comments, 2,214 reads

Posted in SQLStudies on 27 February 2017

What’s in my backup file?

Restoring a backup file is pretty easy right?

RESTORE DATABASE [Test] FROM DISK = 'C:\backups\backup.bak';

Ok, but what if more than one database backup is stored in that single backup file? Didn’t know you could do that?
Yep. You can.

BACKUP DATABASE [Test] TO DISK = 'C:\backups\backup.bak';
BACKUP DATABASE [AdventureWorks2014]…

Read more

0 comments, 278 reads

Posted in SQLStudies on 22 February 2017

Penny wise, pound stupid

Growing up my mother used this phrase quite a bit. Penny wise, pound stupid. (In case you didn’t know the pound is the British equivalent of the dollar.) Basically, it means paying attention to the small stuff at the expense of the big stuff. My favorite example of this… Read more

0 comments, 110 reads

Posted in SQLStudies on 20 February 2017

Deadlock on ALTER DATABASE

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

3 comments, 992 reads

Posted in SQLStudies on 16 February 2017

Server level database permissions

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

4 comments, 283 reads

Posted in SQLStudies on 14 February 2017

Deadlock priority

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

5 comments, 1,776 reads

Posted in SQLStudies on 8 February 2017

SQL Connectivity issues: A guided walk through

The SQL Tiger Team (t) has recently put out a guided walkthrough for SQL Connectivity issues.

Announcement
Walk Through

If you look at the bottom of the announcement you’ll also notice links to walkthroughs for

Troubleshooting Always On Issues
Troubleshooting connectivity issues with Microsoft Azure SQL Database

I’m… Read more

0 comments, 177 reads

Posted in SQLStudies on 6 February 2017

Changing what SSMS opens on startup

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?

In my second ever post (way back in August of 2012) I… Read more

0 comments, 253 reads

Posted in SQLStudies on 1 February 2017

Azure quick start

In case you hadn’t noticed, I love built in help. DBCC HELP, the script button in SSMS, even BOL. Well here’s another one.

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

0 comments, 131 reads

Posted in SQLStudies on 30 January 2017

My SQL logins don’t work. Again.

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

0 comments, 1,709 reads

Posted in SQLStudies on 25 January 2017

Creating a connect entry.

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

4 comments, 788 reads

Posted in SQLStudies on 23 January 2017

Lead Blocker

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

7 comments, 187 reads

Posted in SQLStudies on 18 January 2017

What is COPY_ONLY?

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

2 comments, 1,930 reads

Posted in SQLStudies on 16 January 2017

Older posts