DBA Mafiya: Queies_r mostly

----------------------Access to only one DB-------------

USE MASTER
GO
deny VIEW any DATABASE TO [sqladmin]
GO

USE master;
GO
ALTER AUTHORIZATION ON DATABASE::GenericOB TO [sqladmin];

GO

------------------Alert for Logshipping----------------

USE [master]
GO

begin
set nocount on
DECLARE @Recipients varchar(275)
DECLARE @Subject varchar(275)
DECLARE @Body varchar(MAX)
DECLARE @Server varchar(25)
DECLARE @idx int, @Status varchar(25), @DB varchar(25), @LastCopy varchar(6), @LastCFile varchar(250),
@LastRestore varchar(6), @LastRFile varchar(250), @Latency varchar(3), @Threshold varchar(3)
declare @retcode int
,@primary_id uniqueidentifier
,@primary_server sysname
,@primary_database sysname
,@backup_threshold int
,@is_backup_alert_enabled bit

,@secondary_id uniqueidentifier
,@secondary_server sysname
,@secondary_database sysname
,@restore_threshold int
,@is_restore_alert_enabled bit
,@last_copied_file nvarchar(500)
,@last_copied_utc datetime
,@time_since_last_copy int
,@last_restored_file nvarchar(500)
,@last_restored_utc datetime
,@time_since_last_restore int
,@last_restored_latency int

,@prev_primary_server sysname
,@prev_primary_database sysname

,@monitor_server sysname
,@monitor_server_security_mode int
,@is_monitor_local bit
,@curutcdate datetime
,@linkcmd nvarchar(4000)

SET @Recipients = 'you@company.com'
SET @Body = ''
SET @Server = @@SERVERNAME
SET @Subject = @Server + ' :: Daily Transaction Log Shipping Status'
SET @Body = '' + @Server + ': <hr style="width: 100%; height: 1px;"> '
create table #log_shipping_monitor
(
idx int identity(1,1)
,status bit null
,is_primary bit not null default 0
,server sysname
,database_name sysname
,is_backup_alert_enabled bit null
,time_since_last_copy int null
,last_copied_file nvarchar(500) null
,time_since_last_restore int null
,last_restored_file nvarchar(500) null
,last_restored_latency int null
,restore_threshold int null
,is_restore_alert_enabled bit null
,ts timestamp not null
,primary key (is_primary, server, database_name)
,unique (ts)
)

--
-- create other tables we will use
--
create table #secondary_monitor
(
secondary_server sysname not null,
secondary_database sysname not null,
secondary_id uniqueidentifier not null,
primary_server sysname not null,
primary_database sysname not null,
restore_threshold int not null,
threshold_alert int not null,
threshold_alert_enabled bit not null,
last_copied_file nvarchar(500) null,
last_copied_date datetime null,
last_copied_date_utc datetime null,
last_restored_file nvarchar(500) null,
last_restored_date datetime null,
last_restored_date_utc datetime null,
last_restored_latency int null,
history_retention_period int not null,
primary key (secondary_id, secondary_database)
)

create table #primary_monitor
(
primary_id uniqueidentifier primary key not null,
primary_server sysname not null,
primary_database sysname not null,
backup_threshold int not null,
threshold_alert int not null,
threshold_alert_enabled bit not null,
last_backup_file nvarchar(500) null,
last_backup_date datetime null,
last_backup_date_utc datetime null,
history_retention_period int not null,
unique (primary_server, primary_database)
)

--
-- get current time
--
select @curutcdate = getutcdate()
--
-- Enumerate the primary entries
--
declare #hcprimaries cursor local fast_forward for
select
primary_id
,primary_server
,primary_database
,backup_threshold
,threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_primary with (nolock)
order by primary_server, primary_database

open #hcprimaries
fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled
while (@@fetch_status != -1)
begin
--
-- we have a primary entry
--
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,is_backup_alert_enabled)
values (
1
,@primary_server
,@primary_database
,@backup_threshold
,@is_backup_alert_enabled)
--
-- process secondaries
--
if (upper(@primary_server) = upper(@@servername))
begin
--
-- we are on primary server
-- get monitor server information
--
select @monitor_server = monitor_server
,@monitor_server_security_mode = monitor_server_security_mode
from msdb.dbo.log_shipping_primary_databases
where primary_id = @primary_id
select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end
--
-- enumerate the secondaries listed on primary
--
declare #hcprimarysecondaries cursor local fast_forward for
select secondary_server, secondary_database
from msdb.dbo.log_shipping_primary_secondaries with (nolock)
where primary_id = @primary_id

open #hcprimarysecondaries
fetch #hcprimarysecondaries into @secondary_server, @secondary_database
while (@@fetch_status != -1)
begin
--
-- add this primary secondary to result set
--
insert into #log_shipping_monitor (is_primary ,server, database_name)
values (0, @secondary_server, @secondary_database)
select @secondary_id = NULL
--
-- Enumerate this secondary from msdb.dbo.log_shipping_monitor_secondary
--
if (@is_monitor_local = 1)
begin
--
-- local monitor
--
select
@secondary_id = secondary_id
,@restore_threshold = restore_threshold
,@is_restore_alert_enabled = threshold_alert_enabled
,@last_copied_file = last_copied_file
,@last_copied_utc = last_copied_date_utc
,@last_restored_file = last_restored_file
,@last_restored_utc = last_restored_date_utc
,@last_restored_latency = last_restored_latency
from msdb.dbo.log_shipping_monitor_secondary
where primary_server = upper(@primary_server)
and primary_database = @primary_database
and secondary_server = upper(@secondary_server)
and secondary_database = @secondary_database
end -- local monitor
else
begin
--
-- remote monitor
--
if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
begin
--
-- execute as proxy
--
exec @retcode = sys.sp_MSproxylogshippingmonitorhelpsecondary
@monitor_server = @monitor_server
,@p1 = @primary_server
,@p2 = @primary_database
,@p3 = @secondary_server
,@p4 = @secondary_database
,@p5 = @secondary_id output
,@p6 = @restore_threshold output
,@p7 = @is_restore_alert_enabled output
,@p8 = @last_copied_file output
,@p9 = @last_copied_utc output
,@p10 = @last_restored_file output
,@p11 = @last_restored_utc output
,@p12 = @last_restored_latency output
end
else
begin
delete #secondary_monitor
select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_secondary '
,@retcode = 0
begin try
insert into #secondary_monitor
exec @retcode = @linkcmd
@secondary_server = @secondary_server
,@secondary_database = @secondary_database
end try
begin catch
select @retcode = 1
,@secondary_id = NULL
end catch

