December 16, 2002 at 10:38 am
My SQL Server 2000 transaction logs are filling up on a DBREINDEX. I know that 2000 handles DBREINDEX differently and records this as a transaction. I set the database to BULK_LOGGED before the DBREINDEX command and still no effect when I run the script through the SQL Agent, but when I run it from the command line it seems to work as expected...
Here's the script..
DECLARE @dbName VARCHAR(60)
DECLARE @SQLString VARCHAR(120)
SELECT @dbName = DB_NAME()
DECLARE @Name VARCHAR(60)
DECLARE @oid INT
DECLARE c_table CURSOR FOR
SELECT '['+name+']'
FROM sysobjects
WHERE xtype = 'U'
AND uid = 1
ORDER BY name
--
SET @SQLString = 'ALTER DATABASE '+ @dbName +' SET RECOVERY BULK_LOGGED'
EXECUTE(@SQLString)
OPEN c_table
FETCH NEXT FROM c_table INTO @Name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @oid = OBJECT_ID(@Name)
DBCC DBREINDEX(@Name,'',100)
FETCH NEXT FROM c_table INTO @Name
END
CLOSE c_table
DEALLOCATE c_table
--
SET @SQLString = 'ALTER DATABASE '+ @dbName +' SET RECOVERY FULL'
EXECUTE(@SQLString)
"Keep Your Stick On the Ice" ..Red Green
December 16, 2002 at 11:18 am
Try using DBCC INDEXDEFRAG, I believe that is not logged where DBREINDEX is.
December 16, 2002 at 11:38 am
still logged, just runs in shorter transactions, so should prevent log fills.
Steve Jones
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply