Today, I continuing my series of talks on fundamental SQL Server database topics. I am a proud United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING. The most basic part of a database is a TABLE which consists of COLUMNS. A important decision during the initial design is to choose the data types that will capture the information you want in the least amount of space. I did a good job covering the various…
Tag: John F. Miner III
Monitoring Database VLF’s
The transaction log file records all transactions and the database modifications made by each connection. Heavy application processing against the database can make the transaction log grow quickly. Each physical log file is divided into smaller logical units called virtual log files (VLFs). If there is no auto-growth setting on the log file, the database will stop working, be marked as suspect, when you run out of space. Therefore, it is important to monitor database file space, both data and log, so this does not happen. I will present a…
Check Database Integrity Task
Today, I will be continuing the series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks. We will be designing a SSIS package that checks the database integrity of all the user databases. I am going to start up SQL Server Data Tools (SSDT) which leverages the Visual Studio 2010 shell. This development environment replace the older Business Intelligence Development Studio (BIDS) which was released with SQL Server 2005. I will create a project named “check-database-integrity” under the “c:\ssis-depot\”…
Identity Column Insert
Today, I want to talk about how to insert a value into a identity column. Most of the time, the auto increment or IDENTITY column will just be happy by being left alone. Very seldom, a power user does something stupid like turn off the foreign key constraint and remove a key from a reference table. How do we add back the key? I will be dusting off the [WILDLIFE] database from a prior article. This database has one table named [ANIMALS]. It has 445 unique animal names each with…
Maintenance & History Cleanup Tasks
Today, I will be continuing the series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks. We will be designing a SSIS package that executes a data retention policy in regards to backup files and [msdb] history records. One of the most important job functions of a DBA is to have a recent backup chain in which a point in time restore can be performed. A backup chain consists of the most recent FULL backup, differential (DIFF) backup, and…
Backup Database Task – Part 2
Tonight, I will be continuing the series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks. We will be designing a SSIS package that executes a transaction log backup on all user databases and save the results to a directory of my choosing. One of the most important job functions of a DBA is to have a recent backup chain in which a point in time restore can be performed. A backup chain consists of the most recent FULL…
Backup Database Task – Part 1
SQL Server Data Tools (SSDT) is the newest release of the Visual Studio (VS) development environment for the SQL Server projects. I suggest that you use the 2010 VS Shell since the 2012 VS has some GUI color issues that are reminders of terminal screens in the early 1980’s. I hope they listen to developer feedback and fix this issue in a future release. I will be doing a series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks.…
2013 Goal Statement
I just finished blog entry one hundred yesterday. It is quite an accomplishment since it takes one to two hours to research, design, and compose an article. I figured it was time to reset my goals for the upcoming year. In the next thirteen months, I will be focusing on the Microsoft SQL Server Stack: Database Engine, Integration Services, Reporting Services and Analysis Services. I will of course throw an article off topic from time to time when it suits me. During this time I also want to update my…
Deleting Large Data
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…
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…