Backup Log on top of dependant backup file.

IF OBJECT_ID('tempdb.dbo.#BackupHistory', 'U') IS NOT NULL
  DROP TABLE #BackupHistory;
create table #BackupHistory(
  DatabaseName varchar(100),
  LogicalName varchar(max),
  LastBackupDate date,
  BackupPath varchar(max))

INSERT #BackupHistory(DatabaseName, LogicalName, LastBackupDate, BackupPath)
SELECT a.database_name, b.name, a.backup_finish, b.physical_device_name FROM
( SELECT
bset.database_name,
max(bset.backup_finish_date) as backup_finish
FROM   msdb.dbo.backupmediafamily bfamily
INNER JOIN msdb.dbo.backupset bset ON bfamily.media_set_id = bset.media_set_id
WHERE bset.type = 'I'
OR bset.type = 'D'
GROUP BY bset.database_name
) a
INNER JOIN
( SELECT bset.database_name,
bset.name,
bset.backup_finish_date,
bfamily.physical_device_name
FROM msdb.dbo.backupmediafamily bfamily
INNER JOIN msdb.dbo.backupset bset ON bfamily.media_set_id = bset.media_set_id
) b
ON a.database_name = b.database_name
AND a.backup_finish = b.backup_finish_date

DECLARE @sqlStr varchar(max), @dbname varchar(50), @path varchar(max), @LogicalName varchar(max)
DECLARE db_cursor CURSOR FOR
SELECT DatabaseName from #BackupHistory ORDER BY DatabaseName;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @path = (SELECT BackupPath from #BackupHistory WHERE DatabaseName = @dbname)
SET @LogicalName = (SELECT LogicalName from #BackupHistory WHERE DatabaseName = @dbname)
SET @sqlStr= 'BACKUP LOG '+@dbname+'
TO  DISK = '+@path+'
WITH NAME = '+@LogicalName+', COMPRESSION'

print @sqlStr
--EXEC (@sqlStr)
print char(13) + 'Looping.... Iteration.... Changing Database.....' + char(13) + char(13)
FETCH NEXT FROM db_cursor INTO @dbname;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
DROP TABLE #BackupHistory

Check Log Health

--Log File is a very important property of MSSQL SERVER
--This scripts checks log health very dynamically
--Using Log_Reuse_Wait & DBCC SQLPERF

--Author : Mustak Ahammad Prince
--Email: mustak.prince@gmail.com

IF OBJECT_ID('tempdb.dbo.#TmpLOGSPACE', 'U') IS NOT NULL
  DROP TABLE #TmpLOGSPACE;
IF OBJECT_ID('tempdb.dbo.#TmpLOG', 'U') IS NOT NULL
  DROP TABLE #TmpLOG;
create table #TmpLOGSPACE(
  DatabaseName varchar(100),
  LOGSIZE_MB decimal(18, 9),
  LOGSPACE_USED decimal(18, 9),
  LOGSTATUS decimal(18, 9))

create table #TmpLOG(
  DatabaseName varchar(100),
  LOG_REUSE varchar(30),
  LOGSIZE_MB decimal(18, 9),
  LOGSPACE_USED decimal(18, 9),
  LOGSTATUS decimal(18, 9))


insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS)
EXEC('DBCC SQLPERF(LOGSPACE);')

insert #TmpLOG(DatabaseName, LOG_REUSE, LOGSIZE_MB, LOGSPACE_USED)
(select a.name, a.log_reuse_wait_desc, b.LOGSIZE_MB, b.LOGSPACE_USED
from sys.databases a
inner join #TmpLOGSPACE b
on a.name = b.DatabaseName
where a.name not in ('master','msdb','model','tempdb'))

DROP TABLE #TmpLOGSPACE;
SELECT * FROM #TmpLOG
ORDER BY 2, 4 DESC;
DROP TABLE #TmpLOG

Table Changed BY

select e.name as eventclass,
t.loginname,
t.spid,
t.starttime,
t.textdata,
t.objectid,
t.objectname,
t.databasename,
t.hostname,
t.ntusername,
t.ntdomainname,
t.clientprocessid,
t.applicationname,
t.error
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1f.[value]
FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2)), DEFAULT) T
inner join sys.trace_events e on t.eventclass = e.trace_event_id
where eventclass=164

Check Error Log

