SQL Server Log File Space
Posted on 2016-10-18
I am somewhat of a novice SQL Server user and have some general questions about SQL Server log (.ldf) files. When I create a database in Management Studio (SQL Server 2012), 2 files are automatically created - a data file (.mdf) and a log file (.ldf). My database is now quite large as I have imported millions of rows into it. The data file is approximately 45 GB. The log file is about the same size. What I don't understand is why the log file is as big as the data file and whether there is a way to significantly reduce it. I've tried shrinking it but that only gives back about 8% of the space.
I guess I really don't understand a log file's purpose. I thought it was built to ensure that a database would be "rolled back" to a starting point if a big import query failed three fourths of the way through. If that is what a log file is for, why do they have to stick around after the query runs successfully?
Maybe that's really not their purpose at all.
Can someone help me to better understand them? i've read a few things that I've found on the web but most of them are over my head.
Thanks in advance for your help.