January 19, 2012 at 3:34 am
hi
we are using sqlserver 2005.
1. Every day my traction log file is getting filled upto 10GB.
2. Recovery model of DB :Simple
3. It was enabled to restricted growth, upto diskspace.
4. Enabled Auto growth Option
Do to which i am doing shinking the log file. finally it was reduced to 180MB from 10GB
.Will i need to uncheck the Auto growth option.
January 19, 2012 at 3:44 am
Check the autogrowth value in case it's verry large.
Before you shrink the file look at the modified date, and see if you have a daily job,task... that has completed running around that time.
January 19, 2012 at 3:48 am
Sounds like you have a super huge transaction going on during the day that you need to pinpoint! How big is the database itself?
January 19, 2012 at 3:50 am
It was happening every day .
--we are uploading CSV files to database and we are using this db for monitoring purpose.
January 19, 2012 at 4:13 am
it was around 45gb
January 19, 2012 at 4:58 am
What is the resticted size of your logfile in other what is the free spaceof drive where the log file is located.
it looks like there is insufficient space to grow the log file so ending with error. try to import the data in bunches.
January 19, 2012 at 7:15 am
I strongly recommend setting the log to that larger size and leaving it there. Shrinking it over & over and letting it auto-grow over & over, that's causing fragmentation and performance bottlenecks, every time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 19, 2012 at 8:30 am
With the recovery model set to simple, do as already suggested and batch up this import into say 10k rows at a time. After each batch SQL will probably initiate a checkpoint which will allow the same part of the log to be used again thus removing the requirement to log the entire import operation.
Consider using the CHECKPOINT command between inserts but only if batching the import does not resolve the issue in the first case.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply