The EXISTS and NOT EXISTS operators can often use an index whereas the LIKE and NOT LIKE operators cause a table scan. This makes EXISTS and NOT EXISTS tend to be more efficient.
RAID for Log Files
Transaction log files should be optimized for sequential IO and secured against a single point of failure. Accordingly, RAID1 (mirroring) or RAID 10 (striping across mirrors) is recommended for transaction logs.
Shrinking and Sizing Transaction Logs
It’s true that you don’t want to continually shrink and grow the log file for a few different reasons including fragmentation and incurring the overhead of growing files during a request. However, if you have decided that your transaction log is in fact too large and needs to be shrunk, here is one way to go about it. [Read more…] about Shrinking and Sizing Transaction Logs
SSIS Foreach Loop Container and Access Databases
I was struggling today to get a Foreach Loop Container to trawl a directory full of Access databases. The problem was that I had not correctly built the ConnectionString for my source connection using the Expression Editor. I found my answer at http://www.mssqltips.com/tip.asp?tip=1437. I’ll give you a preview: “Data Source=” + @[User::mAccessLocation] + “;Provider=Microsoft.Jet.OLEDB.4.0;User ID=;Password=;”
Log File Size For DB Restores
Keep the size of the transaction log file in mind when you are restoring a database.
Over the weekend, the backup drive on one of our servers ran out of space. Without transaction log backups, the log grew several times until it consumed the log disk. At that point SQL choked.
We straightened out the SQL problems and things ran smoothly the rest of the weekend. Today we went to restore the backup to a test server. At this point we ran into a problem I haven’t seen before but should’ve anticipated–the backup wouldn’t restore. [Read more…] about Log File Size For DB Restores