if (@retcode = 0)
begin
select @secondary_id = secondary_id
,@restore_threshold = restore_threshold
,@is_restore_alert_enabled = threshold_alert_enabled
,@last_copied_file = last_copied_file
,@last_copied_utc = last_copied_date_utc
,@last_restored_file = last_restored_file
,@last_restored_utc = last_restored_date_utc
,@last_restored_latency = last_restored_latency
from #secondary_monitor
where upper(primary_server) = upper(@primary_server)
and primary_database = @primary_database
end
else
begin
raiserror(32031, 10, 1, @secondary_server, @secondary_database, @monitor_server)
end
end
end -- remote monitor
--
-- do we have data on this secondary
--
if (@secondary_id is not null)
begin
--
-- yes we do - update the entry
--
select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate)
,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate)
update #log_shipping_monitor
set
status = case when (@time_since_last_restore > @restore_threshold or @last_restored_latency > @restore_threshold) then 1 else 0 end
,time_since_last_copy = @time_since_last_copy
,last_copied_file = @last_copied_file
,time_since_last_restore = @time_since_last_restore
,last_restored_file = @last_restored_file
,last_restored_latency = @last_restored_latency
,restore_threshold = @restore_threshold
,is_restore_alert_enabled = @is_restore_alert_enabled
where upper(server) = upper(@secondary_server)
and database_name = @secondary_database
end -- update secondary data
--
-- fetch next primary secondary
--
fetch #hcprimarysecondaries into @secondary_server, @secondary_database
end
close #hcprimarysecondaries
deallocate #hcprimarysecondaries
end -- we are on primary server processing primaries
else
begin
--
-- we are on monitor server
-- get details of the secondaries from msdb.dbo.log_shipping_monitor_secondary
-- if the same monitor is being used by secondaries
--
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,time_since_last_copy
,last_copied_file
,time_since_last_restore
,last_restored_file
,last_restored_latency
,restore_threshold
,is_restore_alert_enabled)
select
case when (datediff(minute, last_restored_date_utc, @curutcdate) > restore_threshold
or last_restored_latency > restore_threshold) then 1 else 0 end
,0
,secondary_server
,secondary_database
,datediff(minute, last_copied_date_utc, @curutcdate)
,last_copied_file
,datediff(minute, last_restored_date_utc, @curutcdate)
,last_restored_file
,last_restored_latency
,restore_threshold
,threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_secondary (nolock)
where primary_server = upper(@primary_server)
and primary_database = @primary_database

end -- we are on monitor server processing primaries
fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled
end -- while cursor for hcprimaries
close #hcprimaries
deallocate #hcprimaries
--
-- Enumerate the secondary entries
-- minus existing secondary entries in resultset
--
declare #hcsecondaries cursor local fast_forward for
select
secondary_server
,secondary_database
,secondary_id uniqueidentifier
,primary_server
,primary_database
,restore_threshold
,threshold_alert_enabled
,last_copied_file
,last_copied_date_utc
,last_restored_file
,last_restored_date_utc
,last_restored_latency
from msdb.dbo.log_shipping_monitor_secondary with (nolock)
where not exists (select * from #log_shipping_monitor
where upper(server) = upper(secondary_server)
and database_name = secondary_database
and is_primary = 0)
order by primary_server, primary_database

open #hcsecondaries
fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id,
@primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled,
@last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency
while (@@fetch_status != -1)
begin
--
-- Have we processed the primary for this secondary
--
if not (upper(@primary_server) = upper(@prev_primary_server)
and @primary_database = @prev_primary_database)
begin
--
-- No - Try to get the details of this primary
--
select @primary_id = null
if (upper(@secondary_server) = upper(@@servername))
begin
--
-- we are on secondary
-- get monitor server information
--
select @monitor_server = monitor_server
,@monitor_server_security_mode = monitor_server_security_mode
from msdb.dbo.log_shipping_secondary with (nolock)
where secondary_id = @secondary_id
select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end
if (@is_monitor_local = 1)
begin
--
-- local monitor
--
select @primary_id = primary_id
,@backup_threshold = backup_threshold
,@is_backup_alert_enabled = threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_primary with (nolock)
where primary_server = upper(@primary_server)
and primary_database = @primary_database
end
else
begin
--
-- remote monitor
--
if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
begin
--
-- execute as proxy
--
exec @retcode = sys.sp_MSproxylogshippingmonitorhelpprimary
@monitor_server = @monitor_server
,@p1 = @primary_server
,@p2 = @primary_database
,@p3 = @primary_id output
,@p4 = @backup_threshold output
,@p5 = @is_backup_alert_enabled output
end
else
begin
delete #primary_monitor
select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_primary '
,@retcode = 0
begin try
insert into #primary_monitor
exec @retcode = @linkcmd
@primary_server = @primary_server
,@primary_database = @primary_database
end try
begin catch
select @retcode = 1
,@primary_id = NULL
end catch

