snippetsqlMinor
SQL Server 2008 log files have minimum sizes, what gives and how do I make them smaller?
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)
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
I know where I went wrong, but how do I fix it easily?
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%)
GObecause 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.
-
Physically delete the log file from disk (this is crucial, but can be risky - see mrdenny's comment below).
-
Attach the database using the
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.