The odd bit

Once is an accident, twice is a coincidence, three times is an enemy action.

The odd bit - Once is an accident, twice is a coincidence, three times is an enemy action.

Sitecore database cleanup problems

As your Sitecore site grows you’ll need to perform a bit of maintenance on the Sitecore databases. Our experience tells us it’s primarily the ‘master’ database that needs some work. The ‘master’ database is the one that gets all the editing while the ‘web’ database is only used as a publishing target.

Our initial wakeup call was a steadily growing ‘master’ database months after adding a synchronisation with an external system. The synchronisation data contains images and as we updated objects the database would keep growing. It’s enough material to create another article but to cut a long story short: the Blobs table would keep growing without removing records. tl;dr orphaned blobs.

An investigation into the deep, dark dungeons of Sitecore.Kernel.dll hinted at a CleanupDatabase() method on the Database class. It does quite a bit of cleaning including the removal of orphaned blobs. We scheduled a task and Eureka! … until recently.

A performance review of our Sitecore installation revealed sporadic timeouts in the logs. And it wasn’t the more or less normal request timeout but a rather alarming SQL timeout. Luckily, the offending SQL statement was also in the logs.

Exception: System.Data.DataException
Message: Error executing SQL command:  declare @x bigint set @x = 0 DECLARE @item TABLE(ID uniqueidentifier,parentID uniqueidentifier) INSERT INTO @item (ID,parentID)   SELECT  [ID],[ParentID] FROM [Items]  DECLARE @temp TABLE(ID uniqueidentifier) WHILE (SELECT count(id) FROM @item ) <> @x begin set @x = (SELECT count(id) FROM @item ) delete from @temp; insert into @temp (ID)   SELECT  id FROM @item where parentID  = @nullId update @item SET Parentid =@nullId where Parentid  in (select id from @temp) delete from @item where  id  in (select id from @temp) end UPDATE [Items] SET [Parentid] = @nullId where [ID]  in (select id from @item) ; DELETE from [Items] where [ID] in (select id from @item)

Nested Exception

Exception: System.Data.SqlClient.SqlException
Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteNonQuery()

Running the query manually proved that it was right on the limit: 4 minutes and 51 seconds on a database with around 44000 items. The standard SQL timeout in Sitecore is 5 minutes. A bit of load on the database server would cause it to go longer than 5 minutes and trigger the timeout error.

Hey, let’s increase the timeout to 10 minutes and we’re done. Wrong! You’re done until the database grows large enough that 10 minutes are no longer sufficient. So I favour a more structural solution: let’s work on that query!

Diving into Sitecore.Kernel.dll once more showed that it was part of the database cleanup, namely the method CleanupCyclicDependences() implemented in SqlServerDataProvider. There are several problems with the query:

  1. A table variable is a bad idea once you’re working with real data (read: more than a couple of rows). If you don’t believe me take a look at the query execution plan. It’s filled with full table scans because SQL Server does not calculate statistics on table variables. The estimated number of rows will always be 1. The query has 2 table variables.
    The right approach would be temporary tables because SQL Server does calculate statistics for temp tables.
  2. Deleting all rows from a table is slower than just truncating that table.
  3. The last two statements are odd: first records are updated and then the same records are deleted. Why update them if the next step is deleting them?

So this is a much better query which will do the same work in 2-4 seconds (+/- 44000 items, depending on server load):

SET @x = 0
SELECT {0}ID{1},
FROM   {0}Items{1}
WHILE (SELECT Count(id) FROM   #item) <> @x
      SET @x = (SELECT Count(id)
                FROM   #item)
      TRUNCATE TABLE #temp;
      INSERT INTO #temp
      SELECT id
      FROM   #item
      WHERE  parentid = {2}nullId{3}
      UPDATE #item
      SET    parentid = {2}nullId{3}
      WHERE  parentid IN (SELECT id
                          FROM   #temp)
      DELETE FROM #item
      WHERE  id IN (SELECT id
                    FROM   #temp)
DELETE FROM {0}Items{1}
              FROM   #item)

Warning: do not run this query on SQL Server because it won’t work! It’s formatted for Sitecore’s data provider.

So the correct solution is:

  1. Create a custom data provider that inherits from SqlServerDataprovider.
  2. Override the CleanupCyclicDependences method.
  3. In the method body type
    this.Api.Execute(@””, “nullId”, ID.Null);
  4. Paste the above query between the double quotes.
  5. Wire your custom data provider in web.config.

And your database cleanup will be a lot faster.

Sitecore Web Service Pitfalls

I recently started an adventure involving Sitecore’s out-of-the-box web service. In fact, I didn’t know such a thing existed in Sitecore until I searched the web for information about accessing Sitecore from a process outside the ASP.NET runtime.

The good news is a web service exists. The bad news is the documentation barely exists. Apart from a document titled The Sitecore Web Service Reference Guide on the Sitecore Developer Network, you literally are on your own.

Thankfully, there’s no problem in getting it to work. The problem is actually using the web service.

Problem 1: Return values

The document describes – in nothing more than just a few words – all available methods. The return value is always of the type System.Xml.Linq.XElement but no part of the documentation shows what the return value actually looks like. After some debugging I found out the result always takes this form:


Problem 2: The documentation is wrong

Ouch! The documentation for the Save method tells us that it needs the XML representation of an item. A special note even points us in the right wrong direction:

You can obtain the XML representation by the GetXML method.

So you call GetXML, modify the XML to include your updates and then supply that XML to the Save method. Been there, done that and received the status ok reply. Woohoo!

… or not. Despite the OK from Sitecore I didn’t see my updated field value. Time to figure out what the Save method actually does using dotPeek. Upon inspection it seems the method expects an XML like:

    <field itemid="{guid-of-item-to-update}"
           fieldid="{guid-of-field-to-update}" />
    <field ... />

That XML is completely different than the XML representation of an item! I changed my methods to produce the “new” XML format and… it worked :)

I really hope I don’t need to create a follow up post!

CSS: Love Hate

The slightly odd title is there for a reason. This post exists for a reason. I’ll explain both…

I’ve always struggled to remember the order in which hyperlink styles should be defined in a stylesheet. Hyperlink styles? Order? I’m talking about these (in alphabetical order):

  • :active
  • :hover
  • :link
  • :visited

They should be specified in the right order or things won’t work the way you’d expect them to. Here they are again but this time in the correct order:

  • :link
  • :visited
  • :hover
  • :active

I could go into details why this is the right order but I won’t :-) That’s not the purpose of this post.

The good news is that there’s a mnemonic to remember the order: LoVe HAte. For those not paying attention: mind the casing of the mnemonic!

So that’s the title of this post. But why? To keep me from googling each time I forget the order 😛

Administrative shares in Windows 7

Those who have experience with NT-based operating systems on a network will certainly be familiar with the concept of administrative shares. If that doesn’t ring a bell, you access them like \\computername\c$. With this intro and the title of this post, you might be alarmed or enter panick mode fearing they might be gone in Microsoft’s latest OS iteration. Well don’t go into cardiac arrest because they are still there… but of course not without the odd problem.

Windows 7 still creates the administrative shares on install, but you can’t use them out of the box. First of all, you need to have File and Printer Sharing enabled. And the way to do that has once again been changed:

  1. Open the control panel.
  2. Go to Network and Internet.
  3. Go to Network and Sharing Center.
  4. In the left column, click on Change advanced sharing settings.
  5. There are two profiles. You probably don’t want this on when you’re on a public network so open Home or Work.
  6. Under the header File and Printer sharing, select the Turn on… option.

And now your administrative shares still don’t work 😛 You’ve just completed step 1 which implies there’s at least a step 2 and here it is: you also need to change the registry.

  1. Click on the orb (= the round button with the Windows logo in the taskbar) and type regedit in the search box.
  2. Open the registry editor.
  3. Navigate all the way to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System.
  4. Right-click in the pane on the right side and add a new DWORD (32-bit).
  5. Give the new setting the name LocalAccountTokenFilterPolicy.
  6. Double click on that setting and give it a value of 1.

It’s probably wise to reboot after doing this, although it might not be needed. I’m not sure if it works straight away because I rebooted without trying.

Anyways, if you’re lucky you will be able to access administrative shares after performing these steps. If you’re unlucky, like me, doing all these things appears to have zero effect at all. Once again don’t panick, because there’s another hint/tip/solution coming your way.

Somewhere at some point during the installation or configuration of Windows 7, you will be asked to do something with a thing called Homegroup. It’s some weird new sharing tool that looks OK but isn’t quite what you’re used to. Once you have enabled that feature, you won’t be able to use the administrative shares. So the tip is: disable the homegroup feature.

  1. Open the control panel.
  2. Go to Network and Internet.
  3. Go to HomeGroup.
  4. Click on the blue link Leave the homegroup.

The popup dialog should point out itself, but I believe I picked to first option. Once I had left the homegroup, the administrative shares started working again.

I hope this post can help anyone who experiences the same problem.

Windows Vista: Explorer opens folders in a new window

Recently, Vista started opening folders in a new window even though “Open each folder in the same window” was selected in the folder options. Obviously something had changed but I didn’t know what. There were more indications that something was wrong, including:

  • Explorer opens folders in a new window, ignoring the folder options setting.
  • The Windows flag on C: is gone.
  • DVD-drives are labelled as CD-drive.
  • Trying to access a CD/DVD results in a Format blank disc dialog box.

Searching the internet resulted in heaps of complaints about the new window issue so finding the right fix wasn’t easy. It turned out that Internet Explorer 8 was the culprit. So there you have solution #1: uninstall IE8. Actually, that’s not a solution so I wasn’t satisfied :)

Another solution that I encountered a couple of times suggested to launch IE8 as administrator. Several users reported that this method fixed the problem, but it certainly didn’t fix it for me.

Eventually, I found a comment on the IEBlog with a list of 19(!) steps to fix it. Fortunately, and as noted in the comment too, step #11 seems to be the critical one. I was able to solve all issues mentioned above by doing this:

  1. Open a command prompt as administrator (right click -> Run as Administrator)
  2. Type: regsvr32 actxprxy.dll
  3. Click OK in the popup window.
  4. Reboot

And Vista was back to its normal self. YMMV!

Counterintuitive or counterproductive?

Do you ever get that feeling where a piece of software seems to make your life as a developer harder instead of easier? Well, I do but I’m not sure whether it’s the software or me.

I’ve worked with an open source content management system written in PHP for close to 6 years. In that timeframe, I got to know the little beast inside out. I knew the strong points of the product but more importantly I also knew the weak points and how to avoid/circumvent them. Anything I do with it just works and if it doesn’t I soon enough find out it was caused by a mistake on my part.

The new content management system is some commercial software written in .NET. I haven’t figured out why, but I seem to be in a constant fight with the software. I can’t have it working perfectly for 2 days straight. I do something, it fails… I finally get it working again and less than a day later it breaks again. Perhaps it’s the software, perhaps it’s me but I’m certainly not used to working with such flaky software. All I know is that it’s pretty frustrating at the moment.

Viewing Tip: Star Trek

I have collected all Star Trek The Next Generation and Star Trek Voyager seasons on DVD over the past few years. That’s a total of 14 Trek seasons which equals to an awful lot of minutes. I’ve watched every episode as I was collecting the seasons, but now I’m at a point where I’m wondering which episodes I’d like to watch again.

Instead of putting my own mind at work I decided to let Google do all the work for me. A blink of the eyes later, I was looking at a top 50 someone compiled. It’s a top 50 that includes all series, except Enterprise I think, but that’s not an issue for me. I just filter for TNG and VOY.

Looking at the list I’ve come up with a new plan: watch all episodes again and create my own catalog with my own ratings. That would be much easier to browse through than all those little booklets. And it also has the benefit that it can finally give me some real content for my web site that’s been in the works since… well, far too long already :-)

Milk Inc: Forever

It started with Milk Inc Supersized in the Sportpaleis in Antwerp to celebrate the 10th anniversary of the most popular Belgian dance group. It was supposed to be a one-time event, but it was such a success that they decided to do 3 concerts the year after. And because they came back, I also headed to Antwerp for Milk Inc Supersized 2.

That was nearly a year ago and back then they already announced Milk Inc Supersized 3 for this year. The event has been renamed to Milk Inc Forever because that’s also the name of their latest album. I’m going this Saturday to what will be the second concert from a total of six. I’m sure it will be one hell of a party :)

PS: And the damn commercials on the radio seem to make it an even longer wait…