Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

Backup before you restore

You should always be recoverable. If you haven’t heard that before, take note of it. It’s important.

A while back I wrote about Operational Recovery. This is when there isn’t a disaster but you still need to restore some or all of a database. Someone forgot the WHERE clause… Read more

2 comments, 83 reads

Posted in SQLStudies on 22 April 2015

The hidden security of msdb

To be fair it’s not actually hidden, just not so well known.

Have you ever wondered how to grant permissions to create/modify jobs? How about SSIS packages?

Every database has the following built in roles:

  • db_accessadmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_ddladmin
  • db_denydatareader
  • db_denydatawriter
  • db_owner
  • db_securityadmin

 
However the msdb database has… Read more

3 comments, 138 reads

Posted in SQLStudies on 20 April 2015

Adding, altering or removing multiple columns from a table

I was recently asked to do some fairly heavy modifications to a table. It involved adding 6 new columns, removing 4 old ones, renaming 4 and then altering the datatype for those 4. I find these kinds of requests rather entertaining in a simplistic sort of way. I try to… Read more

2 comments, 125 reads

Posted in SQLStudies on 16 April 2015

Remotely shutting down an instance

This month for T-SQL Tuesday Mike Donnelly (b/t) decided to pick a topic to fit in with Ed Leighton-Dick’s (b/t) #SQLNewBlogger challenge. His highly open ended topic is to learn something new and blog about it. A great topic for a… Read more

0 comments, 2,181 reads

Posted in SQLStudies on 14 April 2015

Make sure you back up the Service Master Key

I’m by no means an expert in SQL Server encryption. What I do know however, is that the Service Master Key is the top of the encryption chain on an instance. This means that any certificate or key will be encrypted using, in part, the Service Master Key. So if… Read more

3 comments, 120 reads

Posted in SQLStudies on 8 April 2015

What is a CTE

CTEs (Common Table Expressions) are one of the most interesting and useful tools added to T-SQL in the last decade. But even though they have been around for that decade and are widely used I still find that they confuse people somewhat. Among other things this confusion leads… Read more

1 comments, 120 reads

Posted in SQLStudies on 6 April 2015

Microsoft buys the dictionary

It seems that Microsoft has finally gotten tired of all the jokes about the word “performant” or using “ask” as a noun. Microsoft has decided to put it’s money where it’s mouth is and has announced today that they have purchased both Websters and Dictionary.com. Now why would they do… Read more

3 comments, 131 reads

Posted in SQLStudies on 1 April 2015

Port numbers and SQL Server

I made a mistake the other day. I’ll admit it. I was wrong. Now I know all of you are shocked, but it does happen on rare occasions. So what was I wrong about you might ask? One of the dev groups I support has been referring to their instances… Read more

0 comments, 6,117 reads

Posted in SQLStudies on 30 March 2015

Clean out all bad characters from a string.

I have a customer who is having a problem with a load. He is getting a fair number of bad characters including some unicode characters. The unicode characters in particular are making it so those values won’t go into a varchar column. This isn’t an uncommon problem so when he… Read more

10 comments, 157 reads

Posted in SQLStudies on 25 March 2015

0 to Speaker

Recently the last day to submit a session for the 2015 Pass Summit rolled around. I mention this because I actually submitted a session. This is the first time I’ve ever submitted something for the summit and only the third time I’ve submitted a session anywhere. And no, in neither… Read more

10 comments, 133 reads

Posted in SQLStudies on 23 March 2015

Why not NOLOCK?

Most senior DBAs I’ve met shudder when they hear NOLOCK. Ever wonder why? For the same reason they shudder at shrink, MAXDOP of 1 and even occasionally at UDFs (user defined functions). Because frequently we see cargo cults develop around these technologies. Cases where a group of IT professionals (developers… Read more

4 comments, 200 reads

Posted in SQLStudies on 18 March 2015

There are #temp tables and then there are ##temp tables.

Temporary tables are a common enough thing in T-SQL that most of us have probably used them on a fairly regular basis. What I don’t think a lot of people know is that there are two types of temporary tables.

  • Local Temporary Tables
    SELECT * INTO #LocalTempTable FROM sys.databases
  • Global…

Read more

0 comments, 135 reads

Posted in SQLStudies on 16 March 2015

Using Solutions in SSMS

There are a handful of scripts I use on a regular basis. Adam Mechanic’s (b/t) sp_WhoIsActive, My sp_DBPermissions and sp_SrvPermissions, my script for finding where backups are taken, Paul Brewer’s sp_RestoreGene to name a few. It gets tiresome at best to constantly be looking… Read more

3 comments, 279 reads

Posted in SQLStudies on 11 March 2015

Who’s using that database?

A common problem when trying to alter a database (take it offline, add a filegroup, whatever) is that someone else is in the database and you need to find them and kick them out before you can proceed. Historically I would use sp_who and scroll down the list looking for… Read more

0 comments, 6,445 reads

Posted in SQLStudies on 9 March 2015

I want to blog but …

I don’t know how to get started.

Pretty simple. There are a number of free blogging sites out there. Personally I use WordPress. All you need to do is go to http://www.wordpress.com and sign up for a free space.

I have a blog but setting it up seems like a…

Read more

0 comments, 168 reads

Posted in SQLStudies on 4 March 2015

SQL Judo’s Monthly Challenge – March 2014 – Powershell something

Every month SQL Judo (Russ Thomas) (b/t) challenges us to do his Monthly DBA Challenge. I’ve decided it would be fun (and good practice) to do them. Another major benefit is that it will force me to move out of my normal comfort zone. For… Read more

0 comments, 180 reads

Posted in SQLStudies on 2 March 2015

Using sp_DBPermissions and sp_SrvPermissions v6.0

It’s been a while since I posted an updated to usp_DBPermissions and usp_SrvPermissions. Sorry. I got lazy with my posting.

Changes

sp_SrvPermissions
— V5.5
— 7/22/2014 – Changed strings to unicode
— V6.0
— 10/19/2014 – Add @UserLikeSearch and @IncludeMSShipped parameters.

sp_DBPermissions
— V5.5
— 7/15/2014 – Bunch of changes… Read more

1 comments, 145 reads

Posted in SQLStudies on 25 February 2015

Tales of a DBA fed up with NOLOCK. Part 2

Feb 16, 2015
Dear Diary,
A few weeks back I introduced HAL002 to a database managed by some annoying devs. They were not just putting NOLOCK on almost every query but they had SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the beginning of the stored procedures. HAL’s done his job… Read more

6 comments, 6,480 reads

Posted in SQLStudies on 23 February 2015

Test your UPDATEs and DELETEs before you run them

This isn’t so much of a best practice, it comes more under the heading of being careful. Let’s say you have a DELETE statement (or UPDATE) that you need to run. You can certainly put it in a transaction. Run it, test it, then if you’ve made a mistake roll… Read more

10 comments, 1,553 reads

Posted in SQLStudies on 18 February 2015

Tales of a DBA fed up with NOLOCK. Part 1

Nov 12, 2014
Dear Diary,
They asked for help again. Every now and again everything slows down and they see a lot of timeouts. They have tried rebooting the SQL instance and that fixes the problem but it’s only a temporary solution. Of course last time they asked for my… Read more

10 comments, 7,898 reads

Posted in SQLStudies on 16 February 2015

Older posts