| Author |
Topic |
|
hari.loganathan
Starting Member
11 Posts |
 Posted - 2010-12-28 : 11:36:39
|
| There is an application which is configured to sql server 2005 the application freezing when they update ony records in the application,when we check the database and there is always a suspended process that I have to kill in order for them go on..Below in the process.When we check the index build on this tranheader table we found the fill factor was missing,does that couldbe a reason ALTER INDEX [TRANHEADER_ID] ON [dbo].[TRANSCRIPTHEADER] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF ) |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-28 : 11:42:38
|
| A trigger doing something that is being blocked - creating another connection to access the updated data?Have a look at the command executed by the suspecned process.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
hari.loganathan
Starting Member
11 Posts |
Posted - 2010-12-28 : 11:48:10
|
| Thanks, this is the command i see in the suspended processALTER INDEX [TRANHEADER_ID] ON [dbo].[TRANSCRIPTHEADER] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF ) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-28 : 19:35:54
|
| Why is this being done from an application - is it performed for every update?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
hari.loganathan
Starting Member
11 Posts |
Posted - 2010-12-29 : 05:13:17
|
| We have got the index of Transheader table which looks like this:USE [Campus6]GO/****** Object: Index [TRANHEADER_ID] Script Date: 12/22/2010 09:21:19 ******/CREATE UNIQUE NONCLUSTERED INDEX [TRANHEADER_ID] ON [dbo].[TRANSCRIPTHEADER] ( [PEOPLE_CODE] ASC, [PEOPLE_ID] ASC, [TRANSCRIPT_SEQ] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]I found that this does not have the fill factor defined in it, will that be the cause the application to hang when they try update records.. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-29 : 05:21:21
|
| No should use the default.I thought is was the alter index statement that was blocking things?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
hari.loganathan
Starting Member
11 Posts |
Posted - 2010-12-29 : 06:34:03
|
| Thats Right the alter index statement is blocking the things, what can be the possible resolutions? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-29 : 20:34:00
|
| Where i sthe alter index command being executed from. Is it the application that is doing it?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
hari.loganathan
Starting Member
11 Posts |
Posted - 2010-12-30 : 06:05:32
|
| Yes, it is the application which is doing it |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
hari.loganathan
Starting Member
11 Posts |
Posted - 2011-01-03 : 08:37:38
|
| Thanks Tara Kizer, Sorry for the delay in reply, have given the suggestion to check on that ONLINE=OFF |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
hari.loganathan
Starting Member
11 Posts |
Posted - 2011-01-03 : 10:46:28
|
| Well i dont have the access to their SQL server, i need to email the person to send me the details |
|
|
|
|
suspended process