RSS feed refresh – please back off a bit (Brazil and Korea)


Fixed now – thanks so much!


Folks – a couple of people have setup their systems to refresh *all* our category feeds every five minutes – this is putting an undue load on our server (and screws up our server stats). I know I post a lot but not *that* often. I’ve WHOIS’d the IP addresses – one person is in Brazil and another in Korea – I can’t limit how often you do this but I can ban your ISP’s IP addresses if you continue to put this load on us – which I don’t want to do. Please change your refresh rate to be 60 minutes or just subscribe to a whole blog feed rather than all the category feeds.


Thanks!

Chilling out in the Land of Fire and Ice


Hot on the heels of a frenzied few weeks of teaching for Microsoft and we’re off again – this time to Iceland! I’ve been wanting to come here for as long as I can remember but Kimberly’s already been here 3 times before. We’re teaching some seminars in conjunction with Miracle Iceland next week but decided to come a few days early to hang out in Reykjavik and see some sights.


Today we headed out of town with our friends Gunnar Bjarnason (Miracle Iceland’s chief) and his wife Thorun with the aim of getting to the top of the Skjaldbreidur volcano (dormant of course!). First up we headed through the Thingvellir National Park to check out the fault line. Iceland sits on the boundary between the American and Eurasian tectonic plates – hence all the volcanic activity – checkout this link to see how Iceland sits in relation to the plates. In the park you can actually see where the plates come together as the fault line is a very obvious cleft lined with basalt formations. At one point there’s a little bridge across the fault line and the strip-lake that formed in the cleft – not many places you can stand on a plate boundary. Apparently there’s a tunnel that you can scuba-dive through to the nearby Lake Thingvallavatn, and the lake has amazing visibility for diving as it’s fed almost exclusively from springs (see here). This is also where the oldest parliament in the world was established in 930AD. The Icelanders would meet here for a few weeks every year and the new laws would be memorized as there was a shortage of writing materials.


Next we headed out on road 52 for about 20km into the snowy wilderness, until we came to a set of power lines heading to one of the aluminum smelters on the island. Electricity is pretty cheap here (because it can be generated from steam from the geothermal activity) and so it’s actually more economic to ship bauxite (the mineral that aluminum is smelted from) from Australia to here to be smelted and then shipped back again. Electricity here must be really cheap! Anyway, we followed the power lines across country along the side of the volcano and then Gunnar decided ‘here!’ and we simply turned and drove directly up the mountain in the snow. We got about 880m up before we finally got bogged down 200m from the top, even with the balloon tires down to 2 psi so we very carefully turned round and sat admiring the stunning view over lunch before heading back down.


Due to the remoteness of the area and the possibility of things going awry it’s essential to have multiple radios and other emergency gear. We had no bother though, mostly due to Gunnar’s excellent off-road driving skills, and the rugged Land Cruiser we were driving. We had towed along a snow-mobile part of the way with the idea of racing to the top of the mountain but the -7C temperature with *amazing* wind-chill killed that idea. Once back down by the lake we did a spot of bird-watching to add some more species to my life-list (Teal, Barrow’s Goldeneye, Goosander). Kimberly and I dozed through our jet-lag on the drive back to Reykjavik, hitting the hotel 8 hours after we left.


Here’s a couple of photos – click for bigger images.


 Gunnar and Paul unhooking the snowmobile.


 The view from up the mountain looking at other volcanoes.

Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?


This is a really interesting question that came up in the Microsoft Certified Architect class I’m teaching at present – if a database has torn-page protection enabled, and page checksums are enabled, is all the existing torn-page detection lost?


This is an important question, because enabling page checksums doesn’t suddenly make all allocated pages be protected by page checksums (it’s not until a page is read into the buffer pool, modified, and then written back to disk, that it gets a page checksum). If all the existing torn-page protection is discarded when page checksums are enabled, then the pages would be unprotected until they got page checksums on. I couldn’t remember the answer, so I experimented!


My idea was to create a database with torn-page protection, create a table with a simulated torn-page in it, then enable page checksums and see if the torn-page was still reported.



— Create the test database
USE master;
GO
CREATE DATABASE ChecksumTest;
GO
USE ChecksumTest;
GO


— Explicitly set the database to have torn-page detection
ALTER DATABASE ChecksumTest SET PAGE_VERIFY TORN_PAGE_DETECTION;
GO


— Create a test table and insert a row.
CREATE TABLE BrokenTable (c1 INT, c2 CHAR (1000));
INSERT INTO BrokenTable VALUES (1, ‘a’);
GO


— Ensure the page is written to disk and then tossed from the buffer pool
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO


Now I’m going to examine the page. There are two bits in the page header that specify whether the page is protected by torn-page detection or with a page checksum. Specifically, the m_flagBits field will have 0x100 set if the page is encoded for torn-page protection, and 0x200 set if the page has a page-checksum stored on it, and the page has not been modified (i.e. the checksum is stillvalid). You should not see the 0x100 bit set as torn-page encoding is removed when the page is read into the buffer pool – UNLESS the page IS actually torn, in which case the encoding is NOT removed.



sp_allocationmetadata ‘BrokenTable’;
GO
DBCC TRACEON (3604);
GO
DBCC PAGE (‘ChecksumTest’, 1, 143, 3);
GO


<snip>


m_pageId = (1:143)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 67     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594042318848                                
Metadata: PartitionId = 72057594038321152                                 Metadata: IndexId = 0
Metadata: ObjectId = 2073058421      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 1008                       m_slotCnt = 2                        m_freeCnt = 6070
m_freeData = 2118                    m_reservedCnt = 0                    m_lsn = (28:183:2)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 770
      


<snip>     


In this case the torn-page encoding has been removed, and the page is fine. Once I’ve corrupted the page on disk, it’s tricky to be able to see it with DBCC PAGE. I managed to catch it once and saw the following:



m_pageId = (1:143)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8100
m_objId (AllocUnitId.idObj) = 67     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594042318848                                
Metadata: PartitionId = 72057594038321152                                 Metadata: IndexId = 0
Metadata: ObjectId = 2073058421      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 1008                       m_slotCnt = 1                        m_freeCnt = 7083
m_freeData = 1107                    m_reservedCnt = 0                    m_lsn = (28:81:20)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 41949233


Now if I try to select from the table I get:         



SELECT * FROM BrokenTable;
GO


Msg 824, Level 24, State 2, Line 1


SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xaaaaa82a). It occurred during a read of page (1:143) in database ID 8 at offset 0x0000000011e000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ChecksumTest.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.


The crux of the question is whether this will still be reported if the database switches to page checksums – let’s try:



ALTER DATABASE checksumtest SET PAGE_VERIFY CHECKSUM;
GO


SELECT * FROM BrokenTable;
GO


Msg 824, Level 24, State 2, Line 1


SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xaaaaa82a). It occurred during a read of page (1:143) in database ID 8 at offset 0x0000000011e000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ChecksumTest.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.


Cool! The answer is YES – the torn-page is still detected, because the bit in the page header specifies which page protection algorithm the page is using. In fact, it even works if you turn off page checksums and torn-page detection completely.