I was having difficulty shrinking my VSIP SQL Server 2005 Databases to remove unnecessary transactions and empty space from the log file. It took me a while to solve so I thought I should post my results here. I got the answer from http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/ and here is the resulting script (which I have since put in a stored proc - but I will not include that detail here):
Note that you will need your database name **and** the SQL Server name of your log file (different to the NTFS name) for this look at sys.database_files. In this script my database name is "MyDatabaseName" and the SQL Server name of my log file is "MyDatabaseName_log" :
DBCC SHRINKFILE(MyDatabaseName_log, 1);
BACKUP LOG MyDatabaseName WITH TRUNCATE_ONLY
With this done I got back 50% of the entire space on my disk - guess I needed it :-) !!
Please read http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/ for more info - particularly if your log file contains data that is important to you.