purge_vcenter_events.ps1

# http://irisclasson.com/2013/10/16/how-do-i-query-a-sql-server-db-using-powershell-and-how-do-i-filter-format-and-output-to-a-file-stupid-question-251-255/
# http://stackoverflow.com/questions/83410/how-do-i-call-a-sql-server-stored-procedure-from-powershell
# http://everythingsharepoint.blogspot.com/2011/08/call-sql-stored-procedure-from.html
$dataSource = "$env:COMPUTERNAME\VIM_SQLEXP"
$database = “VIM_VCDB”
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=True;”
# write-host $connectionString

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()

$query = “select
	a.FILEID,
	[FILE_SIZE_MB] = 
		convert(decimal(12,2),round(a.size/128.000,2)),
	[SPACE_USED_MB] =
		convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
	[FREE_SPACE_MB] =
		convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
	NAME = left(a.NAME,15),
	FILENAME = left(a.FILENAME,30)
from
	dbo.sysfiles a”

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()


$table = new-object “System.Data.DataTable”
$table.Load($result)

$table | out-file c:\before-purge.txt

#Create a Dataset to hold the DataTable from Wrestlers
$dataSet = new-object "System.Data.DataSet" "spaceused"
#Create a DataAdapter which you'll use to populate the DataSet with the results
$dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $connection)
$dataAdapter.Fill($dataSet) | Out-Null

$purge = $false

foreach ($table in $dataSet.Tables) {
  foreach( $row in $table.Rows){
  	$target = $row.FILE_SIZE_MB * 0.95

  	write-host "Space Used: $($row.SPACE_USED_MB), File Size: $($row.FILE_SIZE_MB), Purge after: $target" 

  	if ($row.SPACE_USED_MB -gt $target) {
    	$purge = $true
    	write-host "purging events table ......"
  	}
  }
}

if ($purge -eq $true){
  # now figure out if we need to purge
  # if yes then we should purge
  $old_ErrorActionPreference = $ErrorActionPreference
  $ErrorActionPreference = 'SilentlyContinue'
  Stop-Service -Force "VMWare VirtualCenter Server" -erroraction "silentlycontinue"
  $ErrorActionPreference = $old_ErrorActionPreference
  
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = "[dbo].[cleanup_events_tasks_proc]"
  $SqlCmd.Connection = $connection
  $SqlCmd.CommandTimeout = 1200
  
  
# Shrink the DB:
# dbcc shrinkdatabase('VIM_VCDB');

# Check DB for corruption:
# dbcc checkdb('VIM_VCDB');

  $SqlCmd.ExecuteNonQuery()

  get-date | out-file -append c:\after-purge.txt
	$command = $connection.CreateCommand()
	$command.CommandText = $query

	$result = $command.ExecuteReader()


	$table = new-object “System.Data.DataTable”
	$table.Load($result)

	$table | out-file c:\after-purge.txt

  Start-Service "VMWare VirtualCenter Server"  -erroraction "silentlycontinue"
}

$query = "
SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY
    SUM(p.rows) DESC
"
# SUM(p.rows) DESC # table with most rows
# SUM(a.total_pages) DESC # table with most pages 
# object_name(i.object_id) # default 

$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()


$table = new-object “System.Data.DataTable”
$table.Load($result)

$table | out-file c:\table_detail.txt


$connection.Close()