March 1, 2012 at 9:42 pm
I have a Table of Employees who have only 2000 records
but I have going to modify this table by means add columns or modifying exists columns its take so so many times
This table contain 7 FK constraints
When I try to disable that constraints it also take so many time
other tables and other database are working fine
Need urgent Help
Regards,
Naseer Ahmed
March 1, 2012 at 10:16 pm
Are you doing schema changes using UI (user interface)?
If yes, I suggest, do the changes using SQL commands. It is much faster because, UI internally drops and recreates the table.
March 1, 2012 at 10:58 pm
I also try so many times through commands also same result so much time it takes I donot know why
Regards,
Naseer Ahmed
March 1, 2012 at 11:49 pm
Run this while schema change is going on:
select session_id, start_time, status, blocking_session_id, wait_type, wait_resource, wait_time
from sys.dm_exec_requests
where session_id = <spid>
March 2, 2012 at 12:05 am
I got this while running on changing schema
session_idstart_timestatusblocking_session_idwait_typewait_resourcewait_time
1412012-03-02 16:03:13.400suspended97LCK_M_ISOBJECT: 11:1795537480:0 10022
532012-03-02 16:03:10.040suspended97LCK_M_ISOBJECT: 11:1795537480:0 13459
972012-03-02 16:03:09.600suspended118LCK_M_SCH_MOBJECT: 11:1795537480:0 13900
what to do ??
Now
March 2, 2012 at 8:46 am
Is your ALTER TABLE command running from SPID 97?
It is blocked by SPID 118
Check what SPID 118 is doing.
March 6, 2012 at 6:31 am
Thanks for replys
No I just execute this simple command
ALTER TABLE EMPLOYEE
ADD Is_Employee_Updated BIT
And find this issue.
How I investigate what SPID 118 is doing.
Regards,
NA
March 6, 2012 at 9:47 pm
DBCC INPUTBUFFER(118)
is the quick and easy way to see the query run by SPID 118
After that you can run the following for more details:
SELECT er.session_id ,
host_name , program_name , original_login_name , er.reads ,
er.writes ,er.cpu_time , wait_type , wait_time , wait_resource ,
blocking_session_id , st.text
FROM sys.dm_exec_sessions es
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = es.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE blocking_session_id > 0
UNION
SELECT es.session_id , host_name , program_name , original_login_name ,
es.reads , es.writes , es.cpu_time , wait_type , wait_time ,
wait_resource , blocking_session_id , st.text
FROM sys.dm_exec_sessions es
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = es.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE es.session_id IN ( SELECT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0 )
(Above query is taken from the book Troubleshooting SQL Server A Guide for the Accidental DBA Jonathan Kehayias and Ted Krueger)
March 6, 2012 at 11:57 pm
Thanks to all
It really helpful for me and solve my issues.
Regards,
NA
March 6, 2012 at 11:59 pm
Would be nice if you'd share what the problem was and how you solved it.
It may help others who may have the same or similar problem.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply