Conference Questions Pot-Pourri #10: Shrinking the database before taking a backup

Here's a question I got from someone who attended our database maintenance workshop at PASS last week (paraphrased): I attended your pre-conference session on database maintenance and found it to be very informative.  From what you told use though, I think I need to change my nightly backup procedure.  I like to get my databases […]

Conference Questions Pot-Pourri #9: Q&A around compression features

Today's post is based on a bunch of questions I've had around the various compression features in SQL Server 2008. Does turning on data compression or backup compression compress the transaction log files on disk? No. The transaction log is not compressed in any way under any circumstances. Rows from tables and indexes that have […]

Conference Questions Pot-Pourri #8: How to move constraint indexes?

It's been a long time since the last Conference Questions Pot-Pourri – in fact it was at the last SQL Connections in Orlando in April. Now we're in Las Vegas doing SQL Connections Fall – Kimberly's lecturing for an hour on partitioning so I can get out a quick post. This is a question that […]

Conference Questions Pot-Pourri #7: How to create Agent alerts

Many times I've been asked to do a blog post about creating Agent alerts, and given that today I demo'd it as part of our Accidental DBA workshop at Connections, it seemed a good time to do the blog post too! I demo this in the context of alerting a DBA when an 823 or […]

Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session?

Here’s a question that came up yesterday in our chalk-talk on database mirroring at TechEd IT Forum that Kimberly and I talked about this morning (here in Barcelona). Q) I have a database mirroring session where the witness and mirror servers are in one physical location, and the principal server is in another. The mirroring session […]

Conference Questions Pot-Pourri #5: Database snapshots, and creating a snapshot of a single filegroup

One of the sessions we did yesterday at IT Forum was an Instructor-Led Lab on database snapshots, an Enterprise Edition feature of SQL Server 2005. Database snapshots are not particularly well known in the DBA community and there are many misconceptions about them. The session generated a bunch of questions, some of which I’ll answer […]

Conference Questions Pot-Pourri #4: How many databases can you really mirror per instance?

This is a question I was asked multiple times over the last week: the Microsoft guidelines for database mirroring say not to mirror more than 10 databases per instance – why is that and is it true? The answer is my favorite 'it depends!'. The number 10 is a rough guess at the sweet-spot for […]

Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations

A couple more questions from the last couple of classes. Q1) Why doesn’t performing an index rebuild alter the fragmentation? A1) Here are the possibilities – all of which I’ve seen happen: There isn’t an index – either DBCC DBREINDEX or ALTER INDEX … REBUILD are being run on a table that only has a heap, […]

Conference Questions Pot-Pourri #2: Database mirroring

  Sitting here in our Disaster Recovery class at SQL Connections and Kimberly’s on till lunch so I’m banging out a quick blog post covering the database mirroring (DBM) specific questions.   Q1) Can I use IP addresses instead of server names when using the DBM Monitor?   A1) Unfortunately not.   Q2) Is there […]

Conference Questions Pot-Pourri #1: Indexes, stats, corruption, and Enterprise-only features

As promised, here’s the first of the grab-bag of questions we were asked during conferences. I’m blogging a selection of the stuff I noted down. These are some of the questions we were asked during our pre-con at SQL Connections on Database Maintenance: From Planning to Practice to Post-Mortem. It was cool that people came prepared […]