Previously on Log Buffer: Log Buffer #119.

And now.

Welcome to Log Buffer #120. My name is Warner, and I’m a SQL Server DBA at The Pythian Group. This is my first time on Log Buffer duties ever, so here’s hoping I can give everyone a fair and unbiased look at this week in the database blogging world (and related).

I admit I had no idea of the community or state of the PostgreSQL RDBMS, and so I definitely learned some new stuff this week. First off, over on “The Scale-out Blog” Robert Hodges invites us all to get our shoephone and get smart about the new world of PostgreSQL replication.

Moving over to “ad’s corner”, Andreas Scherbaum gives us a glimpse of the glitz and glam of PGDay opening. Then he entices us with the title, “Party in the evening” just to horrify us by revealing that—you’d better sit down—by the end of the event half of the beer was still untouched. Next time Andreas, who you gonna call?

“Everything is a DNS problem,” is my new voicemail message, and also the title for Kris Buytaert’s blog, where we go deep into uncovering once again the 10 month-old enigma of why did Sun buy MySQL.

Next up, I will speak to you, Mr. (or Ms.) Cross-Platform DBA. You think you know all of Oracle’s exp command-line switches? All of SQL Server’s bcp formats? And all of the things you can do with . . .  whatever you use with DB2? (more…)

SQL Server 2008 is out of the bag and—luckily for my team—at Pythian we are already seeing customer interest in upgrading, even from SQL Server 2000 and 2005. There are many new features and there will definitely be more blog posts coming from the team regarding them, but for now, I was just browsing around the new 2008 Dynamic Management Views (DMVs), and did a quick overview of some that spiked my attention:

select * from sys.dm_db_mirroring_auto_page_repair

It looks like grouping by db or file id would be useful to zoom in on possible disk issues causing the page errors. Beware—only 100 rows are kept for any database, so if you want to keep a full history you should move those records on a scheduled basis.

select * from sys.dm_db_mirroring_past_actions

This one could be useful for monitoring when there is no witness on a mirroring setup; or for creating a mirroring history report.

select * from sys.dm_db_persisted_sku_features

This one should be used as a warning sign before moving databases between editions, specifically between Enterprise and Standard. Paul Randal has a great post on it: SQL Server 2008: Does my database contain Enterprise-only features?.

(more…)

The morning begins with this page: “a large number of sessions are blocked on one of your managed SQL Server 2005.” So you go and check out the Activity Monitor, and you can tell something unusual is going on:

Blocking chain of 200 procedures, your server is crawling with high CPU and requests are coming out a funnel. Now, don’t worry, the waitresource column provides us with the information to start zooming in on our problem. In this case, we have value “TAB: 7:357576312 [[COMPILE]]”. Disregarding the fact that the resource description says TAB, run the following query with the resource information to get the database and procedure being locked:

select db_name(7)

Use [PerformanceReports] -- the database name we got from the select above
GO

select object_name(357576312)

Knowing the procedure, it’s then a matter of discarding possibilities. The common causes of COMPILE locking are well documented on the Microsoft KB, “Description of SQL Server blocking caused by compile locks”, so if you have a chance, go on and read that. If you don’t, then this is the gist of it:

(more…)