Global Transaction Log Truncation - TSQL

Over the years I've worked with Microsoft SQL Server, I've been faced with transaction logs eating up tons of disk space. Here's some code that will iterate over all databases on the server truncating and shrinking each database. Scheduled as a weekly job, this can keep disk space usage down on a busy SQL Server.


SET QUOTED_IDENTIFIER OFF

DECLARE @name varchar(30),
@header varchar(75)

DECLARE csr CURSOR LOCAL FORWARD_ONLY STATIC FOR
SELECT name
FROM master..sysdatabases
ORDER BY name

OPEN csr
FETCH NEXT FROM csr INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @header = "Database " + RTRIM(UPPER(@name))
EXEC(" backup log " + @name + " WITH TRUNCATE_ONLY ")
EXEC(" DBCC SHRINKDATABASE ( " + @name + ", TRUNCATEONLY) ")
FETCH NEXT FROM csr INTO @name
END

CLOSE csr
DEALLOCATE csr

Migrating SQL 2000 Diagrams to SQL 2005

When copying MDF/LDF files from a SQL 2000 installation to a SQL 2005 installation, diagrams will not open and the solution to getting them to open properly is non-intuitive. Here is T-SQL Code to fix the problem. This is run on the SQL 2005 Server after the MDF has been attached in SQL Management Studio. Substitute your database name where is says "Nexus":

EXEC sp_dbcmptlevel 'Nexus', '90';
go
ALTER AUTHORIZATION ON DATABASE::Nexus TO "sa"
go
use Nexus
go
EXECUTE AS USER = N'dbo' REVERT
go

CAPTCHA for ASP.NET

Over the past year+, I've noted an increase in form spam through the various web sites that I've authored and continue to manage. As a result, I've had to go back and add CAPTCHA controls to the forms in some cases.

I'm working on a new .NET project called ArtOverture and needed to dig into a .NET based CAPTCHA control. Looking around at the source code for dasBlog, I learned about Jeff Atkins CaptchaControl. It's written in VB.NET for Visual Studio 2003 and ASP.NET 1.1. However, I found that you can include the compiled DLL just fine as a toolbox control in Visual Studio 2005. The control works perfectly without any need for recompiling.

Here's the link to Jeff's Code Project writeup on CaptchaControl. Full source code is available.
http://www.codeproject.com/aspnet/CaptchaControl.asp