This week I found a new way to mess up a CLICK site upgrade.  I have a server that has been running the latest release, 5.6.0.5, and needed to update a copy of our production 5.5.3 site to the new version for testing.  Just after I started the restore, using the CLICK program "restore553storeto56", a developer started the CLICK "Entity Manager".  Well, after running for 5 1/2 days at 100% CPU usage the migration process timed out.  The  EntEntityEditor process had used 71 hours of CPU on the dual-core server while doing a total of 8 IO - 4 read, 4 write.  I started the process again from the beginning, with the developers logged out, and the restore and migration took 6 hours.

CLICK has a bunch of timeouts, which will kill certain transactions when exceeded.  When a task has failed or an error was spotted in the WOMlog and the issue was traced to a time-out then I've raised the value, often with the consent of CLICK support.  But without really understanding the trade-off implicit in the timeout I have not set it back after the issue was resolved.  This migration would have failed far sooner, and the issue resolved sooner, if the timeouts had been lower.  But even with "Command" and "Connection" timeouts too high I saw timeout errors.  The best solution is a good description of the trade-off and impacts of the parameter.  This is often tough to put together, though.

Learning: lock out the developers when starting a migration job.

Life is much easier as a developer when we have tool that make fast work of drudgery.  Every quarter I need to produce a reliability report, comparing our actual experience on out production server to our SLA (Service Level Agreement).  This once involved checking every CLICK WOMlog, looking for restarts and other glitches.  Now I use AstroGrep;  2 minutes to search 3 months of logs.

As developers we're often testing scripts on our development sites.  During debugging we write many statements to the WOMlogs, with values at intermediate steps of the process.  Reading these once meant doing a "LOG ROLL" to create a new Click application WOMlog, running the code, opening the log in Notepad, and going to the bottom to see new messages.  Now I create a share on the WOMlog directory, and from my desktop run "Tail for Win32".  It displays the tail (bottom) of the log continuously, so checking out my messages is just a matter of looking at the Tail window.

Source Forge http://sourceforge.net/ has an immense list of shareware utilities, is easily searched, and most work as advertised.  Few are perfect; can’t expect every bell and whistle from someone’s individual effort in their spare time.  But perfection is the enemy of progress, someone said.

AstroGrep is at http://sourceforge.net/projects/astrogrep/ - there are many other GREP utilities, but this one was very simple to use and worked well.  Copy it to a directory, and run it.  Select the folder to search, and entire a file name or wildcard, and let it run.  The list shows files with one or more instances of the search string.  One click and the lower window will list the occurrences; double click and the file opens on Notepad.

Tail is at http://sourceforge.net/projects/tailforwin32/.  Create a windows share to the directory, run Tail.  Create a workspace (window), and use File >> Open to put a file (WOMlog) in the workspace.  When anything is added you will see it.  TAILforWIN is a bit rough around the edges, but does the job I need. 

Both do not require an install; just download the file and run it.

Click Commerce Extranet development involves configuration and creating scripts, largely JavaScript, on objects. To test these debug statements are added which put messages in the WOMlog. Viewing these usually means opening the latest WOMlog file in the directory C:\Extranet\Logs and skimming to the tail. After a few complaints from a favorite developer I found the really neat utility TAIL4WIN http://tailforwin32.sourceforge.net Start it and open a read share into the Extranet\Log directory and a window will continuously show you the tail - bottom - of the log file. This saves a lot of time. Highly recommended.

DBCC prevents transaction log backups

Every Sunday at 3 AM I run a batch scheduled job in SQL Server 2005 SP3 to verify the integrity of my database:

alter database chbdev1 set single_user with rollback immediate

alter database chbdev1 set recovery simple

dbcc checkdb ('chbdev1', repair_rebuild)

dbcc shrinkdatabase (chbdev1, 10, truncateonly)

dbcc shrinkfile (pe, 10, truncateonly)

dbcc shrinkfile (pe_log, 10, truncateonly)

waitfor delay '00:00:05'

alter database chbdev1 set recovery full

waitfor delay '00:00:05'

alter database chbdev1 set multi_user

One of these steps is causing SQL Server to forget that it had a backup last night, so when transaction log backups begin later during the work day they fail. The easy solution is to do a backup at the end of the script. But, does this DBCC change the database enough to justify not allowing tran log backups without a full? I wonder if one of the Microsoft developers made this a conscious decision.

Another problem with this is that about once every 6 months a database is left in single-user mode. I don't know why, but I don't let this job run if I can not check the database on Sunday morning. It's never run before vacation.

Lots more to learn about this. Let me know if you see a better way to do this.

Fixing a transaction log backup on an upgraded database

I created a new database and restored it from a SQL-Server 2000 system to SQL-Server 2005 with no problem. Then I set up a maintenance plan with two sub-plans: nightly full backups and hourly transaction log backups during the work day.

After setting up the backups I changed the database "Compatibility level" from 80 to 90. The nightly backup ran fine that night, but the transaction log backups failed consistently. Turns out that changing the database compatibility level wipes out the database's knowledge of when it was last backed up, and a transaction log backup will only run after a full backup. So I ran another full backup, and then transaction log backups started to run just fine.

The only useful error message, that pointed me to the real problem, was in the SQL-Server Integration Services Output file. The History log, the SQL-Server logs, and the server Event logs were pretty useless. So, always set up job step output files for every step, even if it seems unnecessary.

Full backup of a shrunk database - learning by monitoring

I grew up programming back in antiquity when all production was done by batch jobs. The concept can be foreign to some current system managers, but it sure beats working nights and weekends.

This job is designed to run in an hour or so every Sunday at 3 AM, well outside our SLA (service level agreement with users). It could be run monthly or as needed, but I find that jobs run less then weekly tend to be forgotten, and errors can easily be missed.

select * from sysfiles

ALTER DATABASE IRBDEV SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE IRBDEV SET RECOVERY SIMPLE

DBCC CHECKDB ('IRBDEV', repair_rebuild)

dbcc shrinkdatabase (IRBDEV , 10, truncateonly)

dbcc shrinkfile (pe, 10, truncateonly)

dbcc shrinkfile (pe_Log, 10, truncateonly)

WAITFOR DELAY '00:00:05' --

ALTER DATABASE IRBDEV SET RECOVERY FULL

WAITFOR DELAY '00:00:05'

ALTER DATABASE IRBDEV SET multi_USER

PRINT N'IRBDEV file space '

select * from sysfiles

This works well in both SQL Server 2000 and 2005, I think.

I did find out that TEMPDB can not be shrunk in a simple batch job, as it can be on SQL Server 2000 - see How to shrink the tempdb database in SQL Server

Life with EMAIL

I might get 30 to 50 emails per day, requiring some simple (or complex) response. But I've found that Alexander Graham Bell's newfangled invention can short-circuit a dozen rounds of email.

We went back and forth on setting up a SQL Server linked server to an old Oracle database to support an old reporting system from our new, under development, work flow process system. Linked servers work, but coding for production operation, including robust error recovery, can be a trick. We were able to select, update, and insert OK, but merging that code with the CLICK system and designing error recovery led to a three week stream of email, all focused on one item rather then the problem.

One phone call led to a meeting of only 30 minutes which completely redesigned the approach, and will save considerable development time. We will use Integration Services and the batch processes that have been working reliably for 2+ years.