SQLTeam.com | Weblogs | Forums

Validate Instance Status

----- 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

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

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;

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

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;