Situatie
Mai jos aveti o procedura ce se pune pe baza de date master si cand este rulata returneaza Recovery Modelul bazelor de date de pe server precum si memoria folosita de fiecare baza.
Solutie
create procedure BD_RecoveryModelAndMemoryUsed_prc
as
SELECT
name AS DatabaseName,
CONVERT(varchar(10),create_date,111) as CreateDate,
recovery_model_desc AS RecoveryModel
into #RECOV_MODEL
FROM sys.databases
SELECT
DB_NAME(database_id) AS DatabaseName,
COUNT (1) * 8 / 1024 AS MBUsed
into #MEM
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
select
A.DatabaseName,
A.CreateDate,
A.RecoveryModel,
B.MBUsed
from #RECOV_MODEL A
LEFT join #MEM B ON A.DatabaseName = B.DatabaseName
order by 1
Leave A Comment?