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:
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 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

I forgot to mention that I did a DBCC OPENTRAN and there are no open transactions when I run this inside the script.
Truncating the logs and full backups are possible but I'd like to avoid it.

Hi,
[quote user="satya"]
Have you tried to see whats happening using server side trace in this case?
Article on the rebuilding index tips, if you haven't found nothing from the logs it may be better to seperate the SHRINK operation with another script rather than joining them with reindex one.
[/quote]
The article was semi-useful, you can't use the USE statement in TSQL scripts so that means that its been done in a query analyzer or similar. My aim is an automated script.
The log files must be shrunk after the database is re-indexed, I tried doing this in a script called from the re-indexing script but that did not make any difference.
Regards,
Gareth

I've switched to:
Changing the DB to the bulk-logged transaction model before the re-index
Switching it back to full transaction model after the re-index
Not 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.
There are also no open transactions when the script runs so I give up on that one.
So I'm using the new method as above.
Furthermore there was a Microsoft Knowledge base article that said you may not be able to do DBCC DBREINDEX in full and keep the log under control without a lot of backing up the transaction log and running it in different scripts.