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 |
|
chrissa
Starting Member
3 Posts |
 Posted - 2009-12-08 : 12:11:35
|
| In attempting to rebuild all the indexes we filled up the transaction log. The full back up option was set. We switched the database option to bulk-logged with the understanding that the reindexing would not write to the transaction log.Here are the steps we took:We had previously always backed everything up as Full. The below script was ran hoping it would change the recovery mode to Bulk-Logged, so it wouldn’t write to the transaction log.However, something did. The script took 14 hours to run, so when it was done I was expecting it to turn back the recovery mode to full. However, there was no dump the next day and then a huge transaction file, equivalent to the size of the database, the following day. USE master;ALTER DATABASE dbname SET RECOVERY BULK_LOGGED;USE dbname --Enter the name of the database you want to reindex DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,' ',80) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursorUSE master;ALTER DATABASE dbname SET RECOVERY FULL; |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chrissa
Starting Member
3 Posts |
Posted - 2009-12-08 : 14:31:26
|
| Version info(product version) (product level) (edition) 9.00.3042.00 SP2 Enterprise Edition (64-bit)Can we assume the syntax is correct at least:) |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-08 : 15:00:21
|
yes, it is syntacticly correct. I think Tara asked what version because you should look at using ALTER INDEX instead of DBCC DBREINDEX for your version.DBCC DBREINDEX is still supported though it will be removed in future versions.from BOL:quote:
This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chrissa
Starting Member
3 Posts |
Posted - 2009-12-08 : 16:42:22
|
| Thank You Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Index rebuild