Chris Ogle's Down-home SharePoint Ruminations

I've written about it, and written about it, and written about it some more. A very large (70gb) object in the SharePoint recycle bin resembles a steel wool hairball — you know it's there, it hurts like crazy, and you just can't get rid of it. Microsoft gave me two choices…

I could wait for the WSS/MOSS Service Pack(s) 2, because they're supposed to fix the problem. Well, I tried to install them (on a test system, natch) but WSS SP2 ran for days (no kidding) before I finally gave up. Seems that the update just couldn't handle that big an item in the database. Maybe if you already have SP2 installed and THEN create and delete a hairball, it might work, but I'm not going to try it . Let me know how it goes.

The second choice was to restore the offending library, which contained one folder holding over 100,000 documents, and then delete each document manually, flushing the recycle bin(s) every few hundred documents. I can tell you from personal experience that this works, although the clerical folks who were drafted into doing this grunt work still won't speak to me and have all retired on disability with terminal carpal tunnel syndrome. If you want to try this at home, I suggest you select "Edit in datasheet view", which at least lets you select and delete a bunch of documents at one time. Also allow yourself plenty of time. Lots of time.

It happens everywhere, I suppose. It's close to noon and we are all wondering if we oughtn't get some work done this morning, when somebody asks "Whatever happened to Jonesy?" To be honest for once, I hadn't noticed, and the few who had assume he hasn't returned yet from his annual vacation at Snakentick State Park. Nobody wants to wake the Boss, and the security guys just shift their eyes back and forth like Susanna Hoffs in a long loo queue, so finally someone else grabs the short straw and takes Cheryl from HR to a long lunch. He returns bleary-eyed, to report that Jonesy won't be needing his office chair anymore. I can't go into the details, but if you can explain how the video can be considered an allowable business expense, we'll give you your camera back.

A couple of weeks later, the Boss realized that Jonesy was gone and came to me, worried about some report that Jonesy had left checked out in SharePoint. "What are we going to do? We need that report today!" I told him it would take a lot of hard work, but I could fix it. Of course, all I did was log in as administrator and force the check in, followed by an afternoon of checking eBay listings for camera prices.

My reverie was interrupted when the Boss came back. "What if he left something else checked out? How will we ever know without looking at every single SharePoint page?" Startled at the stillness broken by a query aptly spoken (I didn't realize that he knew that SharePoint HAS pages), I knocked out the following SQL query. 

It returns all documents currently checked out by the given user. Just replace CONTENTDB with your content database name, DOMAIN with your domain, and LOGIN with the user's login. You'll be sawing logs in no time.

Of course, we all practice Safe Sql, don't we? SELECT statements only in a SharePoint database, right? Right??

I've written about it before, but the problem remains. Deleting a very large (78gb) SharePoint document library will get you in trouble. The delete works fine; flushing it from the page recycle bin works like a charm. But thirty days later, when SharePoint's Empty Recyle Bin job tries to really, truly delete it from the second stage recyle bin, and thus from the SqlServer database, your users will be locked out of SharePoint for two hours before the job throws up its figurative hands in despair and aborts. The next night, the same thing happens. And the next…

Opening a ticket with Microsoft gave me a better understanding of the problem, but the darn library and its contents are still there. I know they could write a T-SQL procedure to do the job. I just know it. I could do it myself, except I don't know all of the interrelationships of the various SharePoint tables in the content database. And I don't trust my T-SQL skills. Oh yeah, and it would violate my warranty and Microsoft would never help me again. The only "suported solution" they could give me was to restore the library to its original page and delete the documents one by one, flushing the recycle bins every few hundred documents or so. Did I mention that there are over 100,000 of them? *sigh*

Now I hear that Service Pack 2 for WSS and MOSS are being released today. My MS tech recommends that I try it (on my development system, of course!) because it should solve my problem. Boy, wouldn't that be nice. I'll keep you updated.

Update: read this

Update: Problem solved! See the comments for details.

Is it just me, or is anyone else bothered by SharePoint's inconsistent behavior regarding document library dropdowns. If you are staring glassy-eyed at a site that displays the doclib webpart, you can hover til the cows come home without displaying the dropdown menu needed to delete the document of a lazy user who can't bother to delete his own documents but has to call you to … sorry … without displaying the document's dropdown menu.

Of course, if you click the doclib's name and display it on its own page, dropdown functionality jumps to your service, quite unlike a lazy user who … sorry again.

What makes it even worse is that sometimes the dropdown actually works when viewing the doclib on the site. Sometimes yes, sometimes no, sometimes for one user and not for others. If there's a rule that covers this behavior, I can't find it.

So my users get tired of the constant litany of "click-on-the-name-of-the-document-library-so-it-displays-on-a-page-by-itself", and for once, I don't blame them. Is there a reason for this behavior. Is there a good reason? Are there any plans to change this behavior?

I'd rather deal with real problems. I've got no time for trivialities.

Update: Problem solved! See the comments for details.

Update: read about it here.

As I have blathered on about before in painful and agonizing detail, it is possible to delete a SharePoint object that is too large for the recycle bin to digest. In my case, the object is a single document library containing 140,000 items that run to about 78 gigabytes. Unlike the Windows recycle bin, the SharePoint recycle bin will accept this JabbaTheHutt-like monstrosity without complaint, but when it comes time to purge it (after 30 days is the default), the Recycle Bin Timer job runs about two hours before throwing an exception into the Event Viewer. During this time, nobody can access SharePoint, including my backup job, which runs for hours and hours at least partly because it is trying to back up an additional 78 gigabytes of useless data. The uber-object no longer appears in the recycle bin (update: it is there but you have to click "Deleted from end user recycle bin" to see it), but trust me, the data is still there in your content database. You can do a select statement against dbo.AllDocs, looking for entries that have a Delete Transaction ID and you'll find all 140,000 documents.

As you might imagine, this has generated a bit of frustration that can only be relieved by intense and random road rage.

I finally broke down and opened a support ticket with Microsoft. This time I snagged a good one, who knows both SharePoint and SqlServer. On my development system (which also has the problem), he went into Central Administration > Application Management > Content Databases, selected the right web app, and clicked the Content Database. After checking "Remove Content Database" and OK, the content database is no longer attached to the SharePoint web app. He then clicked Add A Content Database and re-attached the same database. Amazingly, the select statement no longer finds the offending documents. When I shrank the database, it became … well, not slim and trim … but much smaller.

I absolutely refuse to perform this action on my production database unless I return from my vacation, which starts tomorrow. Did I say "unless"? I meant "until". I think.

Update: Although this worked on my development system, it did not work on my production system. Microsoft was unable to tell me why.

If you try this, be aware of the following:

1. Make sure you have a good backup. Do I even need to say this? Uh, yes, I do.

2. Not only does this procedure empty the offending item from the second stage recycle bin, it also empties everything else. I don't know why.

3. Don't shrink the database while it's attached to SharePoint. You will get rude and angry error messages. Repeat the "remove" procedure before shrinking. And re-attach it when you're done.

4. In my case, the shrink took 3 or 4 hours. When it finished, there was no indication, except that the shrink window disappeared. Apparently, this is normal.

5. Be careful when you detach. Make sure you have selected the correct Web Application.

6. Be careful when you re-attach. The default Content Database they show you is NOT the one you want to attach.

7. Don't blame me if this doesn't work for you. I'm not yet certain it will work for me. And I won't find out unless I get back. Did I say "unless"?

Uh oh. I'm going to need extra caffeine tomorrow morning.

I have a custom list that is, in essence, a database of the projects we do for our clients. It is just shy of 4,000 rows. My users rarely access it directly (most of them don't even know it exists), but a home-grown web part accesses the data several thousand times a day, filtering it in various ways and displaying data to said users. Six or eight months ago, I got tired of seeing the message "This list contains a large number of items. Learn about managing a large list or library and ensuring that items display quickly.", so I learned about indexed columns.

"Clever girl", I thought, and set about indexing the most often filtered columns. Much to my surprise, nothing ran any faster. That's when I found out here and other places that indexing doesn't really help much when run from CAML queries, just when you're using the SharePoint GUI to see your data. And as I said, almost nobody uses the GUI for this list.

Flash forward to today. I was creating a view on that list for the one user who actually does access the data directly, and the view required one of the indexed columns, just like several other existing views do. Design, implement, test, done — and it's back to zapping boss creeps. Or maybe not. The phone lit up as user after user complained that the web part now returned no data at all. What the? I tested the view. It worked. I swear. Ya gotta believe me. But now it returns nothing. So does every other view that uses an indexed column.

Astoundingly, I was able to find someone who actually has seen this problem before. Not so astoundingly, the poor guy never got a single answer to his post. I deleted the indexes (they didn't seem to be doing me any good anyway), and Shazam!, everything was back to normal.

Now I'll lie in bed tonight, staring at the ceiling and wondering how this happened. This has to stop. I need my sleep.

Yet another unexplained SharePoint log entry. This one looks like

11/20/2008 06:51:27.78  OWSTIMER.EXE (0×0888)                    0×1340 Office Server                  Setup and Upgrade              8u3j High     Registry key value {SearchThrottled} was not found under registry hive {SoftwareMicrosoftOffice Server12.0}. Assuming search sku is not throttled. 

If I had a regular reader, s/he would know that I have encountered mysterious log entries before, and not one of them has ever been explained, whether to my satisfaction or not.

The priority of this one is rated "High", so it presumably means something reasonably important. Some chained-to-his-desk coder implemented this message thinking it had some purpose, but the usual search engines turn up, once again, nothing but questions with no answers.

Now I'm not one of those dinosaurs who thinks that all software should still come with a hefty manual (I'm a different kind of dinosaur who believes in Weinberg's Second Law), but I do think that software should be documented. I don't expect MS to print an explanation of thousands of obscure error messages, but I do expect them to at least make the explanations available. Isn't that what the Internet is for? Well, besides the porn. And YouTube. And Twitter. And SarahPalinAsPresident.com, which sadly has disappeared.

End rant.

As both of my readers are aware, I've spent too much time spelunking SharePoint log files lately. This time, they (the log files, not the readers) helped instead of hindering.

Some months ago, a user who sends out an official email each week asked me how he could also store that email in SharePoint. As a confirmed mossaholic, I shouted "Great Caesar's Ghost!", mounted my spavined steed, and soon had an email-enabled document library that was just what he wanted.

Months passed. Then last week he complained that the library had 140,000 documents in it. For several days, SharePoint had been stuffing copy after copy of his weekly email, along with all of its attachments, into the doclib. I disabled emailability and started poking around. Finally I discovered that the email was never being deleted from the drop folder, so I manually deleted it and re-enabled emailness. When I sent a test email, SharePoint promptly went back to creating multiple copies in the doclib. Hmmm…

Enter the log files. Since each multiple copy has a time stamp, I was able to find just the right spot in the log file.

11/19/2008 08:56:45.51  OWSTIMER.EXE (0×0888)                    0×08A0 Windows SharePoint Services    E-Mail                         6872 Critical A critical error occurred while processing the incoming e-mail file C:InetpubmailrootDrop1a1598b101c94a4d00000001.eml. The error was: Access to the path '1a1598b101c94a4d00000001.eml' is denied.. 
11/19/2008 08:56:45.51  OWSTIMER.EXE (0×0888)                    0×08A0 Windows SharePoint Services    E-Mail                         6871 Information The Incoming E-Mail service has completed a batch.  The elapsed time was 00:00:01.9844131.  The service processed 0 message(s) in total. 

Access denied? Well, ok, I modified the permissions to the drop folder so my SharePoint service account had full control (wish I had read this first, it would have saved me some time). Bam! The email now gets deleted and life is good once more.

So how did it ever work correctly for months? And what made it break? I'm the only one around with access to the server, or who even knows what a drop folder is. Naturally, nobody on Go-ogle has ever had this problem before — sounds like the story of my life.

Now I just have to delete 140,000 documents from a doclib folder. You should try it sometime. I finally gave up after an hour and a half — which had to be done after hours since it locks up SharePoint tighter than a tick while it's deleting. Or trying to.

UPDATE:

I figured out how to delete the 140,000 documents in a reasonable manner. Actually, my boss suggested it. No, really. I created a new library and copied the 100 or so documents that wanted to keep, then deleted the original document library and renamed the new one to the old name. It went like a snap. If you try this at home, be sure to empty the deleted library from the recycle bin or it inflates the size of your database and reaaaaallly slows down your backup until the library finally falls out of the recyle bin after 30 days or so. Don't ask how I found this out.