DECLARE @SERVER NVARCHAR(50)
SET @SERVER = 'SERVER1\INSTANCE'
DECLARE @sqlStatement1 VARCHAR(200)
SET @sqlStatement1 = @SERVER + '.master.dbo.xp_readerrorlog'
CREATE TABLE #Errors (vchMessage varchar(2000), ID int)
--CREATE INDEX idx_msg ON #Errors(ID, vchMessage)
INSERT #Errors EXEC @sqlStatement1
--select * from #errors
--INSERT INTO TBL_MONITOR_SQL_ERROR_LOG(server,dbName,logMessage,capturedDate)
SELECT @server,'na',RTRIM(LTRIM(vchMessage)),GETDATE() FROM #Errors WHERE
([vchMessage] like '%error%'
or [vchMessage] like '%fail%'
or [vchMessage] like '%Warning%'
or [vchMessage] like '%The SQL Server cannot obtain a LOCK resource at this time%'
or [vchMessage] like '%Autogrow of file%in database%cancelled or timed out after%'
or [vchMessage] like '%Consider using ALTER DATABASE to set smaller FILEGROWTH%'
or [vchMessage] like '% is full%'
or [vchMessage] like '% blocking processes%'
or [vchMessage] like '%SQL Server has encountered%IO requests taking longer%to complete%'
)
and [vchMessage] not like '%\ERRORLOG%'
and [vchMessage] not like '%Attempting to cycle errorlog%'
and [vchMessage] not like '%Errorlog has been reinitialized.%'
and [vchMessage] not like '%found 0 errors and repaired 0 errors.%'
and [vchMessage] not like '%without errors%'
and [vchMessage] not like '%This is an informational message%'
and [vchMessage] not like '%WARNING:%Failed to reserve contiguous memory%'
and [vchMessage] not like '%The error log has been reinitialized%'
and [vchMessage] not like '%Setting database option ANSI_WARNINGS%'
and [vchMessage] not like '%Error: 15457, Severity: 0, State: 1%'
and [vchMessage] <> 'Error: 18456, Severity: 14, State: 16.'
DROP TABLE #Errors

List All Logins & User Mappings

--Step 1 : Create temp tab;le
CREATE TABLE #tempMappings (
LoginName nvarchar(1000),
DBname nvarchar(1000),
Username nvarchar(1000),
Alias nvarchar(1000)
)
--Step 2:Insert the sp_msloginmappings into the temp table
INSERT INTO #tempMappings
EXEC master..sp_msloginmappings
--Step 3 : List the results . Filter as required
SELECT *
FROM   #tempMappings WHERE username LIKE '%'
ORDER BY DBname, username
--Step 4:  Manage cleanup of temp table
DROP TABLE #tempMappings

Running Queries

SELECT
r.session_id,
s.TEXT,
r.[status],
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s 

Memory Utilization Per Table

SELECT object_name(t.object_id) AS [Table Name]
     , memory_allocated_for_table_kb
 , memory_allocated_for_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats dms JOIN sys.tables t
ON dms.object_id=t.object_id
WHERE t.type='U'SELECT object_name(t.object_id) AS [Table Name]
     , memory_allocated_for_table_kb
 , memory_allocated_for_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats dms JOIN sys.tables t
ON dms.object_id=t.object_id
WHERE t.type='U'

Port Info

USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

All DB Space Used & Free

USE master;
GO

SET NOCOUNT ON
DECLARE @Kb float
DECLARE @PageSize float
DECLARE @SQL varchar(max)

SELECT @Kb = 1024.0
SELECT @PageSize=v.low/@Kb FROM master..spt_values v WHERE v.number=1 AND v.type='E'

IF OBJECT_ID('tempdb.dbo.#FileSize') IS NOT NULL  DROP TABLE #FileSize CREATE TABLE #FileSize (  DatabaseName sysname,  [FileName] varchar(max),  FileSize int,  FileGroupName varchar(max),  LogicalName varchar(max)
)

IF OBJECT_ID('tempdb.dbo.#FileStats') IS NOT NULL  DROP TABLE #FileStats CREATE TABLE #FileStats (  FileID int,  FileGroup int,  TotalExtents int,  UsedExtents int,  LogicalName varchar(max),  FileName varchar(max)
)

