Validate Instance Status

1

----- SQL UPTIME

SET NOCOUNT ON
USE master
GO

PRINT 'Server Name...............: ' + CONVERT(VARCHAR(30),@@SERVERNAME)
PRINT 'Instance..................: ' + CONVERT(VARCHAR(30),@@SERVICENAME)
PRINT 'Current Date Time.........: ' + CONVERT(VARCHAR(30),GETDATE(),113)
PRINT 'User......................: ' + USER_NAME()
GO

DECLARE @crdate DATETIME,
@hr VARCHAR(50),
@min VARCHAR(5)

SELECT @crdate=crdate
FROM sysdatabases
WHERE NAME='tempdb'

SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60

IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent <> running'
END
ELSE
BEGIN
PRINT 'SQL Server and SQL Server Agent both are running.'
END

select @@servername as 'DB Instance'

SELECT getdate() as CURRENTTIME, sqlserver_start_time
FROM sys.dm_os_sys_info;

--- muti_user and online

select database_id, name, user_access, user_access_desc,state_desc, recovery_model_desc from sys.databases

--- logspace

DBCC SQLPERF(LOGSPACE);
GO

----services

select servicename, startup_type_desc, status_desc, last_startup_time from sys.dm_server_services

2

Check Database Files

create table #FileSizes (DBName sysname, [File Name] varchar(max), [Physical Name] varchar(max),Size decimal(12,2),Size_MB decimal(12,3),Size_GB decimal(12,2))

