This is the mechanism that the allocation code uses to determine which file to allocate from next when a filegroup contains multiple files. It ensures that allocations are fairly distributed...

No - most objects will have data stored in both files - SQL Server (loosely) round-robins allocations from all files in a filegroup (I can go into details of how...

1. Just the way the design works. Any value that doesn't fit on the rest of a page will have a new page allocated for it.

2. Its really not possible...

Not always. There's a complicated algorithm which determines whether to use a new page or not. For instance, inserting two 2k values into an empty table will use a single...

They're minimlly logged (just the page allocations to allow recovery/rollback to functional correctly) and then they're appended as you describe.

Basically, yes.

Why are you shrinking tempdb? If it grew to a certain size that's because the query workload you have needs that much tempdb space to run in. If you shrink...

Hussam - please don't start a new thread to reply. As I've already explained - this data isn't captured in a log file anywhere. Look up Profiler in BOL.

Thanks

Mostly likely the remote query timeout is still set to 600 seconds and your bcp is running longer than that. Lookup 'remote query timeout option' in BOL for details of...

The storage jumps when you hit 4k per text value because only a single text record of that size can be stored on a page (remember a page is 8192...

Are you talking about the transaction log? That info isn't stored in the transaction log. Have a look at Profiler in BOL.

Thanks

Because your DB has gone suspect, its possible the virus scanner (or whatever was lockign the file) has caused SQL Server to throw the DB offline.

Can you post the output...

The basic shrink operation moves data below the shrink threshold you've set and then releases the free portion of the file to the OS.

The NOTRUNCATE option simply doesn't release this...

Definitely read our whitepaper.

When you say they require reindexing, is it just because the fragmentation has gone up or because the fragmentation is actually causing range scans to slow down?

Are...

In full recovery mode, the log file is also truncated when you do a log backup - was this the case in those two instances?