IF OBJECT_ID('tempdb.dbo.#LogSpace') IS NOT NULL  DROP TABLE #LogSpace CREATE TABLE #LogSpace (  DatabaseName sysname,  LogSize float,  SpaceUsedPercent float,  Status bit
)

INSERT #LogSpace EXEC ('DBCC sqlperf(logspace)')

DECLARE @DatabaseName sysname

DECLARE cur_Databases CURSOR FAST_FORWARD FOR  SELECT DatabaseName = [name] FROM dbo.sysdatabases WHERE [name] <> 'RVR_FSA' ORDER BY DatabaseName OPEN cur_Databases FETCH NEXT FROM cur_Databases INTO @DatabaseName WHILE @@FETCH_STATUS = 0
  BEGIN
 print @DatabaseName
 SET @SQL = '
USE [' + @DatabaseName + '];
DBCC showfilestats;
INSERT #FileSize (DatabaseName, [FileName], FileSize, FileGroupName, LogicalName) SELECT ''' +@DatabaseName + ''', filename, size, ISNULL(FILEGROUP_NAME(groupid),''LOG''), [name]  FROM dbo.sysfiles sf; '
PRINT @SQL
 INSERT #FileStats EXECUTE (@SQL)
 FETCH NEXT FROM cur_Databases INTO @DatabaseName
  END

CLOSE cur_Databases
DEALLOCATE cur_Databases


SELECT
 DatabaseName = fsi.DatabaseName,
 FileGroupName = fsi.FileGroupName,
 LogicalName = RTRIM(fsi.LogicalName),
 [FileName] = RTRIM(fsi.FileName),
 DriveLetter = LEFT(RTRIM(fsi.FileName),2),
 FileSize = CAST(fsi.FileSize*@PageSize/@Kb as decimal(15,2)),
 UsedSpace = CAST(ISNULL((fs.UsedExtents*@PageSize*8.0/@Kb),
 fsi.FileSize*@PageSize/@Kb * ls.SpaceUsedPercent/100.0) as decimal(15,2)),
 FreeSpace = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0)*@PageSize/@Kb),
 (100.0-ls.SpaceUsedPercent)/100.0 * fsi.FileSize*@PageSize/@Kb) as decimal(15,2)),
 [FreeSpace %] = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0) / fsi.FileSize * 100.0),
 100-ls.SpaceUsedPercent) as decimal(15,2))
 FROM #FileSize fsi
 LEFT JOIN #FileStats fs  ON fs.FileName = fsi.FileName
 LEFT JOIN #LogSpace ls  ON ls.DatabaseName = fsi.DatabaseName
 ORDER BY 8, 1 DESC
 --order by 1 means order by column no. 1

Server Uptime

-- Get start time from SQL Server 2008
SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT create_date  AS StartTime
FROM sys.databases
WHERE name = 'tempdb';

Most Utilized DB

select db_name(dbid), count(dbid) as processes ,sum(cpu) as usage from
sysprocesses group by dbid order by usage desc

Most Blocking DB

select db_name(dbid), count(dbid) as processes ,sum(cpu) as usage from
sysprocesses where blocked != 0 group by dbid order by usage desc

Process Running for more than 10 minutes

Select sp.spid, sd.name, sp.waittime/(1000
* 60) as wait_time_mins ,
sp.cpu, sp.memusage, sp.login_time, sp.cmd, sp.program_name,
sp.nt_username, sp.loginame,
sp.hostname, sp.status, sp.waitresource,
sp.blocked from sysprocesses sp, sysdatabases sd
where sp.dbid = sd.dbid and sp.status = ‘runnable’ and
sp.waittime/(1000 * 60) >=
10

Waiting Tasks

select a.spid,b.name,a.cmd,a.memusage,a.cpu,a.program_name,a.nt_username,a.loginame,a.hostname,a.status
from
master.dbo.sysprocesses a,master.dbo.sysdatabases b where a.dbid=b.dbid and
blocked<>0
go

Update Statistics with Full Scan

exec sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

All DB Details

drop table DBInfo
--- Step 1 � Get the current database information

