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.

Changing to simple recovery to clear the log.

Clearing out a full transaction log is a common problem. A quick search will find you dozens of forum entries and blog posts. Because of that I’m not going to talk about the correct methods of dealing with a transaction log full error. What I want to discuss is why… Read more

3 comments, 63 reads

Posted in SQLStudies on 10 February 2016

Impersonation can give you the wrong answer

I use impersonation all the time to let me see additional permissions information I can only get that way and, sometimes to help me see what permissions error someone is getting.

For those of you who aren’t aware, with the right permissions you can impersonate another principal (server or database)… Read more

1 comments, 112 reads

Posted in SQLStudies on 8 February 2016

Blogger questions: What if someone else wrote about the same subject?

I have a moderately popular blog. In part because I syndicate but largely (in my opinion) because I write a lot of posts (twice a week for > 3 years). Over 340 of them in fact. Because of this a friend asked me what I do if I write a… Read more

6 comments, 137 reads

Posted in SQLStudies on 3 February 2016

Running code on the startup of SQL Server

Every now and again you need to run code when your instance starts up. For example you might want to check if the SQL Agent is running.

Startup Procedures

The first thing you have to do is make sure that the scan for startup procs configuration is set to 1. Read more

1 comments, 903 reads

Posted in SQLStudies on 1 February 2016

The CONNECT permission

The CONNECT permission exists at the instance and database levels. Note: as of SQL 2012 it is CONNECT SQL at the instance level, but prior to that it was just CONNECT. It does basically the same thing in both cases. The CONNECT permission allows you to connect to the associated… Read more

4 comments, 995 reads

Posted in SQLStudies on 27 January 2016

Reusing schedules in SQL Agent

I’m a big fan of automation. You get it written, tested, and scheduled, then you just sit back and monitor for unexpected problems (because if they are expected you fixed them right?). One common place for us SQL people to do our automation is SQL Agent. It’s built into SQL… Read more

1 comments, 897 reads

Posted in SQLStudies on 25 January 2016

Start SQL Server without tempdb

tl;dr; Re-start the instance in safe mode (-f startup parameter) and move tempdb. Then re-start without the parameter.

While at the 2015 Summit I walked into the last few minutes of Russ Thomas’s (b/t) session on Stress Inoculation: Maintaining Performance Under Pressure. As a side… Read more

1 comments, 134 reads

Posted in SQLStudies on 20 January 2016

To the page and back again

To the page

It’s not one of those things you have to do frequently but every now and again you need to know what page a particular row is on. It’s not terribly difficult. There is a virtual column called %%physloc%%, but unfortunately it’s not terribly useful on it’s… Read more

1 comments, 118 reads

Posted in SQLStudies on 18 January 2016

What is RESULT SETS?

I was reading some code the other day and it included the statement RESULT SETS. I’d never seen it before so it seemed worth a quick look. I’ll warn you in advance this is somewhat of a BOL blog post. I’m basically taking what you can find in BOL… Read more

11 comments, 1,869 reads

Posted in SQLStudies on 14 January 2016

Ad-hoc Changes

TSQL Tuesday #74: Be The Change

Each month, on the first Tuesday of the month, the announcement for the blog party T-SQL Tuesday comes out. Those that are interested then post their blogs, on the subject selected, on the second Tuesday of the month. If you’ve never heard of T-SQL… Read more

4 comments, 981 reads

Posted in SQLStudies on 12 January 2016

MSDTC Requirements for SSIS Transactions

MSDTC is frequently required when using transactions within SSIS. And not just when you are connecting to two SQL Server instances.

Disclaimer: Neither SSIS nor MSDTC are my strongest skills. So while the information here is correct (to the best of my knowledge) I won’t make any claims as to… Read more

1 comments, 1,922 reads

Posted in SQLStudies on 6 January 2016

2015 a year in review

It’s a new year, so time to look back at the previous year, and forward to the next. I’ll tell you up front, this post is more for me than you. It’s a way to catalog my year and make some goals for the future. That being said I’d of… Read more

7 comments, 113 reads

Posted in SQLStudies on 4 January 2016

Do you check for TRUSTWORTHY databases as part of your security audits?

I ran a twitter poll the other day Do you check for databases with trustworthy turned on when you do security audits?

I wasn’t surprised (although a little disappointed) that out of the 9 people the answered only one person was, and of the rest 5 didn’t even know what… Read more

7 comments, 1,647 reads

Posted in SQLStudies on 30 December 2015

What does that N in nvarchar really mean?

In any study of Data Types in SQL Server you are going to have to look at the various string data types. One important component is the difference between nChar vs Char and nVarChar vs VarChar. Most people reading this are probably thinking “Well that’s ridiculously easy.” If you are… Read more

9 comments, 1,338 reads

Posted in SQLStudies on 28 December 2015

‘Twas 4:45 Friday

I couldn’t think of anything really fun to post for Christmas Eve-Eve and I’ve been busy buying and wrapping presents so here is my Christmas post from last year.  Enjoy :)

‘Twas 4:45 Friday, and all through the office
not a creature was stirring, not even the bosses.
The code… Read more

0 comments, 186 reads

Posted in SQLStudies on 23 December 2015

What is the current database context?

Every now and again it can be very helpful to know what the current database context is when you are writing T-SQL code. It’s actually pretty simple. Use DB_Name() and/or DB_ID().

You can use them without a parameter to return the name or id of the current database context. Read more

3 comments, 248 reads

Posted in SQLStudies on 21 December 2015

Scripting out multiple scripts from the Object Explorer Details window.

A few weeks ago I was reading a blog post by Andrea Allred (b/t) and was just amazed by what I was reading. I’ve never been a big fan of the Object Explorer Details window. It didn’t seem to really add anything. But Andrea pointed out… Read more

5 comments, 175 reads

Posted in SQLStudies on 16 December 2015

Can we get a license for DDL Triggers?

There are several types of triggers.

  • Logon triggers – Fired when someone tries to connect to the instance.
  • DML triggers – Fired after a DML statement. INSERT, UPDATE and DELETE statements.
  • DDL triggers – Fired after a DDL statement. For example CREATE, ALTER, DROP, GRANT, DENY, and REVOKE.

 
All… Read more

1 comments, 1,241 reads

Posted in SQLStudies on 14 December 2015

Using unique constraints

I ran into an interesting problem the other day. Given the title of the post, obviously a unique constraint was involved, although I want to point out that the problem was not caused by the constraint in and of itself. It did get me thinking about this particular kind of… Read more

10 comments, 1,131 reads

Posted in SQLStudies on 10 December 2015

Apathy – A quick path to the naughty list.

It’s the Christmas season! And it’s T-SQL Tuesday! And Bradley Ball (b/t), SQL’s very own superhero (one of many really but he’s the only one I know who dresses the part)

has decided to combine the two. With this month’s subject Naughty or Nice he’d like… Read more

0 comments, 138 reads

Posted in SQLStudies on 8 December 2015

Older posts