The simple select * from query is running from hours without giving any results in MS SQL SERVER. How to fix this?

Question

0.00/5 (No votes)

See more:

I am running a simple

SQL

select * from table_name

query for the table which has 600 records in it, but it is keep on executing from hours not giving any results. when I execute using top operator with max to 10 it is executing and giving result when I give top 11 the same problem is happening. I have the same table in other DB instance with more than 5000 records there when I executes

SQL

select * from table_name

it is executing quickly, even the view also executing in the other instance no changes in the logic of table and views in both the instances. Although everything is same in both the instances then why I'm encountering this issue in this instance even there is less number of records compared to other instance?. if view is not giving results then I can understand there might be performance issue but the table itself is not giving any result.
Please suggest some solution and explain why is this happening ? Thanks in Advance.

What I have tried:

I have checked the indexes, updated the statistics for the table and checked if there is any other active transaction that might be affecting this query. I checked using

SQL

DBCC OPENTRAN

it is showing SPID 88 is active but when i check that SPID with

SQL

EXEC sp_who2

that transaction is only not available

Posted 5-Jun-23 0:21am

S Shuaib Ahmed

Updated 5-Jun-23 2:19am

Add a Solution

Comments

Member 15627495 5-Jun-23 6:46am

hello !

can you show more code please ?
with what you provide we can't undestand...

S Shuaib Ahmed 5-Jun-23 6:53am

Hi!
select * from SAP.SAP_REQUISITION_HEADERS
this is the table I am executing
below is the table structure
CREATE TABLE [SAP].[SAP_REQUISITION_HEADERS](
[REQUISITION_ID] [numeric](18, 0) IDENTITY(1000,1) NOT NULL,
[REQUISITION_NUMBER] [nvarchar](60) NOT NULL,
[TITLE] [nvarchar](240) NOT NULL,
[REQ_STATUS] [nvarchar](50) NOT NULL,
[PREPARER_ID] [numeric](18, 0) NOT NULL,
[REQ_BU_ID] [numeric](18, 0) NOT NULL,
[DESCRIPTION] [nvarchar](240) NULL,
[NOTE_TO_APPROVER] [nvarchar](1000) NULL,
[EMERGENCY_REQ_FLAG] [nvarchar](1) NULL,
[CAT_ITEM_ID] [numeric](18, 0) NULL,
[IFACE_SOURCE_CODE] [nvarchar](25) NULL,
[IFACE_SOURCE_LINE_ID] [numeric](18, 0) NULL,
[SUBMISSION_DATE] [datetime] NULL,
[APPROVED_DATE] [datetime] NULL,
[APPROVED_BY] [numeric](18, 0) NULL,
[HAS_WITHDRAWN_LINES] [nvarchar](1) NOT NULL,
[HAS_CANCELED_LINES] [nvarchar](1) NOT NULL,
[HAS_RETURNED_LINES] [nvarchar](1) NOT NULL,
[HAS_REJECTED_LINES] [nvarchar](1) NOT NULL,
[HAS_INCOMPLETE_LINES] [nvarchar](1) NOT NULL,
[HAS_PENDING_APPR_LINES] [nvarchar](1) NOT NULL,
[FUNDS_STATUS] [nvarchar](30) NULL,
[REVISION_NO] [numeric](18, 0) NOT NULL,
[FUNDS_CHK_FAIL_WARN_FLAG] [nvarchar](1) NOT NULL,
[BUDGET_CONTROL_ENABLED_FLAG] [nvarchar](1) NOT NULL,
[INSUFFICIENT_FUNDS_FLAG] [nvarchar](1) NOT NULL,
[INTERNAL_TRANSFER_REQ_FLAG] [nvarchar](1) NOT NULL,
[CREATED_BY] [numeric](18, 0) NOT NULL,
[CREATION_DATE] [datetime] NOT NULL,
[LAST_UPDATED_BY] [numeric](18, 0) NOT NULL,
[LAST_UPDATE_DATE] [datetime] NOT NULL,
[LAST_UPDATE_LOGIN] [numeric](18, 0) NOT NULL,
[ENABLE_FLAG] [nvarchar](1) NULL,
[APPROVAL_STATUS] [nvarchar](50) NULL,
[APPROVAL_KEY] [nvarchar](50) NULL,
[APPROVAL_TYPE] [nvarchar](50) NULL,
[CATALOG_TYPE] [nvarchar](100) NULL,
[REQUESTER_ID] [numeric](18, 0) NULL,
[SHIP_TO_LOCATION_ID] [numeric](18, 0) NULL,
[DEFAULT_TAXATION_COUNTRY] [nvarchar](10) NULL,
[SHIP_TO_LOCATION_CODE] [nvarchar](60) NULL
) ON [PRIMARY]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_WITHDRAWN_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_CANCELED_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_RETURNED_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_REJECTED_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_INCOMPLETE_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_PENDING_APPR_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ((0)) FOR [REVISION_NO]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [FUNDS_CHK_FAIL_WARN_FLAG]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [BUDGET_CONTROL_ENABLED_FLAG]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [INSUFFICIENT_FUNDS_FLAG]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [INTERNAL_TRANSFER_REQ_FLAG]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('Y') FOR [ENABLE_FLAG]

0x01AA 5-Jun-23 6:46am

I would try backup/restore the database.

Member 15627495 5-Jun-23 6:56am

the ms sql Server need a ';' at end of query

it's probably a syntax error

S Shuaib Ahmed 5-Jun-23 6:58am

I have tried with a ; also no response.

Dave Kreskowiak 5-Jun-23 8:16am

No, it doesn't and it's not "probably a syntax error".

Member 15627495 5-Jun-23 7:00am

select * from SAP

S Shuaib Ahmed 5-Jun-23 7:03am

SAP is a schema

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2023-06-05T02:19:00

A simple SELECT * on a table that has 600 records should return almost immediately. This points to a problem with the database on the server, not any code you're using to execute the query.

Like has been suggested, try to backup the database and restore it to another SqlServer instance. Run the query against the new instance/restored database. If it works as expected, you've got problems with the database on the original server, which is something we can't help with.