Connecting Trace Info To Blocked Users?
Sep 22, 2005
In master.dbo.sysprocesses I can filter for blocked users (Blocked >0)
and I can create a self join(ON SPID = Blocked) to see what user
Loginame is causing the block. In the column [cmd] I can see the
command that the offending blocker is running to cause the block, but
it only says "SELECT" etc. with no details about the stored procedure
that is causing the block.
If I am running a Trace, I can see the exact stored procedures
including the parameters that every cmd is running.
Is there a way to see that same Trace information when looking for
blocked users in master.dbo.sysprocesses, or in some other place?
Ideally what I want is a list of blocked users, who is causing the
blocks and the stored procedure name (or other mischief) causing the
block.
Any help is appreciated.
lq
View 17 Replies
ADVERTISEMENT
Sep 26, 2007
We are using Navision with SQL server 2003.
What kind of methods is there out there to reduce hwo often this happens?
View 10 Replies
View Related
Feb 19, 2004
Folks, I've inherited a SQL 2K server that has a trace running. I can see the trace file being generated(.trc), I can also see the standard templates(.tdf). But BOL is not helpful in finding information about the
trace when you have no information. I just want to shut the damn thing off.
I can open the trc file in SQL Profiler but I am not proficient in the tool's use. I also don't see a trace table anywere in the database.
If anyone can be of any help it would be much appreciated. Thanks.
View 7 Replies
View Related
Jul 25, 2001
I know how to look at active users from Enterprise Manager, but how can I query out the information - what table is that info stored in ??? I don't want a list of all the logins, I just want the list of active users....
Help... thanks in advance,
Nancy
View 2 Replies
View Related
Dec 21, 2006
Hi,
The Report Manager portal has many folders. For each folder there are specific users with different roles.
I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.
Use ReportServer
SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r
WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID
The above query fetches all the users and their roles.
The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query.
TIA
View 1 Replies
View Related
Apr 15, 2008
I have an item table that tracks changes to the item's value:
item_ID, cal_ID, item_value
The cal_ID is sequential in calendar period order.
Every item has at least one record with a cal ID of 0 and the starting value. When the value changes, a new record is written to the table with the item's ID, the cal_ID of the period in which the change occurred, and the new item value
I need to be able to join this to the calendar so I can get the item value for every item for every calendar period. I'm trying to come up with the join syntax that will give me one record for each cal_ID and item_ID with the item_value that was in effect for that cal_ID
If the item table had a begin and end cal_ID on the record, it'd be a straightforward BETWEEN subquery. But all I have is the begin period.
I've been wracking my brain for five hours. Any suggestions?
View 5 Replies
View Related
Jan 31, 2008
Hi Patrons,
Can any one tell me how and what are the steps to Clone the Database and how do the set of users (lets say 300) will be connected to that Cloned Database.
Your helping steps would be appreciated.
Thanks
View 1 Replies
View Related
Jan 2, 2007
I'm trying to figure out how to associate users in my ASPNETDB.MDF to create a shopping cart.IE: I have 3 tables, for a list of existing orders, list of products, and list of specific orders.The idea is for users who've signed up with the built-in user creation control, can then place orders from the shopping cart, and view their existing previous orders. This would normally be done with a unique UserID # to associate orders with specific users, however I don't BELIEVE there's anything like UserID's associated with each user in the ASPNETDB.MDF, it just uses usernames as the unique identifiers right?Anyway I'm not sure I'm explaining what I'm trying to do perfectly and I apologize, but I don't think it's an un-common problem I'm having. Basically I'm trying to avoid having to have existing ASPNETDB users create a sort of SECOND user that would associate them into a seperate Customers table, it seems like extra user steps that shouldn't be necessary.
View 1 Replies
View Related
May 7, 2007
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
ddave
View 3 Replies
View Related
Jul 28, 2015
We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
View 5 Replies
View Related
Mar 6, 2008
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies
View Related
Sep 28, 2015
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
View 2 Replies
View Related
Apr 30, 2008
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country}
who has an idea how to solve this problem?
View 9 Replies
View Related
Dec 7, 1999
Hello...
Is it normal in SQL Server 6.5 the user who only running the query blocking the other user who try to update/add the records?
note: The query is a complex SQL.
Many Thanks!
View 2 Replies
View Related
Mar 17, 1999
We are using SQL server 6.5 and currently have about 100 users connections at a given
point in time. The application is Visual Basic 5.0 based and it allows users to create
MS Word documents from the application. These documents names are stored in a table which basically
acts as a reference table. Every time a document needs to be attached to a record
this table is called with an insert/update query. This results in an exclusive page/table
lock and ends up into a blocked process. This finally results into a major halt for
all the system users.
Manual killing of these blocked processes frees up the resources and brings things back to normal
although disruptive to the users.
Any clue as to why the blocked processes are not able to free themsevles up ?
Are we missing something in our SQL configuration that will help us with unblocking
these processes ?
View 1 Replies
View Related
Feb 27, 2002
Hi,
Issuing 'dbcc shrinkfile (log ,truncateonly)'
I get the error message below. Books online doesn't say any more.
Can anyone explain? I am the only user connected to the db at the time, no jobs are executing.
Cannot shrink log file 2 (log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
View 1 Replies
View Related
Oct 6, 1998
What is SPID -1? I see nothing listing it in any activity monitor, but it has just recently started blocking some processes.
Thanks,
jim craddock
View 3 Replies
View Related
Apr 5, 2006
I'm running a heavy SELECT query using WITH (NOLOCK). This still causes other processes trying to INSERT in one of the tables to get blocked.
I thought the locking hint would prevent from blocking other processes?
View 4 Replies
View Related
Jun 29, 2006
I am getting the following error message when I tried installing the 2005 Developer Edition from the MSDN kit. I previously installed the 2005 Express Edition, and it worked just fine, so I thought that the install failed as it was at a lower version than the Express Edition. I uninstalled the SQLExpress Edition, however, when I try to install SQL Server 2005 again, I get the following message:
Name: Microsoft SQL Server 2005 Tools
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
Name: Microsoft SQL Server 2005 Tools
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
I have also installed Visual Studio NET, and I wonder if some component in the Studio app is holding back the SQL Server install. I have uninstalled all SQL components several times already, and I keep on getting this message.
Gregory
Gregory
View 1 Replies
View Related
May 4, 2008
Hi all,
I have an SQL Server 2005 mirroring config with "High availability". Twice over the past four weeks, the principal server started logging to the event log:
"All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT" I checked the database activity monitor and indeed there were lots of processes in DBMIRROR_DBM_EVENT.
The database was totally unresponsive to queries (all got timeouts), and no failover was done.
This are the log entries for all day:
05/04/2008 08:40:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:39:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:38:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:37:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:36:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:35:44,Server,Unknown,All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on DBMIRROR_DBM_EVENT. Process Utilization 0%.
05/04/2008 08:33:49,Server,Unknown,The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.
05/04/2008 03:24:02,Backup,Unknown,Log was backed up. Database: TeleCable<c/> creation date(time): 2007/08/24(13:38:00)<c/> first LSN: 205089:18926:1<c/> last LSN: 209688:18463:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'\face01f$logbackup'}). This is an informational message only. No user action is required.
05/04/2008 00:00:14,spid24s,Unknown,This instance of SQL Server has been using a process ID of 1700 since 4/9/2008 3:18:54 AM (local) 4/9/2008 1:18:54 AM (UTC). This is an informational message only; no user action is required.
After that, the same entry once per minute.
I restarted the database and everything came to normal.
As you can imagine, the problem is really serious.
Should I forget about mirroring? Anyone has experienced something similar?
Thanks in advance and best regards.
View 11 Replies
View Related
Nov 28, 2006
Hi Team,
In SQL Enterprise Manager, when we expand "Database -->Users", we see the
users there. When we expand "Security --> logins" we see the same users there.
Can you differentiate these two.
Thanks
Santhosh
View 1 Replies
View Related
Jun 10, 2004
Hi guys,
I have a stored procedure which generates the next sequence number...
it uses SERIALIZABLE Option. procs look something like below..
begin
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
Sequence generating statement...
COMMIT TRANSACTION
set @NextSequenceValue = @NextSequenceValue
Return @NextSequenceValue
end
For some reason when i call the proc with below parameters to get next sequence number.. its hungs up..
declare @NextSequenceValue int
set @NextSequenceValue = 0
exec spGetNextSequence 19, 'LotSequence', @NextSequenceValue output, Null
select @NextSequenceValue as NextSequenceValue
When i queried sp_who2 it shows that my processid is blocked by some other processid.. and when i do DBCC INPUTBUFFER (blockingprocessid), the query of blocking processid and my nextsequence generation stored proc is not realted at all..
Can you help shed some light on why my nextsequence generating proc is getting hunged...?
help is appericated..
View 4 Replies
View Related
Jul 21, 1999
If I kill a blocked process, why does the current activity window still show the process? Both processes, blocking and blocked, are scheduled tasks. Also, the blocked process is still listed as a running task in the manage scheduled task window.
View 1 Replies
View Related
May 10, 1999
The following is an extract from sp_who2
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO
----- ---------- ------- -------- ----- ------- ------- ------- ------
162 sleeping om18682 . -1 STIPROD SELECT 236 120
Has anybody experienced a process being blocked by SPID -1.
View 4 Replies
View Related
Oct 18, 2007
Hi,
I have a fairly complex application running which has numerous connections on various threads accessing my sql 2005 database.
Every now and then I find that one of my connections is being blocked by process with a pid of -2.
I assume this is some system process.
The only way I can get my application going again is to restart the sql server.
Can someone tell me what process -2 is?
cheers,
Robert
View 2 Replies
View Related
Sep 25, 2006
HiShortly, I keep invoices in a table.Occasionally, someone will fire the execution of a stored procedure(SP) that performs several UPDATEs against (potentially) all invoicesOLDER than a date that is supplied to the SP as a parameter.The SP is usually a lengthy process (it takes at least 30 mins).The problem is that SQL server 2000 Dev Edition doesn't allow me toinsert new invoices that are "younger", while the SP is executing.How should I specify to SQL Server that new invoices are "harmless"?Thanks.
View 8 Replies
View Related
Jul 20, 2005
I have written a stored procedure to list out all tables in whichrows or the table itself is locked. The only information I amnot able to get is the time when the lock occurred. The way Iwant is that if I run the procedure it should show all lockson a table which are at least 5 or x seconds old. This way I canavoid momentary locks on a table which go away after few seconds.Which table and column of master database has that information?Thanks.--email id is bogus
View 3 Replies
View Related
Oct 11, 2006
Hello,
I am trying to execute next query, but when doing it, TABLE1 locks and it does not finish.
SERVER2 is a linked server.
BEGIN TRAN
INSERT INTO TABLE1
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2 WHERE TAB_F1 IS NULL
COMMIT TRAN
I have same configuration in other 2 computers and it works ok.
What is the problem?
Thank you!!
View 8 Replies
View Related
Apr 2, 2007
Hi guys,
we have a database here and something happened which causes a database block. We tried to run the 'sp_who' 'active' command to see the spid which locked the database, and we found out that some transaction is blocking another transaction. The following is the sample data results from the sp_who 'active'
spid ecid status loginame hostname blk dbname cmd
52 0 sleeping HOSTINGSQLMonitor BLUE2 185 tempdb INSERT
53 0 sleeping sa 10.10.10.106 185 mfgq_live SELECT
56 0 sleeping sa 10.10.10.106 175 mfgq_live UPDATE
57 0 sleeping sa 10.10.10.143 185 mfgq_live SELECT
We killed all spid which casuse the blocking, but they are keep on coming.
Does anybody have any idea on what casuses this problem or a teporary solution for this? Please help.
Thx
View 1 Replies
View Related
Jul 31, 2007
Hi
On one of my SQL servers (SQL 2000 SP4) i have a problem with spid blocked by itself (spid 54 (Blocked by 54)).
On the server it's running a verry simple UPDATE statement (from the Query Analizer)'. The query needs very long time to finalize (on SP3 it was very fast). I searched some forums regarding this issue and i found that maybe the SP4 can cause this problem.
Do you have any idea or advice?
Thanks
View 17 Replies
View Related
Feb 20, 2007
I'm getting the following error when I try to run SQL05 SP2 on my server. When I get to the Authentication screen, I select Windows Authentication and check the box to use for all services. I am logged in using my personal admin account. I click the 'Test' button. Connection to Database Services, instance name 'MSSQLSERVER' is successful. However, connection to Reporting Services, instance name 'myserverSSRS' fails. The error message is: "Login failed: HResult 0x2, Level 16, State 1, Named pipes provider: Could not open a connection to SQL Server[2]. Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server."
I've had problems getting Reporting Services to work. Is this message a result of SSRS not being set up properly? How can I correct this?
View 7 Replies
View Related
Apr 17, 2007
I have an SSIS package that is basic in nature. It imports a flat file and makes some inserts to tables via OLE destination and also updates some tables via stored procedure calls using params. Sometimes the process "locks" and never finishes. When it locks it gets locked by process "-2" or a value that is not an actual process. What does this mean? What is -2?
View 3 Replies
View Related
Jun 8, 2007
Message: Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.
I am using VB2005 Express. I had, evidently, a simpler version of SQL but deleted it, as was recommended, and then downloaded SQL 2005 Express. Now when trying to open an SQL database in a project I get that Information Message.
FURTHERMORE after loading the new SQL it wiped out all of my projects in VB 2005. The back up files were wiped out also.
That was naughty!
Thank you in advance for a reply.
gascitydan@yahoo.com
View 3 Replies
View Related
