using System; using System.Collections.Generic; using System.Text; using Microsoft.SharePoint; using Microsoft.SharePoint.Administration; using System.Data; using System.Data.SqlClient; using Microsoft.SharePoint.Utilities; using System.Web; public class DeleteDatabaseOnSiteDeleted : SPWebEventReceiver { public override void SiteDeleting(SPWebEventProperties properties) { base.SiteDeleting(properties); using (SPSite site = properties.Web.Site) { //Before the site collection is deleted //set status to disabled if the currently being deleted site collection //is the last one in this database if (site.ContentDatabase.CurrentSiteCount == 1) { site.ContentDatabase.Status = SPObjectStatus.Disabled; site.ContentDatabase.Update(); } } } public override void SiteDeleted(SPWebEventProperties properties) { base.SiteDeleted(properties); Uri url = new Uri(properties.FullUrl); string currUrl = properties.FullUrl; string basepath = currUrl.Substring(0, currUrl.IndexOf(url.Host) + url.Host.Length); SPWebApplication webApp = SPWebApplication.Lookup(new Uri(basepath)); List emtpyDbs = new List(); //After the site collection is deleted find all databases that //don't have any sites in them foreach (SPContentDatabase database in webApp.ContentDatabases) { if (database.CurrentSiteCount <= 0) { emtpyDbs.Add(database); } } foreach (SPContentDatabase database in emtpyDbs) { RemoveAndDeleteDb(database); } } private void RemoveAndDeleteDb(SPContentDatabase db) { string dbName = db.Name; if (db.CurrentSiteCount != 0) return; //Remove database from SharePoint db.WebApplication.ContentDatabases.Delete(db.Id); //Get database server name SPWebService service = SPFarm.Local.Services.GetValue(); SPDatabaseServiceInstance defaultDatabaseInstance = service.DefaultDatabaseInstance; string databaseServer = SPHttpUtility.NoEncode(defaultDatabaseInstance.NormalizedDataSource); //Build connection string string connectionString = "Data Source=" + databaseServer + ";Initial Catalog=Master;User ID=uid;Password=pwd;"; SqlConnection connection = new SqlConnection(); connection.ConnectionString = connectionString; string sql; //Force database to close other connections sql = "ALTER DATABASE [" + dbName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"; SqlCommand command1 = new SqlCommand(sql, connection); command1.CommandType = CommandType.Text; //Remove database from sql server sql = "DROP DATABASE [" + dbName + "]"; SqlCommand command2 = new SqlCommand(sql, connection); command2.CommandType = CommandType.Text; using (connection) { connection.Open(); command1.ExecuteScalar(); command2.ExecuteScalar(); } } }