if (@retcode = 0)
begin
select @primary_id = primary_id
,@backup_threshold = backup_threshold
,@is_backup_alert_enabled = threshold_alert_enabled
from #primary_monitor
end
else
begin
raiserror(32030, 10, 1, @primary_server, @primary_database, @monitor_server)
end
end -- processing remote
end
end -- processing on secondary
else
begin
--
-- we are on monitor server
--
select @primary_id = primary_id
,@backup_threshold = backup_threshold
,@is_backup_alert_enabled = threshold_alert_enabled
from msdb.dbo.log_shipping_monitor_primary with (nolock)
where primary_server = upper(@primary_server)
and primary_database = @primary_database
end -- processing on monitor server
--
-- insert primary details if available
--
if (@primary_id is not null)
begin
select @prev_primary_server = @primary_server
,@prev_primary_database = @primary_database
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,is_backup_alert_enabled)
values (
1
,@primary_server
,@primary_database
,@backup_threshold
,@is_backup_alert_enabled)
end -- primary data available
end -- process the primary
--
-- Insert the secondary
--
select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate)
,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate)
insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,time_since_last_copy
,last_copied_file
,time_since_last_restore
,last_restored_file
,last_restored_latency
,restore_threshold
,is_restore_alert_enabled)
values (
case when (@time_since_last_restore > @restore_threshold or @last_restored_latency > @restore_threshold) then 1 else 0 end
,0
,@secondary_server
,@secondary_database
,@time_since_last_copy
,@last_copied_file
,@time_since_last_restore
,@last_restored_file
,@last_restored_latency
,@restore_threshold
,@is_restore_alert_enabled)
--
-- get the next secondary
--
fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id,
@primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled,
@last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency
end -- while cursor for hcsecondaries
close #hcsecondaries
deallocate #hcsecondaries
--
-- return resultset
--
SET @Body = RTRIM(@Body) + '<table cellspacing="0" cellpadding="0" width="100%">'

WHILE EXISTS (SELECT TOP 1 idx FROM #log_shipping_monitor)
BEGIN
SELECT TOP 1 @idx = idx, @Status = (CASE WHEN status = 0 THEN 'Good' ELSE 'Bad' END),
@DB = database_name, @LastCopy = time_since_last_copy, @LastCFile = last_copied_file,
@LastRestore = time_since_last_restore, @LastRFile = last_restored_file,
@Latency = last_restored_latency, @Threshold = restore_threshold
FROM #log_shipping_monitor order by ts

SET @Body = RTRIM(@Body)
+ '<tr><td nowrap style="font-size:11px;font-family:Verdana">'
+ 'Database:' + RTRIM(@DB) + '
'
+ CASE WHEN @Status = 'Good' THEN
'Status:' + RTRIM(@Status) + '
'
ELSE 'Status:' + RTRIM(@Status) + '
' END
+ 'Last Copied File:' + RTRIM(@LastCFile) + '
'
+ 'Time since last Copy (min):' + RTRIM(@LastCopy) + '
'
+ 'Last Restored File:' + RTRIM(@LastRFile) + '
'
+ 'Time since last Restore (min):' + RTRIM(@LastRestore) + '
'
+ 'Restore Latency (min):' + RTRIM(@Latency) + '
'
+ 'Restore Threshold (min):' + RTRIM(@Threshold) + '
'
+ '</td></tr>'
+ '<hr style="width: 100%; height: 1px;"> '
DELETE FROM #log_shipping_monitor WHERE idx = @idx
END

SET @Body = RTRIM(@Body) + '</table>'
EXECUTE msdb.dbo.sp_send_dbmail @recipients = @Recipients
,@subject = @Subject
,@body = @Body
,@body_format = 'HTML'

end

DROP Table #log_shipping_monitor
DROP TABLE #primary_monitor
DROP TABLE #secondary_monitor

----------------------------All DB Backup detals-----------------

SELECT DISTINCT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.Database_Name,
msdb.dbo.backupset.Backup_Start_date,
msdb.dbo.backupset.Backup_Finish_date,

CASE msdb..Backupset.type
WHEN 'D' THEN 'Full'
WHEN 'L' THEN 'Log'
when 'i ' then 'Diff'
END AS Backup_Type,

Cast(msdb.dbo.backupset.backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)',

msdb.dbo.backupset.name AS Backupset_Name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE()-1)
ORDER BY
msdb.dbo.backupset.Database_Name,

msdb.dbo.backupset.Backup_Finish_date

------------------------All User All DB User details-------------------------

USE MASTER
GO
BEGIN
DECLARE @SQLVerNo INT;
SET @SQLVerNo = cast(substring(CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0,charindex('.',CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0)) as int);

IF @SQLVerNo >= 9
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE
IF @SQLVerNo = 8
BEGIN
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
END

CREATE TABLE #TUser (
ServerName varchar(256),
DBName SYSNAME,
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name SYSNAME NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
[sid] VARBINARY(85))

IF @SQLVerNo = 8
BEGIN
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
@@SERVERNAME,
''?'' as DBName,
u.name As UserName,
CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName,
l.name AS LoginName,
NULL AS Default_db_Name,
NULL as default_Schema_name,
u.uid,
u.sid
FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
JOIN [?].dbo.sysUsers r
ON m.groupuid = r.uid)
ON m.memberuid = u.uid
LEFT JOIN dbo.sysLogins l
ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
/*and u.name like ''tester''*/
ORDER BY u.name
'
END

ELSE
IF @SQLVerNo >= 9
BEGIN
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
@@SERVERNAME,
''?'',
u.name,
CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM [?].sys.database_principals u
LEFT JOIN ([?].sys.database_role_members m
JOIN [?].sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN [?].sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R''
/*and u.name like ''tester''*/
order by u.name
'
END

SELECT *
FROM #TUser
ORDER BY DBName,
[name],
GroupName

DROP TABLE #TUser

END

---------------------------------Auto rebuild & Reorganise----------------

-- Script that reorganizes or rebuilds all indexes having an average fragmentation
-- percentage above a given threshold. It also works in the case
-- where Availability Groups are enabled as it determines if the
-- relevant databases are the primary replicas.
--
-- This script supports only SQL Server 2005 or later.
-- Also, if you execute this script in a SQL Server 2005 instance
-- or later, any databases with compatibility level 2000 (80) or earlier
-- will be automatically excluded from the index reorganization/rebuild process.
----

--Initial check - You must be SysAdmin
DECLARE @isSysAdmin INT
SET @isSysAdmin=(SELECT IS_SRVROLEMEMBER ('sysadmin'));

--Initial check - You must be using SQL Server 2005 or later
DECLARE @SQLServerVersion INT
SET @SQLServerVersion=(SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)))-1) AS INT));

