Monday, October 3, 2011

Información util acerca de las bases de datos

Este es mi script para ver informacion acerca de todas las bases de datos disponibles en la instancia del SQL Server
- Bases de datos
- id
- Nombre
- Tamaño
- Disco en el que esta alojado el archivo de datos y log
- tamaños de los archivos de datos y log
- Espacio libre en disco
- Porcentaje de utilización del archivo
- Valores de Auto growth
- Recovery Model
- Compativility Level
- Ultimo backup (Full y Transacion Log)
- Ultimo restore

Archivo Principal

-- MAIN SCRIPT

SET NOCOUNT ON

IF (object_id( 'tempdb..#TMPFIXEDDRIVES' ) IS NOT NULL) DROP TABLE #TMPFIXEDDRIVES

IF (object_id( 'tempdb..#TMPSPACEUSED' ) IS NOT NULL) DROP TABLE #TMPSPACEUSED

IF (object_id( 'tempdb..#HDB' ) IS NOT NULL) DROP TABLE #HDB

CREATE TABLE #TMPFIXEDDRIVES (

DRIVE CHAR(1),

MBFREE INT)

INSERT INTO #TMPFIXEDDRIVES

EXEC xp_FIXEDDRIVES

CREATE TABLE #TMPSPACEUSED (

DBNAME VARCHAR(255),

FILEID INT,

FILENME VARCHAR(255),

SPACEUSED FLOAT)



--// SP_HELPDB

SET NOCOUNT ON

CREATE TABLE #HDB (

name sysname not null,

db_size varchar(25) not null,

owner varchar(40) not null,

dbid int not null,

created smalldatetime not null,

status varchar(500) not null,

compatibility_level int not null



)

INSERT INTO #HDB exec sp_helpdb;

--select * from #HDB

INSERT INTO #TMPSPACEUSED

EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName,fileid, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

SELECT @@servername as servername,

A.Database_id as DBid,

A.NAME AS DATABASENAME

,CASE D.FILEID

WHEN 1 THEN ltrim(XX.db_size)

ELSE NULL

END as db_size

,CASE D.FILEID

WHEN 1 THEN XX.owner

ELSE NULL

END as owner

,CASE D.FILEID

WHEN 1 THEN XX.created

ELSE NULL

END as created

,C.DRIVE,

C.MBFREE AS DISKFREE_MB,

D.FILEID as FileId,

B.NAME AS FILENAME,

CASE B.TYPE

WHEN 0 THEN 'DATA'

ELSE TYPE_DESC

END AS FILETYPE,

(B.SIZE * 8 / 1024)AS FILESIZE_MB,

ROUND((B.SIZE * 8 / 1024) - (D.SPACEUSED / 128),2) as SPACEFREE_MB,

ROUND(100-((((B.SIZE * 8 / 1024) - (D.SPACEUSED / 128))*100)/

CASE(B.SIZE * 8 / 1024)

WHEN 0 THEN 1

ELSE (B.SIZE * 8 / 1024)

END

),2) as [%USED],

b.size,

b.max_size,

b.growth,

b.is_percent_growth,

CASE (B.SIZE * 8 / 1024)

WHEN 0 THEN 1 ELSE 0 END as VALID,

B.PHYSICAL_NAME,

CASE B.TYPE

WHEN 0 THEN A.recovery_model_desc

ELSE NULL

END AS REC_MODEL,

CASE B.TYPE

WHEN 0 THEN A.compatibility_level

ELSE NULL

END AS COMP_LEVEL

,CASE D.FILEID

WHEN 1 THEN BR.last_backup_finish_date

ELSE NULL

END as [Backup]

,CASE D.FILEID

WHEN 1 THEN BR.last_TRLog_backup_finish_date

ELSE NULL

END as TRBackup

,CASE D.FILEID

WHEN 1 THEN BR.last_restore_date

ELSE NULL

END as [Restore]

FROM SYS.DATABASES A

JOIN SYS.MASTER_FILES B

ON A.DATABASE_ID = B.DATABASE_ID

JOIN #TMPFIXEDDRIVES C

ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE

JOIN #TMPSPACEUSED D

ON A.NAME = D.DBNAME

AND B.NAME = D.FILENME

INNER JOIN #HDB XX on XX.dbid= A.Database_id

INNER JOIN

(

SELECT D.database_id,B.last_backup_finish_date,TR.last_TRLog_backup_finish_date,R.last_restore_date

FROM sys.databases D

LEFT JOIN (

SELECT

BS.database_name

,max(BS.backup_finish_date) as last_backup_finish_date

FROM

msdb.dbo.backupset BS (NOLOCK)

INNER JOIN

msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id

WHERE

BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)

AND BS.server_name = @@servername and BS.type='D'

GROUP BY

BS.database_name

) B on D.name=B.database_name

LEFT JOIN (

SELECT

BS.database_name

,max(BS.backup_finish_date) as last_TRLog_backup_finish_date

FROM

msdb.dbo.backupset BS (NOLOCK)

INNER JOIN

msdb.dbo.backupmediafamily MF(NOLOCK) ON BS.media_set_id = MF.media_set_id

WHERE

BS.backup_start_date >= CAST(CONVERT(varchar(10),dateadd(mm,-1,getdate()),120) AS datetime)

AND BS.server_name = @@servername and BS.type='L'

GROUP BY

BS.database_name

) TR on D.name=TR.database_name

LEFT JOIN (

SELECT

rh.destination_database_name,

max(rh.restore_date) as last_restore_date

FROM

msdb.dbo.restorehistory rh (NOLOCK)

INNER JOIN msdb.dbo.backupset BS (NOLOCK) ON rh.backup_set_id=BS.backup_set_id

WHERE

BS.type= 'D' AND

RH.restore_date >=CAST(CONVERT(varchar(10),dateadd(mm,-3,getdate()),120) AS datetime)

GROUP BY

rh.destination_database_name

) R on D.name=R.destination_database_name







) BR on A.Database_id=BR.database_id







--WHERE A.NAME <> 'model'

ORDER BY DATABASENAME



DROP TABLE #TMPFIXEDDRIVES

DROP TABLE #TMPSPACEUSED

DROP TABLE #HDB

1 comment:

  1. This is a really great script. Thanks for sharing!
    - Gregg

    ReplyDelete