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
No hay comentarios:
Publicar un comentario