Blog

Adventures in @BlobEater, sp_WhoIsActive and DBCC CHECKDB

(Originally posted on my personal blog.)

The latest version of sp_WhoIsActive (and apparently last for SQL Server 2005/2008, according to Adam Machanic), was released a week ago.

Yesterday, out of curiosity piqued by my recent three-week SQLskills training, I decided to run sp_WhoIsActive while doing a DBCC CHECKDB on a moderately large database (mainly because it takes longer, so it’s easier to poll). The version was SQL Server 2008 R2 with Service Pack 1 (10.50.2500).

Referring to my notes of how CHECKDB works, I was excited to find sp_WhoIsActive capturing one of the queries that runs during the fact-checking phase:

DECLARE @BlobEater VARBINARY(8000)

SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB)
FROM { IRowset 0x30FAC98600000000 }
GROUP BY ROWSET_COLUMN_FACT_KEY
>> WITH ORDER BY
ROWSET_COLUMN_FACT_KEY,
ROWSET_COLUMN_SLOT_ID,
ROWSET_COLUMN_COMBINED_ID,
ROWSET_COLUMN_FACT_BLOB
OPTION (ORDER GROUP)

I thought this was cool. Knowing how CHECKDB checks that the relationships are consistent in theory is one thing; seeing it in practice is something better. I’ve often found myself preferring an example to demonstrate a theory, to improve my understanding of that theory. Hopefully this will be helpful to you as well.

I also love the name “BlobEater”.

EDIT: I also ran across this query during the Update Statistics phase of a maintenance plan:

SELECT StatMan([SC0], [LC0])
FROM (SELECT TOP 100 PERCENT SUBSTRING ([object], 1, 100)++substring([object],
case when datalength([object])<=200 then 101 else datalength([object])-99 end,
datalength([object])) AS [SC0],
datalength([object]) AS [LC0]
FROM [dbo].[activitylog] WITH (READUNCOMMITTED)
ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL

Increasing SQL Server Error Logs

(Originally posted on my personal blog.)

Via Tim Radney, here’s a quick and easy way to increase the number of error logs from the default of six.

His blog has a walkthrough in SQL Server Management Studio if you prefer the GUI.

Using TSQL you can execute the following statement to increase to 99 files, simply change 99 to how ever many files you would like to retain.

USE [master];
GO


EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 99;
GO

What I did today

(Originally posted on my personal blog.)

Today at the clinic, we did the second of three phases of network reorganisation. In the first phase, which we completed a week ago, our new server was set up with the EMR software, all data was migrated from the old server, and the vendor remoted in to make sure everything was up and running.

Phase two, which took place today, was the physical move of the server into the rack, along with moving the network patch panel and switch into the same rack. We delayed this because we wanted to get in the local network cabling company, who are really good at this sort of thing. They, along with the office manager, built a platform for the new cabinet, because we were all concerned about the weight of the thing against the wall. In any event, the cabinet isn’t going anywhere, and the server is locked safely inside now.

Phase three is to replace the existing D-Link router with a new SonicWall device, and then bridge our existing network wirelessly, to connect a new satellite clinic across the parking lot. It would have been a lot more expensive to run network cable (due to the weather conditions in town, going by the frost line, replacing the asphalt in the parking lot, etc.), so we got two EnGenius external wireless routers, and set one of them up as a Wireless Access Point, with the other being the Client Bridge connecting the satellite clinic.

We’ll only finish this third phase next week, but everything is configured, and the SonicWall should be a drop-in replacement for the D-Link. We anticipate a couple of problems because the DHCP range has been reduced, but nothing that a reboot on the clients can’t handle.

In any event, I spent about two hours setting up the notifications on the server, after it was moved into the new cabinet. There were two things I wanted notifications on: the RAID status, and SQL Server.

I played around briefly with using an external SMTP server, and then gave up and installed SMTP Server on the Windows box. After locking the SMTP up securely, I then enabled the email notifications in the RAID software, which for some reason didn’t want to send.

It turns out that it’s not a good idea to masquerade a domain you’re targeting that sits outside of the network (our email and email runs offsite). The SMTP service simply dropped the test email in the Drop folder and that was that.

So instead of reconfiguring everything again on the SMTP Server, I simply changed the target email addresses to my home email address. So now the RAID and SQL Server will notify the office manager and me if there’s a problem.

The SQL notifications I set up, per Paul Randal’s recommendations (here and here), were:

– Errors 823 and 824, for hard I/O errors;
– Error 825, for transient I/O errors (and which he calls “impending doom”);
– Severity errors 19 through 25.

As pointed out in his script, I’ve also enabled the error message to be attached to the notifications.

The other thing in SQL Server I wanted to be notified about, is failed maintenance jobs. At every step in each of the four maintenance plans, I added a Notification Task to fire on each failure, with emails to be sent to me and the office manager. This was a lot more time-consuming than the other stuff, because I wanted to keep everything consistently named, and the emails to look the same. It takes longer at this stage, but it will save us a lot of time in troubleshooting any errors later.

Link 64-bit SQL Server 2005 to 32-bit SQL Server 2000

(Originally posted on my personal blog.)

For those of you battling with a 64-bit SQL 2005 Server linking to 32-bit SQL 2000 Servers, here’s a handy solution. This problem has been plaguing us for a while now, and today I made it work, thanks entirely to uber1024.

Add this to your SQL Server 2000 instance:

USE master
GO

CREATE PROCEDURE sp_tables_info_rowset_64 (
    @table_name SYSNAME,
    @table_schema SYSNAME = NULL,
    @table_type NVARCHAR(255) = NULL)

AS

DECLARE @Result INT
SELECT @Result = 0
EXEC @Result = sp_tables_info_rowset
    @table_name,
    @table_schema,
    @table_type

GO

GRANT EXECUTE ON dbo.sp_tables_info_rowset_64 TO [public]

GO