declare @SQL nvarchar(max)
set @SQL = ''
select @SQL = @SQL + 'USE' + QUOTENAME(name) + '
insert into #FileSizes
select ' + QUOTENAME(name,'''') + ', Name, Physical_Name, (size/1024.0)*8192 Size_KB,(size/1024.0)*8.192 Size_MB,(size/1024.0)*0.008192 Size_GB from sys.database_files '
from sys.databases
where database_id>4

execute (@SQL)
select * from #FileSizes order by DBName desc

drop table #FileSizes

3

FRAMENTATION

/***************************************************************
**Function : Check all DB fragmentation
**Compatibility : MS SQL Server 2008 and later versions
*****************************************************************/

-- find all database users and their role memberships
DECLARE @dbname nvarchar(80)
, @id int
, @max int
, @getFragmentation nvarchar(4000)

-- temp table to stored the databases
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL
DROP TABLE #db_list

CREATE TABLE #db_list
(
id int identity (1,1)
, dbname nvarchar(80)
);

-- temp table to store the databases, users, roles that users belong to
IF OBJECT_ID('tempdb..#dbs_frag_list') IS NOT NULL
DROP TABLE #dbs_frag_list

CREATE TABLE #dbs_frag_list
(
ServerName nvarchar(80),
DBname nvarchar(80),
TableName nvarchar(80),
IndexName nvarchar(80),
PercentFragmented nvarchar(80),
IndexType nvarchar(80)
);

-- load the database list into the temp table
INSERT INTO #db_list
SELECT db.name
FROM sys.databases db
WHERE db.state = 0
AND db.name not in ('master','model','tempdb','msdb');

-- initialize the counters
SELECT @id = 1, @max = max(id)
FROM #db_list

-- loop to process
WHILE (@id <= @max)
BEGIN
SELECT @dbname = dbname
FROM #db_list
WHERE id = @id;

SET @getFragmentation = 'USE ' +@dbname+
	' SELECT
			@@ServerName,
			DB_NAME(),
			t.name ''TableName'',
   			i.name ''IndexName'',
			frag.avg_fragmentation_in_percent ''Percent Fragmented'',
			i.type_desc

	FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) frag

	JOIN    sys.tables t

	ON      frag.object_id = t.object_id

	JOIN    sys.indexes i

	ON      frag.index_id = i.index_id

	AND     frag.object_id = i.object_id

	WHERE   frag.page_count > 100

	AND     frag.avg_fragmentation_in_percent > 35

	AND     i.type != 0

	AND     t.name != ''ThreadActionCount''

	ORDER BY frag.avg_fragmentation_in_percent DESC';

--selected data above will be inserted to the temp table
INSERT INTO #dbs_frag_list
EXEC (@getFragmentation);

SET @id = @id + 1;

END

SELECT
*,'USE '+DBNAME+' ALTER INDEX ['+IndexName+'] ON [DBO].[' +TableName+'] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)' AS REBUILD_SCRIPT
FROM #dbs_frag_list AS frag;

4

RUNNING QUERY

select r.session_id, s.login_name ,s.host_name, m.name, s.program_name,
r.status, substring(qt.text,(r.statement_start_offset/2) +1,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end -r.statement_start_offset)/2) as stmt_executing,
r.blocking_session_id, r.wait_time,wait_type, r.wait_resource, s.host_process_id,
r.cpu_time,r.total_elapsed_time, r.reads,r.writes, r.logical_reads, s.login_time,
s.last_request_start_time,r.plan_handle

from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) as qt,
sys.dm_exec_sessions s, sys.databases m

where r.session_id > 50 and r.session_id=s.session_id
and r.database_id = m.database_id

order by m.database_id, r.session_id, s.host_name, s.program_name, r.status

5

ALWAYS ON/GAUS

1: ALTER DATABASE MyDb2 SET HADR OFF;
GO
2.Delete the database
3. take full backup and restore on that server
4. ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;

6

Database Mail

DECLARE @body_content nvarchar(max);
SET @body_content = N'

table.GeneratedTable { width: 100%; background-color: #ffffff; border-collapse: collapse; border-width: 2px; border-color: #ffcc00; border-style: solid; color: #000000; } table.GeneratedTable td, table.GeneratedTable th { border-width: 2px; border-color: #ffcc00; border-style: solid; padding: 3px; } table.GeneratedTable thead { background-color: #ffcc00; } ' + CAST( ( /*SELECT td = ArtistId, '', td = ArtistName, '', td = ActiveFrom, '' FROM Music.dbo.Artists*/ SELECT TD=NAME, '', TD=STATE_DESC, '', TD=RECOVERY_MODEL_DESC, '', TD='DATABASE ID', '' FROM SYS.databases 
    FOR XML PATH('tr'), TYPE   
    ) AS nvarchar(max)
) +

N'

Database Name Status Recovery Model Database ID
';

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBAMail',
@recipients = 'arnie.baculot@newmont.com',
@body = @body_content,
@body_format = 'HTML',
@subject = 'Sample Only',
@query = 'USE master;
GO
DBCC SQLperf(logspace)
GO',
@attach_query_result_as_file = 1;

7

--Script to generate backup and restore commands
DECLARE @BackupPath AS VARCHAR(125) = 'D:\CR003676258 ';--<<--
DECLARE @date CHAR(8) = (SELECT CONVERT(char(8), GETDATE(), 112));
DECLARE @RecoveryType as VARCHAR(15) = 'NORECOVERY';
DECLARE @BackupOption as VARCHAR(15) = 'DIFFERENTIAL,';
DECLARE @BackupType as VARCHAR(15) = 'FULL'; -- need comment out, depende kung anong backup ang gagawin
--DECLARE @BackupType as VARCHAR(15) = 'DIFF'; -- need comment out, depende kung anong backup ang gagawin
--DECLARE @BackupType as VARCHAR(15) = 'FINAL'; -- need comment out, depende kung anong backup ang gagawin

IF @BackupType = 'DIFF' SET @BackupType = 'DIFF_' + @date;
IF @BackupType = 'FINAL' SET @RecoveryType = 'RECOVERY';
--IF @BackupType = 'FULL' SET @BackupOption = 'COPY_ONLY,'; --copy only backups for testing
IF @BackupType = 'FULL' SET @BackupOption = '';

;WITH MoveCmdCTE ( DatabaseName, MoveCmd )
AS ( SELECT DISTINCT
DB_NAME(database_id) ,
STUFF((SELECT ' ' + CHAR(13)+', MOVE ''' + name + ''''

  • CASE Type
    WHEN 0 THEN ' TO ''G:\SQLData'
    ELSE ' TO ''H:\SQLLogs'
    END
  • REVERSE(LEFT(REVERSE(physical_name),
    CHARINDEX('',
    REVERSE(physical_name),
    1))) + ''''
    FROM sys.master_files sm1
    WHERE sm1.database_id = sm2.database_ID
    FOR XML PATH('') ,
    TYPE).value('.', 'varchar(max)'), 1, 1, '') AS MoveCmd
    FROM sys.master_files sm2
    WHERE database_id > 4
    )

SELECT
'BACKUP DATABASE [' + name + '] TO DISK = N''' + @BackupPath + name + '' +@BackupType + '.bak''' + ' WITH COMPRESSION, ' + @BackupOption + ' STATS=10;' as [Backup Commands],
'RESTORE DATABASE [' + name + '] FROM DISK = ''' + @BackupPath + '' + name + '' + @BackupType + '.bak''' + ' WITH STATS=10, ' +
case when @BackupType = 'FULL' THEN @RecoveryType + movecmdCTE.MoveCmd ELSE @RecoveryType END + ';' as [Restore Commands]
FROM sys.databases d
INNER JOIN MoveCMDCTE ON d.name = movecmdcte.databasename;