SqlAst: noviembre 2007

Produce un informe sobre:

- cuándo fue reiniciado el servidor
-espacio en discos
-bb en servidor
-backup
-espacio para cada bbdd
- evolución del tamaño de backups (datos y log si aplicable)
- restores

También avisa si alguna bbdd está cercana a su limite de tamaño (tanto para datos como para lo) si así está configurado. Además informa si el espacio no usado en la bbdd es el 80% del total.
Utiliza sp_OAs, (opcioón no permitida por defecto en el 2005)

EL script es contiene partes que encontré en otras páginas junto con cosecha propia.

Como siempre, probarlo antes de usarlo y modificarlo, distribuirlo como queráis

SET NOCOUNT ON

/*declare variables*/

DECLARE @MB dec(28)-- should be bigint, but it wouldn't work on sql 7. arithmetic overflow might happen (on 2K)
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @low nvarchar(11)
DECLARE @name sysname
DECLARE @exec_stmt nvarchar(625)
SET @MB = 1048576

/*****************************
create temp tables
*****************************/

CREATE TABLE #drives (
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL
)

CREATE TABLE #dbsize
(
[dbname] sysname,
dbid smallint null,
dbsize nvarchar(13)null,
owner nvarchar(60)null ,
created datetime

)

CREATE TABLE #helpfile (

[DbName] varchar(140) NULL,
FileLogicalName varchar(400) NULL,
FileID int NULL,
FileGroupID int NULL,
FilePath varchar(400) NULL,
FileGroupName varchar(140) NULL,
FileTotalSizeKB varchar(20) NULL,
FileMaxSizeSetting varchar(20) NULL,
FileGrowthSetting varchar(20) NULL,
FileUsage varchar(20) NULL,
FileTotalSizeMB dec(19,4) NULL,
FileUsedSpaceMB dec(19,4) NULL,
FileFreeSpaceMB dec(19,4) NULL,
)

CREATE TABLE #filestats (
[DbName] varchar(140) NULL,
FileID int NULL,
FileGroupID int NULL,
FileTotalSizeMB dec(19,4) NULL,
FileUsedSpaceMB dec(19,4) NULL,
FileFreeSpaceMB dec(19,4) NULL,
FileLogicalName varchar(400) NULL,
FilePath varchar(400) NULL
)

CREATE TABLE #sqlperf (
[DbName] varchar(140) NULL,
LogFileSizeMB dec(19,4) NULL,
LogFileSpaceUsedpct dec(19,4) NULL,
Status int NULL
)

--get #drives using OA's

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur
FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

-- get #sqlperf

INSERT #sqlperf ([DbName], LogFileSizeMB, LogFileSpaceUsedpct, Status) EXEC ( 'DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS ')

--get #helpfile and #filestats

EXEC sp_MSForeachDB
@command1 = 'Use [?];Insert #helpfile (FileLogicalName, FileID, FilePath, FileGroupName, FileTotalSizeKB, FileMaxSizeSetting, FileGrowthSetting,FileUsage) Exec sp_helpfile; update #helpfile set dbname = ''?'' where dbname is null',
@command2 = 'Use [?];Insert #filestats (FileID, FileGroupID, FileTotalSizeMB, FileUsedSpaceMB, FileLogicalName, FilePath) exec (''DBCC SHOWFILESTATS WITH NO_INFOMSGS ''); update #filestats set dbname = ''?'' where dbname is null'

UPDATE #filestats SET FileTotalSizeMB = Round(FileTotalSizeMB*64/1024,2), FileUsedSpaceMB = Round(FileUsedSpaceMB*64/1024,2)
WHERE FileFreeSpaceMB IS null

UPDATE #filestats SET FileFreeSpaceMB = FileTotalSizeMB - FileUsedSpaceMB
WHERE FileFreeSpaceMB IS null

UPDATE #helpfile SET FileGroupID = 0 WHERE FileUsage = 'log only'

UPDATE #helpfile SET FileGroupID = b.FileGroupID, FileTotalSizeMB = b.FileTotalSizeMB, FileUsedSpaceMB = b.FileUsedSpaceMB, FileFreeSpaceMB = b.FileFreeSpaceMB
FROM #helpfile a, #filestats b

WHERE a.FilePath = b.FilePath and a.FileUsage = 'data only'

UPDATE #helpfile SET FileTotalSizeMB = Round(Cast(replace(FileTotalSizeKB,' KB', '')as dec(19,4))/1024,2)
WHERE FileTotalSizeMB is NULL

UPDATE #helpfile SET FileUsedSpaceMB = Round(FileTotalSizeMB * b.LogFileSpaceUsedpct * 0.01, 2), FileFreeSpaceMB = Round(FileTotalSizeMB * (100 - b.LogFileSpaceUsedpct) * 0.01, 2)
FROM #helpfile a, #sqlperf b
WHERE a.dbname = b.dbname and a.FileUsage = 'log only'

UPDATE #helpfile SET FilePath = STUFF ( FilePath , 1 , 1 , Upper(Left(FilePath,1)) ) Where Unicode(Left(FilePath,1)) between 97 and 122

-- get #dbsize

INSERT INTO #dbsize ([dbname], dbid,dbsize, owner, created)
SELECT [name], dbid, null, convert(nvarchar(60),suser_sname(sid)), crdate
FROM master.dbo.sysdatabases WHERE status & 32 != 32
and status & 256 != 256 and status & 512 != 512
and status & 1024 != 1024 and status & 4096 != 4096
and status & 32768 !=32768 and status & 1073741824 !=1073741824

SELECT @low = CONVERT(varchar(11),low) FROM master.dbo.spt_values
WHERE type = N'E' and number = 1

DECLARE ms_crs_c1 CURSOR FOR
SELECT [dbname] FROM #dbsize
OPEN ms_crs_c1
FETCH ms_crs_c1 INTO @name
WHILE @@fetch_status >= 0
BEGIN

