September 24, 2013 at 7:11 am
Here is my table script:
USE [MyDB]
GO
/****** Object: Table [dbo].[USER_MAC_MAPPING] Script Date: 09/24/2013 18:34:41 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USER_MAC_MAPPING]') AND type in (N'U'))
DROP TABLE [dbo].[USER_MAC_MAPPING]
GO
USE [MyDB]
GO
/****** Object: Table [dbo].[USER_MAC_MAPPING] Script Date: 09/24/2013 18:34:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[USER_MAC_MAPPING](
[MAPPINGID] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NOT NULL,
[mac_id] [nvarchar](50) NOT NULL,
[created_by] [int] NULL,
[created_on] [datetime] NULL,
[modified_by] [int] NULL,
[modified_on] [datetime] NULL,
CONSTRAINT [PK_USER_MAC_MAPPING] PRIMARY KEY CLUSTERED
(
[MAPPINGID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [constraint_name] UNIQUE NONCLUSTERED
(
[userid] ASC,
[mac_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
my issue is : when I run select query it keeps on showing message "Executing Query" and never ends.
This is a newly created table .....there are no data .....or may be maximum 1 record
I tried also select count to print number of records .....this also does not work....it keeps on showing message "Executing Query" and never ends..
what is the issue ? how to fix it ?
September 24, 2013 at 7:58 am
Maybe you have an open transaction? What is the result of this query? select @@TRANCOUNT
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2013 at 8:11 am
One of these queries may help.
--== Locks held in database ==--
SELECT request_session_id AS [spid], DB_NAME(resource_database_id) AS [dbname],
CASE WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN 'n/a'
ELSE OBJECT_NAME(p.object_id) END AS [entity_name], index_id, resource_type AS [resource],
resource_description AS [description], request_mode AS mode, request_status AS [status]
FROM sys.dm_tran_locks t
LEFT OUTER JOIN sys.partitions p ON p.partition_id = t.resource_associated_entity_id
WHERE t.resource_type <> 'DATABASE';
--== Blocking queries in database ==--
SELECT DES.Session_ID AS [Root Blocking Session ID], DER.STATUS AS [Blocking Session Request Status],
DES.Login_Time AS [Blocking Session Login Time], DES.Login_Name AS [Blocking Session Login Name],
DES.Host_Name AS [Blocking Session Host Name], Coalesce(DER.Start_Time, DES.Last_Request_Start_Time) AS [Request Start Time],
CASE WHEN DES.Last_Request_End_Time >= DES.Last_Request_Start_Time THEN DES.Last_Request_End_Time ELSE NULL END AS [Request End Time],
Substring(TEXT, DER.Statement_Start_Offset / 2, CASE WHEN DER.Statement_End_Offset = - 1 THEN DataLength(TEXT) ELSE DER.Statement_End_Offset / 2 END) AS [Executing Command],
CASE WHEN DER.Session_ID IS NULL THEN 'Blocking session does not have an open request and may be due to an uncommitted transaction.'
WHEN DER.Wait_Type IS NOT NULL THEN 'Blocking session is currently experiencing a ' + DER.Wait_Type + ' wait.'
WHEN DER.STATUS = 'Runnable' THEN 'Blocking session is currently waiting for CPU time.'
WHEN DER.STATUS = 'Suspended' THEN 'Blocking session has been suspended by the scheduler.'
ELSE 'Blocking session is currently in a ' + DER.STATUS + ' status.'
END AS [Blocking Notes]
FROM Sys.DM_Exec_Sessions DES(READUNCOMMITTED)
LEFT JOIN Sys.DM_Exec_Requests DER(READUNCOMMITTED) ON DER.Session_ID = DES.Session_ID
OUTER APPLY Sys.DM_Exec_Sql_Text(DER.Sql_Handle)
WHERE DES.Session_ID IN (SELECT Blocking_Session_ID
FROM Sys.DM_Exec_Requests(READUNCOMMITTED)
WHERE Blocking_Session_ID <> 0 AND Blocking_Session_ID
NOT IN (SELECT session_id
FROM Sys.DM_Exec_Requests(READUNCOMMITTED)
WHERE Blocking_Session_ID <> 0
)
);
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply