Workplace Encounters: Reclaiming Space from an Oversized Database

INTRODUCTION

This document records steps, scripts, and ideology behind the maintenance performed on a production database between 22nd April 2015 and 23rd April 2015. The details are specific to an application, but the principles may be useful to others in need of doing similar operations in production.

BACKGROUND

The database had grown to over 1.44TB with the table EPOEvents consuming over half of this size (792GB). Efforts had been made in the past to delete entries in this table older than 90 days. Unfortunately, it proved futile. The job scheduled to achieve this could never complete during any session. The reason was the poor performance of the database.

Upon discussion, a decision was made to purge the entire EPOEvents table by truncating it. The further decision was to prepare the database correctly for the expected data growth in the future.

The details of the database server in question are below:

SERVERNAME: SVR-EPO-02

INSTANCE NAME: SVR-EPO-02\ENG_AVSERVER

OS VERSION: Windows 2008 R2 Enterprise (SP1) 64-bit

DB VERSION: Microsoft SQL Server 2008 R2 (SP1) 64-bit

IP ADDRESS: XX.XX.XX.XX

DATABASE NAME: ePO4_SVR-EPO-02

EPOEVENTS TABLE

The script defining the EPOEvents table is documented in Appendix I.

It is worth mentioning that the AutoID column of this table is in Foreign Key relationships with the tables HIP8_EventInfo, HIP8_IPSEventParameter, and SCOR_EVENTS. This affected the approach used to truncate the EPOEvents table. The summary details are in the table below:

This information was extracted using the statement in Listing 1.

PROCEDURE SUMMARY

Summary of Procedure Used During the Change:

  • Deploy Five 200GB Drives
  • Stop EPO Application Services
  • Backup the EPO Database
  • Create A New File Group
  • Truncate the Table EPOEvents
  • Move EPOEvents to the New Filegroup
  • Recreate Foreign Keys on Child Table
  • Script the EPOEvents Table Definition
  • Backup the EPO Database
  • Shrink Datafiles in the PRIMARY Filegroup
  • Backup the EPO Database
  • Drop the EPO Database
  • Format Drive J
  • Restore the EPO Database with MOVE
  • Relocate TempDB to Drive Q
  • Start EPO Application Services
  • Confirm EPOEvents is Populated

Five new drives were deployed on the server. The complete list of all drives on the server is as follows:

All new drives were formatted using 64K cluster size, as recommended by Microsoft for drives containing MS SQL Server data or log files[1]. Drive J was also reformatted after taking a backup of the database.

[1] The full discussion is accessible at https://msdn.microsoft.com/en-us/library/dd758814.aspx

All application services were stopped to ensure no update on the database during the period of the change. Application Services stopped (or confirmed down) are as follows:

  1. McAfee ePolicy Orchestrator 4.6.8 Application Server
  2. McAfee ePolicy Orchestrator 4.6.8 Event Parser
  3. McAfee ePolicy Orchestrator 4.6.8 Server

The first backup of the database had been taken before any action took place on the existing structure, using the script in Listing 2.

Listing 2: First backup of ePO4_SVR-EPO-02 database

A new file group called FG_LARGE was created with the equivalent of the script in Listing 3. All files added to the filegroup were defined with a 40GB initial size, 1G increment, and 100GB max size.

Listing 3: Creation of Filegroup FG_LARGE

Listing 4 shows the complete set of tasks required to truncate the EPOEvents table successfully. The script includes notes for clarity.

The fundamental issue to note is that it is impossible to truncate a table participating as a parent in a Foreign Key relationship. It would violate referential integrity. Deleting would work and preserve referential integrity since Foreign Keys are typically defined with ON DELETE CASCADE or ON DELETE SET NULL clauses.

Listing 4: Truncating EPOEvents table and Relocating to New Tablespace

The EPOEvents table was moved to the FG_LARGE Filegroup using the script in Listing 5.

Listing 5: Moving EPOEvents Filegroup FG_LARGE

The Foreign Keys on Child tables identified in step 4.1.5, were recreated with the script in Listing 6.

Listing 6: Truncating EPOEvents table and Relocating to New Filegroup

As a backup, it was ideal for scripting the extract of the DDL of the EPOEvents table. This was done by right-clicking and selecting the option Script Table as > CREATE To > New Query Editor Window from the SSMS[1] Object Explorer. The resulting script can be saved to a .sql file.

A second backup of the EPO database was taken with the script in Listing 7 to prepare for the next stage (datafile shrinking).

Listing 7: Second backup of EPO Database

Shrinking is necessary to reclaim the space freed up by truncating the EPOEvents table. It was also crucial in this case, as the data files required relocation to a smaller drive.

The data files were relocated from the drive I — a 3 TB drive to drive P, a 200 GB drive.

The three data files in the PRIMARY Filegroup were logically named ePO4_SVR-EPO-01 (~300GB), ePO4_SVR-EPO-02 (~500GB), and ePO4_SVR-EPO-03(~400GB).

The target was to shrink all files to 40000 MB each. The task demanded five to seven iterations, each removed 50000 MB.

Listing 8: Shrinking Datafiles in the PRIMARY Filegroup

A second backup of the EPO database was taken using the script in Listing 5 to prepare for the next stage (dropping the existing database). The script is present in Listing 9.

Listing 9: First backup of ePO4_SVR-EPO-02 database

The database was dropped using the SSMS GUI — right-click the database and select the “delete” option from the drop-down menu. The equivalent SQL is in Listing 10.

Listing 10: Drop the EPO Database

This step formats the drive J, using the 64K cluster size, as recommended by Microsoft for optimal I/O performance. This task was previously performed on the new drives deployed in step 4.1.1.

Figure 3 shows the option selected in the Format… dialog box.

The restore operation was necessary for three reasons:

  1. To format drive J containing the transaction Log Files (as above).
  2. To remove any fragmentation caused by the shrink operation.
  3. To relocate the database to new drives.

The script in Listing 11 serves for restoring. Note that the backup set used for this restore is the last backup taken in step 4.1.8. Also, observe that the MOVE option serves to relocate the data files in the PRIMARY Filegroup to drive P.

Listing 11: Drop the EPO Database

For optimal performance of large databases, it is also recommended to locate TempDB files on a dedicated drive.

Using the script in Listing 9, TempDB was relocated to drive Q. Note that the redundant TempDB data files were removed during this operation. The number of TempDB data files should match the number of physical CPUs available to SQL Server.

Listing 12: Relocate TempDB

All application services were started once the SQL Server instance was confirmed OK. Application Services started are as follows:

  1. McAfee ePolicy Orchestrator 4.6.8 Application Server
  2. McAfee ePolicy Orchestrator 4.6.8 Event Parser
  3. McAfee ePolicy Orchestrator 4.6.8 Server

The script is listing 13 confirms that the table EPOEvents and the child tables are being populated after the entire process is complete.

Listing 13: Relocate TempDB

POST CHANGE TASKS

1 The job Custom_Purge EPO Events was created to keep the number of events captured within 100 days. It is crucial to ensure this job is always successful.

2 Excessive space previously allocated to the EPO database server can be reclaimed, particularly the drive I — currently 1 TB.

3 A backup job Custom_Daily_Backup was created during the change. Backups made by this job will drop backup sets to I:/MSSQL/Backup/. It is essential to change this path if the drive I will be removed from the system. It is also necessary to preserve these backups according to Group Backup Policy.

APPENDICES

APPENDIX I

APPENDIX II

Subscribe to the CodingSight digest to get SQL Server industry insides!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
{coding}Sight

{coding}Sight

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/