IF @isSysAdmin=1 AND @SQLServerVersion >= 9
BEGIN

--
-- Variable/parameters Declaration
--
DECLARE @dbname NVARCHAR(128);
DECLARE @ReorganizeOrRebuildCommand NVARCHAR(MAX);
DECLARE @dbid INT;
DECLARE @indexFillFactor VARCHAR(5);
DECLARE @fragmentationThreshold VARCHAR(10);
DECLARE @indexStatisticsScanningMode VARCHAR(20);
DECLARE @verboseMode BIT;
DECLARE @reportOnly BIT;
DECLARE @sortInTempdb VARCHAR(3);
DECLARE @isHadrEnabled BIT;
DECLARE @databaseToCheck VARCHAR(250)
DECLARE @dynamic_command NVARCHAR(1024);
DECLARE @dynamic_command_get_tables NVARCHAR(MAX);

--Initializations - Do not change
SET @databaseToCheck=NULL;
SET @dynamic_command = NULL;
SET @dynamic_command_get_tables = NULL;
SET @isHadrEnabled=0;

SET NOCOUNT ON;

---------------------------------------------------------
--Set Parameter Values: You can change these (optional) -
--Note: The script has default parameters set -
---------------------------------------------------------
--if set to 1: it will just generate a report with the index reorganization/rebuild statements
--if set to 0: it will reorganize or rebuild the fragmented indexes
SET @reportOnly = 0;

--optional: if not set (NULL), it will scann all databases
--If name is set (i.e. 'testDB') it will just scan the given database
SET @databaseToCheck = NULL;

--maintains only the indexes that have average fragmentation percentage equal or higher from the given value
SET @fragmentationThreshold = 15;

--fill factor - the percentage of the data page to be filled up with index data
SET @indexFillFactor = 90;

--sets the scanning mode for index statistics
--available values: 'DEFAULT', NULL, 'LIMITED', 'SAMPLED', or 'DETAILED'
SET @indexStatisticsScanningMode='SAMPLED';

--if set to ON: sorts intermediate index results in TempDB
--if set to OFF: sorts intermediate index results in user database's log file
SET @sortInTempdb='ON';

--if set to 0: Does not output additional information about the index reorganization/rebuild process
--if set to 0: Outputs additional information about the index reorganization/rebuild process
SET @verboseMode = 0;
------------------------------
--End Parameter Values Setup -
------------------------------

-- check if given database exists and if compatibility level >= SQL 2005 (90)
IF @verboseMode=1
PRINT 'Checking if database '+@databaseToCheck+' exists and if compatibility level equals or greater 2005 (90)';

-- if given database does not exist, raise error with severity 20
-- in order to terminate script's execution
IF @databaseToCheck IS NOT NULL
BEGIN
DECLARE @checkResult INT
SET @checkResult=(SELECT COUNT(*) FROM master.sys.databases WHERE [name]=RTRIM(@databaseToCheck));
IF @checkResult<1
RAISERROR('Error executing index reorganization/rebuild script: Database does not exist' , 20, 1) WITH LOG;

DECLARE @checkResult2 INT
SET @checkResult=(SELECT [compatibility_level] FROM master.sys.databases WHERE [name]=RTRIM(@databaseToCheck));
IF @checkResult<90
RAISERROR('Error executing index reorganization/rebuild script: Only databases with SQL Server 2005 or later compatibility level are supported' , 20, 1) WITH LOG;
END

IF @verboseMode=1
PRINT 'Initial checks completed with no errors.';

-- Temporary table for storing index fragmentation details
IF OBJECT_ID('tempdb..#tmpFragmentedIndexes') IS NULL
BEGIN
CREATE TABLE #tmpFragmentedIndexes
(
[dbName] sysname,
[tableName] sysname,
[schemaName] sysname,
[indexName] sysname,
[databaseID] SMALLINT ,
[objectID] INT ,
[indexID] INT ,
[AvgFragmentationPercentage] FLOAT,
[reorganizationOrRebuildCommand] NVARCHAR(MAX)
);
END

-- Initialize temporary table
DELETE FROM #tmpFragmentedIndexes;

-- Validate parameters/set defaults
IF @sortInTempdb NOT IN ('ON','OFF')
SET @sortInTempdb='ON';

-- Check if instance has AlwaysOn AGs enabled
SET @isHadrEnabled=CAST((SELECT ISNULL(SERVERPROPERTY('IsHadrEnabled'),0)) AS BIT);

-- if database not specified scan all databases
IF @databaseToCheck IS NULL
BEGIN
DECLARE dbNames_cursor CURSOR
FOR
SELECT s.[name] AS dbName ,
s.database_id
FROM master.sys.databases s
WHERE s.state_desc = 'ONLINE'
AND s.is_read_only != 1
AND s.[name] NOT IN ( 'master', 'model', 'tempdb' )
AND s.[compatibility_level]>=90
ORDER BY s.database_id;
END
ELSE
-- if database specified, scan only that database
BEGIN
DECLARE dbNames_cursor CURSOR
FOR
SELECT s.[name] AS dbName ,
s.database_id
FROM master.sys.databases s
WHERE s.state_desc = 'ONLINE'
AND s.is_read_only != 1
AND s.[name]=RTRIM(@databaseToCheck)
END

-- if Always On Availability Groups are enabled, check for primary databases
-- (thus exclude secondary databases)
IF @isHadrEnabled=1
BEGIN

DEALLOCATE dbNames_cursor;

