In Can the Transaction Log Tell Us What Happened?, I talked about whether Microsoft should take responsibility for providing SQL Server users with a tool for reading the actual contents of the transaction log. I admitted that such a tool might be nice, but it really wasn’t something that I felt was a requirement from a database vendor. You need to take responsibility for monitoring what your system is doing, and if you want to know every command that’s sent you can set up a trace. In fact, with the log, you have no way of finding out who is reading your data because the log records only changes. For very sensitive data, you might want to know every time someone reads it, and for that you’re completely on your own.
Lately, I’ve heard people commenting (or perhaps complaining?) that not only do they want Microsoft to tell them what they’ve done when something unexpected happens, but also to warn them before it happens! The particular behavior that has come up several times recently in discussions with colleagues and clients is called auto_truncate. Most people are aware that SQL Server will automatically truncate the transaction log every time a checkpoint occurs if a database is in the SIMPLE recovery model. But it turns out that there are other situations in which the log will automatically be truncated. If SQL Server can’t back up the log, it won’t bother keeping the log records available, and it will truncate the log at every checkpoint. This behavior occurs if you've truncated the log without backing it up, using the BACKUP LOG WITH TRUNCATE_ONLY command. It also happens if you’ve never made a full database backup, or if you haven’t made a full backup since running the BACKUP LOG WITH TRUNCATE_ONLY command. So even though your database properties indicate your database is in the FULL recovery model, the log can’t be backed up.
If you’re not aware of this behavior, it might cause problems for you, so some of my clients have suggested that Microsoft should warn you of potential problems. But what form should this warning take? Should it be an informational message in the Database Properties dialog box? (However, if you use only T-SQL commands to change database properties, you’ll never see that information.) Should you get a message when you first create a database that warns you that your database is vulnerable until you take your first full backup? People with automated scripts might not appreciate extra messages being returned, especially if they aren’t error messages. I’ll admit, SQL Server Books Online (BOL) could do a better job of documenting this behavior, but that’s where all warnings of this type belong—in the documentation.
If Microsoft were expected to warn you of every potential problem, there are hundreds, or perhaps even thousands, of possible messages it could generate. Would you really want a message to be generated every time you did something that just might cause a problem? A potentially enormous number of warning messages could clutter up your application log or error log, and possibly cause additional problems just by generating the messages about potential problems. Rather than expending resources to figure out all the possible actions that could have negative consequences, I’d much prefer the engineers spend their time enhancing the database engine, polishing the documentation, and investing in white papers and other educational material to help people use SQL Server most effectively.
