THIS WAY HAS WORKED WELL FOR ME:

The easiest way I have found to immediatley shrink the log file is to detach the database, rename the .LDF file then reattach the .MDF file. In SQL Server 2005 you'll need to remove the missing .LDF file from the list before attaching, and one will be created for you.

 

I DID NOT GET THIS ROUTINE BELOW TO WORK WITH SQL SERVER 2005:

 

From the April 2001 edition of SQL Server Magazine:

 

Shrinking Log Files Immediately

You can use the DBCC Shrinkfile statement in SQL Server 7.0 to shrink the size of a specified data file or log file for a related database. But SQL Server professionals often ask me why the DBCC Shrinkfile command doesn't shrink the size of their transaction logs immediately and how to make their transaction logs smaller.

SQL Server 7.0 doesn't immediately shrink log files when you issue the DBCC Shrinkfile command. The DBCC Shrinkfile operation occurs only at checkpoints or transaction log backups. SQL Server segments each physical log file internally into a number of virtual log files (VLFs), which make up the transaction log. SQL Server marks the VLFs as truncateable either after SQL Server has backed them up or at checkpoints. At any given time, you might have VLFs with free or reusable space at the beginning, middle, and end of the log. Only when the VLFs that SQL Server marked as truncateable are at the end of the log file can the DBCC Shrinkfile operation remove the VLFs and shrink the log file. Because SQL Server can shrink a log file only to a virtual-log-file boundary, you can't shrink a log file to a size smaller than the size of a virtual log file—even if you aren't using the log file. (For more information about virtual log files, see SQL Server Books Online—BOL.)

Listing 1 shows a script that shrinks the log file immediately after you stop running the script. The script first marks a shrinkpoint, which tells the DBCC Shrinkfile (or the DBCC Shrinkdatabase) command where to shrink the log file to. The script then forces truncateable VLFs to the end of the log file and issues a BACKUP command to truncate the log. In my experience, you need to run the script for 3 to 4 minutes before stopping it manually.

—Simon Su  yqsu@microsoft.com

 

Listing 1: Script to Shrink a Log File

/* Run "SELECT fileid, name,filename FROM <db_name>..sysfiles" to get
the fileid you want to shrink. */

USE <db_name>
GO
DBCC shrinkfile(<fileid>,notruncate)
DBCC shrinkfile(<fileid>,truncateonly)
CREATE TABLE t1 (char1 char(4000))
GO
DECLARE @i int
SELECT @i = 0
WHILE (1 = 1)
BEGIN
WHILE (@i < 100)
BEGIN
INSERT INTO t1 values ('a')
SELECT @i = @i +1
END
TRUNCATE TABLE t1
backup log <db_name> with truncate_only
END
GO

Microsft SQL Server 2005 does not include the Service Manager system tray application, but a 3rd party application is to the rescue.

http://www.sqldbatips.com/showarticle.asp?ID=46

This works great for a developer's workstation running SQL Server 2005 where you may not want the database running all the time.

DECLARE @TruncateStatement nvarchar(2000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR SELECT N'TRUNCATE TABLE ' +
   QUOTENAME(TABLE_SCHEMA) +
   N'.' + QUOTENAME(TABLE_NAME)
FROM
   INFORMATION_SCHEMA.TABLES
WHERE
   TABLE_TYPE = 'BASE TABLE' AND
   OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
   N'.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
FETCH NEXT FROM TruncateStatements INTO @TruncateStatement
WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM TruncateStatements INTO @TruncateStatement
   EXEC(@TruncateStatement)
END
-- Clean up work
CLOSE TruncateStatements
DEALLOCATE TruncateStatements

You can not rename a SQL Server database from Enterprise Manager, but you can with the sp_renamedb stored procedure.  You must first set the database to singe user.

1. Set the database to Single User by right clicking the database in Enterprise Manager, select Properties, select the Options Tab, check the Restrict Access checkbox, and select Read-Only, then click OK.
2. Close Enterprise Manager (since we can only have 1 user accessing it at a time now).
3. Open Query Analyser.
4. Type:  sp_renamedb <oldname>, <newname>
5. Don't forget to reset the Restrict Access settings on the database that you changed in step 2.

I can’t tell you how many times I’ve done an import and ended up with the wrong database owner.  Use this following script to change the owner on all tables in a database.

In the sql below, set the @oldowner and @newowner variables appropriately.

DECLARE @oldowner sysname, @newowner sysname, @sql varchar(1000)

SELECT
  @oldowner = 'OLD_OWNER',
  @newowner = 'dbo',
  @sql = '
  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
  WHERE
      QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
      AND TABLE_SCHEMA = ''' + @oldowner + '''
  )
  EXECUTE sp_changeobjectowner ''?'', ''' + @newowner + ''''

EXECUTE sp_MSforeachtable @sql

(I found this somewhere on the internet some time ago but have lost it's reference.)