The Latest Research
While I was researching for my book Inside SQL Server 2000, one of the SQL Server developers at Microsoft told me that the logging performed in the Simple model is identical to the logging performed in the Full model. That is, SQL Server logs every row of data inserted during SELECT INTO, bcp, and BULK INSERT operations and every index row generated during CREATE INDEX operations. I was told that the big difference between Full and Simple Recovery models is that in the Simple model, the log is constantly being recycled, analogous to using the truncate log on checkpoint option in previous releases of SQL Server. This new logging behavior is very different from the simplest logging in older versions, which never log CREATE INDEX operations; SQL Server records only the fact that it has built an index. Older versions also offer the option SELECT INTO/BULKCOPY. With this option enabled, SQL Server can perform SELECT INTO and fast bulkcopy operations but records only allocation information and the fact that it carried out the operation.
The simple tests that I ran seemed to verify my understanding that the Full and Simple models performed similar logging. However, I was really just verifying that a lot of activity was happening in the log during a bulk operation even in the Simple model. Because my tests showed a lot of activity, I assumed that they proved my theory.
To look at log rows, I used the undocumented table-valued function fn_dblog(). I included the function as part of Listing 1's script, which shows the type of test I ran to prove my assumption. I won't explain exactly what this function returns; all my script does is display a row count that represents the number of log records. In the beta version of SQL Server 2000, where I originally ran my tests, the number of rows in the logs in the Simple and Full Recovery models was almost the same. In SQL Server 2000 Service Pack 3 (SP3), the log has more rows in the Full Recovery model, but the Simple model still has more rows than I expected. However, my original tests didn't compare the number of log records generated in the Simple model with the number of log records generated in the Bulk-Logged Recovery model—I completely ignored the log size in the Bulk-Logged model. I realized this last point when SQL Server MVP Dan Guzman sent me a script that he used to determine logging behavior.
Guzman's script didn't use any undocumented commands; it merely created the database MyDatabase with a small, fixed-size log, then ran the same SELECT INTO query in all three recovery models. Only in the Full model did SQL Server generate an error saying that the log had filled; neither the Simple nor the Bulk-Logged model generated that error. Listing 2 shows Guzman's tests, which are almost identical to those in Listing 1, except that Guzman's database log file has a fixed maximum size instead of using the default unlimited size as the script in Listing 1 allows. Guzman's script also tests all three models, not just the two models that I tested. It seems pretty clear from the results of Guzman's scripts that the Full model is logging a lot more information than the Bulk-Logged and Simple models are.
You can run more tests of your own. You can add a third test to Listing 1 that includes the Bulk-Logged model. Besides looking at the number of rows in the log after each SELECT INTO operation, you can use the following command to look at the log size:
DBCC sqlperf(logspace)
Although you can now ascertain that the Simple model doesn't log as much information as the Full model, that doesn't mean you should be complacent about log size if you're running a database in the Simple model. The log grows proportionally to the size of the transactions you're running, and log cleaning won't happen automatically until the oldest open transaction completes. So if you have a very large or long-running transaction that overlaps hundreds or thousands of other transactions, the size of your transaction log can still become a problem.
Another lesson to be garnered is to not be complacent about things you think you know, especially when you notice behavior that doesn't seem to support your understanding or when you hear from people whose opinions you respect that things aren't the way they seem. Running some quick tests when doubts start creeping in can not only clear up many misunderstandings, it can also give you additional practice in investigating SQL Server behaviors. And the next time you have a question about how SQL Server works, you can figure it out for yourself.
End of Article
Prev. page
1
[2]
next page -->
