March 12, 2009 at 9:30 am
apat (3/12/2009)
use sp_depends and see if there are any dependencies on this table/view? There might be some parent-child relationship for this table to other tables.You have to drop view first if it is created with schemabinding option.
The sp_depends gave me the files I'm having problems with:
View: V_F42119_1
Dependences: F42119 (problem), F4201 (problem), F0101 (ok)
March 12, 2009 at 9:31 am
ALZDBA (3/12/2009)
Keep in mind you cannot drop objects that are actually in use !
How can I confirm this?
March 12, 2009 at 9:38 am
Try this --
Alter this view alltogather to a different code since you anyways want to drop it.
ex. alter view "viewname" as select getdate();
& then try dropping it.
March 12, 2009 at 10:55 am
The Alter View has been running for 35 minutes so far....
March 12, 2009 at 11:17 am
This proc (in master db please) shows blocking activity
-- ALZDBA install sp_DBA_WhoBlocks.sql
-----------------------------------
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_DBA_WhoBlocks] ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBA_WhoBlocks]') AND type in (N'P', N'PC'))
begin
DROP PROCEDURE [dbo].[sp_DBA_WhoBlocks]
end
GO
CREATE PROCEDURE sp_DBA_WhoBlocks
@Login_Name sysname = NULL
,@Host_Name sysname = NULL
,@DbName sysname = NULL
,@Program_Name sysname = NULL
,@ExtraInfo char(1) = 'N'
-- WITH EXECUTE AS 'myuser'
-- with encryption
as
BEGIN
-- ALZDBA dd 2006/06/07
-- connecties en blockinginfo
--
print '-- Extra Parameters :'
print '-- @Login_Name sysname = ''' + isnull(@Login_Name,'') + ''''
print '-- ,@Host_Name sysname = ''' + isnull(@Host_Name,'') + ''''
print '--,@DbName sysname = ''' + isnull(@DbName,'') + ''''
print '-- ,@Program_Name sysname = ''' + isnull(@Program_Name,'') + ''''
print '-- ,@ExtraInfo char(1) = ''' + isnull(@ExtraInfo,'') + ''''
set nocount on
if upper(@ExtraInfo) = 'Y'
begin
SELECT
-- ES.*
ES.session_id
, ISNULL(ER.blocking_session_id, 0) as BlockedBy
, ES.login_name
, ES.last_request_start_time
, ES.last_request_end_time
, ES.cpu_time
, ES.[host_name]
, db_name(ER.database_id) as DbName
, ES.program_name
, case ES.transaction_isolation_level
when 0 then 'Unspecified'
when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted'
when 3 then 'RepeatableRead'
when 4 then 'Serializable'
when 5 then 'Snapshot'
else cast(ES.transaction_isolation_level as char(14))
end as TRX_isolation_level
, ES.nt_domain
, ES.nt_user_name
--, ES.login_time
--, ES.host_process_id
, ES.client_interface_name
--, ES.client_version
--, ES.security_id
, ES.status
--, ES.context_info
, ES.memory_usage
, ES.total_scheduled_time
, ES.total_elapsed_time
--, ES.endpoint_id
, ES.reads
, ES.writes
, ES.logical_reads
, ES.is_user_process
--, ES.text_size
--, ES.[language]
--, ES.date_format
--, ES.date_first
--, ES.[quoted_identifier]
--, ES.[arithabort]
--, ES.[ansi_null_dflt_on]
--, ES.[ansi_defaults]
--, ES.[ansi_warnings]
--, ES.[ansi_padding]
--, ES.[ansi_nulls]
--, ES.[concat_null_yields_null]
--, ES.transaction_isolation_level
--, ES.[lock_timeout]
--, ES.[deadlock_priority]
, ES.row_count
, ES.prev_error
-- , ER.*
--, ER.session_id
--, ER.request_id
, ER.start_time
, ER.status
, ER.command
--, ER.sql_handle
--, ER.statement_start_offset
--, ER.statement_end_offset
--, ER.plan_handle
, ER.[user_id]
--, ER.connection_id
--, ER.blocking_session_id
, ER.wait_type
, ER.wait_time
, ER.last_wait_type
, ER.wait_resource
, ER.open_transaction_count
, ER.open_resultset_count
--, ER.transaction_id
--, ER.context_info
--, ER.percent_complete
--, ER.estimated_completion_time
, ER.cpu_time
, ER.total_elapsed_time
--, ER.scheduler_id
--, ER.task_address
, ER.reads
, ER.writes
, (tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
, ER.logical_reads
--, ER.text_size
--, ER.[language]
--, ER.date_format
--, ER.date_first
--, ER.[quoted_identifier]
--, ER.[arithabort]
--, ER.[ansi_null_dflt_on]
--, ER.[ansi_defaults]
--, ER.[ansi_warnings]
--, ER.[ansi_padding]
--, ER.[ansi_nulls]
--, ER.[concat_null_yields_null]
, ER.transaction_isolation_level
, ER.[lock_timeout]
, ER.[deadlock_priority]
, ER.row_count
--, ER.prev_error
, ER.nest_level
--, ER.granted_query_memory
, ER.executing_managed_code
--, EST.* --dbid objectid number encrypted text
, EST.dbid
, EST.objectid
, EST.number
, EST.encrypted
, EST.[text]
-- , EQP.* --dbid objectid number encrypted query_plan
-- , EQP.dbid
-- , EQP.objectid
--, EQP.number
--, EQP.encrypted
, EQP.query_plan
-- Blocked by info
, ESBlk.session_id as Blk_session_id
, ESBlk.login_name as Blk_login_name
, ESBlk.nt_domain as Blk_nt_domain
, ESBlk.last_request_start_time as Blk_last_request_start_time
, ESBlk.last_request_end_time as Blk_last_request_end_time
, ESBlk.[host_name] as Blk_host_name
, ESBlk.program_name as Blk_program_name
, ESBlk.status as Blk_status
, ESBlk.cpu_time as Blk_cpu_time
FROM sys.dm_exec_sessions ES
left join sys.dm_exec_requests ER
on ER.session_id = ES.session_id
left join sys.dm_db_task_space_usage tsu
ON ES.session_id = tsu.session_id
and ER.request_id = tsu.request_id
OUTER Apply sys.dm_exec_sql_text(ER.sql_handle) EST
OUTER Apply sys.dm_exec_query_plan(ER.plan_handle) EQP
left join sys.dm_exec_sessions ESBlk
on ESBlk.session_id = ER.blocking_session_id
-- where ER.session_id = @session_id
Where case when @Login_Name is null then 1
else case when ES.login_name like @Login_Name + '%' then 1 else 0 end
end = 1
and case when @Host_Name is null then 1
else case when ES.[host_name] like @Host_Name + '%' then 1 else 0 end
end = 1
and case when @DbName is null then 1
else case ER.database_id when db_id(@DbName) then 1 else 0 end
end = 1
and case when @Program_Name is null then 1
else case when ES.program_name like @Program_Name + '%' then 1 else 0 end
end = 1
order by case when isnull(ER.blocking_session_id,0)=0 then 1 else 0 end, ES.last_request_start_time desc, ES.last_request_end_time desc , ES.login_time desc
END
ELSE
BEGIN
SELECT
-- ES.*
ES.session_id
, ISNULL(ER.blocking_session_id, 0) as BlockedBy
, ES.login_name
, ES.last_request_start_time
, ES.last_request_end_time
, ES.cpu_time
, ES.[host_name]
, db_name(ER.database_id) as DbName
, ES.program_name
, case ES.transaction_isolation_level
when 0 then 'Unspecified'
when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted'
when 3 then 'RepeatableRead'
when 4 then 'Serializable'
when 5 then 'Snapshot'
else cast(ES.transaction_isolation_level as char(14))
end as TRX_isolation_level
, ES.nt_domain
, ES.nt_user_name
--, ES.login_time
--, ES.host_process_id
, ES.client_interface_name
--, ES.client_version
--, ES.security_id
, ES.status
--, ES.context_info
, ES.memory_usage
, ES.total_scheduled_time
, ES.total_elapsed_time
--, ES.endpoint_id
, ES.reads
, ES.writes
, ES.logical_reads
, ES.is_user_process
--, ES.text_size
--, ES.[language]
--, ES.date_format
--, ES.date_first
--, ES.[quoted_identifier]
--, ES.[arithabort]
--, ES.[ansi_null_dflt_on]
--, ES.[ansi_defaults]
--, ES.[ansi_warnings]
--, ES.[ansi_padding]
--, ES.[ansi_nulls]
--, ES.[concat_null_yields_null]
--, ES.transaction_isolation_level
--, ES.[lock_timeout]
--, ES.[deadlock_priority]
, ES.row_count
, ES.prev_error
-- , ER.*
--, ER.session_id
--, ER.request_id
, ER.start_time
, ER.status
, ER.command
--, ER.sql_handle
--, ER.statement_start_offset
--, ER.statement_end_offset
--, ER.plan_handle
, ER.[user_id]
--, ER.connection_id
--, ER.blocking_session_id
, ER.wait_type
, ER.wait_time
, ER.last_wait_type
, ER.wait_resource
, ER.open_transaction_count
, ER.open_resultset_count
--, ER.transaction_id
--, ER.context_info
--, ER.percent_complete
--, ER.estimated_completion_time
, ER.cpu_time
, ER.total_elapsed_time
--, ER.scheduler_id
--, ER.task_address
, ER.reads
, ER.writes
, (tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
, ER.logical_reads
--, ER.text_size
--, ER.[language]
--, ER.date_format
--, ER.date_first
--, ER.[quoted_identifier]
--, ER.[arithabort]
--, ER.[ansi_null_dflt_on]
--, ER.[ansi_defaults]
--, ER.[ansi_warnings]
--, ER.[ansi_padding]
--, ER.[ansi_nulls]
--, ER.[concat_null_yields_null]
, ER.transaction_isolation_level
, ER.[lock_timeout]
, ER.[deadlock_priority]
, ER.row_count
--, ER.prev_error
, ER.nest_level
--, ER.granted_query_memory
, ER.executing_managed_code
-- Blocked by info
, ESBlk.session_id as Blk_session_id
, ESBlk.login_name as Blk_login_name
, ESBlk.nt_domain as Blk_nt_domain
, ESBlk.last_request_start_time as Blk_last_request_start_time
, ESBlk.last_request_end_time as Blk_last_request_end_time
, ESBlk.[host_name] as Blk_host_name
, ESBlk.program_name as Blk_program_name
, ESBlk.status as Blk_status
, ESBlk.cpu_time as Blk_cpu_time
FROM sys.dm_exec_sessions ES
left join sys.dm_exec_requests ER
on ER.session_id = ES.session_id
left join sys.dm_db_task_space_usage tsu
ON ES.session_id = tsu.session_id
and ER.request_id = tsu.request_id
left join sys.dm_exec_sessions ESBlk
on ESBlk.session_id = ER.blocking_session_id
-- where ER.session_id = @session_id
Where case when @Login_Name is null then 1
else case when ES.login_name like @Login_Name + '%' then 1 else 0 end
end = 1
and case when @Host_Name is null then 1
else case when ES.[host_name] like @Host_Name + '%' then 1 else 0 end
end = 1
and case when @DbName is null then 1
else case ER.database_id when db_id(@DbName) then 1 else 0 end
end = 1
and case when @Program_Name is null then 1
else case when ES.program_name like @Program_Name + '%' then 1 else 0 end
end = 1
order by case when isnull(ER.blocking_session_id,0) = 0 then 1 else 0 end, ES.last_request_start_time desc, ES.last_request_end_time desc , ES.login_time desc
END
END
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 12, 2009 at 11:44 am
Wow...that's looks why beyond my minimal SQL expertise. What would that do for me?
March 12, 2009 at 12:04 pm
Ok...looking under Management, Current Activity, Locks/Process ID
Out of the hundreds of entries, there are entries labeled as 'Blocked By XX' When i click on those, I'm getting 3 entries. Those entries are referencing the tables I'm having problems with:
The first line has a DB type lock, Mode S, Status Grant, owner Sess, Index F4101 (Problem Table)
The second line is a TAB lock with a WAIT status
The third line is a KEY lock with a GRANT status
Is this what you were referring to ALZDBA?
March 12, 2009 at 1:22 pm
chicagonj (3/12/2009)
Ok...looking under Management, Current Activity, Locks/Process IDOut of the hundreds of entries, there are entries labeled as 'Blocked By XX' When i click on those, I'm getting 3 entries. Those entries are referencing the tables I'm having problems with:
The first line has a DB type lock, Mode S, Status Grant, owner Sess, Index F4101 (Problem Table)
The second line is a TAB lock with a WAIT status
The third line is a KEY lock with a GRANT status
Is this what you were referring to ALZDBA?
Indeed the SPID (connection) is the process ID that blocks your action.
dbcc inputbuffer(spidnumber) will show the first x bytes of the statement last issued by that connection.
To get rid of it, you can use KILL spidnumber if you are sysadmin.
Then you can drop the object(s) you want.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 12, 2009 at 3:26 pm
chicagonj,
did you try killing this spids and dropping the objects? did it work?
March 12, 2009 at 3:30 pm
apat (3/12/2009)
chicagonj,did you try killing this spids and dropping the objects? did it work?
Sorry for the late response. YES, that did work. One of those locks had everything jammed up.
Thanks everyone for your assistance!!!
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply