How can I change the Initial Size of my sql log LDF file?

Question

Somehow it's been created with a minimum size of 32gb.

The actual database is ~2gb in size.

The database is wiped and recreated every day from an external datasource, and thus doesn't need any logs at all since it's rebuilt from scratch each time.

That said, I'd like to shrink the LDF file as it's taking up a huge amount of space on my dev machine's SSD and it doesn't need to be 16 times the size of the database itself.

I have discovered I can't shrink the log below its minimum size of 32gb, so I need to change the minimum size. If I go to properties on the database, I can change it, but when I press OK nothing happens.

Is there a command or technique I can use to do this other than scripting the database to CREATE and deleting it and remaking it with a smaller LDF?

Answer 1

@AaronBertrand is right you should probably change your process to handle it during the creation phase but I thought I would go over why you are having a problem with the shrink.

First confirm that your database is in Simple recovery mode. From what you are saying you don't need log backups since the database is reloading from an external source every day.

This will let you shrink your log file. Generally this is not something recommended since if a log file grew to a given size there was probably a reason and it's likely to grow back again causing you a different set of problems. In your case though again, you are loading from an external source and your log file may (does?) not need to be the size it is. This being the case shrink your log file to the size you want for an "Initial" size.

Once you have shrunk your file to the size you want you can now change the properties to give it a smaller initial size. You can't do this while the log file is larger than the size you are trying to set it to. That's why you are getting the error "Specified size is less than or equal to current size".

Of course none of this will help if your load method is a restore from another SQL Server. The process of the restore will force the mdf and ldf files to the same size as the backed up version. At that point you would have to add the above steps to the end of your process to shrink the log file after each restore.

Last but not least if you are loading the data by using some ETL tool your load itself may be causing the log to increase in size. If that's the case then your log will re-grow after your load and you will have to do the above steps to shrink it back down again. This will however increase your load time by a certain amount because the log file will have to re-grow during each load.