use master
go
DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)
DECLARE @command VARCHAR(5000)
SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + ''''+ ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
(ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status,
Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate)
EXEC sp_MSForEachDB @command

SELECT
ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB,
FreeSpacePct, PollDate FROM @DBInfo ORDER BY ServerName, DatabaseName

----Step 2 � Store this information in some table inside the SQL Server Database
use tempdb
go
create TABLE  DBInfo
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)


--- Step 3 � Make a scheduled job, which will repeat the step one and two (storing current database information inside the table)
-- Make a scheduled job and add the following codes.

DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)
DECLARE @command VARCHAR(5000)
SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + ''''+ ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
(ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status,
Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate)
EXEC sp_MSForEachDB @command

INSERT INTO tempdb.dbo.DBInfo (ServerName,DatabaseName,FileSizeMB,LogicalFileName,PhysicalFileName,Status,Updateability,RecoveryMode,FreeSpaceMB,FreeSpacePct,PollDate)
SELECT
ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB,
FreeSpacePct, PollDate FROM @DBInfo

--- Step 4 � Extract the Data from the table which you have in table, table created in step 2

select * from tempdb.dbo.DBInfo 

Enlarge Log File

ALTER DATABASE model
MODIFY FILE
(
    NAME = modellog,
    SIZE = 1MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 1MB
);

List All Users

--------------------------------CODE START----------------------------------
SET NOCOUNT ON
DECLARE @name varchar(50)
DECLARE logon_cursor CURSOR FOR
select name from sys.server_principals WHERE
name LIKE '%' AND name NOT LIKE 'NT AUTHORITY%'
OPEN logon_cursor
FETCH NEXT FROM logon_cursor
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @name
-- Get the next author.
FETCH NEXT FROM logon_cursor
INTO @name
END
CLOSE logon_cursor
DEALLOCATE logon_cursor
GO
-------------------------------CODE END-----------------------------------------

Index Rebuild with Fill Factor

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

Index Appropriate Rebuild, Reorganize

--Depending on fragmentation index level appropriated action will be taken (no action, rebuild or reorganize)

--If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index will be reorganized, finally if index average fragmentation is greater than 30% index will be rebuilt.
--Ensure a USE <databasename> statement has been executed first.

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

DECLARE @dbid smallint;

--Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SET @dbid = DB_ID();

SELECT

    [object_id] AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag, page_count

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation

AND index_id > 0 -- Ignore heaps

AND page_count > 25; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N'Executed: ' + @command;

END

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO

Space Used by All Tables

Set statistics time on
declare @PageSize float
select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'

Select object_Name(i.object_id) as [name]
,p.rows
,Convert(varchar(50),@PageSize * SUM(total_pages)) + ' KB' as [reserved]
,Convert(varchar(50),@PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) + ' KB' as [data]
,Convert(varchar(50),@PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) +  ' KB' as [index_size]
,Convert(varchar(50),@PageSize * SUM(total_pages-used_pages)) +  ' KB' as [unused]
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
JOIN sys.tables t ON i.object_id=t.object_id
Where i.type<=1 and a.type=1
and
t.type='U' and is_ms_shipped=0
Group By i.object_id,p.rows
GO
Set statistics time off

Auto Log Backup

IF OBJECT_ID('tempdb.dbo.#TmpLOGSPACE', 'U') IS NOT NULL
  DROP TABLE #TmpLOGSPACE;
IF OBJECT_ID('tempdb.dbo.#TmpLOG', 'U') IS NOT NULL
  DROP TABLE #TmpLOG;
create table #TmpLOGSPACE(
  DatabaseName varchar(100),
  LOGSIZE_MB decimal(18, 9),
  LOGSPACE_USED decimal(18, 9),
  LOGSTATUS decimal(18, 9))

create table #TmpLOG(
  DatabaseName varchar(100),
  LOG_REUSE varchar(30),
  LOGSIZE_MB decimal(18, 9),
  LOGSPACE_USED decimal(18, 9),
  LOGSTATUS decimal(18, 9))


insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS)
EXEC('DBCC SQLPERF(LOGSPACE);')

insert #TmpLOG(DatabaseName, LOG_REUSE, LOGSIZE_MB, LOGSPACE_USED)
(select a.name, a.log_reuse_wait_desc, b.LOGSIZE_MB, b.LOGSPACE_USED
from sys.databases a
inner join #TmpLOGSPACE b
on a.name = b.DatabaseName)

