Monthly Archives: April 2022

Generate script – enable query store for each database

Use the print query result for a pretty SQL script

SET NOCOUNT ON
select 
CONCAT('ALTER DATABASE [',name,'] SET QUERY_STORE = ON',char(10),'GO',char(10),
'ALTER DATABASE [',name,'] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)',char(10),'GO',char(10))
from sys.databases
where is_query_store_on = 0
and database_id > 4

Capture last login datetime per database 

-- create table to store the results
drop table if exists dbo.DatabaseUsages
create table dbo.DatabaseUsages
(
    DbName nvarchar(256) not null,
    LoginName nvarchar(256) not null,
    LastLoginTime datetime not null
)

-- capture the data
MERGE dbo.DatabaseUsages AS tgt
USING 
(
    SELECT 
      db_name(database_id) as [DbName]
    , original_login_name as [LoginName] 
    , MAX(login_time) AS [LastLoginTime]
    FROM sys.dm_exec_sessions as src
    where db_name(database_id) is not null
    GROUP BY db_name(database_id), original_login_name, original_login_name
) as src ON (tgt.DbName = src.DbName AND tgt.LoginName = src.LoginName)
WHEN MATCHED THEN 
    UPDATE SET tgt.LastLoginTime = src.LastLoginTime

WHEN NOT MATCHED THEN
    INSERT VALUES (DbName, LoginName, LastLoginTime);
GO