-- if database not specified scan all databases
IF @databaseToCheck IS NULL
BEGIN
DECLARE dbNames_cursor CURSOR
FOR
SELECT s.[name] AS dbName ,
s.database_id
FROM master.sys.databases s
LEFT JOIN master.sys.dm_hadr_availability_replica_states r ON s.replica_id = r.replica_id
WHERE s.state_desc = 'ONLINE'
AND s.is_read_only != 1
AND UPPER(ISNULL(r.role_desc, 'NonHadrEnabled')) NOT LIKE 'SECONDARY'
AND s.[name] NOT IN ( 'master', 'model', 'tempdb' )
AND s.[compatibility_level]>=90
ORDER BY s.database_id;
END
ELSE
-- if database specified, scan only that database
BEGIN
DECLARE dbNames_cursor CURSOR
FOR
SELECT s.[name] AS dbName ,
s.database_id
FROM master.sys.databases s
LEFT JOIN master.sys.dm_hadr_availability_replica_states r ON s.replica_id = r.replica_id
WHERE s.state_desc = 'ONLINE'
AND s.is_read_only != 1
AND UPPER(ISNULL(r.role_desc, 'NonHadrEnabled')) NOT LIKE 'SECONDARY'
AND s.[name]=RTRIM(@databaseToCheck);
END
END

--
-- For each database included in the cursor,
-- gather all tables that have indexes with
-- average fragmentation percentage equal or above @fragmentationThreshold
--
OPEN dbNames_cursor;
FETCH NEXT FROM dbNames_cursor INTO @dbname, @dbid;
WHILE @@fetch_status = 0
BEGIN

--If verbose mode is enabled, print logs
IF @verboseMode = 1
BEGIN
PRINT ''
PRINT 'Gathering index fragmentation statistics for database: ['+ @dbname + '] with id: ' + CAST(@dbid AS VARCHAR(10));
END;

SET @dynamic_command_get_tables = N'
USE [' + @dbname+ N'];
INSERT INTO #tmpFragmentedIndexes (
[dbName],
[tableName],
[schemaName],
[indexName],
[databaseID],
[objectID],
[indexID],
[AvgFragmentationPercentage],
[reorganizationOrRebuildCommand]
)
SELECT
DB_NAME() as [dbName],
tbl.name as [tableName],
SCHEMA_NAME (tbl.schema_id) as schemaName,
idx.Name as [indexName],
pst.database_id as [databaseID],
pst.object_id as [objectID],
pst.index_id as [indexID],
pst.avg_fragmentation_in_percent as [AvgFragmentationPercentage],
CASE WHEN pst.avg_fragmentation_in_percent > 30 THEN
''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REBUILD WITH (FILLFACTOR = '+@indexFillFactor+', SORT_IN_TEMPDB = '+@sortInTempdb+', STATISTICS_NORECOMPUTE = OFF);''
WHEN pst.avg_fragmentation_in_percent > 5 AND pst.avg_fragmentation_in_percent <= 30 THEN
''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REORGANIZE;''
ELSE
NULL
END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , '''+@indexStatisticsScanningMode+''') as pst
INNER JOIN sys.tables as tbl ON pst.object_id = tbl.object_id
INNER JOIN sys.indexes idx ON pst.object_id = idx.object_id AND pst.index_id = idx.index_id
WHERE pst.index_id != 0
AND pst.alloc_unit_type_desc IN ( N''IN_ROW_DATA'', N''ROW_OVERFLOW_DATA'')
AND pst.avg_fragmentation_in_percent >= '+ @fragmentationThreshold + '';

-- if verbose mode is enabled, print logs
IF @verboseMode=1
BEGIN
PRINT 'Index fragmentation statistics script: ';
PRINT @dynamic_command_get_tables;
END

-- gather index fragmentation statistics
EXEC (@dynamic_command_get_tables);

-- bring next record from the cursor
FETCH NEXT FROM dbNames_cursor INTO @dbname, @dbid;
END;

CLOSE dbNames_cursor;
DEALLOCATE dbNames_cursor;

------------------------------------------------------------

-- if 'report only' mode is enabled
IF @reportOnly=1
BEGIN
SELECT dbName ,
tableName ,
schemaName ,
indexName ,
AvgFragmentationPercentage ,
reorganizationOrRebuildCommand
FROM #tmpFragmentedIndexes
ORDER BY AvgFragmentationPercentage DESC;
END
ELSE
-- if 'report only' mode is disabled, then execute
-- index reorganize/rebuild statements
BEGIN
DECLARE reorganizeOrRebuildCommands_cursor CURSOR
FOR
SELECT reorganizationOrRebuildCommand
FROM #tmpFragmentedIndexes
WHERE reorganizationOrRebuildCommand IS NOT NULL
ORDER BY AvgFragmentationPercentage DESC;

OPEN reorganizeOrRebuildCommands_cursor;
FETCH NEXT FROM reorganizeOrRebuildCommands_cursor INTO @ReorganizeOrRebuildCommand;
WHILE @@fetch_status = 0
BEGIN

IF @verboseMode = 1
BEGIN
PRINT ''
PRINT 'Executing script:'
PRINT @ReorganizeOrRebuildCommand
END

EXEC (@ReorganizeOrRebuildCommand);
FETCH NEXT FROM reorganizeOrRebuildCommands_cursor INTO @ReorganizeOrRebuildCommand;
END;

CLOSE reorganizeOrRebuildCommands_cursor;
DEALLOCATE reorganizeOrRebuildCommands_cursor;

PRINT ''
PRINT 'All fragmented indexes have been reorganized/rebuilt.'
PRINT ''
END
END
ELSE
BEGIN
PRINT '';
PRINT 'Error: You need to be SysAdmin and use SQL Server 2005 or later in order to use this script.';
PRINT '';
END

--End of Script

---------------Cpu_utilise query----

SELECT TOP 10 st.text
,st.dbid
,st.objectid
,qs.total_worker_time
,qs.last_worker_time
,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_worker_time DESC

------------------------DB Growth history--------

SET NOCOUNT ON
/*
Author: Nicholas Williams
Date: 3rd February 2008
Desc: Calculates Growth Info for all databases on a server that are being backed up. Relies on the backup tables, and as a result will only contain as many
days history as do the backup tables(@iNoSamples). If a database is not being backup up the results will be NULL. (For example the Tempdb)
This is a rewrite of something I did a few years ago, as I dont know where I saved the other code. bummer.
Email: Nicholas.Williams@reagola.com
*/

