Why Is My Database Re-indexing Script Not Able To Shrink Databases? (MSSQL 2000)

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:

Go through each table
Run a DBCC DBREINDEX on the table
Move on to the next table
Once the reindexing of one database is complete
IF the database is not in simple mode

Backup the transaction log
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:



Code Snippet
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 0
2 253952 262144 29621 0 64 0
2 253952 516096 29620 0 64 0
2 278528 770048 29619 0 64 0
<cut cut>
2 9043968 370737152 0 0 0 29800000001528500007
2 9043968 379781120 29802 2 64 29800000001528500007
2 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