October 18, 2006 at 1:42 am
Hi.
I am handling a database in SQL 2000 for my client in a shared hosted location.Now i found that the transaction log gaining 80% of total sizes.
As of suggestion on using Shrink,i dont think its of good idea.I need to
send some script to the host who handling that database.kindly suggest me
on what the script can be to truncate the log..will it be ok if i take a back up and truncate the log file..
Your help will be more thankful...
October 19, 2006 at 11:36 am
First things first. What is your database 'recovery mode' - simple, full or bulk-logged ? Answering this question will partially determine the options and best course of action to take. Next question is to ask is how much data can you afford to lose - 1 day, 'n' hours, 1 hour, 15 minutes ? The answer to this question dovetails into the answer to the first question in determining what steps you should take in order to solve your issue properly.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 20, 2006 at 2:48 pm
First thing you need to do is decide how the space is being used. Open Enterprise Manager, expand until you see the database, single click on the db, then right click on the db and select View>Taskpad. Look towards the bottom of the right pane. You should see two horizontal bars that are split into two sections. The bottom bar is for the TLog. The section on the left shows how much DATA is in the log file and the right side shows how much free space is in the log file. Truncating just removes DATA. The free space is still there (in fact there will be more once the data truncates). To return the free space to the operating system, you need it to SHRINK. This is done several ways. 1st, if your database is in SIMPLE Recovery Mode, it will happen automatically. If it is Full Recovery, you need to have Auto-Shrink turned on, or you need to shrink the file (DBCC SHRINKFILE ....).
Before you shrink the file, you need to find out why it is growing or why it grew. 1 - your default log size is set that large, or 2- you have LOTS of transactions (as they are committed/log backed up the data amount will shrink, but the free space will remail), or 3 - you are doing reindexing of the database (via maintenance plan or script). Reindexing can cause the TLog file to grow 2 1/2 times the size of the database itself.
References: Books OnLine: Transaction Log
DBCC SHRINKFILE
DBCC REINDEX
-SQLBill
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply