Sql-server – Sql Server Simple Recovery – Backup selective file groups

If you switch over to full recovery mode, you can do this with filegroups, but it's really, really clumsy. You leave the data in the primary filegroup, and put the indexes in a separate (non-default, that's the key) filegroup.

Then you stagger your backups so that you're doing filegroup backups of the primary every night, and transaction log backups every X minutes.

When disaster strikes, you restore the primary filegroup by itself. The data is suddenly online, but the indexes are not. However, to get back to normalcy, you'll need to export that data into a new clean database and add indexes from there. You can't bring the database completely online without restoring all of the filegroups, and you can't say "I don't need that other filegroup anymore anyway."

For more about how this works, check out my video tutorial on filegroup restores.

I had a similar problem. I needed to delete a couple million old records. The delete failed after about 30 minutes because the available space for the logfile was too small. We solved it by modifying the delete statement to delete only 500,000 records at a time. This solved the problem.

To apply that knowledge to your case. Can you run a few smaller updates instead of one big one? This can be done by explicitly adding 'begin transaction' and 'commit' statements in your stored procedure. In addition to statements at beginning and end of the stored procedure, you can issue a commit after a million lines and start a new transaction than. I wouldn't do a commit after every update since this will slow down performance greatly. Another option would be to limit the stored procedure to a certain amount and call it several times or create similar stored procedures that work on different parts of the data.

Since you are not using explicitly the transaction boundaries, you might have some luck setting the transaction isolation level to uncommitted reads. See here. I think it might speed up the processing, but I don't expect it to have a major impact on the transaction log. Be aware that you enable dirty reads by setting the isolation level to uncommitted reads.