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
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
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
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.
The first thing you have to do is make sure that the scan for startup procs configuration is set to 1. Read more
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
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
tl;dr; Re-start the instance in safe mode (-f startup parameter) and move tempdb. Then re-start without the parameter.
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
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
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
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
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
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
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
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
You can use them without a parameter to return the name or id of the current database context. Read more
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
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
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
has decided to combine the two. With this month’s subject Naughty or Nice he’d like… Read more