How to Shrink I/NET SQL Database when It Becomes too Big

NOTICE

POTENTIAL FOR DATA LOSS.
The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure, please contact Product Support Services prior to attempting the procedure below.

Issue

When the I/NET SQL database becomes too big, performance for I/NET will be low. And when tried to backup the big database and restore it to another computer, it will take several hours to finish the whole restore.

Product Line

TAC I/NET

Environment

I/NET

SQL Server

Cause

There are two files for the SQL database for I/NET: InetDb.mdf and InetDb_log.ldf. The InetDb.mdf file is the data files of the database, where all of the tables, indexes (other than full-text catalogs), stored procedures, etc. are stored. The .mdf is the primary data file for the whole database. While The InetDb_log.ldf is the transaction log. The transaction log is where all transactions are written to. Sometimes it will be the InetDb_log.ldf growing too big. Since it is less important, it can be shrank without any impact on I/NET operation or any data/events/transactions lost.
 

Resolution

1. Be sure to use Dbcreate to backup the database first.

2. Prepare the Microsoft SQL Server Management Studio Express. Click here to down this tool for version 2005 free of charge. If for version 2008, click here to download. Then install this tool.

3. Open the Microsoft SQL Server Management Studio Express and connect to the database.

4. Right click on InetDb and choose Property. Set Recovery model to Simple in the Options.

5. Right click on InetDb and choose Tasks-->Shrink-->Files. Choose the File type to Log, and keep the Shrink action in Release unused space.

6. Check the file size of InetDb_log.ldf. It should have decreased to much smaller now.