-- The script I used to clean up my DBVT.COM SQL Server database.  
-- FOR REFERENCE ONLY!  Do not load and run!!!  You've been warned

-- A big thanks to Keyvan for his work in SQL Server database reduction.  
-- Also to CoffeeCat and others who provided additional info.

-- Keyvan's table size report.  I exported to .XLS and Ctrl-H/removed ' KB', converted columns to numbers sorted with largest at top

CREATE TABLE TableSizes
(
table_name SYSNAME,
row_count int,
reserved_size varchar(10),
data_size varchar(10),
index_size varchar(10),
unused_size varchar(10)
)
 
INSERT TableSizes
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
 
SELECT * FROM TableSizes ORDER BY table_name
 
DROP TABLE TableSizes


----------------------------

-- Keyvan's cs_referrals and cs_urls script

DELETE FROM cs_Referrals
WHERE UrlID IN
(
  SELECT UrlID
  FROM cs_Urls
  WHERE Url LIKE 'http://google.%'
    OR Url LIKE 'http://%.yahoo.%'
    OR Url LIKE 'http://yahoo.%'
    OR Url LIKE '%/Search/%'
    OR Url LIKE '%/Search?%'
    OR Url LIKE 'http://search.%'
    OR Url LIKE 'http://bloglines.%'
)
 
DELETE FROM cs_Urls
WHERE Url LIKE 'http://google.%'
  OR Url LIKE 'http://%.yahoo.%'
  OR Url LIKE 'http://yahoo.%'
  OR Url LIKE '%/Search/%'
  OR Url LIKE '%/Search?%'
  OR Url LIKE 'http://search.%'
  OR Url LIKE 'http://bloglines.%'
  
  
-----------------------------

--  Urls strings added from my own inspection

DELETE FROM cs_Referrals
WHERE UrlID IN
(
  SELECT UrlID
  FROM cs_Urls WHERE
  url like '%sex%' or
  url like '%mortgage%' or
  url like '%poker%' or
  url like '%discount%' or
  url like '%holdem%' or
  url like '%del.icio.us%' or
  url like 'http://1%' or
  url like 'file://%' or
  url like '%adipex%' or
  url like '%adult%' or
  url like '%affordable%' or
  url like '%pills%' or
  url like '%altavista%' or
  url like '%antique%' or
  url like '%search.msn%' or
  url like '%ask.com%' or
  url like '%casino%' or
  url like '%search.live%' or
  url like '%xxx%' or
  url like '%blinklist%' or
  url like '%blogdigger%' or
  url like '%blogsearch%' or
  url like '%xanax%' or
  url like '%buy%' or
  url like '%ca-america%' or
  url like '%canadian%' or
  url like '%pharmacy%' or
  url like '%insurance%' or
  url like '%chumly%' or
  url like '%cocomment%' or
  url like '%constant%' or
  url like '%dalin-ina%' or
  url like '%dbvt.com/login%' or
  url like '%doobu%' or
  url like '%dotnetslackers%' or
  url like '%feelor%' or
  url like '%feedster%' or
  url like '%walla%' or
  url like '%findory%' or
  url like '%sex%' or
  url like '%highprofit%' or
  url like '%hotbot%' or
  url like '%iceglue%' or
  url like '%images.google%' or
  url like '%juris-net%' or
  url like '%kapoorsolutions%' or
  url like '%live.com%' or
  url like '%localhost%' or
  url like '%mamma.com%' or
  url like '%/mail.%' or
  url like '%megite%' or
  url like '%mywebsearch%' or
  url like '%//news%' or
  url like '%nutzu%' or
  url like '%//o%' or
  url like '%//party%' or
  url like '%//pay%' or
  url like '%//ph%' or
  url like '%//pic%' or
  url like '%plus%' or
  url like '%//razor%' or
  url like '%rojo%' or
  url like '%//rss%' or
  url like '%strategic%' or
  url like '%fireball%' or
  url like '%lycos%' or
  url like '%technorati.com%' or
  url like '%//texas%' or
  url like '%thebest%' or
  url like '%topix%' or
  url like '%//u%' or
  url like '%//v%' or
  url like '%ask.com%' or
  url like '%whvc%' or
  url like '%//z%' or
  url like '%//y%' or
  url like '%/x%' 
)


delete FROM cs_Urls where
url like '%sex%' or
url like '%mortgage%' or
url like '%poker%' or
url like '%discount%' or
url like '%holdem%' or
url like '%del.icio.us%' or
url like 'http://1%' or
url like 'file://%' or
url like '%adipex%' or
url like '%adult%' or
url like '%affordable%' or
url like '%pills%' or
url like '%altavista%' or
url like '%antique%' or
url like '%search.msn%' or
url like '%ask.com%' or
url like '%casino%' or
url like '%search.live%' or
url like '%xxx%' or
url like '%blinklist%' or
url like '%blogdigger%' or
url like '%blogsearch%' or
url like '%xanax%' or
url like '%buy%' or
url like '%ca-america%' or
url like '%canadian%' or
url like '%pharmacy%' or
url like '%insurance%' or
url like '%chumly%' or
url like '%cocomment%' or
url like '%constant%' or
url like '%dalin-ina%' or
url like '%dbvt.com/login%' or
url like '%doobu%' or
url like '%dotnetslackers%' or
url like '%feelor%' or
url like '%feedster%' or
url like '%walla%' or
url like '%findory%' or
url like '%sex%' or
url like '%highprofit%' or
url like '%hotbot%' or
url like '%iceglue%' or
url like '%images.google%' or
url like '%juris-net%' or
url like '%kapoorsolutions%' or
url like '%live.com%' or
url like '%localhost%' or
url like '%mamma.com%' or
url like '%/mail.%' or
url like '%megite%' or
url like '%mywebsearch%' or
url like '%//news%' or
url like '%nutzu%' or
url like '%//o%' or
url like '%//party%' or
url like '%//pay%' or
url like '%//ph%' or
url like '%//pic%' or
url like '%plus%' or
url like '%//razor%' or
url like '%rojo%' or
url like '%//rss%' or
url like '%strategic%' or
url like '%fireball%' or
url like '%lycos%' or
url like '%technorati.com%' or
url like '%//texas%' or
url like '%thebest%' or
url like '%topix%' or
url like '%//u%' or
url like '%//v%' or
url like '%ask.com%' or
url like '%whvc%' or
url like '%//z%' or
url like '%//y%' or
url like '%/x%' 


--------------------------------

-- reindex tables

dbcc dbreindex (cs_referrals)
dbcc dbreindex (cs_urls)

---------------------------------

-- On my site I didn't see a need for these...

truncate table cs_statistics_site
truncate table cs_exceptions
truncate table cs_eventlog

---------------------------------

-- Using file storage for photos, so removing old photo binary data from cs_postattachments

update cs_postattachments set content = '0x' where sectionID = 6 -- "6" is my photo gallery.  Yours will probably be different

---------------------------------

-- rebuilt my search barrel indexes.  

TRUNCATE TABLE cs_SearchBarrel
TRUNCATE TABLE cs_SearchIgnoreWords
UPDATE cs_Posts SET IsIndexed=0

---------------------------------


--  Script provided by CoffeeCat on CS Forums.  I let the searchbarrel reindexing run awhile to make sure the index was
--  fully rebuilt to get a valid estimate of the new file size resulting from the following processes

DUMP TRANSACTION mydbname WITH NO_LOG
BACKUP LOG mydbname WITH NO_LOG
DBCC SHRINKDATABASE(mydbname)

------------------------------