SQL to copy Maintenance Plans between SQL Servers

SELECT        
    name, 
    id, 
    description, 
    createdate, 
    folderid, 
    ownersid, 
    cast(
        cast(
            replace(
                cast(
                    CAST(packagedata AS VARBINARY(MAX)) AS varchar(max)
                ), 
            'OldServer', 'NewServer') 
        as XML) 
    as VARBINARY(MAX)) as packagedata, 
    packageformat, 
    packagetype, 
    vermajor, 
    verminor, 
    verbuild, 
    vercomments, 
    verid, 
    isencrypted, 
    readrolesid, 
    writerolesid

FROM
    msdb.dbo.sysssispackages
WHERE        
    (name in ('MaintPlan1','MaintPlan2'))

SQL Script for updating statistics on all tables in a database

DECLARE @sql nvarchar(MAX);
SELECT @sql = (SELECT 'UPDATE STATISTICS ' +
                      quotename(s.name) + '.' + quotename(o.name) +
                      ' WITH FULLSCAN; ' + char(13) + char(10) AS [text()]
               FROM   sys.objects o
               JOIN   sys.schemas s ON o.schema_id = s.schema_id
               WHERE  o.type = 'U'
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
PRINT @sql
EXEC (@sql)

Non-Clustered Indexes, Tipping point for Bookmark Lookup vs Table Scans

https://www.sqlpassion.at/archive/2013/06/12/sql-server-tipping-games-why-non-clustered-indexes-are-just-ignored/

When you have a specific query which produces a Bookmark Lookup, it depends on the number of retrieved pages if SQL Server is doing a full scan or using the Bookmark Lookup. The number of retrieved pages dictates if it’s good or not good to do a Bookmark Lookup! Therefore it is completely *irrelevant* how many records a specific query returns, the only thing that matters is the number of pages.

The Tipping Point is somewhere between 24% – 33% of the total pages the query has to read.

Before that range, the Query Optimizer chooses a Bookmark Lookup, after the query tipped over, the Query Optimizer produces an Execution Plan with a full scan of the table (with a Predicate inside the Scan operator). This also means that the size of your records defines where the Tipping Point lives. With very small records you can only retrieve a smaller set of records from your table, with larger records you can retrieve a huger set of records, before the query is over the Tipping Point and is doing a full scan.

Example:

A table consists of 4000 data pages in the leaf level of the Clustered Index, which means the Tipping Point is somewhere between 1000 and 1333 pages that we are reading for that specific table. This means you can read about 1.25% – 1.67% (1000/80000, 1333/80000) of the records from the table, before the Query Optimizer decides to do a full scan of the table.

Tipping Point in terms of number of rows = 24% – 33% of the total pages at leaf-level of heap/clustered index divided by total number of rows in the table

With Bookmark Lookups you can’t achieve Plan Stability!

A Non-Clustered Index, which isn’t a Covering Non-Clustered Index has a very, very, very, very, very selective Use Case in SQL Server! Think about that the next time when you are working on your indexing strategy.

Tipping Point is only relevant for Non-Covering Non-Clustered Indexes. An index which acts as a Covering Non-Clustered Index for a specific query doesn’t have a Tipping Point.

 

Extract members of Local Administrators group using PowerShell

function get-localadmin {
param ($strcomputer)

$admins = Gwmi win32_groupuser –computer $strcomputer
$admins = $admins |? {$_.groupcomponent –like '*"Administrators"'}

$admins |% {
$_.partcomponent –match “.+Domain\=(.+)\,Name\=(.+)$” > $nul
$matches[1].trim('"') + “\” + $matches[2].trim('"')
}
}

$rs ="CMSServer"

$slist = invoke-sqlcmd -serverinstance $rs -database 'DBADB' -query "SELECT ServerName FROM AllServers ORDER BY ServerName"

foreach ($sl in $slist)
{
$ServerName = $sl.Servername
$members=Get-LocalAdmin $ServerName

foreach ($m in $members)
{

invoke-sqlcmd -serverinstance $rs -database 'DBADB' -query "
insert LocalAdminMembers (ServerName, LocalAdminMember)
select '$ServerName','$m'"
}

}

Script out CREATE DATABASE using PowerShell and SMO

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$server = [system.environment]::MachineName

$server = "SQLServerName"

$SQLFile = "CreateDatabase_"+$server+".sql"

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server

$dbs=$s.Databases

foreach($database in $dbs | where {($_.IsSystemObject -ne $True) -And ($_.name -ne "DB_to_be_excluded")})
{
Write-Host $database.Name
$dbs[$database.Name].Script() | Out-File $SQLFile -Append
Add-Content $SQLFile "`nGO"
}
Use full Regex
find SIZE = (.*?)KB
replace SIZE =8

SQL code to fix high VLF count

SET NOCOUNT ON

DECLARE @SQL1 varchar(1000), @SQL2 varchar(1000), @SQL3 varchar(1000)

select @SQL1=

'ALTER DATABASE ['+db_name()+'] MODIFY FILE (NAME='+name+', filegrowth='+CONVERT(varchar(10),floor(((max_size-size)/(20*128) + 99) / 100) * 100)+')'

from sys.master_files where type_desc='LOG' and database_id=db_id()

EXEC(@SQL1)

select @SQL2=

'CHECKPOINT;DBCC SHRINKFILE ('+name+','+ CONVERT(varchar(10),floor((FILEPROPERTY(name,'SpaceUsed')/128+99)/100)*100)+')'

from sys.master_files where type_desc='LOG' and database_id=db_id()

select @SQL3=

'ALTER DATABASE ['+db_name()+'] MODIFY FILE (NAME='+name+', size='+CONVERT(varchar(10),floor(((size)/(128) + 99) / 100) * 100)+')'

from sys.master_files where type_desc='LOG' and database_id=db_id()

EXEC(@SQL2)

EXEC(@SQL3)

create table #vlf_temp 

(

 recoveryunitid int

, fileid varchar(3)

, filesize numeric(20,0)

, startoffset bigint

, fseqno bigint

, status char(1)

, parity varchar(4)

, createlsn numeric(25,0)

)

insert into #vlf_temp

exec ('dbcc loginfo () with no_infomsgs')

select count(*) VLFCount from #vlf_temp

drop table #vlf_temp

Extract SQL Server SSL certificate expiry date through PowerShell

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

#Threshold: How many days before the certificate expiry date should DBA be notified

$threshold = 15
$rs ="CentralManagementServer"

#Set deadline date
$results = new-object object[] 0
$deadline = (Get-Date).AddDays($threshold)

#Retrieve list of SSL-enabled servers from text file
$servers = `
"server1",
"server2",
"server3"
`
foreach ($server in $servers) {

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server

$majorversion=$srv.VersionMajor
$minorversion=$srv.VersionMinor

If ($majorversion -eq 10 -And $minorversion -eq 50)
{$majorversion="10_50"}

#Write-Output $server, $majorversion

#Read the appropriate registry path to retrieve SQL Server SSL certificate thumbprint value

$Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $server)
$RegKey= $Reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL$majorversion.MSSQLSERVER\\MSSQLServer\\SuperSocketNetLib")
$certtp = $RegKey.GetValue("Certificate")

$store=new-object System.Security.Cryptography.X509Certificates.X509Store("\\$server\my","LocalMachine")

$store.open("ReadOnly")

$store.certificates | % {

If ($_.NotAfter -lt $deadline) {

If ($_.Thumbprint –eq $certtp )
{

$results +=$_ | Select Subject, NotAfter,FriendlyName, @{Label="ExpiresInDays"; Expression={($_.NotAfter - (Get-Date)).Days}}

}
}

}

}

#Write-Output $results

foreach ($r in $results)
{
$Subject=$r.Subject
$NotAfter=$r.NotAfter
$FriendlyName=$r.FriendlyName
$ExpDays=$r.ExpiresInDays

invoke-sqlcmd -serverinstance $rs -database 'DBAMonitoring' -query "
insert dbo.sslcert_expiry(CertSubject, ExpiryDate, CertFriendlyName, ExpiresInDays)
select '$Subject','$NotAfter', '$FriendlyName','$ExpDays'"

}