When you have a fairly active DB, your going to get a pretty large log file unless you have a suitable maintenance plan.
We've had a few problems before where large log files have taken up all of the available disc space, causing application problems when acessing the DB.
If you don't need the logs, then this will tell you how to shrink them. But you
must set up maintenance plans to look after you databases and conserve resources (it will also make your DBs a little more efficient).
If you don't know how to set up a maintenance plan,
read this article. If you still don't know after that - then find someone who does and they might be able to help you out.
Anyway, on to the task of shrinking the log file. I use a fairly dirty method to do this myself, but it works and frees up diskspace instantly. It is not appropriate if you actually need the log files.
What you need to do is this:
- Open SQL Enterprise Manager (EM) and connect to your database server with the huge log file(s).
- Select the DB in question and go to
Tools>SQL Query Analyser
- In Query Analyser, execute the following command:
BACKUP LOG <DATABASE NAME> WITH TRUNCATE_ONLY
GO
This will truncate the log for you, but won't reductthe file size just yet.
- Back in EM, alternate select on your DB and got to
All Tasks>Shrink Database
- In the window that opens, click the 'Files...' button. This will open a new window.
- Change the select list at the top tospecify the log file (usualy ends in '_log') and under the Shrink Action section, select 'Truncate free space from the end of the file'.
- 7. Click OK, and then click OK again.
- 8. Done - make some tea or coffee, take a deep breath and relax. You log file is now smaller and you got some disk space back.
WARNING: If you are at all uncertain about any of these steps, go find a DBA or someone who know more about SQL to help you.