Skip navigation

A database in a SQL server typically has full recovery mode enabled, which creates a transaction log that grows over time. Unless a full SQL backup is made or you have set the growth limit of your SQL database transaction log, this file will continue to consume your storage space.   So what is the function of this file?  SQL Server’s database transaction log is useful for data recovery, as it allows a DBA to recover data  from a certain point of time (from the last time a full backup was made).  And in production and data critical systems, transaction logs are kept in separate partition of SAN and similar storage systems.   But not most database systems needs this function.  So I made this article if you ever find yourself  needing to delete, shrink or disable your database’s transaction log under Microsoft SQL Server

The Burden of Transaction Log Size
Some admins (including me) forgot about this transaction logs after a database for a certain application is created. Some application as well, that auto-creates the database for you typically leaves the recovery model to full recovery, and if you leave it  as it is, and don’t do a full backup regularly, this will grow over time and consume your partition space.

So how do you remove or shrink the size of your database’s transaction log?

  1. The safest way is to do a FULL Backup.  This assumes that you still have remaining space to do so.  Doing a full backup is easy, follow this steps from Microsoft :  How to Backup a Database SQL Server (SQL Management Studio) or if you are using a dinosaur SQL Server, here’s the steps for SQL Server 2000.  After your full backup, your transaction log is reset.  If you want to completely disable transaction log, then follow the next step
  2. To disable transaction logging, you need to convert recovery method from Full to Simple Recovery.   This is also one way to delete or remove a large transaction log without needing to do full backup (in case free space is a problem).
  3.  Login to your server using SQL Server Management Studio.  Right click on the database  you’d like to turned off transaction logging then click properties.  Go to Options.  Change the Recovery Model from Full to Simple. Click Ok.
  4. To change the recovery mode of SQL  in  SQL Server 2000, follow the steps  in Microsoft site:  SQL Server 2000 Backup Types and Recovery Models
  5. Now we can shrink the transaction file log and recover the enormous disk space it uses .  While your in SQL Management Studio, right click the database you’ve just converted to Simple Recovery, then click Tasks > Shrink > Database.  Tick Reorganize files… then finally click OK.
  6. And here’s a link for SQL Server 2000 users from Microsoft website:  How to Shrink a Database using Enterprise Manager
There you go.   With your SQL Server Recovery model set to Simple, your SQL Server’s disk IO is much less busy, without you worrying about the disk space in your server.   Still, a regular full backup is still the right way to go, in making sure the transaction log size is maintain.
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: