June 7, 2019 at 8:08 pm
Is there a way to find without having access to database when the database was last used? My understanding is Without having SQL access it won’t be possible to get when the database was last used or updated right?
June 7, 2019 at 8:39 pm
without access to SQL, no you cannot find out.
if you have access to view server state, the DMV's for index stats has the last time an index was used to read or write since the last time the SQL Service was restarted.
i use this query to find last read /last write,and also how big the database is, on the off chance that a db has not been used in a long time, and i could potentially drop it.
SET NOCOUNT ON
DECLARE @CurrentDB NVARCHAR(128)
-- The table #tblServerDatabases holds the names of databases on the server.
-- This table is used here to allow us to loop through each database,
-- rather than to run undocumented procedures, such as sp_msforeachdb
-- (which eliminated the need for a loop).
IF OBJECT_ID('tempdb..#tblServerDatabases', 'U') IS NOT NULL
DROP TABLE #tblServerDatabases
CREATE TABLE #tblServerDatabases (DBName NVARCHAR(128),state_desc NVARCHAR(128))
-- The table #tblDBFilesExtendedInfo holds the data and log files info
-- (name, size, used, free spaces, etc.).
IF OBJECT_ID('tempdb..#tblDBFilesExtendedInfo', 'U') IS NOT NULL
DROP TABLE #tblDBFilesExtendedInfo
CREATE TABLE #tblDBFilesExtendedInfo (
Idx INT IDENTITY(1, 1),
FileID INT,
FileGroupID INT,
TotalExtents BIGINT,
UsedExtents BIGINT,
DBFileName NVARCHAR(128),
LogicalFileName NVARCHAR(128),
DBFilePath NVARCHAR(1024),
DBFileType VARCHAR(16),
DBName NVARCHAR(128),
[TotalFileSize(MB)] MONEY,
[TotalUsed(MB)] MONEY,
[TotalFree(MB)] MONEY,
[SpaceUsed(%)] MONEY,
Status INT)
-- This table will hold the output of sp_helpfile, for each database.
-- This is needed in order to get the log file path and file name.
IF OBJECT_ID('tempdb..#tblDBFilesBasicInfo', 'U') IS NOT NULL
DROP TABLE #tblDBFilesBasicInfo
CREATE TABLE #tblDBFilesBasicInfo (
DBName NVARCHAR(128),
DBFileName NVARCHAR(128),
FileID INT,
FilePath NVARCHAR(1024),
FileGroupDesc NVARCHAR(128),
FileSizeKB NVARCHAR(64),
MaxSizeDesc NVARCHAR(64),
Growth NVARCHAR(64),
Usage NVARCHAR(64))
-- First - the data files are handled throught the
-- DBCC SHOWFILESTATS command.
INSERT INTO #tblServerDatabases (DBName,state_desc)
SELECT dbz.[name],state_desc
FROM master.sys.databases dbz
SELECT @CurrentDB = MIN(DBName)
FROM #tblServerDatabases
WHERE state_desc='ONLINE'
WHILE @CurrentDB IS NOT NULL
BEGIN
INSERT INTO #tblDBFilesExtendedInfo(
FileID,
FileGroupID,
TotalExtents,
UsedExtents,
DBFileName,
DBFilePath)
EXEC ('USE [' + @CurrentDB + '] DBCC SHOWFILESTATS')
UPDATE #tblDBFilesExtendedInfo
SET DBName = @CurrentDB,
DBFileType = 'Data File'
WHERE DBName IS NULL
-- Run the sp_helpfile in order to get log file data.
INSERT INTO #tblDBFilesBasicInfo(
DBFileName,
FileID,
FilePath,
FileGroupDesc,
FileSizeKB,
MaxSizeDesc,
Growth,
Usage)
EXEC ('USE [' + @CurrentDB + '] EXEC sp_helpfile ')
UPDATE #tblDBFilesBasicInfo
SET DBName = @CurrentDB
WHERE DBName IS NULL
SELECT @CurrentDB = MIN(DBName)
FROM #tblServerDatabases WITH (NOLOCK)
WHERE DBName > @CurrentDB
AND state_desc='ONLINE'
END
-- Update the total file size, used and free space, based on the
-- extents information returned from DBCC SHOWFILESTATS.
UPDATE #tblDBFilesExtendedInfo
SET [DBFileName] = RIGHT(DBFilePath, CHARINDEX('\', REVERSE(DBFilePath)) -1),
[TotalFileSize(MB)] = CAST(((TotalExtents*64) / 1024.00) AS MONEY),
[TotalUsed(MB)] = CAST(((UsedExtents*64) / 1024.00) AS MONEY),
[TotalFree(MB)] = CAST(((TotalExtents*64) / 1024.00) AS MONEY)
- CAST(((UsedExtents*64) / 1024.00) AS MONEY),
[SpaceUsed(%)] = CASE
WHEN CAST(((TotalExtents*64) / 1024.00) AS MONEY) = 0.0 THEN 0.0
ELSE (CAST(((UsedExtents*64) / 1024.00) AS MONEY)*100)
/ CAST(((TotalExtents*64) / 1024.00) AS MONEY)
END
-- We are now done with the data file statuses, and we shall move
-- on to get the log files info, by using DBCC SQLPERF(LOGSPACE)
INSERT INTO #tblDBFilesExtendedInfo (DBName, [TotalFileSize(MB)], [SpaceUsed(%)], Status)
EXEC('DBCC SQLPERF(LOGSPACE)')
UPDATE a
SET [TotalUsed(MB)] = (a.[SpaceUsed(%)]/100.00)*a.[TotalFileSize(MB)],
[TotalFree(MB)] = (1.0 - (a.[SpaceUsed(%)]/100.00))*a.[TotalFileSize(MB)],
DBFileType = 'Log file',
DBFilePath = b.FilePath,
DBFileName = RIGHT(b.FilePath, CHARINDEX('\', REVERSE(b.FilePath)) -1)
FROM #tblDBFilesExtendedInfo a
INNER JOIN #tblDBFilesBasicInfo b
ON a.DBName = b.DBName
WHERE a.DBFileType IS NULL
AND b.Usage = 'log only'
--we want the logical file name as well, in case we need it for other purposes, like getting everything in a single spot to move files
UPDATE MyTarget
SET MyTarget.LogicalFileName = mff.name
FROM #tblDBFilesExtendedInfo MyTarget
INNER JOIN sys.master_files mff
ON MyTarget.DBFilePath = mff.physical_name
SET NOCOUNT OFF
;WITH AllDatabases
AS
(
SELECT [t].[database_id],[t].[name] AS TheDatabase,[t].[recovery_model_desc],[t].[user_access_desc],[t].[state_desc] FROM [master].[sys].[databases] t WHERE [t].[name] NOT IN('master','tempdb','model','msdb')
),
myCTE
AS
(
SELECT
[AllDatabases].[TheDatabase],
[AllDatabases].[recovery_model_desc],
[AllDatabases].[user_access_desc],
[AllDatabases].[state_desc],
[statz].[last_user_seek],
[statz].[last_user_scan],
[statz].[last_user_lookup],
[statz].[last_user_update]
FROM AllDatabases
LEFT OUTER JOIN [sys].[dm_db_index_usage_stats] statz
ON [AllDatabases].[database_id] = [statz].[database_id]
),NearlyDone
AS
(
SELECT
ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name='tempdb'),
[x].[TheDatabase],
MAX([x].[recovery_model_desc]) AS recovery_model_desc,
MAX([x].[user_access_desc]) AS user_access_desc,
MAX([x].[state_desc]) AS state_desc,
MAX([x].[last_read]) AS last_read,
MAX([x].[last_write]) AS last_write
FROM
(
SELECT [myCTE].[TheDatabase],[myCTE].[recovery_model_desc],[myCTE].[user_access_desc],[myCTE].[state_desc],[myCTE].[last_user_seek] AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT [myCTE].[TheDatabase],NULL,NULL,NULL,[myCTE].[last_user_scan], NULL FROM myCTE
UNION ALL
SELECT [myCTE].[TheDatabase],NULL,NULL,NULL,[myCTE].[last_user_lookup], NULL FROM myCTE
UNION ALL
SELECT [myCTE].[TheDatabase],NULL,NULL,NULL,NULL, [myCTE].[last_user_update] FROM myCTE
) AS x
GROUP BY TheDatabase
)
SELECT NearlyDone.ServerRestartedDate,
CONVERT(VARCHAR(128),@@servername) AS ServerName,
FileSizes.DBName,
[NearlyDone].[recovery_model_desc],
[NearlyDone].[user_access_desc],
[NearlyDone].[state_desc],
NearlyDone.[last_read],
NearlyDone.[last_write],
FileSizes.[TotalFileSize(MB)] ,
CASE WHEN NearlyDone.[last_read] IS NULL AND NearlyDone.[last_write] IS NULL THEN FileSizes.[TotalFileSize(MB)] ELSE 0 END AS [PotentialSavings(MB)]
FROM (
SELECT DBName,CONVERT(DECIMAL(12,2),SUM([TotalFileSize(MB)])) AS [TotalFileSize(MB)] FROM #tblDBFilesExtendedInfo GROUP BY DBName
UNION ALL
SELECT DBname, NULL FROM #tblServerDatabases WHERE DBname NOT IN(SELECT DbName FROM #tblDBFilesExtendedInfo)
)FileSizes
LEFT JOIN NearlyDone
ON NearlyDone.TheDatabase = FileSizes.[DBName]
WHERE dbname NOT IN('master','model','tempdb','msdb')
ORDER BY DBName
Lowell
June 10, 2019 at 1:07 pm
Defining "used" may lead you to different ways of checking this. I would first get this definition so that you can understand what you then need to do to ensure you can validate use through querying the DMVs, as was already suggested, setting up some Extended Events to capture more detailed and specific "database use" depending on what we've defined, looking at the system_health session for general info, or some other type of auditing specific to your definition of "used".
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply