Hello, this is Kevin Kline - join me tomorrow, along with Quest Domain Expert Brent Ozar and SQL Server MVP Ron Talmage, for a roundtable discussion on SQL Server consolidation and virtualization.  We’ll discuss a variety of issues that seem to come up constantly in the discussion forums - How are your consolidated / virtualized SQL Servers going to be used?  Are they running production environments with strict SLAs and heavy workloads?  Do SQL Server licensing costs make a big difference for your organization?  What about SQL Server management costs?  Will performance troubleshooting be an issue?  

The webcast is tomorrow, Thursday July 30th, at 8:00 AM PST / 11:00 AM EST.  Register for the webcast here.

I hope to see you there tomorrow!

We got a question about the best practices way to truncate their SQL Server log files, and that kinda scares me.

It’s somewhat akin to asking, “What’s the best way to cut my hand off to free myself from this bear trap before I starve to death in the wilderness?”  Well, you shouldn’t be sticking your hand in bear traps to begin with, but if you find your hand in a bear trap, ANY way to get out of it is a good way.  Pocket knife, teeth, band saw, whatever it takes.  (Now there’s a sunny image to start the day.)

When people ask about truncating their log files, what they usually mean is, “We don’t have enough space on our log drive, and I need to free it up often.”  This happens in systems where folks didn’t get buy enough space for their log files, and they have to repeatedly truncate their logs or else their system will run out of space.

If we’re talking best practices, then really the answer is not to truncate the logs, period.  Avoid that by:

• Increasing the size of the log drive.  This is the easy answer, and it’s the best long-term answer, but sometimes we don’t have the money to do this, or there’s no space left for drives in the server or the SAN.
• Add an additional drive for logs, and either move some of the database log files there or add secondary log files on those drives.
• If the transaction logs truly don’t matter, then change the databases to simple recovery mode so they don’t need to be backed up.  The log files won’t keep growing (unless there’s a monster transaction).
• Do transaction log backups more frequently (maybe we’re doing t-logs every hour, and need to be done every 15 minutes).  I’m not wild about this answer because at some point, your log backups will fail for some reason (like your backup drive ran out of space) and your log drive will still fill up.

If you do find yourself in a bear trap with a full transaction log drive, then the fastest way to get out of it is to run a query manually via T-SQL – forget LiteSpeed.  Just use the native command:

BACKUP LOG [DatabaseName] WITH TRUNCATE_ONLY

However, like cutting your hand off, that has some really nasty implications – you lose your ability to do a point-in-time restore once you do that.  Do a full backup as soon as possible afterwards, and then resume normal transaction log backups.

Update 8/14/2008 - Paul Randal wrote a good blog post on what to do when your transaction log fills up.

Although I blog at least once a week on average, I’ve always been the sort of blogger who spends a lot of time thinking about my blog posts.  In effect, I’ve treated them like small magazine articles in which I write up the blog post, reread it, tech edit it, noodle on it some more, and finally post it.  But that takes a long time.  And, after much thought, I’m not sure that it dramatically improves either the efficacy of the information or is the best use of my time.

So, I’ve been thinking about shifting gears on blogging into the “near Twitter” realm of blogging rapidly and without too much emphasis on clean grammar, well-structure paragraphs, and the like.

What do you think?

Is more content better?  Or is better content more important?

Thanks,

-Kev

Basically, you want to create a distributed query. This is a very common, and relatively simple process from a SQL Server standpoint. From the SQL Server side you can add a linked server then access the external data by using OPENDATASOURCE.

Here’s a good start-to-finish walkthrough entitled Configuring a Linked Microsoft Access Server on SQL 2005 Server that should help in your particular case.

SQL Server Reporting Services (SSRS) will attempt to determine whether the data in the ChunkData table is still needed all by itself. If you review the error log, you should see messages where the SSRS engine is attempting to purge the data by itself using the following query:

DELETE ChunkData FROM ChunkData INNER JOIN #tempSnapshot ON
ChunkData.SnapshotDataID = #tempSnapshot.SnapshotDataID

I would venture a guess that the following error is being thrown:

ERROR: Sql Error in
CleanOrphanedSnapshots: System.Data.SqlClient.SqlException: Timeout
expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.

If that is the case, you should monitor that instance to see whether there are blocking locks or other performance bottlenecks that are stopping the purge and tune accordingly.

Before I begin, I want to mention that there is a wealth of information on this topic on quest.com including the Don’t Hate - Consolidate and How Do I Know When to Defragment My Database? webcasts, the Tips on SQL Server Consolidation podcast and more.

Consolidation projects should answer the following questions (and this is not intended to be a complete list):

1. What is the deadline for the consolidation?
2. What is involved in the consolidation?
3. What are the performance characteristics of the environments you’re consolidating?
4. What are the growth trends of the environments?
5. What kind of maintenance windows will you have once you’ve consolidated?

What is the deadline for the consolidation? How much time do you have? This information will drive how you proceed. Ideally, you’ll want a minimum of 3-6 months to determine all the characteristics of both the source and the target environments so you can accurately determine the nuances of all of the environments involved. Information is your best friend here, but be aware that the information is only as good as your ability (read: time and resources) to process it.

What is involved in the consolidation? When you look to consolidate SQL Server environments, you have to bear in mind that you’re dealing with more than just the SQL Server instances and their databases. There are always a number of people and applications that rely on an instance of SQL Server, including websites, internal applications, third-party tools, and existing maintenance plans to name a few. The best way to determine what uses the SQL Server is to set a period of time when that SQL Server environment will be monitored to determine exactly who or what uses the SQL Server that will be moved or migrated before the project is undertaken. You’re going to need time to plan for outages, to schedule migrations and to simply communicate the venture to all of the appropriate parties.

What are the performance characteristics of the environments you’re consolidating? If you consolidate high-traffic environments, its possible that you’ll be creating performance bottlenecks where there weren’t any before. Again, your best defense here is to gather enough data about those environments to ascertain these types of issues. Also, what about the hardware on the consolidation target? Having good, thorough performance data that includes the specs of the hardware you were monitoring is critical in defining the subsystem of the consolidation target environment. And crunching numbers is just the first step here - once you have the hardware configured, you’d be well-advised to stress test that environment, preferably using real databases and real application code. There are some good products in the marketplace from Microsoft, Quest and others that will help you simulate user loads using the application SQL you captured during your monitoring period.

What are the growth trends of the environments? Whenever you deal with a SQL Server database, you’ll inevitably have to deal with database growth trends. Are you still allowing data and log files to auto-grow? This could pose a fatal problem in a consolidated environment where it was just a periodic performance-killer in the past. The 3-6 month monitoring period I suggested should also be used to determine, pretty accurately if you’re allowing that much time, how your databases grow. Whether you use complex algorithms or simple linear plotting, having an idea of your consolidated environments disk storage needs will help you order the right hardware and save a lot of time and hassle with Systems Administrators, Storage Administrators and management in the future.

What kind of maintenance windows will you have once you’ve consolidated? Database backups, index maintenance, ensuring up-to-date statistics, and maybe creating data-marts are activities all DBAs are familiar with. But even though a DBA is familiar with maintenance plans, there is usually some degree of superstition or other “don’t touch it unless something breaks” attitude in every environment. Well, when you’re gearing up to perform a consolidation you’d better get familiar with what’s running and when. Conflicting backups are just one of many issues you could run into. The monitoring you’re performing will tell you when different activities are being performed and should expose when certain additional or different maintenance windows will be available. If you realize you won’t have time for maintenance under the current application and environment settings, you’ll need to adjust your consolidation plan because this is definitely a step you can’t ignore. Missing a backup is something every DBA understands the criticality of, but not maintaining indexes or statistics can ultimately lead to decreased performance, throughput and even to shorter maintenance windows.

In summary, you need to plan, monitor, diagnose, and test before you start consolidating environments. Good communication is important throughout the process, both to ensure that everyone that needs to be in the loop is, in fact aware, but also because this is one of the best ways to expose potential problems before they arise. I hope this helps!

Detective By Accident - that’s what DBA really stands for, not Database Administrator.  Come on, if it was really Database Administrators, we’d be DA’s.  Granted, most of us don’t look like Warrick Brown or Sara Sidle, but when something disappears in the datacenter, people pick up the phone and call us.

On July 24th, Jason Hall and I are doing a webcast called CSI:DBA - Going Back in Time to Understand What Happened to Your Data.  We’re going to cover how to find out who made schema changes, who deleted/truncated tables, and how to get the data back without time-consuming full restores.  I’ll be talking through some of the native SQL Server tools to do it, and Jason will be showing off the Quest tools that make the job easier.

Performance Dashboard Reports

These say Performance in the name, so it would sound like they’re not the right answer to this problem, but it turns out there’s a great report in there to address this very issue.  The Schema Changes History report shows what logins made schema changes in each database, and when the change was made.  Here’s an example:

SQL Server Performance Dashboard

These reports run off the default server-side trace that runs all the time in SQL Server 2005 and 2008, so there’s no additional performance impact in order to capture this information.  These reports are included with Microsoft SQL Server 2008, and for 2005, they’re a separate download - download the Performance Dashboard Reports.

These reports have been part of the solution in all of my recent webcasts.  I’m a big believer that every DBA needs to have these installed and play around with them because they’re so useful.  I was just giving career advice to a DBA yesterday: he specialized in writing reports, and I said if there’s one type of report to produce, it’s RDLC reports that integrate with SQL Server Management Studio.

That’s a great start - it does tell us who did it and a little bit about what they did - but it only works with SQL 2005 & 2008, and it doesn’t give us exactly what their statement was, and it doesn’t give us anywhere near enough to roll back their devious work.  We need more information, so let’s get the toolbox out of our Hummers and check out our next tool.

Database-Wide DDL Triggers

SQL Server 2005 introduced DDL triggers that can be fired off whenever someone runs Data Definition Language code like altering a table, adding an index, or dropping a view.

People have written DDL triggers that log schema changes to a table - here’s one example of auditing DDL statements in a table, and here’s another.  I’ve used some of this code myself to track schema changes, and I do love the results:

DDL Trigger Results

I get a nice table with who the guilty culprit is and I get their exact SQL statement.  That’s a step in the right direction, because I can print it out and take it with me into the interrogation room.  (Every DBA has an interrogation room, right?)

That gets us our confession and our guilty verdict, but it doesn’t get us back to how things used to be.  For that, you’ll need to tune into our next episode - I mean, webcast!

A lot of people sent me emails asking for a way to get ahold of Quest Software’s new DMV poster.  The poster shows all of the major DMVs in SQL Server 2005/2008, what they’re used for, and how to query them.

You can get the poster (for free, of course) by signing up for and attending the accompanying webcast about DMV’s that I’m doing next Thursday, July 10th.  If you don’t know much about Dynamic Management Views (DMVs), then you’ll want to attend this informative webcast with me and my PASS cohort Tom LaRock.  Get all the details about the webcast at http://www.quest.com/backstage/pow.aspx.

I hope this helps,

-Kevin