CREATE TABLE ##tbl_DataSize
(
Size DECIMAL(20)
)

CREATE TABLE #tbl_GrowthData
(
DatabaseName VARCHAR(50)
,NoSampleDays DECIMAL(20,3)
,DataSizeMB DECIMAL(20,3)
,LogSizeMB DECIMAL(20,3)
,BackupSizeMB DECIMAL(20,3)
,TotalSpaceMB DECIMAL(20,3)
,DataGrowth DECIMAL(20,3)
,LogGrowth DECIMAL(20,3)
,GrowthPercentage DECIMAL(20,3)
)

DECLARE
@iNoSamples INT
,@nMaxBackupSize DECIMAL
,@nMinBackupSize DECIMAL
,@nMaxLogSize DECIMAL
,@nMinLogSize DECIMAL
,@nMaxDataSize DECIMAL
,@nMinDataSize DECIMAL
,@vcDatabaseName VARCHAR(50)
,@dtMaxBackupTime DATETIME
,@dtMinBackupTime DATETIME
,@iMinBackupID INT
,@iMaxBackupID INT

DECLARE file_cursor CURSOR FOR
SELECT [name] FROM master.dbo.sysdatabases
ORDER BY [name]
OPEN file_cursor

FETCH NEXT FROM file_cursor
INTO @vcDatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @dtMaxBackupTime = (SELECT MAX(backup_finish_date)FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' and backup_finish_date >= '2017-01-16 01:41:51.000')
SET @dtMinBackupTime = (SELECT MIN(backup_finish_date)FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' and backup_finish_date >= '2017-01-16 01:41:51.000')
SET @iNoSamples =
DATEDIFF
(
dd
,@dtMinBackupTime
,@dtMaxBackupTime
)

SET @nMaxBackupSize = (SELECT backup_size FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMaxBackupTime)
SET @nMinBackupSize = (SELECT backup_size FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMinBackupTime)

SET @iMaxBackupID = (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMaxBackupTime)
SET @iMinBackupID = (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMinBackupTime)

SET @nMaxLogSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile WHERE backup_set_id = @iMaxBackupID AND file_type = 'L')
SET @nMinLogSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile WHERE backup_set_id = @iMinBackupID AND file_type = 'L')
SET @nMaxDataSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile WHERE backup_set_id = @iMaxBackupID AND file_type = 'D')
SET @nMinDataSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile WHERE backup_set_id = @iMinBackupID AND file_type = 'D')

EXEC ('
INSERT INTO ##tbl_DataSize
SELECT CAST((SUM(size)) as DECIMAL(20,3)) FROM '+@vcDatabaseName+'.dbo.sysfiles'
)

INSERT INTO #tbl_GrowthData
SELECT
@vcDatabaseName DatabaseName
,@iNoSamples NoSampleDays
,@nMaxDataSize
,@nMaxLogSize
,@nMaxBackupSize / 1048576
,((size * 8192) / 1048576) TotalSpaceUsed
,@nMaxDataSize - @nMinDataSize
,@nMaxLogSize - @nMinLogSize
,(((@nMaxDataSize + @nMaxLogSize) - (@nMinDataSize+ @nMinLogSize)) / (@nMinDataSize+ @nMinLogSize)) * 100.00
--growth percentage is calculated based upon the original data size, before the growth. as a result it may look a little funny, but it is accurate. or at least I think so :)
FROM ##tbl_DataSize

TRUNCATE TABLE ##tbl_DataSize

FETCH NEXT FROM file_cursor
INTO @vcDatabaseName

END
CLOSE file_cursor
DEALLOCATE file_cursor

SELECT
*
FROM #tbl_GrowthData

DROP TABLE ##tbl_DataSize
DROP TABLE #tbl_GrowthData

SET NOCOUNT OFF

---------------------Index Fragmentation on ALL Indexes in a Database

SELECT dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',

dbindexes.[name] as 'Index',

indexstats.alloc_unit_type_desc,

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent desc

****************************************************

SELECT OBJECT_NAME(OBJECT_ID) as tableName,index_id,index_type_desc,index_level,

avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count

FROM sys.dm_db_index_physical_stats(DB_ID(), Object_Id('Emp'), Default, Default ,Default)

---------------------Kill all connection DB-----

=====For MS SQL Server 2012 =====

USE [master];

DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'

FROM sys.dm_exec_sessions

WHERE database_id = db_id('MyDB')

EXEC(@kill);

=====MS SQL Server 2000, 2005, 2008=====

USE master;

DECLARE @kill varchar(8000); SET @kill = '';

SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'

FROM master..sysprocesses

WHERE dbid = db_id('MyDB')

EXEC(@kill);

----------------Last modified table info

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( 'DB Name')

AND OBJECT_ID=OBJECT_ID('Table Name')

---------Licencing count realted scripts----

SELECT
SERVERPROPERTY ('productversion'),
SERVERPROPERTY ('edition'),
SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('licensetype'),
CAST(SERVERPROPERTY('numlicenses') AS VARCHAR(255))

---------------------------
select @@version

-------------------------
SQL 2008 and above

SELECT cpu_count AS [Logical CPUs],
(case hyperthread_ratio
when 1 then
'1'
else
(cpu_count / hyperthread_ratio)
end)AS [Physical CPUs],
hyperthread_ratio
,physical_memory_kb / 1048576 AS 'mem_MB'
,virtual_memory_kb / 1048576 AS 'virtual_mem_MB'
,max_workers_count
,os_error_mode
,os_priority_class
FROM sys.dm_os_sys_info

---------------------------------------
2005----

SELECT cpu_count AS [Logical CPUs],
(case hyperthread_ratio
when 1 then
'1'
else
(cpu_count / hyperthread_ratio)
end)AS [Physical CPUs],
hyperthread_ratio
,physical_memory_in_bytes / 1048576 AS 'mem_MB'
,virtual_memory_in_bytes / 1048576 AS 'virtual_mem_MB'
,max_workers_count
,os_error_mode
,os_priority_class
FROM sys.dm_os_sys_info

