T-SQL Tuesday #193 – A Note to Your Past, and a Warning from Your Future

~ Jeff Iannucci ~ Leave a comment

I haven’t posted in a while (well, not here at least since I’ve been posting at the Straight Path Solutions site recently), but I did want to reply to the latest T-SQL Tuesday call. This month’s edition is hosted by Mike Walsh, who asks folks to write two short but thoughtful notes to yourself: one to your past self of 10 years go, and one from yourself 10 years in the future.

One quick note: am I writing this to myself? Absolutely. But hopefully these words resonate with others not as far down my path, as I’ve learned these truths the proverbial “hard way”.

Continue reading “T-SQL Tuesday #193 – A Note to Your Past, and a Warning from Your Future” →

What I’ve learned in my first 100 days with a consulting group

~ Jeff Iannucci ~ Leave a comment

In case you don’t follow me on LinkedIn or Twitter, your humble host joined the smart folks at Straight Path Solutions in March. This is a talented group of SQL Server consultants founded by Mike Walsh and includes several former Microsoft MVPs, and I’m incredibly honored to be part of their team.

Especially since I didn’t have much experience as a consultant.

Maybe you don’t either, which is why I wrote this post. If you ever wanted to know how consulting is different than a typical job as a fulltime DBA, I’ve assembled this brief retrospective of things I’ve learned so far.

Let’s get to it then.

Continue reading “What I’ve learned in my first 100 days with a consulting group” →

How to find tables with 0 records using space

~ Jeff Iannucci ~ 3 Comments

This is a follow up post to the previous one about tables with no records using data space, with the idea coming from twitter of all places. Sometimes on twitter the conversations kill, but after reading that post my friend Chris Wood suggested writing something to FIND those tables.

Which is a solid idea, so let’s get to it. No time to wait too long.

Continue reading “How to find tables with 0 records using space” →

How can a table with 0 records use so much space?

~ Jeff Iannucci ~ 2 Comments

Just like that puppy you got during the pandemic, your databases hunger for more. And they grow. And occasionally make a mess. I’ll stop the analogy.

Anyhow, it’s worthwhile to occasionally review the tables in a database to see which ones are growing every day, using the most space.

But what if during a review you see the largest table looks like this?

Image

That’s around 24 GB of sweet drive space allocated for 0 records. But…how?

Let me show you how.

Continue reading “How can a table with 0 records use so much space?” →

How to get Change Data Capture to work in an Availability Group

~ Jeff Iannucci ~ 7 Comments

If you’ve ever had to implement Change Data Capture (CDC) for a database in an Availability Group, then you know that the CDC jobs don’t really consider the Availability Group. The capture and cleanup jobs created are set up as if the database exists only on a single instance.

And that’s a problem, because I would guess quite lot of databases are in Availability Groups. Maybe even some of yours. If you have this issue, I’ve put together a step-by-step solution in this post.

I know this post is a little long compared to others here, but I’ve tried to be thorough to save you headaches.

Continue reading “How to get Change Data Capture to work in an Availability Group” →

sp_GetRowcount: How to count the number of rows in any SQL Server table fast

~ Jeff Iannucci ~ 4 Comments

Have you ever had to find the number of rows in a user table, and then wrote a little “SELECT COUNT(*) FROM tblWhatever” and hit execute…and waited…and waited…and waited some more? And then started wondering what was going on?

If so, this post is for you.

Let me explain the problem, and then give you a reusable solution. Consider this a turning point in your career.

Continue reading “sp_GetRowcount: How to count the number of rows in any SQL Server table fast” →

How to tell if Change Data Capture (CDC) is working as expected

~ Jeff Iannucci ~ 8 Comments

I’m not sure how many of you use Change Data Capture (CDC) on your instances, but I’ve had to support it for a while now and I thought I’d share a little script I use fairly frequently to help troubleshoot the capture and cleanup, as well as some advice for resolving issues.

Sharing is such a gentle trait.

Continue reading “How to tell if Change Data Capture (CDC) is working as expected” →

#TSQL2sday – Preconceived Notions About Who Is Smart

~ Jeff Iannucci ~ Leave a comment
Image

For this month’s #TSQL2day, Andy Yun asked folks to share about “something you’ve learned, that subsequently changed your opinion/viewpoint/etc. on something.”

Now, there are technical subjects I could share where I’ve changed my opinion, but in thinking about possible answers I realized all of them start with one thing that has changed: who I thought was “smart”.

I put the word smart in quotes, because it’s a totally subjective and ever-changing opinion. Let me show you what I mean.

Continue reading “#TSQL2sday – Preconceived Notions About Who Is Smart” →

What permissions are required for temporary tables?

~ Jeff Iannucci ~ 2 Comments

Managing permissions is a constant issue for Database Administrators, but rarely do DBAs consider permissions for tempdb. Everybody’s looking for something, but how often do you get requests for “access to read and write in the tempdb database”? Like…never?

OK, but what if you were asked the subject of this post in a job interview? Even if you’ve worked with SQL Server for ages, would you know how to answer this? Moreover, would you know why the answer should give you some concern?

Hold your head up, because I’ll start answering these questions now.

Continue reading “What permissions are required for temporary tables?” →

TempDB configuration for people in a hurry

~ Jeff Iannucci ~ 3 Comments

If you’ve ever had to play administrator to a SQL Server instance, you’ve probably had to deal with TempDB data or log files that have grown unexpectedly. I know a lot of folks have had to do this, because the most read post on this site is “Help! My tempdb database won’t shrink!”

Consider it a right of passage of sorts. Also, consider that it might be entirely avoidable. Let me show you.

Continue reading “TempDB configuration for people in a hurry” →