george25 (11/9/2010)

---

I have quite a few very large databases and, like Giles above, I need to shrink the log files after large amounts of data are imported into the databases. All the databases use the simple recovery model. Is it such a bad thing to shrink the log file when using the simple recovery model?

paul.knibbs (11/9/2010)

---

and make sure you have regular backups so they don't keep growing out of control.

giles.clapham (11/9/2010)

---

Hi Paul,
I appreciate your comments and advice but just to clarify, and I'm open to being educated on this, my DBs are in Simple recovery they get backed up (Full) frequently.
Occasionally we have the need to import large amounts of data which needs some form of processing and validation and we end up with a bloated transaction log. An empty but bloated transaction log.
This transaction log now gets backed up as part of the Full backup which takes additional time and resources.
So would you still say shrinking the log file back to it's pre mass import size is a bad thing?
Giles

arr.nagaraj (11/9/2010)

---

Taking frequent t-log backups using log shipping should reduce/stop ldf growth.
Shrink of log file doesnt distrub log shipping.

Joshua T. Lewis-378876 (11/9/2010)

---

In an effort to curb the necessity of shrinking, I have always taken a few steps whenever setting up a DB:

1.) Always separate by partition (or even physical disk and controller) the three key portions of a database; Data, indexes, and logs. Also, if you have the resources, put the tempdb on a separate partition; This can frequently be the culprit of disk space issues, and the only way to shrink is a restart of the SQL Server.

2.) Using the separated partition schema, build a secondary file and file group in the DB, intended strictly for indexes. When building any nonclustered indexes, always build them to this filegroup. CAUTION: If you make the mistake of building a primary key or clustered index to your "Indexes" file group, you will not only build the index there, but will also cause all data in the object the index is built on to move into the secondary file group.

3.) Build two ldf files on any DB running in a "Full" Recovery model. The first file is set at a fixed size with no auto-growth allowed, and the second is configured very small, but with auto-growth allowed. Monitor your logs through a full business cycle, and you will have an idea how large your fixed file should be resized to. When you see the second log file begin to grow again, you know that your transaction load has increased, and the fixed file needs to grow again.

As a side note that applies to all of this, when configuring auto-growth, I would always recommend using a fixed MB as opposed to a percentage, and further, make sure that your fixed MB growth size is divisible by 8KB (This is the size of a single SQL I/O write). This should prevent page segmentation due to auto-growths.

Just my two cents, but in my experience, having everything distributed and compartmentalized like this makes managing growth of a DB much less cumbersome, and also has a positive impact on performance.