I have a SQL 2008 R2 database, whose .mdf file was 2.2 GB, but the transaction log file (.ldf) was 350 GB! We had a problem, where the log file was set to autogrow by 10%. When that would happen, our disk latency would bring the server to a stand still for 20 minutes. I know we haven't been taking very good care of our database, and I would like to change that moving forward.
Last night, I did a backup of the transaction log. The backup file is 318 GB. Next, I ran a shrink > files on the "log" file type, which only ran for a couple of seconds at most. Now my transaction log file is still 318 GB. On the shrink screen, it says my available free space is 308567.05 MB (99%). So it seems like I have tons of room in the log file, but I can't seem to shrink the file size.
I was looking at this question: http://www.experts-exchange.com/Database/MS-SQL-Server/Q_24466045.html
, and it looks like I may need to Trucate the log file, by using this command:
BACKUP LOG <database_name> WITH TRUNCATE_ONLY;
Then, I can shrink the file to a specified size, using something like:
DBCC SHRINKFILE (ur_db_log_filename, 3000);
Is that the best thing to do? Do I need to truncate first? If I run a DBCC SQLPERF(logspace), it shows that my log is only using 0.75% (less than 1%). I'm just not sure what size I should shrink to.
Once I get the log file down, what is the best practice to keep the file small?
Thanks in advance for your help. Unfortunately, I'm not a DBA, it just one of the many hats I have to wear, and I certainly don't know everything there is to know about SQL.