So I was brought into a problem yesterday at a client where replication hadn’t been working for a few days.  It’s a straight forward SQL 2005 sp2 transactional replication setup with no special filters, just replicates 35 articles.  However as it had been down for a few days a new snapshot had to be generated.  Easy enough.  I went to generate a new snapshot and ended up receiving this error:

“The process encountered operating system error ‘ ɫ’.”

Yeah I don’t know what that character is either, and Google hasn’t heard of that error.  So after proving that replication works with the publisher, distributor and subscriber through a new database with a single table and a single record, I recreated the snapshot, adding an article at the time in case it was a data problem.  Well after adding about 25 of the 35 articles I got the bubble that said C: Drive was running out of space.  I thought, that was weird as the snapshot is going to the L drive, so something else must be happening and I started digging through.

Well it turns out that in the case of compressed snapshots, it first writes the uncompressed snapshot to the temporary location based upon the SQL Server Service account’s user environment variables.  In this case it was the default of “C:\Documents and Settings\[Service Account]\Local Settings\Temp\[GUID]\”.

When you’re not doing a compressed snapshot you can specify any location you want, however in the case of compressed snapshots, you only specify the location of the compressed snapshot, I have not found a way to specify a default location for the uncompressed snapshot. 

So if you’re doing compressed snapshots make sure that the C drive has plenty of space or change the environment variables to point to another drive which does have enough room.

Well it’s been about 6 weeks since the last Cumulative Update so it must be time for a new one.  So as of today (6/16/08) we now have 8 Cumulative Updates for SQL 2005 SP2 which you can get here.  I haven’t downloaded it yet, but I can tell you that I can’t wait until SP3 comes out.  I’ve installed all of them from CU1 through CU7 so far and actually found something interesting with CU6 and CU7.  CU1 through CU5 got progressively larger to the point that CU5 was around 200 MB if I remember correctly, however when I downloaded CU6 and then CU7 it was significantly smaller (under 20 MB) each, so not sure what happened there.

Anyway, as always the best place I have found to look for SQL version information is SQLSecurity.com.  Although Microsoft also keeps one for 2005 here and SQL Server Central also keeps a list here.