--------------------------------

exec master..xp_cmdshell 'systeminfo'
or
msinfo32.exe in run command

------------------------------------------

/*
Name: SystemInfo.sql
Description: TSQL script to query the host name, domain name, time zone, OS, system type, total physical memory, and system boot time
Developer: JP Chen
Date Created: October 30, 2013 10:00 PM
*/
-- variables delcarations
declare @HostName nvarchar(125)
, @DomainName nvarchar(125)
, @TimeZone nvarchar(125)
, @OS nvarchar(125)
, @SystemType nvarchar(125)
, @Processor nvarchar(125)
, @TotalPhysicalMemory nvarchar(125)
, @SystemBootTime nvarchar(125)
, @xp_cmdshell bit
, @isshowadvancedoptions bit

if @@microsoftversion / power(2, 24) >= 9
begin
-- check if enabled 'show advanced options', if no enable it
select @isshowadvancedoptions = convert(int, isnull(value, value_in_use))
from sys.configurations
where name = 'show advanced options'

if @isshowadvancedoptions = 0
begin
exec sp_configure 'show advanced options', 1;
reconfigure;
end

-- check if enabled 'xp_cmdshell', if not then enable it
select @xp_cmdshell = convert(int, isnull(value, value_in_use))
from sys.configurations
where name = 'xp_cmdshell';

if @xp_cmdshell = 0
begin
exec sp_configure 'xp_cmdshell', 1;
reconfigure;
end

-- drop the #systeminfo if it exists
if object_id('tempdb..#systeminfo') is not null
drop table #systeminfo;

-- create the #systeminfo table
create table #systeminfo
(data nvarchar(255))

-- load the systeminfo into the #systeminfo table
insert into #systeminfo
exec master..xp_cmdshell 'systeminfo'

-- host name
select @HostName = ltrim(replace(data, 'Host Name:',''))
from #systeminfo
where data like 'Host Name%'
-- domain name
select @DomainName = ltrim(replace(data, 'Domain:',''))
from #systeminfo
where data like 'Domain%'

-- time zone
select @TimeZone = ltrim(replace(data, 'Time Zone:',''))
from #systeminfo
where data like 'Time Zone%'

-- os
select @OS = (select ltrim(replace(data, 'os name:',''))
from #systeminfo
where data like 'os name%')
+ right(@@version, len(@@version)+3- charindex ('build', @@version))

-- system type
select @SystemType = ltrim(replace(data, 'System Type:',''))
from #systeminfo
where data like 'System Type%'

-- processor
select @Processor = ltrim(replace(data, 'Processor(s):',''))
from #systeminfo
where data like 'Processor(s)%'

-- system type
select @SystemType = ltrim(replace(data, 'System Type:',''))
from #systeminfo
where data like 'System Type%'

-- total physical memory
select @TotalPhysicalMemory = ltrim(replace(data, 'Total Physical Memory:',''))
from #systeminfo
where data like 'Total Physical Memory%'

-- system boot time
select @SystemBootTime = ltrim(replace(data, 'System Boot Time:',''))
from #systeminfo
where data like 'System Boot Time%'
-- output
select @HostName as [Host Name]
, @DomainName [Domain Name]
, @TimeZone [Time Zone]
, @OS [OS]
, @Processor [Processor(s)]
, @SystemType [System Type]
, @TotalPhysicalMemory [Total Physical Memory]
, @SystemBootTime [System Boot Time]

-- revert the option on show advanced options
if (select convert(int, isnull(value, value_in_use)) from sys.configurations
where name = 'xp_cmdshell') <> @xp_cmdshell
begin
exec sp_configure 'xp_cmdshell', @xp_cmdshell;
reconfigure;
end

if (select convert(int, isnull(value, value_in_use)) from sys.configurations
where name = 'show advanced options') <> @isshowadvancedoptions
begin
exec sp_configure 'show advanced options', @isshowadvancedoptions
reconfigure with override
end

end

-------------------------------

DECLARE @xp_msver TABLE (
[idx] [int] NULL
,[c_name] [varchar](100) NULL
,[int_val] [float] NULL
,[c_val] [varchar](128) NULL
)

INSERT INTO @xp_msver
EXEC ('[master]..[xp_msver]');;

WITH [ProcessorInfo]
AS (
SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
,CASE
WHEN hyperthread_ratio = cpu_count
THEN cpu_count
ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
END AS [number_of_cores_per_cpu]
,CASE
WHEN hyperthread_ratio = cpu_count
THEN cpu_count
ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
END AS [total_number_of_cores]
,[cpu_count] AS [number_of_virtual_cpus]
,(
SELECT [c_val]
FROM @xp_msver
WHERE [c_name] = 'Platform'
) AS [cpu_category]
FROM [sys].[dm_os_sys_info]
)
SELECT [number_of_physical_cpus]
,[number_of_cores_per_cpu]
,[total_number_of_cores]
,[number_of_virtual_cpus]
,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
FROM [ProcessorInfo]

-----------------SELECT
SERVERPROPERTY ('productversion'),
SERVERPROPERTY ('edition'),
SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('licensetype'),
CAST(SERVERPROPERTY('numlicenses') AS VARCHAR(255))

---------------------------
select @@version

-------------------------
SQL 2008 and above

SELECT cpu_count AS [Logical CPUs],
(case hyperthread_ratio
when 1 then
'1'
else
(cpu_count / hyperthread_ratio)
end)AS [Physical CPUs],
hyperthread_ratio
,physical_memory_kb / 1048576 AS 'mem_MB'
,virtual_memory_kb / 1048576 AS 'virtual_mem_MB'
,max_workers_count
,os_error_mode
,os_priority_class
FROM sys.dm_os_sys_info

---------------------------------------
2005----

