Month: July 2023

Find memory in use by sql databases

July 10, 2023 / / 0 Comments

Run this query in SSMS to show how much memory the DB’s are currently using.

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters 
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';

;WITH src AS
(
SELECT 
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767 
THEN 'Resource DB' 
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3), 
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

Search group policies for specific setting

July 10, 2023 / / 0 Comments

Sometimes you need to search in a domain environment for a specific setting in GPO’s. This script will search through all the GPO’s in the domain and list the results at the end!

# Get the string you want to search for
$string = Read-Host -Prompt "What string do you want to search for?"

# Set the domain to search for GPOs
$DomainName = $env:USERDNSDOMAIN

# Find all GPO's in the current domain of logged on user
write-host "Finding all the GPO's in $DomainName"
Import-Module grouppolicy
$allGposInDomain = Get-GPO -All -Domain $DomainName
[string[]] $MatchedGPOList = @()

# Look through each GPO's XML for the string
Write-Host "Starting search...."
foreach ($gpo in $allGposInDomain) {
    $report = Get-GPOReport -Guid $gpo.Id -ReportType Xml
    if ($report -match $string) {
        write-host "********** Match found in: $($gpo.DisplayName) **********" -foregroundcolor "Green"
        $MatchedGPOList += "$($gpo.DisplayName)";
    } # end if
    else {
        Write-Host "No match in: $($gpo.DisplayName)"
    } # end else
} # end foreach
write-host "`r`n"
write-host "Results: **************" -foregroundcolor "Yellow"
foreach ($match in $MatchedGPOList) {
    write-host "Match found in: $($match)" -foregroundcolor "Green"
}