I Need To Make A Change With My IT Support! Call (225) 706-8414

Microsoft SQL Log File to big? Here’s how to get it back under control!

You are running Microsoft SQL Server and your log files are huge and continue to grow.

Solution

Remember its not really a good idea to shrink any log file. This only causes log file fragmentation which leads to performance issues.  Its better to have created an oversized log file the first time rather than shrinking or growing the file. Just think of it as a page file. Just like the one in Windows. The more fragmented it is the more resources it will use locating all the parts and pieces. One contiguous file is best. So if you have the resources… just leave it. Although if you feel that its already causing you a performance hit because of auto grow or you just want to start over, here is a quick way to do that. No data loss and no downtime.

What you need to do is simply change databases recovery model from Full to Simple and back again. Once you flip over to simple the log file will disappear because simple mode does not use it.

After the log file is gone, switch back to Full and the log file will be recreated with the settings that it was configured for. You can also change these settings if needed.

You can do this via SQL Manager or with the following script.

To change the recovery model

  1. Connect to the Database Engine.
  2. From the Standard bar, click New Query.
  3. Copy and paste the following example into the query window and click Execute. This example shows how to change the recovery model in the model database to SIMPLE by using the SET RECOVERY option of the ALTER DATABASE statement.

Transact-SQL

USE master ; ALTER MYDATABASE model SET RECOVERY SIMPLE ;

The wait for the log file to be removed and run the following.

USE master ; ALTER MYDATABASE model SET RECOVERY FULL ;

Your done!

 

Concerned About Cyber Attacks?

CLICK HERE >

Want to Migrate to the Cloud?

CLICK HERE >
Office 365

Ready to Experience Microsoft Office 365?

Want the latest IT news directly in your inbox? Subscribe now!