June 17, 2011 at 12:11 pm
Hi all,
Have a "hopefully" quick question.
I'm responsible for administering a database server that I've just been given control over. The database server is being used by several applications. Within the database server are two databases that are being used.
I've decided to do some database cleanup because there are serious space limitations on the machine hosting the database server. I've done some investigating into the databases, and have found many tables and procedures which appear to not be in use. In order to determine whether or not a table / procedure are being used, I've renamed the ones which fall into this category, by appending a "_DEL" flag to the end of the names.
I had previously thought that, should something attempt to then access those objects, either directly or through the use of a stored procedure, an error would be logged in either the SQL Server Agent log, or the SQL Server log. However, it appears as though this is not the case. A simple test of just doing a "SELECT * FROM ABC" causes an error, however, nothing appears in the error logs.
After further investigation, I figured that using the Alerts would be an effective means to this end, however, it appears as though the alerts do not trigger on "Invalid Object" errors, as well as "Could not find stored procedure" errors. Reading up on this, it appears as though the only errors that the Alerts trigger off of are those that are listed in this query:
SELECT *
FROM sys.messages
WHERE (is_event_logged = 1 OR severity > 20)
AND language_id = 1033
ORDER BY severity, message_id
So, the question I have is this - how can I correctly achieve my objective of cleaning up the databases, if I have no way of being able to determine if there are errors caused by attempted accesses of a table / procedure that I have removed?
June 17, 2011 at 12:52 pm
These two scripts usually get it done but you need at least 3-6 months if not a year depending on your application.
Our App was financial so we had to run it for a year to catch quarterly/yearly procedures.
LAST TABLE ACTIVITY:
begin
WITH LastActivity (ObjectID, LastAction) AS
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id) AS TableName,
MAX(la.LastAction) as LastSelect
FROM sys.objects so
LEFT
JOIN LastActivity la
on so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)[/sub]
STORED PROCEDURE LOGGING:
begin
--This script creates the objects necessary for database event logging.
--Logging can be activated for any procedure by inserting this code at the beginning:
--Log Event--------------------------------------------------------------------------------------------------
--declare@EventText varchar(1000)
--declare@ProcedureName varchar(50)
--
--set@ProcedureName = OBJECT_NAME(@@PROCID)
--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LogEvent]'))
--set@EventText = @ProcedureName + ' '
-- + dbo.LogParam('@Param1', @Param1)
-- + dbo.LogParam('@Param2', @Param2)
-- + dbo.LogParam('@Param3', @Param3)
-- .
-- .
-- + dbo.LogParam('@ParamN', @ParamN)
-- elseset@EventText = 'dbo.LogParam function not found.'
--
--if @ProcedureName is not null and exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LogEvent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
--exec LogEvent @ProcedureName, @EventText
-- --------------------------------------------------------------------------------------------------------------------
--This table will hold a log of events.
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dbo].[EventLog]
([EventName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EventTime] [datetime] NOT NULL ,
[Login] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EventText] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
end
go
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DF_EventLog_EventTime]') and OBJECTPROPERTY(id, N'IsConstraint') = 1)
begin
ALTER TABLE [dbo].[EventLog] ADD
CONSTRAINT [DF_EventLog_EventTime] DEFAULT (getdate()) FOR [EventTime]
end
go
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DF_EventLog_Login]') and OBJECTPROPERTY(id, N'IsConstraint') = 1)
begin
ALTER TABLE [dbo].[EventLog] ADD
CONSTRAINT [DF_EventLog_Login] DEFAULT (convert(varchar(50),(suser_sname() + ', (' + host_name() + ')'))) FOR [Login]
end
go
Create procedure LogEvent
(@EventName varchar(50),
@EventText varchar(500))
as
--Procedure LogEvent
--blindman, 2006
--Inserts a record into the EventLog table.
--Event logging can be turned on or off for the database by commenting out the insert statement.
begin
insert into EventLog
(EventName,
EventText)
values(@EventName,
@EventText)
end
GO
CREATE function LogParam(@ParameterName varchar(50), @ParameterValue sql_variant)
returns varchar(100) as
--function LogParam
--blindman, 2006
--This function returns a string formatted for concatenation and input to the
--LogEvent procedure
declare@ReturnString varchar(100)
if @ParameterValue is null set @ReturnString = ''
else if isnumeric(convert(varchar(50), @ParameterValue)) = 1 set @ReturnString = @ParameterName + '=' + convert(varchar(50), @ParameterValue) + ', '
else set @ReturnString = @ParameterName + '=''' + convert(varchar(50), @ParameterValue) + ''', '
return @ReturnString
end
GO
June 20, 2011 at 6:54 am
So your solution was to basically log all activity against the tables / procedures and, upon an acceptable amount of time, deem them to not be used.
There is no way to capture the errors that are caused by attempted use of the tables / procedures?
July 6, 2011 at 11:22 am
Open Sql Profiler select save to table option
In Events select Errors and Warnings then select:
UserErrorMessage
And in Column Filter select "Error" = 2812 for stored procedures
Setup another profiler session for Error = 208 missing tables.
Then you will only catch
"Could not find stored procedure 'usp_doesnotexist'." and "Invalid object name 'tbl_doesnotexist'."
July 6, 2011 at 11:25 am
Thanks for the suggestion!
I like that idea, except for one problem - that requires a trace to be running in order to capture the results.
If I apply the relevant filters such that the trace only captures those instances where the errors occur, is this still a fairly intensive operation, such that I would not be able to leave it running for weeks at a time?
July 6, 2011 at 11:35 am
AlexSQLForums (7/6/2011)
Setup another profiler session for Error = 208 missing tables.
Just be aware that may fire a lot of false alerts. If a batch is submitted that contains a create table (typically a temp table) and queries using it, that will fire a 208 error (as part of compile) but it's not actually an error and it's not a problem, SQL just ignores it and marks the query in question for a deferred compile.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2011 at 8:02 am
In addition to "invalid object" alerts and running DMV queries to determine object usage, you could perhaps identify those infrequently referenced database objects by searching application source code using a RegEx tool. There are a few tools specifically designed for doing this.
http://www.regular-expressions.info/examplesprogrammer.html
http://www.codeproject.com/KB/vb/Stored_Proc_Call_Search.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply