Joseph

SQL Server 2000 Standard will only use 2GB whatever you do. That's the theoretical maximum - more often you see something like the 1.7GB you are witnessing.

John

Excellent article, Frédéric. I have recommended this to people in the past.

One thing I don't quite understand about the left and right bounds. Suppose I wanted to add...

One way of doing it is to build a script to perform the reindexing and then run it. Here's some pseudo code; yours will no doubt end up more...

Reindexing automatically updates the statistics, so you don't need to include both. I don't know exactly how a maintenance plan decides what it is going to reindex, but if...

Or, if you don't want to toggle recovery modes but also want to reduce your backup size, you could truncate the log without backing up, after reindexing. Of course,...

Yes, I think you're right. However, that appears to be an undocumented table or view in SQL 2000 that didn't survive to 2005. That's the danger of using...

INFORMATION_SCHEMA is an ANSI standard, so it should work on most RDBMSes, although I've never tried it on other vendors' platforms. It certainly works on SQL 2000 and 2005.

John

Was there a transaction running at the time you tried shrinking? What was the original size of the log file (ie when you created the database)?

John

Only shrink your log file if you're sure it's never going to grow to its current size again. Otherwise you waste processor and disk resources when it re-grows and...

Darryl Sibson (5/14/2008)

---

I've tested this already in our training environment which is why I use a backup device. Rather than doing each transaction separately I can roll back the...

Doing differential backups will reduce the number of backups to be restored from and make it...

Yes, it does. If you're looking for values that start with "LONDON",...

If you don't use brackets (parentheses), the conditions will be evaluated in the order they appear. So WHERE condition1 AND condition2 OR condition3 OR condition4 is the same as WHERE...

Might be an idea to recycle your errorlog every week or so to prevent it from growing very large. But I agree with the others - don't restart SQL...

MD (5/12/2008)

---

Agree with Steve, collecting 2G of changes in 20 minutes is a heck of a throughput. What I suspect is that your log file size is already 2Gs...