/* Insert row for each database */
SELECT @exec_stmt = 'update #dbsize
SET dbsize = (SELECT STR(CONVERT(DEC(15),SUM(SIZE))* ' + @low + '/ 1048576,10,2)+ N'' MB'' FROM '
+ quotename(@name, N'[') + N'.dbo.sysfiles) WHERE current of ms_crs_c1'

EXECUTE (@exec_stmt)

FETCH ms_crs_c1 INTO @name
END
CLOSE ms_crs_c1
DEALLOCATE ms_crs_c1

--Create Report

PRINT '*************************************************'
PRINT 'Server Name: ' +convert(char(24), @@SERVERNAME)
PRINT 'Session User: ' + convert(char(30),SESSION_USER)
PRINT 'Workstation: ' + convert(char(30),host_name())
PRINT CONVERT(VARCHAR(32), getdate())
PRINT '**************************************************'
PRINT ' '
PRINT ' ================== '
PRINT '************ DBCoE SPACE REPORT ******************'
PRINT ' ================== '
PRINT ' '
PRINT ' '
DECLARE @serverup varchar(20)
SET @serverup = (SELECT convert(varchar(20),crdate) from master..sysdatabases where [name] = 'tempdb')
PRINT 'SERVER UP SINCE: ' + @serverup
PRINT ' '
PRINT ' '
PRINT 'DRIVES REPORT.-'
PRINT '---------------'
PRINT ' '
SELECT Drive, TotalSize AS 'Total(MB)',

FreeSpace AS 'Free(MB)',

CAST((FreeSpace/(TotalSize*1.0))*100.0 AS dec(10,2)) AS 'Free(%)'

FROM #drives

ORDER BY drive

PRINT ' '
PRINT ' '
PRINT 'DATABASE REPORT.-'
PRINT '-----------------'
PRINT ' '
IF (select count([name]) from master..sysdatabases where crdate > getdate()-8 and [name] <>'tempdb')=0

print 'No dbs added in the last seven days'

ELSE

BEGIN

print 'New dbs added in the last seven days:'

SELECT convert(varchar(36),[name]) AS 'DBName', crdate AS 'CreatedDate'

FROM master..sysdatabases

WHERE crdate > getdate()-8 AND [name] <> 'tempdb'

END

print ' '

select convert(varchar(36),a.name) as 'DBName',b.dbsize, a.dbid, suser_sname(a.sid)as 'Owner', a.crdate from master..sysdatabases a

JOIN #dbsize b

ON a.[name]= b.[dbname]

ORDER BY a.crdate
PRINT ' '
PRINT ' '
PRINT 'BACKUP REPORT.-'
PRINT '---------------'
PRINT ' '
SELECT CONVERT(char(25),a.[name]) AS 'DBName', --lists all the db in the server

ISNULL(CONVERT(VARCHAR(62),c.physical_device_name),'WARNING: NO BACKUP!!!!') AS 'Bakup Path',

Type= case type --backups type:
WHEN 'D' then 'Db'
WHEN 'I' then 'Diff'
WHEN 'L' then 'Log'
WHEN 'F' then 'File'
ELSE ' '
END,

ISNULL(CONVERT(CHAR(12),BACKUP_FINISH_DATE),' ') AS 'Last Success Bak Run',

ISNULL(RTRIM(CONVERT (CHAR(5),DATEDIFF(SECOND, BACKUP_START_DATE,BACKUP_FINISH_DATE)/60))+' m '+

RTRIM(CONVERT (CHAR(3),DATEDIFF(SECOND, BACKUP_START_DATE,BACKUP_FINISH_DATE)%60))+' s',' ')AS 'Duration'

FROM master..sysdatabases a

LEFT OUTER JOIN -- left join in order to show db´s without bak as well

(
SELECT DISTINCT MAX(media_set_id)as media_set_id,database_name,type,

MAX(backup_start_date)AS 'BACKUP_START_DATE', --get only the latest for each bak

MAX(backup_finish_date)AS 'BACKUP_FINISH_DATE'

FROM msdb..backupset

WHERE DATEDIFF(DAY,backup_finish_date,getdate())<8
--since there are bak that only run weekly

GROUP BY DATABASE_NAME,USER_NAME,type

) B

ON A.NAME= B.DATABASE_NAME

LEFT OUTER JOIN msdb..backupmediafamily c ON c.media_set_id = b.media_set_id

ORDER BY A.CRDATE

PRINT ' '
PRINT ' '
PRINT 'DATABASE SPACE REPORT.-'
PRINT '-----------------------'
PRINT ' '
print 'Review the following files (too much unused space):'
print ' '
SELECT convert(varchar(36),FileLogicalName) AS 'File Logical Name',

str(convert(numeric (15,2),FileTotalSizeMB),10,2) AS 'File Size(Mb)',

str(convert(numeric(15,2),(FileFreeSpaceMb*100/FiletotalSizeMb)),6,2)+'%'AS 'File Free Space (%)'

FROM #helpfile b

WHERE convert(numeric(15,2),filetotalsizemb) > 3000 -- will show only > 3Gb

AND CONVERT(numeric(15,2),(filefreeSpaceMb*100/FiletotalSizeMb))> 90 -- % free space on file

print ''
print ''

-- verify if growth settings are near the limit

IF (SELECT COUNT (a.name) from ( SELECT CONVERT(varchar(36),FileLogicalName) AS 'Name',

'SizeUsed' = case

WHEN b.FileMaxSizeSetting = 'Unlimited' THEN 0

ELSE convert(dec (4,4),b.FileTotalSizeMB*100/

cast(replace(b.fileMaxSizeSetting,' kb','') AS dec(19,2))/1024)
END

FROM #helpfile b) a

WHERE sizeused > 80) = 0

print ' ' -- no files getting their limits
ELSE

BEGIN
print 'Warning!!! Please, check the growth settings of the following dbs:'
print ' '
SELECT a.Name, a.SizeUsed from
(
SELECT convert(varchar(36),FileLogicalName) AS 'Name','SizeUsed' = case

WHEN b.FileMaxSizeSetting = 'Unlimited' THEN 0
--when b.FileMaxSizeSetting = '2147483648 KB ' THEN 0

ELSE
convert (dec (16,2),(convert(numeric(16,2), b.filetotalsizemb)*100)
/ (convert(numeric (16,2),replace(b.filemaxsizesetting,'kb',' '))/1024))

END
FROM #helpfile b) a

where a.sizeused > 80
END

print ' '
print ' '

SELECT convert(varchar(36),FileLogicalName) AS 'File Logical Name',
convert(varchar(62),FilePath) as 'File Path',
str(convert(numeric(12,2),(convert(dec(10,2),(cast(replace(b.filetotalsizekb,' kb','')as dec(19,2))/1024)*100) /a.totalsize)),6,2)+'%' as 'Drive Ocupied(%)',
str(convert(numeric (15,2),FileTotalSizeMB),10,2) AS 'File Size(Mb)',
str(convert(numeric(15,2),FileUsedSpaceMB),10,2) As 'File Used (Mb)',
str(convert(numeric(15,2),FileFreeSpaceMB),10,2) AS 'File Free Space (Mb)',
str(convert(numeric(15,2),(FileFreeSpaceMb*100/FiletotalSizeMb)),6,2)+'%'AS 'File Free Space (%)',
FileMaxSizeSetting,
'Size Setting Used' = case

WHEN b.FileMaxSizeSetting = 'Unlimited' THEN 'Unlimited'
ELSE convert (varchar (9),convert(dec (4,4),b.FileTotalSizeMB*100/
cast(replace(b.fileMaxSizeSetting,' kb','') AS dec(19,2))/1024)) + '%'
END,
FileGrowthSetting as 'File Growth Setting'
FROM
#drives a, #helpfile b
WHERE a.drive= substring(b.filepath,1,1)
ORDER BY Dbname, FileID

PRINT ' '
PRINT ' '
PRINT 'DATABASE BACKUP SIZE PROGRESS.-'
PRINT '------------------------------- '
PRINT ' '

SELECT CONVERT(char(30),a.name) AS 'DATABASES IN SERVER', --lists all the db in the server

CONVERT(char(15),dbsize) as 'DB SIZE',

isnull((str(CONVERT(numeric(10,2),backup_size/1048576),10,2) + 'Mb'),space(7)+'n/a') AS 'BAK SIZE(MB)', --outputs size in Mb´s

isnull((sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEweek)/BACKUP_SIZE*100),10,2) +'%'),space(7)+'n/a')AS '%Last Week',

