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 |
|
rikleo2001
Posting Yak Master
185 Posts |
 Posted - 2005-10-25 : 16:21:02
|
| Hello EXPERTS,I think this is bad week for me, all problems are appearing at the same time. I need your favour in this regard.I have database of 11 GB, and 2 weeks earlier its Log was ONLY 168 MB and Now its gone up to 13 GB. (I am running out of disk space)I did not create or delete any indexes. Only process I am doing is that I am inserting/Deleteing the data Quiting often from that Database.I tried to shrink that file with DBCC Shrinkfile (1,1), BUT NO use. Just stays there as it is..Please help me..ThanksSKR |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-25 : 16:22:44
|
| What is your database recovery model set to? If FULL, are you performing regular transaction log backups?Tara |
 |
|
|
activecrypt
Posting Yak Master
165 Posts |
Posted - 2005-10-26 : 00:42:33
|
| Hi,Read below discussed threads and article , it will clear yr doubtsGenrally when you are doing Bulk DML operations / DBREINDEX their are chances for T-LOG getting full , i suggest you to read follow :[url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9676[/url][url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=7648[/url][url]http://www.sql-server-performance.com/images/SQLServer2000FastAnswers_Chap03.pdf [/url][url]http://www.sql-server-performance.com/absolutenm/templates/default.aspx?a=260&template=printarticle.htm [/url]-----------------------------------------------------------MSSQL Server encryption software http://www.activecrypt.com |
|
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2005-10-29 : 17:38:37
|
| Sorry Guys..I got sick, was on bed for last 2 days... Yes My Database recovery model is SET TO FULL..I will go through those articles, and will post my success or failure..Thanks guys for help.SKR |
|
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2005-10-29 : 18:43:34
|
| Great..I just applied those commands and it did the magic. from 14 GB to 1 MB... wowwww1. BACKUP LOG 'DATABASE' WITH TRUNCATE_ONLY2. DBCC SHRINKFILE(2,1) Now I have some concerns.. does that normal? is that going to effect my applications in any sense? how often I need to fire those commands??Here are my database setting for your consideration.. please let me know what is the best configuration i need to adopt..1. My application is combination of Bulk /Import/Insert all DML queries, Reporting.... Etc..IT MEANS ALL THE TIME SQL SERVER IS BUSY DOING SOMETHING..Databse Configuaration:1. Data File File Growth by 10% and unrestricted file growth.2. log File File Growth by 10% and unrestricted file growth.3. Options..Recovery Model = Full 4. Auto Update Stat and auto Create Stat is Checked only all others are not checked..That's it. please let me know what would be the best configuration for that type of database..Thnaks a lot allllllllllSKR |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-30 : 02:27:44
|
| If new data is only arriving via bulk import AND you could re-import that data after a crash (i.e. the files are still available for a while) then you could change your recovery model to Simple.If not, or you need recovery to point-in-time, then make sure you are making TLog backups regularly - every 10 minutes or so will probably keep a lid on the size requirement of the Log file.Do NOT shrink your log files etc. as a routine. Its OK after some abnormal activity (like a massive purge of data), but SQL Server has to reacquire the disk space for the logs, that has a "cost", and leads to greater fragmentation.I would suggest that you change your file growth setting to a number of MB, rather than percentage. I don't know how wizzy your servers are, but on mine a file growth of 1.5GB would take probably half an hour, during which time the server will slow to a crawl.General rule of thumb is that the Log file may grown to about 130% of the Data file - that's about what yours has reached, so you may find that is actually what you need (or the effort to avoid it getting that big is unacceptable!!)Kristen |
|
|
|
rikleo2001
Posting Yak Master
185 Posts |
Posted - 2005-10-30 : 15:04:47
|
| Thanks a Lot for your reply.I am statisfied and happy now.SKR |
|
|
|
|
|
|
|
|
Transaction Log is Growing Rapidly