Hi Experts. This is MSSQL 2014.
We are having this discussion at the office.
Recently drop index operations are taking several minutes, even up to an on a table which has 500 M rows.
We use Commvault as our backup tool, not native SQl Server backups.
This is our question: Does a database backup block certain operations such as drop index?
And visa versa - could a drop index block a database backup until the drop index completes?
I say 'No' to both but just asking for other opinions.
Thanks
The native backup process in SQL Server does not lock or block any object, including dropping/altering indexes. It is possible that there are delays due to IO contention; after all, backups and index creation/altering require IO.
The only other possibility I can think of is if Commvault is doing something beyond what the native backup does that may be causing the delays/blocking.
Thanks JamesK. That's what I thought. We restarted this server over the weekend and the issue has gone away.
It may be that Commvault had queued up backups that were interfering and the reboot flushed those out. So we're not yet sure what was causing the slow load times - still monitoring.
I believe a backup snapshots the database. Dropping an index would (could?) then require the dropped pages to be written to tempdb in order to maintain the snapshot, aka "copy-on-write". Naturally that process has some overhead; exactly how much delay, if any, there would be is dependent on your system, usually particularly your I/O responsiveness.