I had previously posted how to Truncate Logs for SQL Server 2005. Unfortunately, this method does not work in SQL Server 2008. The reason is because the “WITH TRUNCATE_ONLY” command is no longer in SQL 2008. Assuming you run in full recovery mode, the new script to do this is:

1
2
3
4
5
6
7
8

USE [{DatabaseName}]
GO
ALTER DATABASE [{DatabaseName}] SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE({TransactionLogLogicalName})
GO
ALTER DATABASE [{DatabaseName}] SET RECOVERY FULL
GO

Simply setting the database mode into simple recovery mode performs the actual truncation but the file is not shrunk by that. DBCC SHRINKFILE will take care of that second step. And don’t forget to put it back into full recovery mode at the end!!

-Shane

IndyTechFest registration is now open! This year there is a limit of 500 registrations (I believe last year’s was like 400 and it was booked up within just a couple weeks). So I strongly encourage you to register sooner rather than later!

There is a great lineup of speakers and sessions at this year’s IndyTechFest! Some of the speakers I have seen speak before include Paul Hacker, Larry Clarkin, Michael Eaton, Arie Jones, Tom Pizzato, Dan Rigsby, and Bill Steele. There are many other great speakers that I know or have heard of. This should be an excellent event and one that is worth a good long drive to get to!

Some of the sessions that I’m really looking forwards to include Test Driven Development (TDD) w/ VS 2008, Tips and Tricks for the New C#, Tips and Tricks for the New VB .NET, Duplexing WCF in the Enterprise, and Virtualization of SQL Server. There are many other sessions that I’m going to hope to get to but alas, with it being a one-day event, I doubt I’ll get to most of the ones I really want to see.

Props to the people who worked hard to make this event possible, including Brad Jones, Dave Leininger, John Magnabosco, Mark McClellan, and Bob Walker, as well as all of the support of the local user groups to help drive the event!

Just as I was wrapping this post up, I received a phone call. Apparently as of 1pm (1 hour after registration opened), nearly HALF of all available registration slots were filled! If you read this post and have not registered, go register NOW and don’t wait or you’ll be left out!

I’m frequently trying to figure this out but never remembering the exact syntax, so here it is! A quick/easy way to truncate logs on a database via script!

* NOTE: I don’t recommend this for production databases unless you tweak it to be good. This is more for dev environments where you don’t really care about transaction logs!

1
2
3
4
5

USE [{DatabaseName}]
GO
DBCC SHRINKFILE({TransactionLogLogicalName}, 1)
BACKUP LOG [{DatabaseName}] WITH TRUNCATE_ONLY
DBCC SHRINKFILE({TransactionLogLogicalName}, 1)

Happy truncations!

-Shane

How many times have you tried to run a database restore on SQL Server 2005 only for it to fail because you don’t have exclusive access to the database (for whatever reason from SSMS being open to anything else)? Happens to me quite frequently. Here’s a handy piece of code to help prevent that:

1
2
3
4

ALTER DATABASE xxxxxxxxxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Your restore script here.
ALTER DATABASE xxxxxxxxxx SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

What this does is sets the database into single-user mode (which effectively kills all other connections to it), restores the database, then when it’s done it sets it back to multi-user mode. I LOVE using this for restores that I seem to do over and over.