T I G

Perhaps this trick was an old trick, but i’ve just do this after several years i use SQL Server 2000. It’s begin when my database transaction grows until 2GB. When i try to backup and bring it to my PC, I ran out space. So, i try to ask my friend madhi about how to shrink Transaction log on SQL Server 2000. Here is how to shrink this transaction log.

Step by Step :

1. First, Open SQL Server Enterprise Manager and browse database on your server.
2. Press Right Click on database that you want to shrink transaction log and choose properties.
3. Choose on tab, select Option and then you see like those picture on the top.
4. Select all of those red mark like on the picture.
• Recovery Model : SIMPLE

Information, this the short explanation about Recovery Model.
Simple Recovery
Simple Recovery allows the database to be recovered to the most recent backup.
Full Recovery
Full Recovery allows the database to be recovered to the point of failure.
Bulk-Logged Recovery
Bulk-Logged Recovery allows bulk-logged operations.

• Setting :
• Auto update Statistics : Specify that out-of-date statistics needed by a query for optimization are built automatically during optimization.
• Torn Page Detection : Specify that incomplete pages can be detected.
• Auto Close : Specify that the database is shut down after its resources are freed and all users exit.
• Auto Shrink : Specify that the database files are candidates for automatic periodic shrinking.
• Auto Create Statistics : Specify that any missing statistics needed by a query for optimization are built automatically during optimization.

On part 2 i’ll try to explain more detail so you can choose what’s best for your SQL server 2000.