SharePoint content deployment woes

A little Google searching for issues related to MOSS content deployment will quickly reveal this as a buggy (to be kind) SharePoint feature. I personally have had a lot of frustration trying to use it. The good news is that the Infrastructure Update released in July 2008 has solved many of the content deployment problems I experienced. Though it is still not without issues, content deployment will now at least copy the stuff from one farm to another more-or-less reliably. We have found that for new pages or other objects, you may still need to go into the target site afterwards and tweak a few things, so always check the new items on the target site immediately after a content deployment. Updated items, e.g. content changes, don’t seem to have any issues thus far.

Here are the links to the Microsoft KBs for the updates. Note that as MOSS is based on WSS, you’ll need to apply both updates, first for WSS then for MOSS (you will see this in the documentation).

WSS 3.0 (Windows SharePoint Services) Infrastructure Update

MOSS (Microsoft Office SharePoint Services) Infrastructure Update

I was directed to these updates after breaking down and calling Microsoft for help. :) Out of that conversation came a couple of other things:

• You should always create the target site collection with the Collaboration/Blank template. I saw mixed information on this on various blogs; some recommended creating a truly empty site collection using an stsadm command (there is no GUI equivalent). I found after applying the updates the Collaboration/Blank template seemed to work fine, but here is the stsadm command just in case:

  stsadm -o createsite -url http://something.example.com -ownerlogin login -owneremail email

• stsadm -o export and stsadm -o import is equivalent to doing a content deployment from Central Admin. The advantage of stsadm is that it creates fairly detailed yet oddly nebulous logs in good old text files, which can help pinpoint problems a bit easier than wading through Central Admin screens to view errors. Either way, the information appears equivalent to me and I suspect Central Admin just captures the information from the same logs stsadm generates.

Shrinking SQL Server 2005 log files

Note: for the purposes of this article I am assuming a full recovery model (which is the default).

I have occasionally had database transaction logs grow out of control on SQL Server 2005, e.g. 100M of data and 5G of transaction log. (Specifically, SharePoint configuration databases have done this to me a number of times.) This can affect performance and will most certainly affect backups, aside from obviously consuming inordinate amounts of disk space. You might be thinking “don’t autogrow the transaction log”, but that does not really solve the problem, it simply shortens the amount of time before you have to deal with it. :)

A transaction log has entries for each change you make to the database since the last backup. Internally, the transaction log file is divided into sections called virtual log files. When the current virtual log file fills up and a new one is needed, SQL Server 2005 will allocate the virtual log file that is closest to the beginning of the physical file(s). Under normal circumstances, following a database or transaction log backup, most of the virtual log files will be considered inactive, especially those at the end of the transaction log file. In such cases, if you tell SQL Server to shrink the files you get most of the unused disk space freed. What sometimes happens, though, is that a transaction log entry is still active in a virtual log file closer to the end of the physical file. In such cases, you may only see a small amount of file space in use because there are only one or a few virtual log files in use, but because of the location of the virtual log file within the physical file you cannot shrink the physical file.

To resolve this, first open SQL Server Management Studio to your database instance, and under Management open the Activity monitor. Look at the applications holding locks and shut them down or otherwise prevent them from maintaining a connection so that you have nothing actively using the database but your own login. Then open a new query and execute the following statements, one at a time.

BACKUP LOG database_name TO DISK = ‘X:\some\backup\folder\database.bak‘
DBCC SHRINKFILE (logical_database_log_name, 10) WITH NO_INFOMSGS

If you do that and the file does not shrink, or does not shrink very much, run the statements again; you may want a new backup filename. For some odd reason, it sometimes takes two runs to get a result. You read that right; even Microsoft’s own knowledgebase says to do the same (article 907511).

Another approach that has sometimes worked for me when the above does not:

• Assign a second log file
• Migrate all of the log to the 2nd log file
• Shrink the transaction log files
• Migrate all of the 2nd log back to the first
• Remove the second log

You can do all of this through SQL Server Management Studio.

Update: Please see my article Shrinking SQL Server 2005 log files – part deux for yet another approach.

Also, if you are not aware of it, a good practice is to create a maintenance plan that backs up the transaction logs on a periodic basis. I usually set up daily maintenance plans that backup the transaction logs, shrink the files, do index rebuilds and update statistics, then schedule these to run at a time least likely to interfere with anything but be done before users start their day.