Re: Can anyone 'crash course' me in SQL maintenance?

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Sat, 16 Feb 2008 17:14:33 -0800 (PST), Ryan <mindflux98@xxxxxxxxx>
wrote:

If you run the DBCC DBREINDEX statement, the transaction log may
expand significantly when your SQL Server database is in Full recovery
mode.

The Rebuild Index Task uses the ALTER INDEX statement when connected
to SQL Server 2005, and the DBCC DBREINDEX statement when connected to
SQL Server 2000.

Thanks for that. Do you know how any of this aplies to SQL Server
2000 w/ SP4?

AFAIK this issue was first fixed in the maintenance plan in SQL Server
2005. The recommended practice was either to use DBCC INDEXDEFRAG.

You can also try to switch from full recovery mode to bulk-logged -->
re-index --> back to full recovery:

USE master
ALTER DATABASE db_name SET RECOVERY BULK_LOGGED
GO

<yore commands go here

USE master
GO
ALTER DATABASE db_name SET RECOVERY FULL


Perhaps I should turn off my db-reindex job for a week
or two (it will run tomorrow morning) and see how my transaction log
looks at that point. Maybe it's simply this reindex?

Yes, give it a try. And change to DBCC INDEXDEFRAG.

P.S. Looking up the syntax and comments on DBCC DBREINDEX in SQL
Server 2005 BOL (Sep. 2007): "Feature will be removed ... Use ALTER
INDEX instead.

jas
.

Relevant Pages

  • Re: Transaction log with huge proportions
    ... DMS DBCC SHRINKFILE ... > Set your database to SIMPLE Recovery (Enterprise manager, ... >>> SQL Server in BOL for a complete discussion of all the concepts involved. ...
    (microsoft.public.sqlserver.clustering)
  • Re: torn page detection and auto shrink: performance?
    ... And I have up-to-time recovery possibility. ... Curently, database size shows 18GB, but the used space shows 8GB. ... Shirinking files, will cause that by expanding files, sql server will ... There should be enough free space to permit reindexing of this table. ...
    (comp.databases.ms-sqlserver)
  • Re: Restore leaves DB in Loading state
    ... The SQL server is doing precisely what you told it to do. ... Standby means to hold active transactions in the 'standby' file and not ... the database to go live, then you need to 'recover' it. ... just the database name and the recovery command. ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction log with huge proportions
    ... See BOL for syntax and warnings. ... read books-online and learn about SQL Backup and Recovery. ... otherwise you can set your database to SIMPLE ... >> SQL Server in BOL for a complete discussion of all the concepts involved. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Transaction log with huge proportions
    ... I konw that i lack some skills, i'm still taking microsoft courses. ... If you want point in time recovery, you will need to set up a regular ... otherwise you can set your database to SIMPLE recovery. ... > SQL Server in BOL for a complete discussion of all the concepts involved. ...
    (microsoft.public.sqlserver.clustering)