Quick removal for DB snapshots

This is probably one of those isolated things that nobody will ever run into, but it’s worth putting out there.  My organization uses CommVault for backups.  Machine, Fileshare, Sharepoint, SQL…  if it can be backed up, it goes through CommVault.  One of our on-prem sharepoint servers is a bit older, and was set up… not according to best practice.  When we pointed the CommVault Sharepoint backup service at this instance, everything would back up OK in SQL, but the service couldn’t clean up the DB snapshots that it created for the backups.  Over time, this resulted in a very full disk.

CommVault confirmed that there was no reason to keep the snapshots, so we began to delete them.  Through Management Studio, this is a one-by-one process.  When you have hundreds of snapshots, you begin looking for a different way.  Here’s what I came up with:

declare
@sql1 nvarchar(255),
@sql2 nvarchar(255);

declare a cursor for
select
'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'''+name+'''',
'DROP DATABASE ['+name+']'
from sys.databases where source_database_id is not null;

open a;
fetch next from a into @sql1, @sql2;

while @@FETCH_STATUS = 0
begin
exec(@sql1);
exec(@sql2);

fetch next from a into @sql1, @sql2;
end

close a
deallocate a

There really isn’t any magic to the solution – it’s a pretty simple thing to loop through databases. The trick that caught me for a minute was how to identify only the snapshots. As you can see, this is accomplished by filtering on the source_database_id column in sys.databases. If there is a value in this column, then it is a snapshot; for a normal database, this is null.

With this script, what was shaping to take an hour or two of monotonous clicking was reduced to about 60 seconds of wait time. Mission accomplished!