isnull((sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEMonth)/BACKUP_SIZE*100),10,2) +'%'),space(7)+'n/a')AS '%Last Month',

isnull((sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEqrtr)/BACKUP_SIZE*100),10,2) +'%'),space(7)+'n/a')AS '%Last Qrtr'

FROM master..sysdatabases a

JOIN #dbsize on a.name =#dbsize.dbname

LEFT OUTER JOIN -- to eliminate db´s without backup

--current size
(
SELECT DISTINCT database_name,

MAX(backup_size)AS 'BACKUP_SIZE'

FROM msdb..backupset

WHERE datediff(day,backup_finish_date,getdate())<8 --since there are bak that only run weekly

and TYPE <> 'l'

GROUP BY DATABASE_NAME

) B

ON A.NAME= B.DATABASE_NAME

LEFT OUTER JOIN
(

-- one week ago

SELECT DISTINCT database_name,

MAX(backup_size)AS 'BACKUP_SIZEWeek'

FROM msdb..backupset

WHERE datediff(day,backup_finish_date,getdate())>7 --since there are bak that only run weekly

and datediff(day,backup_finish_date,getdate())<15

and TYPE <> 'l'

GROUP BY DATABASE_NAME

) C

ON A.NAME= C.DATABASE_NAME

LEFT OUTER JOIN
(
--last month

SELECT DISTINCT database_name,

MAX(backup_size)AS 'BACKUP_SIZEMonth'

FROM msdb..backupset

WHERE datediff(month,backup_finish_date,getdate())=1 --since there are bak that only run weekly

and TYPE <> 'l'

GROUP BY DATABASE_NAME

) D

ON A.NAME= D.DATABASE_NAME

LEFT OUTER JOIN
(
--last quarter

SELECT DISTINCT database_name,

MAX(backup_size)AS 'BACKUP_SIZEQrtr'

FROM msdb..backupset

WHERE datediff(quarter,backup_finish_date,getdate())=1 --since there are bak that only run weekly

and TYPE <> 'l'

GROUP BY DATABASE_NAME

) E

ON A.NAME= E.DATABASE_NAME

