You may size the transaction log file at
100MB, but if a bulk-insert comes along, SQL Server will treat it as a single transaction.
SQL Server will log every individual INSERT statement, sometimes causing the transaction
log to grow to enormous sizes (I??™ve seen 100MB go to 160GB). When this happens, you
must manually shrink the transaction log down to a reasonable size. This can be a frustrating
process, because you often must run the shrink command multiple times or in
conjunction with other functions. Lots of material is available both on Microsoft??™s Knowledge
Base and in the SQL community itself, so I won??™t go into details here. Suffice it to say
that Simple Recovery is not necessarily the answer for log maintenance. That??™s where
Bulk-Logged Recovery comes in.
nNote Many claim that you should never use Simple Recovery mode for a production database. I??™ll challenge
this notion in later chapters.
Bulk-Logged Recovery
Bulk-Logged Recovery mode has always been misunderstood. Most DBAs have a general
idea of what it does and how it works, but when it comes to articulating the specific
details and implications of its use, clear speech usually devolves into a semiaudible
mumbling.
Pages:
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81