Database Recovery Model and Memory Usage

Configurare noua (How To)

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

Tip solutie

Permanent

Voteaza

(12 din 20 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?