Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-12-31 : 08:14:37
|
| kaveh writes "I am using of sql server 2000 as RDBMS for hosting my database .when i insert and delete records the transaction file ( LDF ) becomemany big .But the amount of available data in database is very less than it .for example when i insert 100000 record in database and thendelete 99999 record the transaction file is very big .How i can control the size of transaction file ?Thank you kaveh dargahi " |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2003-12-31 : 09:32:33
|
| you mean the transaction log. how big is your transaction log?. how big is your database size?.if there are no space constraints , dont disturb the transaction log.still, if you are looking for shrikning file http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27029 .-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
 |
|
|
nishithrn
Yak Posting Veteran
58 Posts |
Posted - 2004-01-03 : 01:09:26
|
| Hey Kaveh,it seems the recovery model of your database is "FULL". I recommend, you change the same to "SIMPEL".Regards |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-01-03 : 01:48:38
|
quote:
Originally posted by nishithrn
Hey Kaveh,it seems the recovery model of your database is "FULL". I recommend, you change the same to "SIMPEL".Regards
I don't recommend you do this. This means you will not have the ability to perform a point in time recovery. So many DBAs make this change without understanding the consequences of this change. If you can afford to lose transactions, go ahead and change to a simple recovery model it's your neck not mine.What you need to do is schedule regular backups of your transaction log. This will keep them from growing wildly out of control, as well as allowing you to maintain recoverability of your database. An easy way to set this up is to use the maintence plan wizard in enterprise manager. Check BOL for details on how to manage your transaction logs.Also, when you DELETE from a table, that transaction is logged. That is why your logfile grows. If you want to delete all the contents of a table without logging, use the truncate table command instead. -ec |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-05 : 14:28:22
|
| If you need the ability to restore to a point in time, then yes keep the recovery model at FULL. If you do not need this, then change it to SIMPLE. Some systems can afford to lose all of the data since the last backup, so SIMPLE is fine for these. On about 90% of the systems that I work on though, FULL is required.Tara |
|
|
|
scottpt
Posting Yak Master
186 Posts |
Posted - 2004-01-07 : 11:13:43
|
| How about backing up the tran log? creat a job to backup then tran log and create and alert to backup the tran log if the tran log is 90% full. This will work even if you choose no_log. |
|
|
|
|
|
|
|
|
Size Of Transaction File