Don't quite understand SQL Server transaction logs
I've read up on SQL Server transaction logs, but still not entirely comfortable with how to use/manage them. I get that they are important for things like transaction rollbacks, mirroring, replication and log shipping etc.
To me, they still seem li开发者_StackOverflow中文版ke a black box and I'm not entirely comfortable doing anything with them. Are there tools that allow me to view a transaction log file or get information about it? If I don't need things like transaction log shipping, is it ok for me to shrink and/or truncate my log files periodically? Particularly in the case of restoring backups onto a test instance - do I really need a multi-gigabyte log file taking up space? What other features do I need to be aware of that have particular dependency on transaction logs and would not work if I shrank/truncated log files?
As a general rule you should not need to 'do' anything with transaction logs except ensure they don't get too big. While it is possible to read from them with log mining tools this is rare and and until you are comfortable with other aspects of the DBA role, don't concern yourself with it.
They are a black box and they work fine like that.
You should select a recovery model that suits your business needs. Generally this will involve determining if you need point in time recovery, if so probably "Full Recovery Model" else "simple Recovery Model".
If you do use Full Recovery Model than you need to backup your DB on occasion (daily or weekly and differentials are common options here) and your transaction logs each 15 minutes, hour couple of hours depending on the volumne of transactions, space available and your maximum amount of data you can afford to lose in the worst case scenario.
If you use Simple recovery backup your DB on occasion but understand that you cannot do point in time recovery.
It's common to have "Full Recovery Model" in Prod and "Simple Recovery Model" in dev and test. But you have to identify your particular needs and make the decision yourself.
The safest way to 'shrink' transaction logs is to manage your backups so that you never have to explicitly do so.
Read up on recovery models and feel free to ask follow up questions.
精彩评论