Most of the time, the transaction log is your friend since it can be used for a point in time recovery. However, when deleting large amounts of data from a database, it can be your enemy. I will be reviewing THREE different TSQL patterns to delete data. Today, we will be using the MATH database that contains prime numbers from 2 to 6 million. I will post the actual files on this article so that they can be detached and reattached as needed for each test. We need to remove…
Tag: CHECKPOINT
Shrinking the Transaction Log
The transaction log file records all transactions and the database modifications made by each transaction. Heavy application processing against the database can make the transaction log grow quickly. Therefore, the transaction log must be truncated on a ADHOC basis to keep it from filling up. Each physical log file is divided into smaller logical units called virtual log files (VLFs). Truncation provides free space by deleting inactive virtual log files. To avoid running out of log file space or physical disk space if growth is set to unlimited, one should…
Full-Text Search – Part 1
Today, I want to go over why text searching for patterns is an expensive query operation. I am going to create a [WILD LIFE] database that contains a table called [ANIMALS]. A identity column called [ID] will be populated by the system automatically and a [NAME] column will be loaded with 445 animals names that I grabbed from WIKIPEDIA. The idea is to search for animals that have a root word in common. The snippet below creates the database and table. The full SQL script including insert statements is at…