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 |
|
some_cs_student
Starting Member
5 Posts |
 Posted - 2007-08-03 : 00:53:03
|
Hi,Currently we have a variety of SQL 2000 (and 2005) database servers, we are having issues with the maintenance plan of a few SQL2000 boxes where they no longer have enough hard disk space to do a full index-rebuild on the system.Now we want to re-build the databases indexes approximately once a week, or maybe a little less often, in the past this has worked fine with maintenance plans.However, we now have issues because we have some databases in offline mode, and we are quite low on disk space with no plans for hardware upgrades anytime soon. The temporary solution is to turn the index rebuilds off.I have been working on a script that will: * Cycle through each database and within that database: o Go through each table o Run a DBCC DBREINDEX on the table o Move on to the next table * Once the reindexing of one database is complete * IF the database is not in simple mode o Backup the transaction log o Run a DBCC SHRINKDATABASE with the required amount of free space * Go to the next database until all are complete.The logic is quite simple but so far this has not worked, it would appear something is locking the transaction log until the script exits. Now the script works fine excluding the shrinkdatabase, I always get:DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]Shrinking database: inf_dev target percentage: 10 at: Aug 2 2007 5:33PM [SQLSTATE 01000]Cannot shrink log file 2 (INF_PROD_Log) because all logical log files are in use. [SQLSTATE 01000] Where I'm indexing the INF_Prod database. A DBCC LOGINFO shows something along the lines of:BACKUP LOG successfully processed 45162 pages in 152.607 seconds (2.424 MB/sec). [SQLSTATE 01000]FileId FileSize StartOffset FSeqNo Status Parity CreateLSN ----------- ---------------------- ---------------------- ----------- ----------- ------ ---------------------------2 253952 8192 29618 0 128 02 253952 262144 29621 0 64 02 253952 516096 29620 0 64 02 278528 770048 29619 0 64 0<cut cut>2 9043968 370737152 0 0 0 298000000015285000072 9043968 379781120 29802 2 64 298000000015285000072 9043968 388825088 29801 0 64 29800000001528500007 Clearly there is something in the log file towards the end. However, I don't know why this is happening as I'm running the script in the master database and I've backed up the transaction log of the database I'm working on. I've tried doing Full backup + Transaction log + Shrink, it fails.I've tried waiting 10minutes in the script + shrink, it also fails. However, if I open a query analyzer and do a backup log, then a shrink it works perfectly every time. However in the script it always fails no matter what I do. Where am I going wrong here? Regards,Gareth |
|
|
some_cs_student
Starting Member
5 Posts |
Posted - 2007-08-03 : 05:27:49
|
| I forgot to mention that I did a DBCC OPENTRAN and there are no open transactions. Truncating the logs and full backups are possible but I'd like to avoid it. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-03 : 11:37:47
|
| Don't shrink db if server has enough disk space, that's performance killer. |
|
|
|
some_cs_student
Starting Member
5 Posts |
Posted - 2007-08-03 : 19:55:24
|
Hi,quote:
Don't shrink db if server has enough disk space, that's performance killer.
Agreed, I might begin asking for the hard disk again, its not like it costs that much. However, currently the problem is lack of disk space so I want to shrink the log files after a index rebuild and they get massive. Eg. DB is 30 gig, log file will get to 10gig if the database is not well indexed. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-03 : 23:41:15
|
| You can shrink log file easily after log backup and switching to simple recovery model, but may need change whole sequence like:1. backuk log2. switch to simple recovery model3. shrink log file4. switch recovery model back5. full db backup |
|
|
|
some_cs_student
Starting Member
5 Posts |
Posted - 2007-08-04 : 04:13:28
|
quote:
You can shrink log file easily after log backup and switching to simple recovery model, but may need change whole sequence like:
I could do this but I'm avoiding it, this means that the database has to go out of the full-recovery mode for a short amount of time unless I do full backup before and after the index rebuilding, either way its not ideal :(Any idea of why the current method is not working?Regards,Gareth |
|
|
|
some_cs_student
Starting Member
5 Posts |
Posted - 2007-08-06 : 05:30:36
|
| I've switched to:Changing the DB to the bulk-logged transaction model before the re-indexSwitching it back to full transaction model after the re-indexNot performing the shrink as its now unnecessary.Seems to work really well, I cannot do the shrink database no matter what I do. I've checked the open locks, no open locks at the time the script runs.I've tried checkpoint, did not help.So I'm using the new method as above. |
|
|
|
|
|
|
|
|
Why re-indexing script not able shrink databases?