how to tell if accessed

I just got assigned to be our companies sql server dba, there are about 80 databases on various servers. I am suspecting many are not even used. Is there a way for me to tell the last time they were accessed and by whom? And, more importantly, the last time any changes have been made in the database? Thanks in advance.Let me guess. The old DBA quit suddenly without leaving any documentation?|||Actually, there has never really been a sql server dba here. I'm new, and like all the other dbas,we are life long oracle dbas. But, someone has to get these under some control. Until now, all microsoft has been left in the hands of the developers to admin. Fox guarding the hen-house so to speak. Nobody wanted to touch it until I came along. (oh, and the dozens of developers involved have come and gone over the years, not much help)|||Inheriting 80 databases on multiple servers in lord knows what state of maintenance, you folks might consider hiring a SQL Server DBA...
Having gone from SQL Server to Oracle I can tell you that there will be some learning curve, and what you are describing sounds like a full-time position.

As far as telling whether a database is being used, that is difficult after the fact. You can set up tracing or auditing on it to track future access.|||That's a lot of licensing..

Do you have an inventory of all the boxes?

I would start there. Are they physically located near you? Or is it remote?

AND I would change the sa password and see what comes out of the wood work.

AND I would revoke any sa level priviliges.

Do you have a traditional server landscape?

Development, Production, Contingency?|||What's that awful grinding sound I hear? A room full of developers gnashing their teeth?

Heh heh heh...|||I'd check when they were last backed up, then build a custom trace to track logins and db's that they access. Very often DB info is not available in the trace if you use one of the predefined templates, so carefully select the events to ensure that db info is captured.|||Thanks for all the info. Everything is fine, backups have been taken care of and are scheduled now, and licensing was already addressed. SA passwords were changed and I'm working on the multiple file shares where data files reside :mad: with full access for Everyone who maps to it. Internal database privileges are next.

I've tried setting up a trace, but can only figure out how to track the login which, as stated, does not really show which database they are connecting to. These are all version 2000(8.0) and if anyone can help me with an event that will show me what database is being connected to, I'd appreciate it.

How to tell if a table is being used.

Currently I've been assigned the glorious task of cleaning up our database. I've gone ahead and marked a sizable list of tables that I don't believe are accessed anymore. Now, I need to find out whether or not they are being accessed. I've ran sp_depends on all of them to find out what stored procedures/triggers may be accessing them, and the plate is clear. But, there are many individual programs, reports, etc... running against this database, so I'm going to need more proof than that. My boss suggested some sort of SELECT trigger, but that doesn't exist (to what I've found) for SQL Server 2000.

So, does anyone know how to determine if a table is being accessed or not (other than manually looking through thousands of lines of code)?

Thanks for any help you can give me.If you happen to have a test system, go ahead and rename the tables, and do a round of testing. Good luck.|||Originally posted by MCrowley
If you happen to have a test system, go ahead and rename the tables, and do a round of testing. Good luck.
I wish it were that simple. There is no test environment that tests all the applications/reports/etc... that are accessing this database. And, if I rename the tables, I've been told that the stored procedures will continue to work properly, while programs executing miscellaneous queries will fail. My initial idea has been to create copies of all the tables that I want to delete (named tablename_DELETE) and drop the original tables. Then if "shit hits the fan", I can rename the backup copy table back to its original form.

This still puts me in a situation where I have a lot of uncertainty about what I will be affecting. That's why I wanted to find some sort of an access log, or SELECT triger, or something I could use to see if these tables were being accessed.|||You can periodically look into syscacheobjects table and see if helps.|||Originally posted by rdjabarov
You can periodically look into syscacheobjects table and see if helps.
Good idea. I'm formulating some sort of a job to check this now, but I really curious to know what happens on instances where a table is accessed sparingly. In this case, would an entry exist in the syscacheobjects?|||What about using SQL Profiler and running a trace?|||Also you could use Profiler for saving tables activity (only selects, inserts, deletes and updates) and analyzing.|||Originally posted by Brett Kaiser
What about using SQL Profiler and running a trace?
You are reading my mind... ;)|||The profiler idea is a good one. I'm going to try that out.

Thanks for the help!|||My only qualm about Profiler is that it may not be able to pick up views an/or procedures that access this table. I know sysdepends did not show anything, but how accurate is that?|||I sometimes log procedure execution by creating the following table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProcedureLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ProcedureLog]
GO
CREATE TABLE [dbo].[ProcedureLog] (
[ProcessName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcessTime] [datetime] NULL ,
[Application] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CurrentUser] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SystemUser] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProcedureLog] WITH NOCHECK ADD
CONSTRAINT [DF_ProcedureLog_ProcessTime] DEFAULT (getdate()) FOR [ProcessTime]
GO
setuser
GO
EXEC sp_bindefault N'[dbo].[DF_SYS_APPNAME]', N'[ProcedureLog].[Application]'
GO
EXEC sp_bindefault N'[dbo].[DF_SYS_USERNAME]', N'[ProcedureLog].[CurrentUser]'
GO
EXEC sp_bindefault N'[dbo].[DF_STRING_EMPTY]', N'[ProcedureLog].[Notes]'
GO
EXEC sp_bindefault N'[dbo].[DF_STRING_EMPTY]', N'[ProcedureLog].[ProcessName]'
GO
EXEC sp_bindefault N'[dbo].[DF_SYS_SUSERSNAME]', N'[ProcedureLog].[SystemUser]'
GO
setuser
GO

...and then putting this code at the start of each procedure I want to monitor:

--Record the event
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProcedureLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
insert into dbo.ProcedureLog (ProcessName) values ('YourProcedureName')

This monitors who, what, and when a procedure is called.

You could put the same code in a trigger on a table.

Actually, I also include a notes field where I pass the values of all the parameters submitted to the procedure, but this may not be necessary for what you want to do.|||Yeah, except as the poster said, - there are no dependent ptocedures found for the tables that he wants to research the usage of.

You could put the same code in a trigger on a table.

Have they come up with a trigger for SELECT already? Man, the technology is just leaping into the future, leaving us behind every day :)|||Old technology, actually.

Just because a relationship doesn't show up in sysdepends doesn't mean it doesn't exist. Dynamic dependencies are not documented in sysdepends.

And as far as searching for references, maybe you should just script out your database and application code and do string searches for the suspect objects.|||Originally posted by blindman
Old technology, actually.

Just because a relationship doesn't show up in sysdepends doesn't mean it doesn't exist. Dynamic dependencies are not documented in sysdepends.

And as far as searching for references, maybe you should just script out your database and application code and do string searches for the suspect objects.

Nah...he's worried about applcation connecting to the server...

There are no TRIGGERS for SELECT btw

bol

{ [DELETE] [,] [INSERT] [,] [UPDATE] }

Are keywords that specify which data modification statements, when attempted against this table or view, activate the trigger. At least one option must be specified. Any combination of these in any order is allowed in the trigger definition. If more than one option is specified, separate the options with commas.

For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.|||Thanks, Brett, that's what I thought :)|||Originally posted by Brett Kaiser
Nah...he's worried about applcation connecting to the server...

There are no TRIGGERS for SELECT btw

bol
Yes, I was more worried about external programs running adhoc queries than I was about stored procedures accessing the tables. I felt reasonably confident with the sp_depends, and I even searched the syscomments system table for references to the tables in quetion in the text field. So, basically a SELECT trigger would be an ideal option, but the profiler is serving its purpose for now. I'm going to give it a week, and if I haven't seen any activity by then, I'm smoking the tables.

Thanks again, for all the help