Moving a SQL Server 2005 transaction log

Since I don’t do this very often, I always have to refresh my memory on the process. It is easily found via your favorite search engine, but since part of my reason for this blog is to consolidate the places I have to go to look for this kind of thing, I’m going to post it here. :)

1. Document the current database permissions.
2. Make sure there are no active connections to the database. Really, you need to do this.
3. Check where the current files are located before you detach the database.
4. Detach the database. In SQL Server Management Studio (SSMS), just right-click the database and choose Tasks > Detach… and click OK. The database will disappear from SSMS (gasp!).
5. Move the transaction log file.
6. Back in SSMS, right click Databases and pick Attach…, click Add, find your .mdb file and click OK.
7. In the dialog on the bottom part (database details), you will see the log file has Not Found under the Message column. Fix the pathname of the log file to the new location, use the … button if you like or just type it in. The error message should disappear.
8. Click OK. Your database is back.
9. Check the database permissions and fix them up as needed from the documentation you created earlier.

32-bit Remote Desktop in Windows 2008

I find 16-bit color desktops cause me more eyestrain than 32-bit, so I wanted my Windows Server 2008 remote desktops in all their 32-bit glory. By default, they are limited to 16-bit but this is easily remedied. Go to Start > Administrative Tools > Terminal Services Configuration (or Start > Run > tsconfig.msc), right-click the connection under Connections and select Properties, and select the Client Settings tab. Color Depth probably has Limit Maximum Color Depth checked and 16 bits per pixel in the dropdown. Uncheck it, or change the dropdown to 32 bits per pixel. Click OK and you’ll likely get a dialog about how this won’t change the current session. So close that and log off and back on, making sure your client is set to use 32 bits. You should now have a 32-bit display depth and a little less eyestrain.

Waiting in batch files

Unfortunately, I still have the occasional need to write a batch file that has to run on generic XP. And on such occassions, sometimes I want the script – if we can call it that – to wait for a couple of seconds at some point. Alas, XP has no sleep or similar command, but there is a way to simulate the functionality. I found the gist of this idea at Rob van der Woude’s Scripting Pages, and truth be told cannot believe I had never seen or thought of this before. The core idea is to use a number of ping commands to localhost.

So for example, I wanted to kill some Documentum Desktop processes that integrate with the explorer.exe task that makes up the Windows desktop interface. But, I wanted it to pause for a moment before relaunching explorer; ping to the rescue. Here is the batch file I ended up creating for the purpose.

@echo off
rem
rem  Kills core Documentum Desktop processes and restarts Explorer so they relaunch
rem
taskkill /f /im dcathmgr.exe /im dcevtsrv.exe /im explorer.exe
rem tasklist /nh |sort
rem --- the next line just delays for a moment
ping -n 3 localhost >nul
start explorer.exe

The second-to-last line simply pings localhost 3 times, buying a couple of seconds of delay. You can adjust the number of times upward if you want it to wait a little longer. Normally the pings will be separated by a second, but the last attempt will terminate the ping command line so you really want number of seconds + 1, e.g. -n 6 would wait about 5 seconds.

And that’s all I have to say about that.

Shrinking SQL Server 2005 log files – part deux

In Shrinking SQL Server 2005 log files I gave some standard approaches to shrinking transaction logs. I was having trouble making those work on a development server, due in part to an extreme shortage of disk space. I came up with a neat little trick that seems to work pretty well, with some caveats.

The trick is to change the database recovery model from Full to Simple, change the recovery model back to Full, then do the dbcc shrinkfile. (The recovery model is found in the database’s properties in SQL Server Management Studio.) I think this works because changing the model to simple clears the committed transaction log entries, effectively emptying the transaction log if it is not actively being used (in which case this is probably not a good idea anyway). I should note that in case Something Bad Happens™, it would be prudent to take a backup of the database first if possible.

Hopefully the combination of the previous article and this little trick will help you get those transactions logs back under control.

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.