SQL Server 2008 log files have minimum sizes, what gives and how do I make them smaller?

Question

Ok, to start with, I screwed up when I created the databases, using a create script roughly like so: (artificial linebreaks and names/paths for wrapping purposes)

CREATE DATABASE [EXAMPLE] ON  PRIMARY 
( NAME = N'EXAMPLE_Data', FILENAME = N'J:\SQLServer2008\MSSQL.INSTANCE\EXAMPLE.mdf', 
    SIZE = 446046KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'EXAMPLE_Log', FILENAME = N'J:\SQLServer2008\MSSQL.INSTANCE\EXAMPLE.ldf', 
    SIZE = 664505KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

because I scripted it out from an existing development database, and I just wanted to get something going. I screwed up when I didn't change my sizes to something reasonable (like 4096KB) and so now I can't shrink the logfile below roughly 600MB.

I know where I went wrong, but how do I fix it easily?

Answer 1

1. Detach the database.

   sp_detach_db @dbname = 'EXAMPLE'

2. Physically delete the log file from disk (this is crucial, but can be risky - see mrdenny's comment below).

3. Attach the database using the sp_attach_single_file_db stored procedure.

   sp_attach_single_file_db @dbname = 'EXAMPLE', @physname = N'J:\SQLServer2008\MSSQL.INSTANCE\EXAMPLE.mdf'

Answer 2

Hope you have enough privileges to access SSMS. If you do, follow these steps

1. Open SSMS
2. Connect to you SQL Server Instacne
3. Open Object Explorer, Right click the database (which you want to shrink the log file min size) and select “Properties”
4. From “Properties” window – select “files”
5. You’ll see Database files grid at bottom right
6. Click add to add new log file, give
   • logical name
   • File Type = Log
   • Initial size = 1 MB ( or your preference)
   • Cick Autogrowth and set preferences accordingly
   • FileName
7. Now click on existing log row and select “remove”
8. That’s it! now you have new log file with desired min size.