Do you use Microsoft SQL server?

Is your database file size out of control? It's your Transaction Log File! This tutorial will walk you through how to take control and shrink it to a smaller size!

I really hate how the SQL Transaction logs get really big therefore making my databases unstable (Also since I have my website on a shared hosting account; it gets really expensive due to disk usage of these logs).

I know EasyCFM.COM is a ColdFusion web site; but since every good ColdFusion application requires a good database server I figured a SQL Server tutorial could come in handy.

So I have a database that is over 2GB in size (and 1.7GB of it is transaction logs.. Yes, it does get that big!) What are transaction logs you ask and why are they so big in size?

Well, they are logs of every SELECT, DELETE, UPDATE, INSERT (and other commands) your web site has performed on the database. If your site is busy and gets lots of traffic then this will make your database transaction logs HUGE and in turn your database file size really big!

So how do you shrink transaction logs to allow you to make smaller backups or to lower your disk usage?

Well, you use the folowing two commands in SQL:

The first one is to backup your log file:
Backup log [NAME_OF_YOUR_DATABASE] with truncate_only

The next one is:
DBCC SHRINKFILE([NAME_OF_YOUR_DATABASE_LOG_FILE], 2)

Here is how you use them.

1) Go to your database in SQL Enterprise Manager; this is the tool you use to connect to the database.

2) Go to your database and select it. This will basically tell MS SQL Enterprise Manager that you are wanting to use this database.

3) Now from the menu on the application, select Tools > SQL Query Analyzer. This will load SQL Query Analyzer and load your database into it automatically (It also automatically logs and authenticates your users to the SQL server). This allows you to now execute SQL queries to your database (This is where we will run the SQL commands) directly.

4) Now let's say that your database is named "MyDatabase". You would type this in:
Backup log MyDatabase with truncate_only

Then click the "Green Arrow" that will "RUN or EXECUTE" your SQL code. It will take a moment then the bottom screen will tell you:
The command(s) completed successfully.

5) Now let's put the second piece of code:

DBCC SHRINKFILE(MyDatabase_log, 2)

Then click the "Green Arrow" that will "RUN or EXECUTE" your SQL code. It will take a moment then the bottom screen will tell you:
The command(s) completed successfully.

That's it... now your database file size is really little and therefore this will make your website faster and your disk usage far less.