SELECT cpu_count AS [Logical CPUs],
(case hyperthread_ratio
when 1 then
'1'
else
(cpu_count / hyperthread_ratio)
end)AS [Physical CPUs],
hyperthread_ratio
,physical_memory_in_bytes / 1048576 AS 'mem_MB'
,virtual_memory_in_bytes / 1048576 AS 'virtual_mem_MB'
,max_workers_count
,os_error_mode
,os_priority_class
FROM sys.dm_os_sys_info

--------------------------------

exec master..xp_cmdshell 'systeminfo'
or
msinfo32.exe in run command

------------------------------------------

/*
Name: SystemInfo.sql
Description: TSQL script to query the host name, domain name, time zone, OS, system type, total physical memory, and system boot time
Developer: JP Chen
Date Created: October 30, 2013 10:00 PM
*/
-- variables delcarations
declare @HostName nvarchar(125)
, @DomainName nvarchar(125)
, @TimeZone nvarchar(125)
, @OS nvarchar(125)
, @SystemType nvarchar(125)
, @Processor nvarchar(125)
, @TotalPhysicalMemory nvarchar(125)
, @SystemBootTime nvarchar(125)
, @xp_cmdshell bit
, @isshowadvancedoptions bit

if @@microsoftversion / power(2, 24) >= 9
begin
-- check if enabled 'show advanced options', if no enable it
select @isshowadvancedoptions = convert(int, isnull(value, value_in_use))
from sys.configurations
where name = 'show advanced options'

if @isshowadvancedoptions = 0
begin
exec sp_configure 'show advanced options', 1;
reconfigure;
end

-- check if enabled 'xp_cmdshell', if not then enable it
select @xp_cmdshell = convert(int, isnull(value, value_in_use))
from sys.configurations
where name = 'xp_cmdshell';

if @xp_cmdshell = 0
begin
exec sp_configure 'xp_cmdshell', 1;
reconfigure;
end

-- drop the #systeminfo if it exists
if object_id('tempdb..#systeminfo') is not null
drop table #systeminfo;

-- create the #systeminfo table
create table #systeminfo
(data nvarchar(255))

-- load the systeminfo into the #systeminfo table
insert into #systeminfo
exec master..xp_cmdshell 'systeminfo'

-- host name
select @HostName = ltrim(replace(data, 'Host Name:',''))
from #systeminfo
where data like 'Host Name%'
-- domain name
select @DomainName = ltrim(replace(data, 'Domain:',''))
from #systeminfo
where data like 'Domain%'

-- time zone
select @TimeZone = ltrim(replace(data, 'Time Zone:',''))
from #systeminfo
where data like 'Time Zone%'

-- os
select @OS = (select ltrim(replace(data, 'os name:',''))
from #systeminfo
where data like 'os name%')
+ right(@@version, len(@@version)+3- charindex ('build', @@version))

-- system type
select @SystemType = ltrim(replace(data, 'System Type:',''))
from #systeminfo
where data like 'System Type%'

-- processor
select @Processor = ltrim(replace(data, 'Processor(s):',''))
from #systeminfo
where data like 'Processor(s)%'

-- system type
select @SystemType = ltrim(replace(data, 'System Type:',''))
from #systeminfo
where data like 'System Type%'

-- total physical memory
select @TotalPhysicalMemory = ltrim(replace(data, 'Total Physical Memory:',''))
from #systeminfo
where data like 'Total Physical Memory%'

-- system boot time
select @SystemBootTime = ltrim(replace(data, 'System Boot Time:',''))
from #systeminfo
where data like 'System Boot Time%'
-- output
select @HostName as [Host Name]
, @DomainName [Domain Name]
, @TimeZone [Time Zone]
, @OS [OS]
, @Processor [Processor(s)]
, @SystemType [System Type]
, @TotalPhysicalMemory [Total Physical Memory]
, @SystemBootTime [System Boot Time]

-- revert the option on show advanced options
if (select convert(int, isnull(value, value_in_use)) from sys.configurations
where name = 'xp_cmdshell') <> @xp_cmdshell
begin
exec sp_configure 'xp_cmdshell', @xp_cmdshell;
reconfigure;
end

if (select convert(int, isnull(value, value_in_use)) from sys.configurations
where name = 'show advanced options') <> @isshowadvancedoptions
begin
exec sp_configure 'show advanced options', @isshowadvancedoptions
reconfigure with override
end

end

-------------------------------Licencing count realted scripts

DECLARE @xp_msver TABLE (
[idx] [int] NULL
,[c_name] [varchar](100) NULL
,[int_val] [float] NULL
,[c_val] [varchar](128) NULL
)

INSERT INTO @xp_msver
EXEC ('[master]..[xp_msver]');;

WITH [ProcessorInfo]
AS (
SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]
,CASE
WHEN hyperthread_ratio = cpu_count
THEN cpu_count
ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
END AS [number_of_cores_per_cpu]
,CASE
WHEN hyperthread_ratio = cpu_count
THEN cpu_count
ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
END AS [total_number_of_cores]
,[cpu_count] AS [number_of_virtual_cpus]
,(
SELECT [c_val]
FROM @xp_msver
WHERE [c_name] = 'Platform'
) AS [cpu_category]
FROM [sys].[dm_os_sys_info]
)
SELECT [number_of_physical_cpus]
,[number_of_cores_per_cpu]
,[total_number_of_cores]
,[number_of_virtual_cpus]
,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]
FROM [ProcessorInfo]

----------permission on a particular job to execute on SQL Server 2005

USE [msdb]
GO
CREATE USER [sqladmin] FOR LOGIN [sqladmin] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember N'SQLAgentUserRole', N'sqladmin'

GO

---------------------Restore_Status

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a

WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

------------Shrink All DBLog file

set nocount on
SELECT
'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
+ 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)'
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
sys.master_files mf
JOIN sys.databases d
ON mf.database_id = d.database_id
WHERE d.database_id > 4 and mf.type_desc = 'LOG'

DBCC CHECKDB

DBA Mafiya

Search This Blog

Wednesday, 7 February 2018

Queies_r mostly_used

1 comment: