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 |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2003-12-08 : 10:33:32
|
| I ran a DBCC REINDEX on my 2,200+ table DB this weekend to solve fragmentation problem. It worked since my database in use is now 5Gig instead of 12Gig which is where it should be.However, my trx log grew from 200MB to 12gig with 98% in use. Is it possible that REINDEX caused this?I performed a BACKUP LOG and now the trx log in use is down to 5% (707MB). Why would it still be using 707MB?I need the space so I performed a DBCC SHRINKFILE (2,TRUNCATEONLY) to which I get an error...Cannot shrink log file 2 (JDE_DEVELOPMENT_log) because all logical log files are in use.I also tried DBCC SHRINKFILE(2,5000) & still get the same error.How can I get around this problem?Thanks in advance... |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-08 : 11:50:12
|
| OK, I solved SHRINFILE problem by doing a BACKUP LOG [MyDB] WITH NO_LOGDBCC SHRINKFILE(2,1000)Still wondering why my trx log is using 55MB, and if REINDEX caused it to jump to 12 Gig if anybody knows.To resolve this in the future would it be wise on my part that after the DBCC REINDEX of all tables that I do...BACKUP LOG [MyDB]DBCC SHRINKDATABASE(MyDB,20) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-08 : 13:00:39
|
| Yes DBCC DBREINDEX is going to use a lot of transaction log space. The command is doing a whole lot of work. I would not recommend performing DBCC SHRINKFILE or DBCC SHRINKDATABASE in a production environment unless you can not afford the disk space needed. Next time that you run DBCC DBREINDEX, it is going to need the space anyway, so just let it have it. DBCC DBREINDEX should be run about once per week. How often does your backup log job run?55MB is not a lot of space used in the transaction log. I would not worry about that at all.Tara |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-08 : 13:13:46
|
| This database is a production environment which is in use Monday-Friday from approximately 8am-6pm using Full Recovery Model.I do a backup log every hour from 6am-6pm Monday-Friday, a full db backup is performed via Veritas every Monday-Friday night around 10pm, and then another trx log is performed at 11:45pm every Monday - Friday with the INIT option.The DBCC REINDEX job occurs every Sunday at 2am. These are the steps...EXEC sp_MSforeachtable @command1="dbcc dbreindex('?')"BACKUP LOG [MyDB] TO DISK = N'D:\SQL BACKUPS\Production Trx Log Backup' WITH NOINIT , NOUNLOAD , NAME = N'MyDB Transaction Log Backup', NOSKIP , STATS = 10, DESCRIPTION = N'Hourly Backup of MyDB Trx Log', NOFORMATBACKUP LOG [MyDB] WITH NO_LOGUSE MyDBDBCC SHRINKDATABASE([MyDB],20)All Comments/Suggestions appreciated!Thanks Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-08 : 13:17:46
|
| My recommendeation is to remove DBCC SHRINKDATABASE. There is no need for it unless you are VERY low on disk space or will no longer need the transaction log space. DBCC DBREINDEX does need the space, so keep it.Tara |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-08 : 13:50:41
|
| Yeah, I somewhat agree. However, since this is the only operation that will expand my trx log to 12GB I thing it's just wasting space. On the other hand, if I keep the trx log at 12GB then I know my REINDEX would not fail. Currently I have a 100GB drive that only has 20Gig free. If I let this stay at 12GB, then I only have 8GB. Perhaps it's time to get some more space. |
|
|
|
|
|
|
|
|
DBCC REINDEX made 200MB TRX log > 12Gig