SQL Server Admin 2014 :: SPID Causing Locks With No Query Text Shown
Oct 1, 2015
I just ran into an issue with cascading locks due to a SPID on one of my production servers. When researching the lock, I noticed that there was no sql text. SP_Who 2, nor the following query captured anything,
SELECT sqltext.TEXT
, sqlplan.query_plan
, req.session_id
, req.status
, req.command
[Code] ....
I spoke to the user causing the lock and he ran into a visual basic error when this occurred and didn't close out that window. So my guess is that it's due to an uncommitted transaction. However, shouldn't I still see something if that was the case?
View 4 Replies
ADVERTISEMENT
Feb 11, 2015
I have a sql snippet from a 3rd party application that will not complete its transaction. The SELECT statement executes but does not finish. Instead the statement just sits in AWAITING COMMAND for 1000 seconds then dies, thus killing the UPDATE statement that is supposed to follow.
The CROSS JOIN and CROSS APPLY seem suspect.
(
@p0 DATETIME,
@p1 INT,
@p2 INT,
@p3 NVARCHAR(4000),
@p4 INT,
[code]....
View 9 Replies
View Related
Apr 2, 2015
I have a production DB that all of a sudden it seems that any and every insert causes massive locks/blocks.
If I kill the offending spid anther spids pops up with the block/lock.!
View 7 Replies
View Related
Aug 6, 2015
I need to restore a SQL 2008 db on SQL 2014 instance with out upgrading the database(changing compatibility level).
This database has full text enabled. I see one full text catalog an 2 full text indexes.
Do I need to worry about anything or can I perform a clean restore from the backup with full text import?
Do I need to rebuild the catalog in this case?
View 1 Replies
View Related
Oct 15, 2015
I need to modify a table to reside on a new filegroup and also point TEXTIMAGE_ON to that filegroup instead of PRIMARY. Apparently in the past, the only way to achieve this via SQL is to create a new table, copy over data, drop the old table and rename the new table to the original name. I found this solution in the SQL Server 2005 forum.
Is there any other way to alter this table in order to point the TEXTIMAGE_ON to new filegroup using SQL Server 2014? We are on Standard edition. The technique I am using is the drop constraint (with move option) and add constraint (to new filegroup) commands. The data and indexes move, but not the text data (it still is in primary filegroup).
View 0 Replies
View Related
Oct 28, 2015
We are running SQL 2014 SP1. We are using defined Full text indexes on several tables in the database. However, on one specific set of servers, a certain search will not return any data. This exact same search works on another set of servers built identically. The first responses I'm sure will be stop list, but I have dropped and recreated the FTI multiple times with different stop lists or no stop list at all and get the same results.
The specific word being searched on is YUM. If I change the value to YUMk, it actually returns, and if I change the data to TUM it returns, but YUM does not. This exact query is working on multiple other systems, so it seems to be something environmental, but I haven't been able to pinpoint it.
View 3 Replies
View Related
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
Feb 6, 2014
Query to get CU# of sql server Instance ?
View 3 Replies
View Related
Mar 13, 2014
I can easily query multiple servers using the multi-server query function in Central Management Server and write some of the results to logging tables. I would like to be able to do this via a scheduled job. So far I am finding that even setting up Master/Target Servers this may not work and the only workaround is either using SSIS, SQLCMD (by basically hard coding the servername) and possibly Powershell.
tell me if they have been successful just using standard jobs and querying against multiple servers?
If I can't save the results to a 'central' database/table (I can do this when in SSMS), but can still query against multiple servers I was thinking I could write the results to a CSV file that a SSIS job picks up.
I have attempted using SSIS to iterate through servers and have been plagued with intermittent connection issues when using a For...Loop container.
View 1 Replies
View Related
Feb 7, 2015
I have this command :
Exec 'update .... insert ..... delete ..... insert ...'
I Execute these command in one execution.
exec ('...')
Are these commands act as a transaction? If one of them create error , another commands run or rull backed?
View 1 Replies
View Related
Jul 27, 2015
Is there any single TSQL query which provides below info.When did my AlwaysOn Availability group failed over and from which node it failed to which new node(i.e. replica)?
View 3 Replies
View Related
Jul 20, 2002
Here's a quick script that will return the inputbuffer of processes that are holding more than a certain threshold of locks (by default, 10, but it's easy to modify). Handy for tracking down what query is holding those 1400 locks.
Yes, it uses a cursor. I'd welcome amendment by anyone who has the insight on converting it to pure set-based.
Cheers
-b
CREATE PROCEDURE sp_lock4 AS
set nocount on
DECLARE @tSpids table(spid int PRIMARY KEY CLUSTERED,count int)
DECLARE @iSpid int,@iCount int
insert into @tSpids (spid,count)
select convert (smallint, req_spid) As spid,
count(*) as count
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
group by converT(smallint,req_spid),'dbcc inputbuffer(' + cast(req_spid as varchar(4)) + ')'
having count(*)>10
order by count(*) desc
DECLARE cLoop cursor for
select spid,count from @tSpids
OPEN cLoop
FETCH NEXT FROM cLoop INTO @iSpid,@iCount
WHILE @@FETCH_STATUS=0
BEGIN
select 'spid ' + cast(@iSpid as varchar(4)) + ' has ' + cast(@iCount as varchar(5)) + ' locks.'
exec ("dbcc inputbuffer (" + @ispid + ")")
FETCH NEXT FROM cLoop INTO @iSpid,@iCount
END
CLOSE cLoop
DEALLOCATE cLoop
return (0) -- sp_lock
GO
View 1 Replies
View Related
Sep 21, 2015
I want print state of running query to output, because my query need long time to run. But print statement dos not work correctly!!
Note: I cannot use "go" in my query!
/* My Query */
print 'Fetch Data From Table1 is Running...'
insert into @T1
select x,y,z
from Table1
print 'Fetch Data From Table1 Done.'
[Code] .....
View 1 Replies
View Related
Oct 29, 2015
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;
[Code] ....
View 8 Replies
View Related
Feb 12, 2014
I would like to find Query to retrieve User DataBase Startup Time from SQL server that we see in the Sql Server Log.
I was able to find the Server Startup Time but not the individual Databases .
View 1 Replies
View Related
Mar 18, 2015
How to convert float to timestamp in single select query
E.g., i have float as 1.251152515236 ,
I want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00
View 1 Replies
View Related
Aug 27, 2015
I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
View 0 Replies
View Related
May 18, 2015
Over the weekend I decided to give it the ability to do a case sensitive character swap. Updating the code was pretty straight forward but when I was through, I noticed that I was getting Cardinality Estimate warnings that I wasn't getting before.
Anyway, here is some test data and two versions of the executed SQL (the base code is all dynamic and the two code versions are the result of toggling the @MatchCase parameter).
/* ========================================
CREATE TABLE
======================================== */
CREATE TABLE [dbo].[PersonInfoSmall](
[PersonID] [BIGINT] NOT NULL,
[FirstName] [NVARCHAR](50) NOT NULL,
[MiddleName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NOT NULL,
[code]....
View 8 Replies
View Related
Apr 26, 2014
I have two records...is having pid=10
idpid
10110
10210
1035
1046
1065
then i executed the below query..
update Child set pid=10 where id in (101,102)
the sql server showing message like
(2 row(s) affected) but as per data no records updated so i need to change this message type
if i ran the above update query the the result should be like
(0 row(s) affected)
is there any way to change this...
View 3 Replies
View Related
Apr 14, 2015
I inherited a lot of Servers to upgrade to 2014 to include an SSRS Server.
The encryption Key was never backed up and it seems that no one knows what the password is?
Do I have to manually load the reports? There are a lot of Reports.
[URL]
View 4 Replies
View Related
Feb 25, 2014
There are times when I either compile a stored procedure, or right now as I am creating indexes on tables, when sp_who2 shows a SPID assigned to my name that I do not currently have opened in SSMS. When I check the statement with dbcc inputbuffer it shows a query hitting many of the system views. Earlier this week, someone tried killing one of these SPIDS and SQL Server generated a new SPID with the same type of query.
View 5 Replies
View Related
Sep 26, 2013
I want to use BCP to load data from a text file.
By default, constraints are turned off in bcp, so I use the CHECK_CONSTRAINTS hint.
bcp aborts if ANY of the rows contains a FK violation. No data get loaded.
So if I add the -b 1 batch size option, it loads all data UNTIL the first FK violation, but nothing after that.
I want to load EVERYTHING ... except for the violations. But bcp won't let me. Is there a way?
View 2 Replies
View Related
Jan 13, 2014
If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?
View 9 Replies
View Related
Mar 21, 2014
My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.
View 9 Replies
View Related
Jun 18, 2014
I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.
View 2 Replies
View Related
Jun 25, 2014
In our environment applications are using a DNS name which points to the physical server ip address. Now we are planning to move to 2014. We are planning to have servers in different subnets so we will be having two ip adresses for listener. How we can point the DNS to the listener ips? If failover happens can the DNS point to the exact ip address of the listener where it's primary node?
View 1 Replies
View Related
Jul 31, 2014
Is there a way to schedule a sql job to run at different intervals
For eg:
The job should run at
7:00 Am
8:00 AM
and then at 10:00 Am
View 3 Replies
View Related
Nov 19, 2014
We installed the Sql 2014 in Test server.
We observed frequently
"Process 0:0:0 (0x1e10) Worker 0x00000006B6D341A0 appears to be non-yielding on Scheduler 13. Thread creation time: 12906028806348. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 13%. System Idle 84%. Interval: 70189 ms."
Is it better to run the profiler or performan counter?
What are the filters we have to select in the profiler to monitor the Sql server
View 0 Replies
View Related
Dec 8, 2014
I have a SQL server box running 2014 reporting services. I have another server running IIS v8.
I would like to be able to connect to the IIS site and be given the SSRS report browser.
So externally if I browse to [URL], I am presented with the report server interface, the same as if I browse to http://xxx.xxx.xxx.xxx/reports internally.
What are my options?
View 4 Replies
View Related
Dec 11, 2014
What is the best approach for a read only copy of a database that is ~ 1TB. The primary database is fed nightly with an ETL process. We are currently trying to duplicate the ETL to read only server but that process is not going well. So we are looking at other options to let SQL make the copy.
The primary database is on a Win12R2 with SQL 12 or 14, a 2 node A/P failover cluster.
The read only copy will be on a Win12R2 with SQL 12 or 14. It is not a requirement to fail over to the read only copy if the primary should go down.
What would best the approach to accomplish the end result?
View 3 Replies
View Related
Jan 12, 2015
I did not worked on AVG in sql 2012, what are the possible errors and how to resolve in always on in sql 2012 .
View 1 Replies
View Related
Jan 18, 2015
I have 10 databases which are configured as principal in mirroring I need to failover all the databases as part of failover , instead of writing query each database as parner failover, is an script which will generate the databases as principal to failover ?
View 3 Replies
View Related
Feb 18, 2015
How to check the delay in between replicas in AG?
View 3 Replies
View Related
