optimisng database maintenance plans

Fisrt step will be to identify the database files read,writes,i/o stats. For that we may utilise the below metioned queries.

1. To list NumberReads,NumberWrites,IOStallMS and Recovery_Model use the below mentioned script.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


CREATE TABLE #tbl_DB_FileStats
(
DatabaseName varchar(255),
FileName varchar(255),
NumberReads numeric(18,0),
NumberWrites numeric(18,0),
IOStallMS numeric(18,0)
)



EXECUTE master.dbo.sp_msforeachdb '
DECLARE @DBID int;
USE [?];
SET @DBID = DB_ID();
INSERT INTO #tbl_DB_FileStats
SELECT
DB_NAME(DBID) AS DatabaseName,
FILE_NAME(FileID) AS FileName,
NumberReads,
NumberWrites,
IoStallMS
FROM ::fn_virtualfilestats(@DBID, -1);'





create table #data (name sysname , db_size nvarchar(13), owner sysname, dbid smallint, created datetime, status varchar(600),
compatability_level tinyint)
insert into #data Exec sp_executesql N'sp_helpdb'

select name as 'Database',db_size,owner,substring(status,patindex ('%Recovery%',status)+9,patindex ('%, Ver%',status)- patindex ('%Recovery%',status)-9 ) as Recovery_Model
into #t1
from #data
where name not in ('model','master','tempdb','msdb')

select DatabaseName as 'Database' ,NumberReads,NumberWrites,IOStallMS
into #t2
from #tbl_DB_FileStats
where FileName not like '%log'
and DatabaseName not in ('model','master','tempdb','msdb')




select t1.[Database],t1.db_size,t1.[owner],t2.NumberReads,t2.NumberWrites,t2.IOStallMS,t1.Recovery_Model
from #t1 t1
inner join #t2 t2 on t1.[Database]=t2.[Database]




drop table #tbl_DB_FileStats
drop table #data
drop table #t1
drop table #t2

2. Database Index Fragmentation Information use the below mentioed script

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


CREATE TABLE #tbl_DB_IDX_Stats
(
[Database] sysname,
[Object] sysname,
[Index_ID] int,
[Index_Name] sysname,
avg_fragmentation_in_percent numeric(18,0)
)


EXECUTE master.dbo.sp_msforeachdb '
DECLARE @DBID int;
USE [?];
SET @DBID = DB_ID();
INSERT INTO #tbl_DB_IDX_Stats
SELECT db_name(ps.database_id) as [Database], Object_name(ps.OBJECT_ID) as [Object],
ps.index_id as [Index_ID], b.name as [Index_Name],ps.avg_fragmentation_in_percent as avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@DBID, NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
and avg_fragmentation_in_percent>70
and name is not null
and @DBID not in (1,2,3,4)
ORDER BY ps.OBJECT_ID;'

select * from #tbl_DB_IDX_Stats

drop table #tbl_DB_IDX_Stats


Tushar

Identify the Recovery Model

Hi All,
In this post I will like to share a script which will generate a report about the detail of the databases on the server. The script can be modified to show more information as per requirement. I have used sp_helpdb to get all the information.

Script:
create table #data (name sysname , db_size nvarchar(13), owner sysname, dbid smallint, created datetime, status varchar(600),
compatability_level tinyint)
insert into #data Exec sp_executesql N'sp_helpdb'
select name as 'Database',db_size,owner,substring(status,patindex ('%Recovery%',status)+9,patindex ('%, Ver%',status)- patindex ('%Recovery%',status)-9 ) as Recovery_Model
from #data
where name not in ('model','master','tempdb','msdb')
drop table #data


Hope this helps a lot of accidental DBA's to perform thier task of reporting.

Tushar

SQL Server Backup and Restore History

Hi All.. This is one place I know most you must have knowledge about. Just mentioning this so that you all have one stop for the information.

To identify the backup history for the databases on the server.

SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name, b.type
ORDER BY a.name, b.type

To identify the restore history of the databases on the server.
select restore_history_id,restore_date,destination_database_name,
user_name,restore_type,backup_set_id from msdb..restorehistory


Tushar