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)

    SIZE = 664505KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

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?

  • 1
    What is wrong with a 600 Meg log file? That isn't exactly very large. – mrdenny Jan 7 '11 at 10:43
  • Because I just created it and want it to be shrinkable later to a minimum size, not to 600MB. I'm just doing the setup, not maintaining it (atm, that may change) – jcolebrand Jan 7 '11 at 13:11
  • do you have privileges to use SSMS to modigy log files? – CoderHawk Jan 10 '11 at 8:31
  • @Sandy ~ Yes, full permissions. – jcolebrand Jan 10 '11 at 14:22
  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'

  • 4
    If you detach the database and delete the transaction log file there is a chance that the database will not reattach to the database engine. It is also possible that the data within the database is not consistent because there could have been transactions in flight within the database log file which hadn't been flushed from the transaction log to the data file. – mrdenny Jan 7 '11 at 10:44
  • @mrdenny in this particular case there are no records in the tables, so the transaction logs should be empty. But in the future I'll keep that in mind. – jcolebrand Jan 7 '11 at 17:13
  • @marek ~ I was really hoping there was just a place in the code of the database somewhere I could toggle a setting in a table and then shrink the file – jcolebrand Jan 7 '11 at 17:13
  • 1
    @jcolebrand I know it's pretty complicated, but that's the only way I know. – Marek Grzenkowicz Jan 7 '11 at 17:56
  • If you don't mind if I leave it open a few more days of course? Otherwise you're getting the upvote. – jcolebrand Jan 7 '11 at 18:29

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.

alt text

  • I do have full permissions on this instance and this database (I was setting it up remember) and when I did just what you describe it said I couldn't remove the primary log for the database. Because that was my first thought, what you're describing. What could cause that error message? (for the record, my eventual action after about 10 minutes of fussing was to drop the old files, and recreate them with the script properly setting the file size this time. – jcolebrand Jan 10 '11 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.