-->
SQL Clone
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.

Default Database Reports – Disk Usage by Table

Every now and again you need to know how big a table is. Or several tables. Or all of the tables. Number of rows is frequently handy when you’re going to create a new index or otherwise modify the table. The amount of space used by the indexes can be… Read more

1 comments, 115 reads

Posted in SQLStudies on 19 July 2017

Default Instance Reports – Configuration Changes History

Hey, who changed the max memory setting for the xyz instance? The good news is that this information is captured in the default trace. Even better is the fact that there is a report that will pull the data out of the default trace and make it nice and… Read more

0 comments, 84 reads

Posted in SQLStudies on 17 July 2017

Default reports in SSMS

As DBAs our stock in trade is information and there is certainly an impressive amount available. The diagnostic views are the most common place to get the information we need but every now and again it’s nice to get an organized/pretty view. To that end, you can write your own… Read more

1 comments, 419 reads

Posted in SQLStudies on 13 July 2017

Save and save often

It’s the second Tuesday of the month, and as I’m sure you remember that means it’s time for T-SQL Tuesday! This month we are hosted by Raul Gonzalez (b/t) and the topic is Lessons learned the hard way.

I’m going to take this opportunity to… Read more

2 comments, 143 reads

Posted in SQLStudies on 11 July 2017

What is the system_health extended events session?

Similar to the default trace the system_health session is automatically started up when the instance starts and collects information about what’s going on.

Per BOL you get the following information:

  • Errors with a severity of >= 20.
  • Memory related errors (Errors 17803, 701, 802, 8645, 8651, 8657 and 8902).
  • Non-yielding…

Read more

1 comments, 909 reads

Posted in SQLStudies on 5 July 2017

SQL Homework – July 2017

For years Russ Thomas (b/t) has done a Monthly DBA Challenge and in fact I’ve used it as insperation a number of blog posts myself. Here is part of his description of it:

Often the task is what I consider a low frequency / high liability…

Read more

8 comments, 165 reads

Posted in SQLStudies on 3 July 2017

SQL Crossword : T-SQL Keywords : June 2017

I did a SQL crossword last month (not my first one either) and it was pretty popular so I asked around and I’ve decided to make this a regular thing (end of the month or so) for a little while (till I get bored with it). I’m going… Read more

0 comments, 1,672 reads

Posted in SQLStudies on 28 June 2017

Temp table quiz

Brent Ozar (b/t) posted a pop quiz on twitter earlier today.

Go ahead and give it a shot .. I’ll wait.

So? What do you think? Did you get it right? I did, but I wasn’t 100% certain, nor did I initially think through all of… Read more

10 comments, 280 reads

Posted in SQLStudies on 26 June 2017

Dynamically generate the command line DMA statement for each database

If you are upgrading your instance to 2016 (or 2017 soon) then you probably are going to want to run the DMA (Database Migration Assistant). If you are doing a BUNCH of upgrades at once (say 100 instances or something) then you probably don’t want to use the… Read more

0 comments, 296 reads

Posted in SQLStudies on 21 June 2017

Adding Identity to an existing column

A friend had an interesting problem today. A really big table (multiple millions of rows) and no primary key. He then ran into an issue where he had to have one. The easiest thing is to create a new int column with an identity column right? Unfortunately in this case… Read more

1 comments, 184 reads

Posted in SQLStudies on 19 June 2017

Turning ANSI_PADDING off, and why you shouldn’t

I ran into an interesting error the other day while doing a partition switch.

Partition switch failed because : column ‘xyz’ does not have the same ANSI trimming semantics in tables ‘a’ and ‘b’

It turned out it was because the ANSI_PADDING setting was different between the two tables (well,… Read more

3 comments, 1,827 reads

Posted in SQLStudies on 15 June 2017

Buzzword Bingo

Grant Fritchey (b/t) is our host for T-SQL Tuesday this month and surprise surprise he’d like to talk about DEVOPS. (As he put it, it was either that or execution plans.) Now I’m not highly knowlegable about DevOps myself. I mean I have a general… Read more

0 comments, 488 reads

Posted in SQLStudies on 13 June 2017

UDP vs TCP

If you’ve worked much with named instances you’ve probably had to deal with the question “What port is that instance on?”. You probably are aware that the default instance uses port 1433. You might also be aware that the browser uses port 1434. What’s the browser I hear you… Read more

3 comments, 281 reads

Posted in SQLStudies on 7 June 2017

Backup to NUL

Yes, I spelled that correctly. NUL not NULL. NUL is basically a location you can send a backup to. In fact, one of the awesome bonuses of this location is that your backup will take up exactly 0 bytes of space. You got it, absolutely none! Now before you… Read more

6 comments, 144 reads

Posted in SQLStudies on 5 June 2017

SQL Crossword

I thought I’d do another crossword. Enjoy! If you need any help with it feel free to let me know

Across
4. Traditional form of an index.
6. Encrypt a column without revealing the encryption keys to the Database Engine.
8. Feature used to limit the amount of CPU,… Read more

7 comments, 2,086 reads

Posted in SQLStudies on 31 May 2017

Running multiple queries at the same time.

I read an interesting question today. Someone wanted to be able to run the same stored procedure multiple times, at the same time, but with different parameters each time. One way to do it would be to open multiple query windows, and run them all as close to at… Read more

8 comments, 2,425 reads

Posted in SQLStudies on 24 May 2017

What’s in a partition?

In my last post on partitioning I used the $Partition command in passing. I’ve been thinking it deserves a bit more attention.

So what does it do? Per BOL

Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function in…

Read more

0 comments, 159 reads

Posted in SQLStudies on 22 May 2017

T-SQL FizzBuzz

I was looking around for something to write about this evening and came across one of Russ Thomas’ (b/t) old monthly challenges (Feb 2016).

The challenge is two-fold. A FizzBuzz problem is a common programming interview challenge that asks a coder to print the numbers from…

Read more

10 comments, 2,411 reads

Posted in SQLStudies on 17 May 2017

Why shouldn’t I shrink my database log file?

TL;DR: It’s pretty pointless and can cause performance issues.

Let’s start by asking why you might want to shrink your log.

It’s too big
I find that people who say this frequently don’t have a firm idea of what is too big or even why it might be as big… Read more

1 comments, 186 reads

Posted in SQLStudies on 15 May 2017

Dynamic SQL and the joys of QUOTENAME

I’m a big fan of dynamic SQL. In the past I’ve written a How to, a Best Practices and even a Generic Dynamic SP although that last one was a bit so so in my opinion. Depending on what type of dynamic code you are writing QUOTENAME will be… Read more

5 comments, 235 reads

Posted in SQLStudies on 11 May 2017

Older posts