DROP TABLE #TmpLOGSPACE;
DECLARE db_cursor CURSOR FOR
SELECT DatabaseName from #TmpLOG
where (LOGSPACE_USED>50)
AND (DatabaseName not in ('master','tempdb','model','msdb') )
AND LOG_REUSE = 'LOG_BACKUP';
DECLARE @now nvarchar(max) = (select replace(convert(varchar(8), getdate(), 112)+convert(varchar(8), getdate(), 114), ':',''));
declare @LogicalName nvarchar(100) = DB_NAME() + '_TransactionLogBackup_' + @now;
declare @BackupPath nvarchar(200) = 'D:\DBA_Work_Files\BackUp_root\';
declare @newPath nvarchar(200) = ''''+ @BackupPath + @LogicalName + '.trn'+'''';
DECLARE @sqlStr varchar(max), @dbname varchar(50)

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN

       --Do stuff with scalar values
SET @LogicalName = ''''+ DB_NAME() + '_TransactionLogBackup_' + @now + '''';
declare @percent decimal = (Select LOGSPACE_USED from #TmpLOG where DatabaseName = @dbname)

print char(13)+ 'DATABSE: '+ @dbname +char(13)+'Log Used Percentage: '+ convert(varchar(10),@percent)

SET @sqlStr= 'BACKUP LOG '+@dbname+'
TO  DISK = '+@newPath+'
WITH NAME = '+@logicalName+', COMPRESSION'

       --print char(13)+(@sqlStr)
  EXEC (@sqlStr)

       FETCH NEXT FROM db_cursor INTO @dbname;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
drop table #TmpLOG;

--to make this effective, create a job to execute T-SQL than put it there.

All User on other server

SELECT *
FROM OPENROWSET
(
  'SQLNCLI',
--Server information, Username & Password here
  'Server=10.5.6.102;UID=prince;PWD=123;',
  'SELECT name, type_desc FROM sys.server_principals
WHERE TYPE IN (''U'', ''S'', ''G'') and name not like ''%##%''
ORDER BY name, type_desc;'
)

User Privileges

select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id

Server Info

select name, create_date, state_desc, recovery_model_desc, page_verify_option_desc, log_reuse_wait_desc from sys.databases

Data & Log File Info

use master
go
declare @dbname
sysname
set @dbname = null
if @dbname
is not null and @dbname
not in (select name from
master.dbo.sysdatabases)

begin

raiserror('You''re just one step away
from the results. please verify the database name is correct and try again. www.sqlServer-Training.com', 16,1)
end
set nocount on
if exists (select * from sysobjects where name = '#sizeinfo' and type = 'u')

drop table #sizeinfo
create table #sizeinfo
(
db_name varchar(100) not null primary key clustered,
total dec (7, 1),
data dec (7, 1),
data_used dec (7, 1),
[data (%)] dec (7, 1),
data_free dec (7, 1),
[data_free (%)]
dec (7, 1),
log dec (7, 1),
log_used dec (7, 1),
[log (%)] dec (7, 1),
log_free dec (7, 1),
[log_free (%)]
dec (7, 1),
status dec (7, 1)
)
set nocount on
insert
#sizeinfo ( db_name, log, [log (%)] , status
) exec ('dbcc sqlperf(logspace)
with no_infomsgs')
print '' print ''
if @dbname
is null

declare dbname cursor for select name from master.dbo.sysdatabases where
not status
& 32 = 32
and not status & 512
= 512 order
by name asc
else if @dbname is not null
begin
delete from
#sizeinfo where db_name <>
@dbname

declare dbname cursor for select name from master.dbo.sysdatabases where
not status
& 32 = 32
and not status & 512
= 512 and
name =
@dbname
end
open
dbname
fetch next from dbname
into @dbname
while @@fetch_status = 0
begin
----- adding .0 at the end of interger to avoid divide by zero error

exec ( ' use [' + @dbname
+ '] declare @total dec(7,1),
@data dec (7, 1),
@data_used dec (7, 1),
@data_percent dec (7, 1),
@data_free dec (7, 1),
@data_free_percent dec (7, 1),
@log dec (7, 1),
@log_used dec (7, 1),
@log_used_percent dec (7, 1),
@log_free dec (7, 1),
@log_free_percent dec (7, 1)
set @total = (select sum(convert(dec(15),size)) from sysfiles) * 8192.0 /1048576.0
set @data = (select sum(size) from sysfiles where (status & 64 = 0))* 8192.0 / 1048576.0
set @data_used = (select sum(convert(dec(15),reserved)) from sysindexes
where indid in (0, 1, 255)) * 8192.0 / 1048576.0
set
@data_percent = (@data_used * 100.0 / @data)
set @data_free = (@data - @data_used)
set @data_free_percent = (@data_free * 100.0 / @data
)
set @log = (select log from #sizeinfo where db_name = '''+@dbname+''')
set @log_used_percent = (select [log (%)] from #sizeinfo where db_name ='''+@dbname+''')
set @log_used = @log * @log_used_percent / 100.0
set @log_free = @log - @log_used
set @log_free_percent =@log_free * 100.0 / @log
update #sizeinfo set total = @total,

data = @data ,
data_used = @data_used,
[data (%)] = @data_percent,
data_free = @data_free,
[data_free (%)] = @data_free_percent,
log_used = @log_used,
log_free = @log_free,
[log_free (%)] = @log_free_percent
where db_name = '''+@dbname+'''' )

fetch next from dbname
into @dbname
end
close
dbname
deallocate
dbname
if ((select count(*) from #sizeinfo
) <> 1)
select @@servername as
'ServerName',db_name, total, data, data_used, [data (%)], data_free, [data_free (%)],
log,
log_used, [log (%)], log_free,
[log_free (%)]

from #sizeinfo order by db_name asc
else
select @@servername as
'ServerName',db_name, total, data, data_used, [data (%)], data_free, [data_free (%)],
log,
log_used, [log (%)], log_free,
[log_free (%)]

from #sizeinfo
drop table #sizeinfo

Index Care

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''  

   -- create table cursor
   EXEC (@cmd)
   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           EXEC (@cmd)
       END
       ELSE
       BEGIN
          -- SQL 2000 command
          DBCC DBREINDEX(@Table,' ',@fillfactor)
       END

       FETCH NEXT FROM TableCursor INTO @Table  
   END  

   CLOSE TableCursor  
   DEALLOCATE TableCursor

   FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor

Index Fragmentation Percentage

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'%'), OBJECT_ID(N'%'), NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;  

Job Info

SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'Error Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' ELSE
'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
CONVERT(VARCHAR(500),jh.message) AS step_description
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status

Job History

SELECT
    name,
    CONVERT(DATETIME,CONVERT(CHAR(8),run_date) + ' ' +  STUFF(STUFF((LEFT('000000',6-LEN(run_time))+ CONVERT(VARCHAR(6),run_time)),3,0,':'),6,0,':')) AS start_time,
    DATEADD(MINUTE,DATEDIFF(MINUTE, '0:00:00', CONVERT(TIME,STUFF(STUFF((LEFT('000000',6-LEN(run_duration))+ CONVERT(VARCHAR(6),run_duration)),3,0,':'),6,0,':'))),CONVERT(DATETIME,CONVERT(CHAR(8),run_date) + ' ' +  STUFF(STUFF((LEFT('000000',6-LEN(run_time))+ CONVERT(VARCHAR(6),run_time)),3,0,':'),6,0,':'))) AS end_time,
    run_status,
    instance_id
FROM msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
WHERE step_id = 0
ORDER BY 2 desc

Find String in All DB SP

sp_msforeachdb 'use ?;select name,''?'' from sys.procedures where object_definition(object_id) like ''%dbcc%'''

Auto Restore Last Backup

declare @backupfile as Varchar(255)

SELECT TOP 1 @backupfile=mf.physical_device_name from msdb..backupset bk    
join msdb..backupmediafamily mf on bk.media_set_id = mf.media_set_id  
where database_name=N'sourcedatabasename' and bk.type='D' order by
backup_set_id desc

ALTER DATABASE [databasename] SET  SINGLE_USER  WITH ROLLBACK IMMEDIATE

RESTORE DATABASE databasename
FROM DISK = @backupfile
WITH MOVE 'datafile' TO 'databasefilepath',
MOVE 'logfilename' TO 'logfilepath', REPLACE

ALTER DATABASE [databasename] SET  MULTI_USER  WITH ROLLBACK IMMEDIATE

Backup History

SELECT
  DISTINCT
        a.Name AS DatabaseName ,
        CONVERT(SYSNAME, DATABASEPROPERTYEX(a.name, 'Recovery')) RecoveryModel ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), MAX(backup_finish_date), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'D'
                            AND is_copy_only = '0'
                 ), 'No Full') AS 'Full' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(12), MAX(backup_finish_date), 101)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'I'
                            AND is_copy_only = '0'
                 ), 'No Diff') AS 'Diff' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(20), MAX(backup_finish_date), 120)
                   FROM     msdb.dbo.backupset
                   WHERE    database_name = a.name
                            AND type = 'L'
                 ), 'No Log') AS 'LastLog' ,
        COALESCE(( SELECT   CONVERT(VARCHAR(20), backup_finish_date, 120)
                   FROM     ( SELECT    ROW_NUMBER() OVER ( ORDER BY backup_finish_date DESC ) AS 'rownum' ,
                                        backup_finish_date
                              FROM      msdb.dbo.backupset
                              WHERE     database_name = a.name
                                        AND type = 'L'
                            ) withrownum
                   WHERE    rownum = 2
                 ), 'No Log') AS 'Before-LastLog'
