Claus Topholt’s Blog

I’m posting this because I can never remember how to properly shrink files in SQL Server 2005. It’s something that should be setup in a Maintenance Plan and coordinated properly with the facility management responsibles, but sometimes a client has an immediate problem with a transaction log growing too big and something needs to be done ASAP.

Selecting Database -> Tasks -> Shrink -> Files even after doing a backup with log truncation never seems to work. So an immediate solution in T-SQL is to do the following:

USE [database_name]
GO
BACKUP LOG database_name WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (database_name_log)
GO

Edit: Less than 24 hrs after I posted this code, a client needed me to do a quick transaction log truncation using the above method..

The release date for SQL Server 2008 is February 27, 2008. I am really looking forward to this release for a number of reasons.

I can’t wait to see the promised tighter integration with Visual Studio. In particular Intellisense for T-SQL, which is supposed to rudimentary, but at least it’s there. Even though most of the data access on my projects take place through an Object-Relations Mapper toolset, sometimes there’s no way around designing views, sprocs or doing the odd direct database CRUD operation using good old T-SQL.

Other great things include the addition of new datatypes such as Date, Time, DateTime2 and DateTimeOffset, the MERGE statement for doing conditional UPDATE/INSERTs easily and encryption of data and log files!

In short, It’s going to be a very interesting spring 2008

Read more here:

http://www.microsoft.com/windowsserver2008/default.mspx