All to often I see people online saying that they detach a database then delete the log file to shrink it. This is a very dangerous operation which can leave you with a corrupt database that you can’t do anything with.
While you can normally attach a database without the log file, it doesn’t always work. Sometimes SQL Server won’t be able to recover from this and create a new log file and you’l have lost the database.
If your database is growing and has gotten very large, then you’ll want to change the recorvery mode from FULL to SIMPLE. This will help keep the transaction log file small, and is perfectly safe to do. The only thing you will loose is the ability to do point in time restores of the transaction log. However point in time restores are only available if you backup the transaction log (which would have prevented the log from growing out of control to begin with).
The reason that many people do this, is that the name of the file is misleading. Normally logs are very safe to delete, however the transaction log isn’t your normal log file. A transaction log keeps a record of every change to the database, along with the before and after values. All changes are written to the transaction log before they are commited to the database itself so that if the server fails the database is left in a consistant state.
If your log file has simply gotten huge, and you need to make it smaller, you can use the DBCC SHRINKFILE command to shrink the log file to a smaller size. This shouldn’t be done on a regular basis. If you are doing this on a regular basis leave the file large it’s at the size it needs to be or change the database recovery to SIMPLE.
I’ve got a 15GB DB and I’m using weekly full backups with 15m tlog backups. I keep four weeks worth of fulls and tlogs as we are still seeing a lot of base level application code changes and are being extra cautious in terms of point-in-time recovery ability.
I was planning on adding in daily differential backups so that I can toss my old tlogs mostly to reduce the number of files to manage but also to speed up recovery events. Given that differentials are really a rolling up of tlogs since the last full back up I am wondering if:
1 Can I do point-in-time recovery using: the newest full backup prior to the time I want + the newest differential backup prior to the time I want + tlogs as necessary from that differential backup to the actual point-in time.
2 This is probably answered by the first question’s answer but for clarity sake: is it ok to delete the tlogs that are older than differentials?
Differential backups aren’t just a rollup of the transaction logs. They are actually a copy of each record which has had an internal flag changed.
1. Yes, provided that the point in time is after the differential was taken.
I realize that I wasn’t clear in my question but you answered me anyway. I specifically wanted to know if point-in-time recovery can be done for periods where there are differential backups but not tlogs and the answer is no.
Thanks again – great blog!
Sorry, Yesterday was kind of a ruff day, I thought I got all your questions answered. But at least you got the info you needed. 🙂