FROM    sys.databases a
        LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
WHERE   a.name <> 'tempdb'
        AND a.state_desc = 'online'
GROUP BY a.Name ,
        a.compatibility_level
ORDER BY a.name

Backup History with Details

DECLARE @DBName SYSNAME;
SET @DBName = DB_NAME(); -- modify these as you desire.
SET @DBName = NULL; -- comment this line if you want to limit the displayed history

SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSize = bs.compressed_backup_size
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType = CASE bs.type
        WHEN 'D' THEN 'Database'
        WHEN 'L' THEN 'Log'
        ELSE '[unknown]' END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf
        ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE (bs.database_name = @DBName
    OR @DBName IS NULL)
    AND bs.type = 'D'
ORDER BY bs.backup_start_date DESC;

Last Backup Time

--Author: Mustak Prince

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

LastWeek Backup History

--Author: Mustak Ahammad Prince

SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
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() - 7)
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date

Failed Jobs

SELECT a. name, b.step_name, b.message, b.run_date, b.run_time, b.run_duration
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysjobhistory b
ON a.job_id = b.job_id
WHERE b.message like '%The step failed.%'
ORDER BY b.run_date DESC

Differential Backup Size Prediction

/*SQL Differential Backup Size Prediction - Mustak Ahammad Prince */

