This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#requires dbatools | |
$server = "servername" | |
$localCertPath = "C:\CertificateRequests" | |
$remoteCertPath = "C:\CertificateRequest\" | |
$adminuser = Import-Clixml C:\user.cred #stored credentials to access remote server | |
# Generate the CSR and download locally | |
$session = New-PSSession $server -Credential $adminuser |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#Create new Certificate Request for SQL Server security | |
# Should be made into a function at some point | |
# Needs to be able to handle Cluster names/IP addresses | |
#Set location of the server | |
$Location = "City" | |
$State = "State" | |
$OU = "OU" | |
$Company = "Organization" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
use SSISDB | |
GO | |
CREATE PROC dbo.usp_PurgeSSISCatalogLogs | |
@RowsToDelete int = 5000 | |
AS | |
BEGIN --Proc | |
/* | |
Script name: Purge SSIS Catalog log tables | |
Author: Tim Mitchell (www.TimMitchell.net) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#Note - will want to install "Chocolatey" first in order to use this file to install software. | |
# https://chocolatey.org/docs/installation | |
# Open a command prompt (cmd) and run the following: | |
# @"%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin" | |
# .. | |
# or open a PowerShell Admin prompt and run: | |
# Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1')) | |
# After Chocolatey is installed, open a PS prompt in Administrator mode, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--Adapted from http://www.chilledsql.com/welcome/tip_category_dateandtime/tip_detail_dateandtime_createmastercalendartable | |
--If this is an existing table, do not drop/recreate the table, just adjust dates and run the appropriate population script | |
--Adjust code to ignore dates already in the calendar table. | |
SET NOCOUNT ON | |
SET ANSI_WARNINGS ON | |
SET ARITHABORT ON | |
SET ARITHIGNORE ON |