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 |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2003-10-28 : 10:14:59
|
| Hi,Our database is in full recovery model and we are running a long script that contains:insert statementtruncate statementbcp statementselect statementTransaction log size is 4GB..While monitor transaction log using sqlperf(logspace)...i can see that %used go up about 50% and then immediatelydrop to <1% and the script is still running ..it goes again till 50% and then drop < 1%..log file size remains same 4GB...we are not taking any backups...this is the only user using the sql server..How can logfile used %size reduces automatically without doing backup in full recovery model?Thanks--rubs |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-28 : 12:38:41
|
| I would check the SQL Server Error Log to see if backing up of the transaction log is actually occurring. You might have a maintenance plan out there that you don't know about. The Error Log would show if a backup occurred during that time. If not, then I'm not sure why the utilization changes, maybe someone else here can answer that. Rob?The log file size will stay the same unless you run DBCC SHRINKFILE. The log does not shrink by itself, the free space in the log file will chance depending upon the statements that are running.Why aren't you backing up the transaction log? What's the point of FULL recovery model if you aren't backing up the transaction log?Tara |
 |
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2003-10-28 : 12:54:00
|
| This is just a test machine and i am checking the result/performance of this script.....so when it will go in production we do hourly log backup.....there is no maintainence plan since this is test machine...what i mentioned in post is that %used(virtual log truncation) is decreasing without the backup..it is not shrinking ....size remains the same 4GB...May be sqlperf(logspace) only shows active portion of log and not the log that might be needed for recovery.Thanks--rubs |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-28 : 12:58:37
|
quote:
Originally posted by rubs_65
May be sqlperf(logspace) only shows active portion of log and not the log that might be needed for recovery.
I would suspect that you are correct on this.Tara |
|
|
|
|
|
|
Transaction log problem