ghotz

[Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO');
[Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended');

function Get-SQLInstance($InstanceName, $Login, $Password)
{
	$SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $InstanceName;
	if ($Login -eq $null) {
		$SQLInstance.ConnectionContext.LoginSecure = $true;
	}
	else {

ghotz

[Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO');
[Void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended');

$SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" "localhost";
$DatabaseName = "TestDB";
$QueryStmt =
@"
	SELECT	last_log_backup_lsn
	FROM	sys.database_recovery_status
	WHERE	database_id = DB_ID('$DatabaseName');

ghotz

# just a draft, need massive polishing :)
cls;
$reader = new-object System.IO.StreamReader("ERRORLOG")

$count = 1
$numlines = 0
$fileName = "{0}{1}.{2}" -f ("ERRORLOG_stripped_", $count, "txt")
while(($line = $reader.ReadLine()) -ne $null)
{
	if ((++$numlines % 1000) -eq 0) { Write-Output "Processed $numlines lines" }

ghotz

--
--	xp_readerrorlog @p1, @p2, @p3, @p4
--	based on http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql
--
--	@p1		0 = current, 1 = archive numeber 1 etc
--	@p2		Log type: 1/NULL=SQL Server, 2=SQL Server Agent
--	@p3		First search string
--	@p4		Second search string
--	@p5		Search start time
--	@p6		Search end time

ghotz

# Get Iternet headers from MailItem in PowerShell
Add-type -assembly "Microsoft.Office.Interop.Outlook" | out-null
$OutlookApp = new-object -comobject outlook.application
$MAPI = $OutlookApp.GetNamespace("MAPI")
$Message = $MAPI.GetDefaultFolder([Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox).Items.GetFirst();

# http://msdn.microsoft.com/en-us/library/ms530451.aspx
$Message.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x007D001E")

ghotz

-- Retrieve job names and categories 
SELECT	C.name AS category_name, J.name AS job_name
FROM	dbo.sysjobs AS J
JOIN	dbo.syscategories AS C
  ON	J.category_id = C.category_id
ORDER BY
	C.name;

ghotz

# Parse WinDBG !uniqstack output
$file = 'SQLDump0008.uniqstack.txt';
$SearchLog = [regex] '\.\s{0,2}(?<thread>\d{1,4}).*?\r\n.*?\r\n.*?\r\n.*?\r\n.{8}`.{8} .{8}`.{8} (?<topstack>.*)\r\n';

$log = [io.file]::ReadAllText($file);
$match = $SearchLog.Match($log);

$logentries = @();

while ($match.Success) {

ghotz

SELECT
		task_state
	,	SUM(CASE WHEN session_id IS NULL THEN 0 ELSE 1 END) AS tasks_session_bound
	,	SUM(CASE WHEN session_id IS NULL THEN 1 ELSE 0 END) AS tasks_session_unbound
	,	COUNT(*) AS total_tasks
FROM	sys.dm_os_tasks
GROUP BY
		task_state

ghotz

#
# Save to MHTML file
# Adapted from http://stackoverflow.com/questions/995339/saving-webpage-as-mhtm-file
#
# COM SaveOptionsEnum
# Const adSaveCreateNotExist = 1
# Const adSaveCreateOverWrite = 2
#
# COM StreamTypeEnum
# Const adTypeBinary = 1

ghotz

--
--	Audit applications that connects to a specified database using
--	Windows Integrated Authentication storing the applications names
--	and the number of connections.
--
--	Requires SQL Server versione >= 2012
--	Activate SQLCMD mode before running
--

:setvar DatabaseName TestDatabase