IF isNULL(object_id('tempdb.dbo.##showFileStats'), 1) <> 1
DROP TABLE ##showFileStats

CREATE TABLE ##showFileStats (
fileID INT,
fileGroup INT,
totalExtents BIGINT,
usedExtents BIGINT,
logicalFileName VARCHAR (500),
filePath VARCHAR (1000)
)

IF isNULL(object_id('tempdb.dbo.##DCM'), 1) <> 1
DROP TABLE ##DCM

CREATE TABLE ##DCM (
parentObject VARCHAR(5000),
[object] VARCHAR(5000),
field VARCHAR (5000),
value VARCHAR (5000)
)

/*we need to get a list of all the files in the database.  each file needs to be looked at*/
INSERT INTO ##showFileStats EXEC('DBCC SHOWFILESTATS with tableresults')

DECLARE @currentFileID INT,
@totalExtentsOfFile BIGINT,
@dbname VARCHAR(100),
@SQL VARCHAR(200),
@currentDCM BIGINT,
@step INT

SET @dbname = db_name()
SET @step = 511232

DECLARE myCursor SCROLL CURSOR FOR
SELECT fileID, totalExtents
FROM ##showFileStats

OPEN myCursor
FETCH NEXT FROM myCursor INTO @currentFileID, @totalExtentsOfFile

/*look at each differential change map page in each data file of the database and put the output into ##DCM*/
WHILE @@FETCH_STATUS = 0
BEGIN

SET @currentDCM = 6
WHILE @currentDCM <= @totalExtentsOfFile*8
BEGIN
SET @SQL = 'dbcc page('+ @dbname + ', ' + CAST(@currentFileID AS VARCHAR) + ', ' + CAST(@currentDCM AS VARCHAR) + ', 3) WITH TABLERESULTS'
INSERT INTO ##DCM EXEC (@SQL)
SET @currentDCM = @currentDCM + @step
END

FETCH NEXT FROM myCursor INTO @currentFileID, @totalExtentsOfFile
END
CLOSE myCursor
DEALLOCATE myCursor

