HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
2008whatlogsqlandminimummakesizesfilesgives

Problem

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?

Solution

-
Detach the database.

sp_detach_db @dbname = 'EXAMPLE'

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

-
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'

Context

StackExchange Database Administrators Q#364, answer score: 7

Revisions (0)

No revisions yet.