What's the simplest way to shrink transaction log file on a mirrored production database?
I have to, as my disk space is running out.
I will make a full database backup before I do this, so I don't need to keep anything from the transaction log (right? I have daily full database backup, probably never need point-in-time restore, though I'll keep the option open if I can – that's all the .ldf is really for, correct?).
Solved:
OK, after doing 2 backups through SSMS (not TSQL) of just the log, creating a totally new backup set, the Shrink-Files-Log dialog in SSMS finally actually worked, freeing some disk space.
Not sure why 2 backups where needed, or why TSQL didn't work, and there was no difference in the reported "space available to be reclaimed" in the shrink dialog (it was at 99% for all the shrink attempts after the first backup too, but still didn't free any space), but problem solved for now. Thanks all.
Best Answer
Do a transaction log backup, with whatever method you feel most comfortable with.
This will cause the transaction logs that have already been committed to the database to be deleted from disk. Ideally, you should actually create a database maintenance task to do this for you on a recurring basis for precisely this reason - to eliminate the old transaction logs so you don't fill up your disk.
Per the other bit of your question... no, not really. Yes, they perform that function, but not only that function.
Databases are not backed up (or written to) in the traditional fashion that other files are, because the database file itself is constantly in use and constantly changing. So a single "point in time" back up would either require taking the database offline to "freeze it" in a consistent state or result in different parts of the backup containing different data than was there when the backup started.
What transaction logs are is records of every "transaction" the database performed. Rather than writing to the database file every time a record is changed, updated, added, removed, etc., those actions are written to a separate file, a transaction log, and then committed into the database file when the SQL server determines it's safe to do so without bringing any activity to a halt. So transaction logs are, in effect, where changes to the database go before they actually become changes to the database [file].
So, if you need to get back to a given database state, or point in time, the transaction logs are "replayed." Essentially, not copying the file data, but going to the most recent point-in-time state found for the database and then doing all the same things that got the database to the specified [later] state. But, it's important to note that at any given time, your transaction logs will contain transactions that will not have been committed to the database yet. So they're more than just the ability to perform a point-in-time restore. They contain [some] changes that are being made, or will shortly be made to the database.
This is why you're forced to do a backup before purging the transaction logs - once that backup's done, the system has a point-in-time copy of the database to reference for any future restores, and is able to determine which transactions have been committed to the database, and which have not. And with that information, the system knows which obsolete transaction logs to delete for you and which not to.
This can, however, take some time, depending on the size of your transaction logs. If you've never done one before, brace yourself it'll take a while.