SQL 2005 Cleaning up
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
there is no maintenance window period = Correct, 24x7 uptime.
>>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.
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.
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.
>> 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.
ASKER
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?
>>when is the transaction log emptied in the simple recovery mode?<<
When using the Simple Recovery Model the log is truncated after a checkpoint.
When using the Simple Recovery Model the log is truncated after a checkpoint.
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.