January 31, 2005 at 6:43 am
How do I find out how much activity a database has had or if it is being used at all. Are there any system tables or DBCC commands that will gather this info?
January 31, 2005 at 8:01 am
Give this a try, it is an addon to the DBHistory process I wrote a while back. It collects IO history(Logical), by DB. You need to schedule it to run regularly, the job is created below as well.
I have a couple of Querys that get the data out by month, etc. PM me if you need further.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IOStats]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IOStats]
GO
CREATE TABLE [dbo].[IOStats] (
[StatTime] [datetime] NOT NULL ,
[ServerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DBName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FileName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NbrReads] [bigint] NOT NULL ,
[NbrWrites] [bigint] NOT NULL ,
[BytesRead] [bigint] NOT NULL ,
[BytesWritten] [bigint] NOT NULL ,
[IOStallMS] [bigint] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_IOStats] ON [dbo].[IOStats]([StatTime]) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP__DBHistory_LOG_IOStats]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USP__DBHistory_LOG_IOStats]
GO
CREATE PROCEDURE USP__DBHistory_LOG_IOStats
AS
SET NOCOUNT ON
DECLARE @li_LoopCnt INTEGER
DECLARE @li_MaxCnt INTEGER
DECLARE @li_DBID INTEGER
DECLARE @li_FileId INTEGER
DECLARE @sys_DBName SYSNAME
DECLARE @sys_FileName SYSNAME
DECLARE @Dynamic_SQL VARCHAR(500)
/*
DROP TABLE tempdb..tmp_IOStats
DROP TABLE tempdb..tmp_IOStats_Prior
DROP TABLE tempdb..tmp_DBFiles
TRUNCATE TABLE IOStats
*/
IF OBJECT_ID('tempdb..tmp_DBFiles') IS NULL
BEGIN
--
-- Build a list of all DBs
--
DECLARE @ldb_DBs TABLE
(
ID INT IDENTITY,
DBID INT,
DBName SYSNAME
)
INSERT INTO
@ldb_DBs
SELECT
DBID,
NAME
FROM
Master.dbo.SYSDATABASES
ORDER BY
DBID
CREATE TABLE tempdb..tmp_DBFiles
(
ID INT IDENTITY,
DBID INT,
FileID INT,
FileName SYSNAME,
DBName SYSNAME
)
SET @li_LoopCnt = 1
SELECT
@li_MaxCnt = MAX(ID)
FROM
@ldb_DBs
WHILE @li_LoopCnt <= @li_MaxCnt
BEGIN
SELECT
@li_DBID = DBID,
@sys_DBName = DBName
FROM
@ldb_DBs
WHERE
ID = @li_LoopCnt
SET @Dynamic_SQL = 'INSERT INTO tempdb..tmp_DBFiles( DBID, FileID, FileName, DBName)
SELECT ' + STR(@li_DBID) + ', FileID, Name, ''' + @sys_DBName + '''
FROM ' + @sys_DBName + '.dbo.SysFiles'
EXEC (@Dynamic_SQL)
SET @li_LoopCnt = @li_LoopCnt + 1
END
END
IF OBJECT_ID('tempdb..tmp_IOStats') IS NOT NULL
BEGIN
Truncate TABLE tempdb..tmp_IOStats
END
ELSE
BEGIN
CREATE TABLE tempdb..tmp_IOStats_Prior
(
StatTime DATETIME,
DBName SYSNAME,
FileName SYSNAME,
NbrReads BIGINT,
NbrWrites BIGINT,
BytesRead BIGINT,
BytesWritten BIGINT,
IOStallMS BIGINT
)
CREATE TABLE tempdb..tmp_IOStats
(
StatTime DATETIME,
DBName SYSNAME,
FileName SYSNAME,
NbrReads BIGINT,
NbrWrites BIGINT,
BytesRead BIGINT,
BytesWritten BIGINT,
IOStallMS BIGINT
)
END
SELECT
@li_MaxCnt = MAX(ID)
FROM
tempdb..tmp_DBFiles
SET @li_LoopCnt = 1
WHILE @li_LoopCnt <= @li_MaxCnt
BEGIN
SELECT
@li_DBID = DBID,
@li_FileId = FileID,
@sys_DBName = DBName,
@sys_FileName = FileName
FROM
tempdb..tmp_DBFiles
WHERE
ID = @li_LoopCnt
INSERT INTO tempdb..tmp_IOStats
(
StatTime,
DBName,
FileName,
NbrReads,
NbrWrites,
BytesRead,
BytesWritten,
IOStallMS
)
SELECT
GETDATE(),
@sys_DBName,
@sys_FileName,
NumberReads,
NumberWrites,
BytesRead,
BytesWritten,
IOStallMS
FROM
:: FN_VIRTUALFILESTATS(@li_DBID, @li_FileId)
SET @li_LoopCnt = @li_LoopCnt + 1
END
IF (SELECT COUNT(*) FROM tempdb..tmp_IOStats_Prior) = 0
BEGIN
INSERT INTO DBHistory..IOStats
(
StatTime,
ServerName,
DBName,
FileName,
NbrReads,
NbrWrites,
BytesRead,
BytesWritten,
IOStallMS
)
SELECT
StatTime,
@@ServerName,
DBName,
FileName,
NbrReads,
NbrWrites,
BytesRead,
BytesWritten,
IOStallMS
FROM
tempdb..tmp_IOStats
INSERT INTO tempdb..tmp_IOStats_Prior
SELECT
StatTime,
DBName,
FileName,
NbrReads,
NbrWrites,
BytesRead,
BytesWritten,
IOStallMS
FROM
tempdb..tmp_IOStats
END
ELSE
BEGIN
INSERT INTO DBHistory..IOStats
(
StatTime,
ServerName,
DBName,
FileName,
NbrReads,
NbrWrites,
BytesRead,
BytesWritten,
IOStallMS
)
SELECT
tempdb..tmp_IOStats.StatTime,
@@ServerName,
tempdb..tmp_IOStats.DBName,
tempdb..tmp_IOStats.FileName,
tempdb..tmp_IOStats.NbrReads - tempdb..tmp_IOStats_Prior.NbrReads,
tempdb..tmp_IOStats.NbrWrites - tempdb..tmp_IOStats_Prior.NbrWrites,
tempdb..tmp_IOStats.BytesRead - tempdb..tmp_IOStats_Prior.BytesRead,
tempdb..tmp_IOStats.BytesWritten - tempdb..tmp_IOStats_Prior.BytesWritten,
tempdb..tmp_IOStats.IOStallMS - tempdb..tmp_IOStats_Prior.IOStallMS
FROM
tempdb..tmp_IOStats
JOIN tempdb..tmp_IOStats_Prior ON
tempdb..tmp_IOStats.DBName = tempdb..tmp_IOStats_Prior.DBName
AND
tempdb..tmp_IOStats.FileName = tempdb..tmp_IOStats_Prior.FileName
WHERE
tempdb..tmp_IOStats.NbrReads - tempdb..tmp_IOStats_Prior.NbrReads <> 0
OR
tempdb..tmp_IOStats.NbrWrites - tempdb..tmp_IOStats_Prior.NbrWrites <> 0
OR
tempdb..tmp_IOStats.BytesRead - tempdb..tmp_IOStats_Prior.BytesRead <> 0
OR
tempdb..tmp_IOStats.BytesWritten - tempdb..tmp_IOStats_Prior.BytesWritten <> 0
OR
tempdb..tmp_IOStats.IOStallMS - tempdb..tmp_IOStats_Prior.IOStallMS <> 0
TRUNCATE TABLE tempdb..tmp_IOStats_Prior
INSERT INTO tempdb..tmp_IOStats_Prior
SELECT
StatTime,
DBName,
FileName,
NbrReads,
NbrWrites,
BytesRead,
BytesWritten,
IOStallMS
FROM
tempdb..tmp_IOStats
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vDisplay_IOStats]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop VIEW [dbo].[vDisplay_IOStats]
GO
-- Script generated on 3/17/2004 2:45 PM
-- By: kknudson
-- Server: Server
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'DBHistory Collect IOStats')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''DBHistory Collect IOStats'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'DBHistory Collect IOStats'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'DBHistory Collect IOStats', @owner_login_name = N'sa', @description = N'Collect the IO Statistic information', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Collect IOStats', @command = N'USP__DBHistory_LOG_IOStats', @database_name = N'DBHistory', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Collect DBStats', @enabled = 1, @freq_type = 4, @active_start_date = 20040317, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
CREATE VIEW vDisplay_IOStats
AS
SELECT
CONVERT(CHAR(10), StatTime, 108) AS StatTime,
CAST(DBName AS CHAR(20)) AS 'DBName',
CAST(FileName AS CHAR(50)) AS 'FileName',
LEFT(CONVERT(Char(12), CAST(NBRREADS AS Money), 1), 9) AS Reads,
LEFT(CONVERT(Char(12), CAST(NBRWRITES AS Money), 1), 9) AS Writes,
LEFT(CONVERT(Char(20), CAST(BYTESREAD AS Money), 1), 17) AS 'Bytes Read',
LEFT(CONVERT(Char(20), CAST(BYTESWRITTEN AS Money), 1), 17) AS 'Bytes Written',
LEFT(CONVERT(Char(12), CAST(IOSTALLMS AS Money), 1), 9) 'IO Wait'
FROM
DBHistory..IOStats
GO
KlK
January 31, 2005 at 8:28 am
Thanks.
February 1, 2005 at 5:11 am
Alternatively, you can run a profiler trace and filter on database id to see who is logging in and what they are doing etc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply