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 @dbName sysname | |
DECLARE @backupPath NVARCHAR(500) | |
DECLARE @cmd NVARCHAR(500) | |
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) | |
DECLARE @lastFullBackup NVARCHAR(500) | |
DECLARE @lastDiffBackup NVARCHAR(500) | |
DECLARE @backupFile NVARCHAR(500) | |
SET @dbName = 'someDBName' |
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
-- Parameters: | |
-- int - log file number 0 is current, any other the nth file descending | |
-- int - 1 for SQL Server logs, 2 for Agent logs | |
-- string - search word 1 | |
-- string - search word 2 | |
-- date - from | |
-- date - to | |
-- desc - for descending, otherwise ascending as default |
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 | |
SERVERPROPERTY('MachineName') AS [ServerName], | |
SERVERPROPERTY('ServerName') AS [ServerInstanceName], | |
SERVERPROPERTY('InstanceName') AS [Instance], | |
SERVERPROPERTY('Edition') AS [Edition], | |
SERVERPROPERTY('ProductVersion') AS [ProductVersion], | |
Left(@@Version, Charindex('-', @@version) - 2) As VersionName, | |
SERVERPROPERTY('LicenseType') as LicenseType, SERVERPROPERTY('NumLicenses') as NumLicenses |
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 @ServiceStartDateTime AS DATETIME | |
SELECT @ServiceStartDateTime=sqlserver_start_time -- for last restart data | |
FROM | |
sys.dm_os_sys_info | |
SELECT | |
@@SERVERNAME AS ServerName | |
,@ServiceStartDateTime AS SQLServerServiceLastRestartDateTime |
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
exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY' | |
-- full scan for entire db HAS to be done this way | |
EXEC sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN;' |
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 MASTER | |
GO | |
DECLARE @DML1 nvarchar(MAX), | |
@DML2 nvarchar(MAX), | |
@DML3 nvarchar(MAX), | |
@DML4 nvarchar(MAX) | |
DECLARE @Aggregate_IO_Statistics TABLE |
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 [master] | |
GO | |
CREATE PROCEDURE [dbo].[sp_sizing] @granularity | |
varchar(1) = NULL, @database_name sysname = NULL AS | |
/*------------------------------------------------------------- | |
dbo.sp_sizing Stored Procedure | |
Created by Tim Ford, www.sqlcruise.com, www.thesqlagentman.com | |
Use freely but review code before executing. |
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 @BackupDirectory SYSNAME = @BackupFolder | |
IF OBJECT_ID('tempdb..#DirTree') IS NOT NULL | |
DROP TABLE #DirTree | |
CREATE TABLE #DirTree ( | |
Id int identity(1,1), | |
SubDirectory nvarchar(255), | |
Depth smallint, | |
FileFlag bit, |
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
IF SERVERPROPERTY ('IsHadrEnabled') = 1 | |
BEGIN | |
SELECT | |
AGC.name -- Availability Group | |
, RCS.replica_server_name -- SQL cluster node name | |
, ARS.role_desc -- Replica Role | |
, AGL.dns_name -- Listener Name | |
FROM | |
sys.availability_groups_cluster AS AGC |
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 @cnt varchar(300); | |
DECLARE @path NVARCHAR(260); | |
declare @cnt_n int; | |
--SELECT path FROM sys.traces WHERE is_default = 1 | |
--SELECT @path=path FROM sys.traces WHERE is_default = 1 | |
set @path='E:\SQLSYSTEM\MSSQL12.INST1\MSSQL\Log\log_244.trc' | |
select @path |
OlderNewer