ORDER BY CREATED
PRINT ' '
PRINT ' '
PRINT 'LOG BACKUP SIZE PROGRESS.-'
PRINT '--------------------------'
PRINT ' '

SELECT CONVERT(char(30),a.name) AS 'DATABASES IN SERVER', --lists all the db in the server

CONVERT(char(15),dbsize) AS 'DB SIZE',

str(CONVERT(numeric(10,2),backup_size/1048576),10,2) + 'Mb' AS 'LOG SIZE(MB)', --outputs size in Mb´s

sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEweek)/BACKUP_SIZE*100),10,2) +'%'AS '%Last Week',

sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEMonth)/BACKUP_SIZE*100),10,2) +'%'AS '%Last Month',

sTR(CONVERT(numeric(10,2),(BACKUP_SIZE-BACKUP_SIZEqrtr)/BACKUP_SIZE*100),10,2) +'%'AS '%Last Qrtr'

FROM master..sysdatabases a

JOIN #dbsize on a.name = #dbsize.dbname

JOIN -- to eliminate db´s without backup

(
SELECT DISTINCT database_name,

MAX(backup_size)AS 'BACKUP_SIZE'

FROM msdb..backupset

WHERE datediff(day,backup_finish_date,getdate())<8 --since there are bak that only run weekly

and datediff(year,backup_finish_date, getdate())=0

and TYPE = 'l'

GROUP BY DATABASE_NAME

) B

ON A.NAME= B.DATABASE_NAME

LEFT OUTER JOIN
(

-- one week ago

SELECT DISTINCT database_name,

MAX(backup_size)AS 'BACKUP_SIZEWeek'

FROM msdb..backupset

WHERE datediff(day,backup_finish_date,getdate())>7 --since there are bak that only run weekly

and datediff(day,backup_finish_date,getdate())<15

and TYPE = 'l'

GROUP BY DATABASE_NAME

) C

ON A.NAME= C.DATABASE_NAME

LEFT OUTER JOIN
(
--last month

SELECT DISTINCT database_name,

MAX(backup_size)AS 'BACKUP_SIZEMonth'

FROM msdb..backupset

WHERE datediff(month,backup_finish_date,getdate())=1 --since there are bak that only run weekly

and TYPE = 'l'

GROUP BY DATABASE_NAME

) D

ON A.NAME= D.DATABASE_NAME

LEFT OUTER JOIN
(
--last quarter

SELECT DISTINCT database_name,

MAX(backup_size)AS 'BACKUP_SIZEQrtr'

FROM msdb..backupset

WHERE datediff(quarter,backup_finish_date,getdate())=1 --since there are bak that only run weekly

and TYPE = 'l'

GROUP BY DATABASE_NAME

) E

ON A.NAME= E.DATABASE_NAME

ORDER BY CREATED

print ' '
print ' '
print ' RESTORE HISTORY REPORT.-'
PRINT '-------------------------'
print ' '
print ' '
Select convert(varchar(26),a.name) as 'DbName', isnull(convert(varchar(32),x.user_name),' --')as 'Restored by',

isnull(x.[date],'00:00:00') as 'Date',

isnull(x.restore_type,'-- ') as 'Restore Type'

from master..sysdatabases a left outer join

( select b.destination_database_name, b.user_name,c.date ,b.restore_type

from MSDB..restorehistory b

join

(select destination_database_name, max(restore_date)as 'Date'

from msdb..restorehistory group by destination_database_name) c on

c.date = b.restore_date

)x on a.name = x.destination_database_name

order by x.date desc, a.name

PRINT '***********************************END OF REPORT**********************************'
print ' '
print ' '

DROP TABLE #HELPFILE
DROP TABLE #FILESTATS
DROP TABLE #SQLPERF
DROP TABLE #DRIVES
DROP TABLE #DBSIZE

SET NOCOUNT OFF

SqlAst

viernes, 30 de noviembre de 2007

Herramientas gratuitas

miércoles, 28 de noviembre de 2007

SQL Log Rescue gratis!!!

Monitorizar espacio backups y más

jueves, 22 de noviembre de 2007

Qué monitorizar I

Entradas populares

Etiquetas