/*remove all unneeded rows from our results table*/
DELETE FROM ##DCM WHERE value = 'NOT CHANGED' OR parentObject NOT LIKE 'DIFF_MAP%'
--SELECT * FROM ##DCM

/*sum the extentTally column*/
SELECT SUM (extentTally) as totalChangedExtents, SUM(extentTally)/16 as 'diffPrediction(MB)', SUM(extentTally)/16/1024 as 'diffPrediction(GB)'
FROM
/*create extentTally column*/
(SELECT extentTally =
CASE
WHEN secondChangedExtent > 0 THEN CAST(secondChangedExtent AS BIGINT) - CAST(firstChangedExtent AS BIGINT) + 1
ELSE 1
END
FROM
/*parse the 'field' column to give us the first and last extents of the range*/
(SELECT (SUBSTRING(field,(SELECT CHARINDEX(':', field, 0))+1,(CHARINDEX(')', field, 0))-(CHARINDEX(':', field, 0))-1))/8 as firstChangedExtent,
secondChangedExtent =
CASE
WHEN CHARINDEX(':', field, CHARINDEX(':', field, 0)+1) > 0 THEN (SUBSTRING(field,(CHARINDEX(':', field, CHARINDEX(':', field, 0)+1)+1),(CHARINDEX(')', field,CHARINDEX(')', field, 0)+1))-(CHARINDEX(':', field, CHARINDEX(':', field, 0)+1))-1))/8
ELSE ''
END
FROM ##DCM)parsedFieldColumn)extentTallyColumn

Estimate All_DB Backup Size Total

use msdb
go
/* Please change device_type and backupset type combination as you need.
backupmediafamily device_type
2 = File
5 = Tape

backupset type
L = Log
I = Differential
D = Full
*/

SELECT
      (SUM(backup_size) + SUM(1536)) / 1024 / 1024 As MBs,
      (SUM(backup_size) + SUM(1536)) /1024 / 1024 / 1024 As GBs
FROM
      backupset
INNER JOIN
(
      SELECT
            database_name,
            MAX(backup_start_date) as LastFullBackupDate
      FROM
            backupset
      WHERE
            media_set_id IN (SELECT media_set_id FROM backupmediafamily WHERE device_type = 2) AND
            type = 'D'
      GROUP BY
            database_name
) AS GetLastDate
ON
      backupset.database_name = GetLastDate.database_name AND
      backupset.backup_start_date = GetLastDate.LastFullBackupDate

Estimate full backup size

BACKUP DATABASE <DatabaseName> to disk='NULL'

--Look closely to the output
--it will give an output like that:
--BACKUP DATABASE successfully processed 12345 pages in XXX seconds (YYY MB/sec).
--Backup size estimation = XXX * YYY

Create few tables & insert some data...

--For testing purpose, sometimes we may need to create some tables & insert some data into the tables.
--Here is a dynamic script to create a number of tables & insert lots of data.
--Author: Mustak Ahammad Prince
--Purpose: Create few tables & insert some data...

USE Loan
GO

DECLARE @tableStr varchar(max)
DECLARE @insertStr varchar(max)
DECLARE @tableStr1 varchar(max) = 'CREATE TABLE '
DECLARE @tableStr2 varchar(max) = ' (id int, value1 varchar(max),value2 varchar(max))'
DECLARE @insertStr1 varchar(max) = 'INSERT INTO '
DECLARE @insertStr2 varchar(max) = ' VALUES ( '
DECLARE @insertStr3 varchar(max) = ', ''ABC'',''DEF'')'
DECLARE @tableName varchar(20) = 'dba_'
DECLARE @start int = 1

while (@start<=20)
BEGIN
DECLARE @newName varchar(max)= @tableName + cast(@start as varchar(max))
--print @newName + char(13)
SET @start = @start +1
SET @tableStr = @tableStr1 + @newName +@tableStr2
EXEC (@tableStr)
--print char (13)+@tableStr+char(13)

--print char(13)+ @insertStr1 + char(13)
DECLARE @newP int = 1
while (@newP<100)
BEGIN
SET @insertStr = @insertStr1 + @newName + @insertStr2 + cast(@newP as varchar(max) )+ @insertStr3
EXEC (@insertStr)
--print(@insertStr)
SET @newP = @newP +1
END
END

Administering Microsoft SQL Server

Administering Microsoft SQL Server