Database last used

  • 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?

    • This topic was modified 4 years, 9 months ago by  Admingod.
  • 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

    • This reply was modified 4 years, 9 months ago by  Lowell.
    • This reply was modified 4 years, 9 months ago by  Lowell.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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