none
Count Function running Countinue and error occured timeout. RRS feed

  • Question

  • We are exececute Count function in particular table but query continue running and sometimes after error occured timeout.

    we have try below steps.
     
    Firstly we ran 'DBCC opentran()' command in a database and result out 'SPID - 41s' and 'Name - IFTSAutoNested'.
    Then we also ran 'sp_who2' and saw that SPID 41 running on Master database 'Command FTBATCHCMPLETE'.we were trying 
    to kill this SPID but unable to kill.
    Tuesday, June 30, 2020 1:21 PM

All replies

  • All processes below 50 are system processes, which you can not kill.

    Count on large tables can take some time. From where are you running the query, from SSMS or an own application. Are you using a filter (WHERE) in the query? How does the execution plan of the query look like?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, June 30, 2020 1:29 PM
  • Hi Sumit Majhi,

    In general, query timeout should first find the reason from  the SQL statement and the structure of data table. 
    Optimizing SQL statements and creating indexes for database query fields are the most common methods.

    In addition,you can refer to this link, it will tells you how to kill SPID.
    KILL SPID command in SQL Server


     Best Regards,
     Echo


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 1, 2020 3:15 AM
  • Query running on SSMS and i am not using (Where) clause.how to stop SPID 41?
    Wednesday, July 1, 2020 4:26 AM
  • Is it server installed locally? Did you try to cancel this query directly in SSMS? 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 1, 2020 4:33 AM
    Moderator
  • Yes did this directly cancel in SSMS.

    Now reboot the system and also run this count.now SPID 12c block the database. how to solve this issue.

    Wednesday, July 1, 2020 7:47 AM
  • Also SQLServer error 1222 is occured.
    Wednesday, July 1, 2020 8:30 AM
  • I'm not sure what exactly this error mean (can you post the full error message)?

    Is the table you're trying to do a count a huge table? Does it have indexes (clustered index)?

    I have a query I use to get quick row counts in all tables:

    ;with cte as (SELECT  s.[name] + '.' + quotename(t.[name]) as [Table Name], 
     SUM(p.rows) as [Rows Count] 
     
     FROM sys.partitions AS p
      INNER JOIN sys.tables AS t
      ON p.[object_id] = t.[object_id]
      INNER JOIN sys.schemas AS s
      ON s.[schema_id] = t.[schema_id]
      WHERE p.index_id IN (0,1) -- here we may add schema and table names exclusions
      GROUP by s.[name],  t.[Name] )
    
    select *, sum([Rows Count]) over () as [Total all Tables Rows Count]
    from cte
      order by [Rows Count] DESC, [Table Name];


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Wednesday, July 1, 2020 1:24 PM
    Moderator
  • I'm not sure what exactly this error mean (can you post the full error message)?

    Just ask the right one: SQL Server ;-)

    select *
    from sys.messages
    where message_id = 1222 and language_id = 1033

    = "Lock request time out period exceeded."

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, July 2, 2020 6:43 AM
  • I know I often was not patient enough to wait for completion of simple 

    select count(*) from HugeTable (~80 mln rows).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 2, 2020 3:31 PM
    Moderator
  • select count(*) from HugeTable (~80 mln rows).

    If you want to get the row count from a table without a filter, then you can get the count from DMV sys.dm_db_partition_stats => row_count, see script "Detailed list of all tables and their size"

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 3, 2020 6:29 AM
  • Hi Olaf,

    I do have a script I called QuickRowCount (and posted in the beginning of this thread) which I use to get a quick row count of my tables.

    BTW, Olaf - can you still change that script? If yes, can you please fix the comment lines above your script?

    List all database tables and there indexes with  
    -- detailed information about row count and  
    -- used + reserved data space.  

    Should be their indexes with the


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Friday, July 3, 2020 1:42 PM
    Moderator
  • Hi Sumit Majhi,
    Has your problem been solved? If it is solved, please mark the point that you 
    think is correct as an answer. This can help others who encounter similar problems.

    Best Regards
    Echo

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    3 hours 5 minutes ago