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
