We help IT Professionals succeed at work.

SQL 2005 Cleaning up

336 Views
Last Modified: 2012-02-08
1.) I have a 20 billion byte table in SQL 2005.  I will be deleting about 1/3 of the rows in the table.  When I perform these deletions, can i temporarily stop from writing to the log file by setting the backup to SIMPLE or if I do this am i asking for trouble by switching from FULL to SIMPLE and back again to FULL.  I assume I should do a full backup, switch to SIMPLE, perform my deletions, then switch back to FULL backup?

2. ) What gets written to the Log file when you delete an index from a table?

3.) I was told to delete non clustered indexes first, then the clustered index?
Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Alfredo Luis Torres SerranoASP .Net Developer

Commented:
1.
    Simple – in simple recovery mode, the transaction log is not backed up so you can only recover to the most recent full or differential backup.
    Full – in full recovery mode you backup the database and the transaction log so that you can recover the database to any point in time.
    Bulk Logged – in bulk logged mode, most transactions are stored in the transaction log, but some bulk operations such as bulk loads or index creation are not logged.

2.

  The previous state of the records in the table

3

Why you need to delete clustered index? It holds pointers for all non-clustered indexes. Deleting Clustered index means you are locking whole table during the deletion process and your table is heap. Heap table means you can't remove fragmentation of indexes. So avoid that thought to delete clustered index. Well you can delete non-clustered index with no issue.

Commented:
dastaub,

What are trying to accomplish with 'cleaning up' exactly?  It seems like you want to archive old data, and have fresh indexes.  You also want to minimize downtime because you are stopping writes while doing this as maybe there is no maintenance window period.  Is that correct?

Author

Commented:
there is no maintenance window period = Correct, 24x7 uptime.
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>can i temporarily stop from writing to the log file by setting the backup to SIMPLE<<
No.  The Transaction Log is always written to regardless of the Recovery Model used.

Whatever you do, do not enable Auto-Shrink and DBCC SHRINKFILE should only be used in an emergency.
CERTIFIED EXPERT
Top Expert 2012

Commented:
Masteraco,

>> but some bulk operations such as bulk loads or index creation are not logged.<<
Slight correction: bulk operations under Bulk logged Recovery Model use minimal logging.  So yes, even bulk operations are logged.

Author

Commented:
if the Transaction Log is always written to regardless of the Recovery Model used, when is the transaction log emptied in the simple recovery mode?
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>when is the transaction log emptied in the simple recovery mode?<<
When using the Simple Recovery Model the log is truncated after a checkpoint.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.