I’ve found several times in the past times where some genius setup the initial file size of a SQL server transaction log to be huge. Today for example, I came across a database on Microsoft SQL 2008 R2 server that had a log file with an initial size of 100+gb while the database itself was only around 450mb.
So here is the quick dirty way to change the initial size of the transaction log file:
- Create a full backup of the database in question
- go to the database properties (right click on the DB -> properties), under the “options” section, change the database recovery model to “Simple” then press OK
- right click on the database to go “Tasks” -> “Shrink” -> “Files”
- From the “File Type” menu, select “Log” and press OK
- go back to database properties -> options and change the recovery model back to “Full” (do not press OK)
- click on the “Files” section of the properties window and change the initial size and growth rate of the log file. then press OK
- jump for joy, your log file is now a reasonable size!