March 27, 2009 at 6:17 am
Dear experts,
I have a question concerning log files in sql server 2000.
I have a database that I fill every day with a package. The data comes from several others databases.
Every day I truncate the tables and fill them again. The backup plan is in simple mode.
Still my log file files up every day. I don't need the logging, if something goes wrong, I just refill the tables.
Is it possible to delete the logfile and not work with a log file?
thanks, Vera
March 27, 2009 at 6:23 am
No, the log provides integrity for transactions. You must have one.
If it's filling, it's not large enough or you have a transaction issue in your code.
March 27, 2009 at 7:27 am
Vera (3/27/2009)
Is it possible to delete the logfile and not work with a log file?
That's an abuse:-D.
Transaction log file is such an important part of a database. Even if you don't have a log file SQL server will build one for you. SQL server can't think of working without a log file.
How heavy are your insert operations?
March 27, 2009 at 7:40 am
I do millions of inserts every time the package runs. The database itself is 16 G.
Vera
March 27, 2009 at 7:55 am
Well if the transaction log file is increasing it means it needs that much of space. You can shrink the log file but, your log file would increase anyway.
How are you inserting your data? Can you post the code? Did you check your code to see any flaws?
DBCC SQLPERF - what does it return?
March 27, 2009 at 8:07 am
DBCC SQLPERF (logspace): logsize: 74.929688logspace used: 83.246666
The inserts are too complex to post here. It's actually data that comes from an AS/400 system, combined with data of several SQL dbs and cobol db.
The funny thing is that it is running for 2 years already, but only since a few days I get problems with the log file. I always thought that when the backup plan is in simple mode, there is hardly anything logged.
Further, in the beginning, I did a delete of all my records, but I changed that to truncate table because that goes faster.
I executed DBCC SHOWCONTIG and I see a lot of fragmented tables. Guess I need to do some INDEXDEFRAG.
Vera
March 27, 2009 at 8:47 am
Everything is always logged. Simple mode just allows the space to be reused when the transaction is committed.
There might be more data in the package, or some other change that's causing the log to fill.
You can go through the details of what's happening, maybe commit more often, find out why more space is needed, but if it's needed, it's needed. You need to keep the log file at the size necessary to log everything from the package.
March 27, 2009 at 10:11 am
Vera (3/27/2009)
DBCC SQLPERF (logspace): logsize: 74.929688logspace used: 83.246666
That's a fair percentage. As Steve advised, have u changed the way you are inserting the data? As Steve advised its always better to load and delete data as chunks.
I executed DBCC SHOWCONTIG and I see a lot of fragmented tables. Guess I need to do some INDEXDEFRAG.
Vera
This provokes me to think about the way your file growth is? How did you set that up? ?Did you set up autogrowth as small increments?
April 2, 2009 at 3:19 am
I changed the value of max size of the log file.
And I'm changing the way the records are deleted and inserted.
I'm inserting in different steps so that I get a commit after every step.
Thank you for your valuable replies.
Vera
April 2, 2009 at 3:41 am
Vera (4/2/2009)
I changed the value of max size of the log file.And I'm changing the way the records are deleted and inserted.
I'm inserting in different steps so that I get a commit after every step.
Thank you for your valuable replies.
Vera
You are welcome 🙂
But take a look at this where table variables aren't logged, have had a conversation in another thread:
April 8, 2009 at 5:06 pm
Log file is must for the DB in Sql. If you want to get rid of the big log file, at the end data migration process, add the following steps:
1. Detach the DB.
2. Rename the Log file
3. Attach the DB (it will automatically create a new log file)
4. Delete the old log file
Thanks
April 8, 2009 at 5:07 pm
Log file is must for the DB in Sql. If you want to get rid of the big log file, at the end data migration process, add the following steps:
1. Detach the DB.
2. Rename the Log file
3. Attach the DB (it will automatically create a new log file)
4. Delete the old log file
Thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply