Tuesday, July 25, 2006

Unicode SQL Scripts and TortoiseMerge

Version control is a vital part of collaborative software development. Being able to compare differences between files and resolving differences is a vital aspect of maintaining code in a repository. We use Subversion which is only supplied with a command line interface. The ability to expand and build upon open source programs like Subversion has allowed development of Tortoise SVN, a second program that acts as a GUI interface to Subversion. Its tight integration with Windows file explorer allows easy access to all the functionality of the commandline version.

Comparing differences between your current edition of a file and those in the repository is handled extremely well by Tortoise SVN. TortoiseMerge places the files side by side, you can compare differences per line. However, it needs to know how to display different file formats and currently Unicode isn't supported by TortoiseMerge. We only became aware of this after trying to compare SQL scripts that had been exported from the UniPhi database.

Exporting stored procedures, views and tables from Microsoft SQL Server Enterprise Manager using Generate SQL Script defaults to Unicode file format. By changing the file format to ANSI under the Options tab, you can start visually comparing files that use this format.

Monday, July 24, 2006

Keeping MSSQL Transaction Logs in Check

An essential part of maintaining any Microsoft SQL Server 2000 (MSSQL) Database driven application is a robust backup strategy, not only for the case of disaster recovery but also for keeping the size of your database transaction logs in check. By default, these transaction logs will grow over time to an infinitely large size, or if an upper limit is placed, the log file will fill up without scheduled maintenance, locking users out of the application.

The following Microsoft KB article is very helpful in this regard: http://support.microsoft.com/kb/272318/EN-US/

My recommendations for transaction log maintenance for UniPhi and related databases is as follows:

Since the ES2 (Innerprise Search) database is repopulated on a daily basis, the transaction log is not of a great importance, the following commands could be scheduled for backup purposes, keeping the log file size down:

However for the UniPhi database, we do want to have the transaction logs available in case of an emergency, so:
BACKUP LOG uniphi TO uniphilogbackup
DBCC SHRINKFILE(uniphi_log,100)