paschott

--With thanks to Bill Fellows for the script from here:
-- http://stackoverflow.com/questions/21781351/how-can-i-clean-up-the-ssisdb
USE SSISDB;

SET nocount ON;

IF Object_id('tempdb..#DELETE_CANDIDATES') IS NOT NULL
  BEGIN
      DROP TABLE #delete_candidates;
  END;

paschott

DECLARE @NewDataFileGrowth varchar(100) = '500MB'
DECLARE @NewLogGrowth varchar(100) = '100MB'

select 
  CASE WHEN physical_name like '%.ldf'
    THEN 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILEGROWTH = ' + @NewLogGrowth + ')'
    ELSE 'ALTER DATABASE [' + DB_NAME(mf.database_id) + '] MODIFY FILE (NAME = [' + mf.name + '], FILEGROWTH = ' + @NewDataFileGrowth + ')'
  END as ChangeAutoGrowSettings, 
  DB_NAME(mf.database_id) database_name, 
  mf.name logical_name, 

paschott

#PowerShell script
################################
########## PARAMETERS ##########
################################ 
$projectFolder = "C:\SSIS\myProject\myProject"
$dtutilPath = "C:\Program Files\Microsoft SQL Server\130\DTS\Binn\dtutil.exe"
# The number changes per SQL Server version
# 130=2016, 120=2014, 110=2012
# Also check the drive where SQL Server is
# installed

paschott

Import-Module dbatools

$scripts = get-childitem "C:\ScriptsToRun\" -Filter *.sql | sort-object Name
$servers = "servername"
$database = "databasename"
    
foreach ($script in $scripts) {
    $OutputFile = $script.directoryname + "\" + $script.basename + ".txt"
    Write-Host $OutputFile
    Invoke-DbaQuery -SqlInstance $servers -File $script.FullName -Database $database -MessagesToOutput | Out-File -FilePath $OutputFile

paschott

SELECT DISTINCT
   CAST(SERVERPROPERTY('ServerName') as nvarchar(100)) AS Principal,
   m.mirroring_partner_instance AS Mirror,
   DB_NAME(m.database_id) AS DatabaseName,
   SUM(f.size*8/1024) AS DatabaseSize,
   CASE m.mirroring_safety_level
      WHEN 1 THEN 'HIGH PERFORMANCE'
      WHEN 2 THEN 'HIGH SAFETY'
   END AS 'OperatingMode',
   RIGHT(m.mirroring_partner_name, CHARINDEX( ':', REVERSE(m.mirroring_partner_name) + ':' ) - 1 ) AS Port

paschott

--TableName:Backup_MostRecent
;with
db as (
	select [Instance] = @@SERVERNAME,
		[Database]  = name,
	    [RecoveryMode]   = DATABASEPROPERTYEX(name, 'Recovery'),
	    [CreationTime]   = crdate,
	    [Status]         = DATABASEPROPERTYEX(name, 'Status')
		from master..sysdatabases
		where name!='tempdb'

paschott

DECLARE @Time TIME,
        @RowsToDelete INT,
        @PurgeDataPriorToDate DATETIME2,
        @RowsDeleted INT,
        @EndLoopTime TIME,
        @StartLoopTime TIME
SELECT @RowsToDelete = 5000
SELECT @EndLoopTime = '20:00:00'
SELECT @StartLoopTime = '08:00:00'

paschott

--Get SQL Agent Job names for SSRS Subscriptions
SELECT Schedule.ScheduleID AS JobName,
       [Catalog].Name AS ReportName,
       Subscriptions.Description AS Recipients,
       [Catalog].Path AS ReportPath,
       StartDate,
       Schedule.LastRunTime
  FROM [ReportServer].dbo.ReportSchedule
 INNER JOIN [ReportServer].dbo.Schedule
    ON ReportSchedule.ScheduleID     = Schedule.ScheduleID

paschott

SELECT
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID

paschott

Import-Module dbatools
Import-Module sqlserver #used for Invoke-SqlCmd to run scripts w/ the "GO" batch separator

$CurrentDate = (Get-Date).ToString("yyyyMMdd_hhmmss")

$Servername = "localhost"
$databases = 'CSV_Set_Of_DB_Names'


#Get permissions for databases