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 |
|
JozzaTheWick
Starting Member
16 Posts |
 Posted - 2002-09-18 : 14:20:33
|
| All,I am having problems reducing the size of my transaction logs. When backed up, they are currently about the same size as the db backup itself (2 ~3 gig).However, they are only this big when they are backed up (via the maintenance plan) on the same day as the DB backup. Other days, they are about 400kb. Could it be that the transaction log on that day contains all transactions executed during the DB backup itself? (The logs are backed up after the db - should they be backed up before?)In addition - even after the logs are backed up (and dbcc SQLPERF (logspace) gives 'Log Space used' as < 1%), I cannot shrink the log file (e.g. using the command 'dbcc shrinkfile (2,100)). dbcc returns the message: 'Cannot shrink log file 2 (DB_Log) because all logical log files are in use.'If I'm not able to shrink the file, can I instead point the db to a new log file and delete current one?thanks,Joseph |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-09-18 : 14:28:43
|
Let me guess, the maintenance plan is doing reindexing as well?This will cause your log to grow a lot.Are you on 7.0 or 2000? You should be able to use dbcc shrinkfile. quote:
If I'm not able to shrink the file, can I instead point the db to a new log file and delete current one?
I would not suggest doing that, if you want to keep your database.-Chad |
 |
|
|
JozzaTheWick
Starting Member
16 Posts |
Posted - 2002-09-18 : 17:01:08
|
| Thanks Chad, you are correct. The plan is reindexing as well. This is essentially an OLAP system with very infrequent updates, so don't think I need to reindex that frequently.I am using SQL Server 2000. Still don't know why I can't use dbcc shrinkfile - but smaller transaction log backups will do for now - thanks again.cheers,J. |
|
|
|
chadmat
The Chadinator
1974 Posts |
|
|
JozzaTheWick
Starting Member
16 Posts |
Posted - 2002-09-18 : 18:04:30
|
| Thanks again, Chad. I wasn't backing up the transaction log. (well, I _thought_ I was, via the Enterprise Manager, but clearly I wasn't).I now have the logs backed up and am able to shrink them down to reasonable sizes. Thanks again!J. |
|
|
|
|
|
|
|
|
Unable to shrink HUGE transactions logs