November 15, 2002 at 12:01 pm
Hi Guys
one of my application locking these objects
sp_setapprole
sp_add_log_file_recover_suspect_db
sp_changeowner
sp_createstats
sysobjects
sp_prepare etc
is it normal ?
November 15, 2002 at 2:22 pm
It is if that app is creating a database.
November 15, 2002 at 2:44 pm
How long are the locks held? I wouldn't expect most of these to run long.
Steve Jones
November 16, 2002 at 12:03 am
Application is not creating database
Most of the time i see these locks
particularly
sp_setapprole
sp_add_log_file_recover_suspect_db
Thanks
November 16, 2002 at 5:10 am
Just a thought is sp_add_log_file_recover_suspect_db used when the log file has to grow?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 16, 2002 at 8:29 am
The stored procedure sp_setapprole is definitely not unusual at all. If you have an application role setup on a database, sp_setapprole is what is used to activate it.
The following stored procedures are not usual unless databases are getting created:
sp_add_log_file_recover_suspect_db
sp_changeowner
sp_createstats
The text on sp_add_log_file_recover_suspect_db from BOL reads:
quote:
Adds a log file to a filegroup when recovery cannot complete on a database due to an "insufficient log space" (9002) error. After the file is added, this stored procedure turns off the suspect setting and completes the recovery of the database. The parameters are the same as those for ALTER DATABASE ADD LOG FILE.
Locks on sysobjects usually occurs when objects are getting created. If you use temp tables, you'll see sysobjects get locked in tempdb.
The stored procedure sp_prepare is not usual either if a query is being prepared. It's not documented in the BOL but it's not all that unusual either.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 16, 2002 at 2:08 pm
There is no Application Role . Server is SQL SERVER 7.0 on Windows NT .I saw the profiler
there no as such commands calling those objects. Again your reply greatly appreciated
November 16, 2002 at 2:27 pm
I wouldn't know of any system functions that would require an application role, so it doesn't make sense a lock is occuring on the stored procedure for that. I would agree that what you are seeing is a bit unusual. At this time, though, I'm not sure locks against these objects are a cause for concern. Here is a question, what are you using to see the locks? EM?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
November 16, 2002 at 5:31 pm
Yes, how are you seeing the locks to know they are there?
November 17, 2002 at 8:12 pm
I seeing these thrrough EM , sp_lock and
some of scripts from discussion forum which goes agains syslock and sysprocess system tables
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply