Mike Scalise (9/29/2016)

---

John Mitchell-245523 (9/14/2016)

---

Mike

The log backup will only be huge if you haven't backed it up for a long time (or, more accurately, if there has been a lot of changes in the database since the last log backup). Speak to the stakeholders of the database and find out what's the most data they can afford to lose (in terms of minutes) in the event of disaster and then schedule your log backups to run with (at least) that frequency.

Sometimes you can back up the log file and free space won't be released. In such cases, you can get a clue about what's stopping it by running this:SELECT log_reuse_wait_desc

FROM sys.databases

WHERE name = 'MyDatabase'

I recommend you read through Gail Shaw's articles on managing transaction logs. You should be able to find them with a simple search.

John

In a development database I'm working with, I initially sized the transaction log at 500MB and, because I wasn't performing log backups, it has grown to 647MB. I ran a log backup, which was 647MB in size and then:

DBCC SHRINKFILE (log_name, 500);

GO

Then, I ran the query John shared:

SELECT log_reuse_wait_desc

FROM sys.databases

WHERE name = 'MyDatabase'

Beforehand, it returned "LOG BACKUP" It now returns "NOTHING" which I expected and which I was happy about because I thought for sure when I queried sys.database_files, it would show that it was back down to 500MB. It's not--it's still at 647MB. Also, I checked at the file system level and it's 647MB there too.

Again, I'm sure there's a good reason. Can anyone shed some light?

Thanks,

Mike