I am trying to migrate a database from 2016 to 2014 through Task-->Generate Scripts. There are some stored procedures that are locked and seems like they are encrypted as well. They are causing error while generating the script. I have tried both schema or schema and data. I have also tried using Task--> Export Data tier application but that is generating error related to logins. Looking for an advice in terms of best way to do this or how to fix the above errors. Thanks in advance.

I've recently inherited a MSSQL database as our DBA has moved on to greener pastures. I'm not a DBA and have limited to moderate SQL knowledge but have had to absorb some of his duties and have an issue I am having some trouble navigating around. We have had an issue where users who access the database via Excel have a tendency to grind database operations to a halt and it impacts our ERP software. Our current method of resolution is to look at the activity monitor in SSMS and kill the offending Microsoft Office application. I'd like to have a scripted task that runs every few minutes to keep things moving and not have users flock to IT when the system goes down. I've found that the following query will allow me to pull the lock type and other useful data such as wait time but not the specific application found in the activity monitor. select * from sys.dm_exec_requests where wait_type = 'LCK_M_S' Ideally, this code would find the application Microsoft Office with the wait_type of LCK_M_S and kill the longest waiting task and repeat as needed throughout the day. Thank you in advance for your responses.

I came to a dead point in a deadlock analyze. According to msdn : RangeX-X are Exclusive range, exclusive resource lock; used when updating a key in a range. RangeI-N are Insert range, null resource lock; used to test ranges before inserting a new key into an index. So I understand that if I have an Index on 2 key columns - and I insert a new key I would have RangeI-N lock but if I update an existing key from the index I would have RangeX-X. But my question is more or less complicated. Say I have the index IX_keys_included on column A, B and included column C. In Serializable isolation mode I insert a new value for the included column C. Will there be RangeI-N or RangeX-X locks for the index IX_keys_included? Actually , will there be any locks given the fact that I insert a new column for an included column in the index?

I have blocked sessions and see which session is blocking them, having session_id and serial#. Is it possible to connect to such session? How? Background: using V$SESSION_BLOCKERS I see sessions are blocked by each other by INSERT statements to one of two tables: _REQUEST and _RESPONSE Blocked sessions wait event: enq: TX - row lock contention Query 1: insert into _REQUEST (creation_date, IS_PROCESSED, name, packet, PARENT_SKID, BATCH_SKID, retry_delay, revision, SERVICE_NAME, ttl, type, REQUEST_SKID) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 ) Query 2a (from DB trigger, :B1 is a payload): SELECT COALESCE(ERROR_DETAILS, ERROR_MESSAGE, FAULT_REASON, SOAP_ERROR) AS ERROR_MESSAGE, ERROR_CODE FROM XMLTABLE( '//*:DataHandlerWebServiceException | //*:Fault | //*:PLMDataHandlerError' PASSING XMLTYPE(:B1 ) COLUMNS ERROR_DETAILS VARCHAR2(256) PATH '*:errorDetails' ,ERROR_MESSAGE VARCHAR2(256) PATH '*:errorMessage' ,ERROR_CODE VARCHAR2(8) PATH '*:errorCode' ,FAULT_REASON VARCHAR2(256) PATH '*:Reason/*:Text' ,SOAP_ERROR VARCHAR2(256) PATH '*:COMMON_LOG_MESSAGE/*:LOG_MESSAGE' ) Query 2b (we already know APPEND is ignored here, see Note 1): INSERT /*+ append */ INTO _response( RESPONSE_SKID, REQUEST_SKID, HAS_FAILED, WAS_TIMEOUT, IS_PROCESSED, SOAP_REQUEST_TIMESTAMP, SOAP_RESPONSE_TIMESTAMP, RESPONSE_HTTP_STATUS, ERROR_MESSAGE, RESPONSE_BODY ) values ( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 ) How is it possible that INSERTs, to different tables, are blocking the sessions? I was looking into V$ACTIVE_SESSION_HISTORY but don't find anything yet (I'm not a DBA yet) Note 1: APPEND hint ignored

If I have a query that reads from a table, if that query is proceeded with the WAITFOR keyword (within the same batch and transaction) will the table lock of the preceding query be held until the WAITFOR completes and the transaction finishes? (I mean in the context of a Read Committed isolation level server.) Example query: BEGIN TRANSACTION SELECT * FROM ReallyLargeTable WAITFOR DELAY '00:30'; COMMIT TRANSACTION Does the WAITFOR DELAY cause the preceding query to hold its table lock against ReallyLargeTable for an additional 30 seconds until the delay is over and the transaction is committed?

I've been seeing some very basic updates timing out lately and have not been able to determine the cause. An example: //# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 UPDATE photos SET position = position + 1 WHERE (photo_album_id = 40470); The same log has no entries with a Lock_time > 0. Running show innodb status does not reveal any related locks either. This problem appears to be affecting at least 5 different tables based on my app server logs (which show a Mysql::Error: Lock wait timeout exceeded error related to each corresponding entry in the mysql-slow log). Any idea on where to go from here? I'm hitting dead-ends in all directions. Thanks. EDIT:

CREATE TABLE photos (
  id int(11) NOT NULL auto_increment,
  type varchar(255) NOT NULL,
  photo_album_id int(11) NOT NULL,
  user_id int(11) NOT NULL,
  title varchar(255) default 'Untitled',
  description text,
  credit varchar(255) default NULL,
  photo_file_name varchar(255) default NULL,
  photo_content_type varchar(255) default NULL,
  photo_file_size int(11) default NULL,
  photo_updated_at datetime default NULL,
  position int(11) default '0',
  views int(11) default '0',
  folder varchar(255) default NULL,
  published tinyint(1) default '0',
  published_at datetime default NULL,
  created_at datetime default NULL,
  updated_at datetime default NULL,
  album_published tinyint(1) default '0',
  comment_count int(11) default '0',
  audio_file_name varchar(255) default NULL,
  audio_content_type varchar(255) default NULL,
  audio_file_size int(11) default NULL,
  audio_updated_at datetime default NULL,
  cover tinyint(1) default '0',
  slug varchar(255) default NULL,
  comments_count int(11) default '0',
  delete_from_s3 tinyint(1) default '0',
  batch int(11) default NULL,
  audio varchar(255) default NULL,
  PRIMARY KEY  (id),
  KEY index_photos_on_album_published (album_published),
  KEY index_photos_on_batch (batch),
  KEY index_photos_on_comment_count (comment_count),
  KEY index_photos_on_created_at (created_at),
  KEY index_photos_on_delete_from_s3 (delete_from_s3),
  KEY index_photos_on_photo_album_id (photo_album_id),
  KEY index_photos_on_published (published),
  KEY index_photos_on_published_at (published_at),
  KEY index_photos_on_type (type),
  KEY index_photos_on_user_id (user_id)
) ENGINE=InnoDB AUTO_INCREMENT=42830 DEFAULT CHARSET=utf8

I found - https://dba.stackexchange.com/q/126734/104401 but there are no answers and isn't exactly the same as my question (though very similar). ------- Let's say I do the following: 1. Create a function myfunc() 2. Start a transaction from client A 3. Start a transaction from client B 4. In transaction B, use "create or replace function" to revise the definition of myfunc() 5. Commit transaction B 6. Call myfunc() from transaction A **What happens in step 6?** Am I calling the original function as defined in step 1? Or the modified form from step 4 (committed in step 5)? ----- And if the function is dropped in step 4 rather than being modified, will step 6 fail or succeed? (This is probably the same question but modifications may work differently.) ---- Where is the documentation about this?

How can I view real-time locks in innodb when information_schema.innodb_locks is not available?

I'm working with SQL Server 2012 SP2 and I have a problem with a stored procedure. I have a table where we do a lot of things in a second: we insert and update rows very often. I have a stored procedure to update a row, and it works fine most of the time, but sometimes it doesn't update a row but @@ERROR is zero. This is the update statement (@isAuto is another parameter): -- Set new Commisioning Flag value depending on is an auto or manual read. if (@isAuto = 1) set @newCommFlagValue = 20 -- Commissioning Auto else set @newCommFlagValue = 120 -- Manual Commissioning. [ ... ] -- Get Code PK to do fast queries. @code is a stored procedure parameter -- Serial has an unique constraint set @codeId = (select CodeId from Code where Serial = @code); -- Get current flag. SET @currentCommFlag = (SELECT CommissioningFlag FROM Code WHERE CodeId = @codeId); if (@currentCommFlag is null) begin if (@@TRANCOUNT > 0) rollback transaction SELECT @message = 'ReadCode ' + @code + ' return -17'; EXEC master..xp_logevent 60000, @message, informational; return -17 -- @code doesn't exist on Code table. end [ ... ] begin try if (@helperCodeId is not null) UPDATE Code SET CommissioningFlag = @newCommFlagValue , Source = @source , UserName = @username , LastChange = CAST(SYSDATETIMEOFFSET() as nvarchar(50)) , SentToNextLevel = 0 , HelperCodeId = @helperCodeId WHERE CodeId = @codeId else UPDATE Code SET CommissioningFlag = @newCommFlagValue , Source = @source , UserName = @username , LastChange = CAST(SYSDATETIMEOFFSET() as nvarchar(50)) , SentToNextLevel = 0 WHERE CodeId = @codeId SELECT @ErrorVar = @@ERROR , @RowCountVar = @@ROWCOUNT; end try begin catch SELECT @message = 'ReadCode ' + @code + ' error ' + ERROR_NUMBER() + ' - ' + ERROR_MESSAGE(); EXEC master..xp_logevent 60000, @message, informational; end catch -- If it hasn't been updated... IF @ErrorVar != 0 BEGIN SELECT @message = 'ReadCode ' + @code + ' error update Code'; EXEC master..xp_logevent 60000, @message, informational; SET @code = NULL if (@@TRANCOUNT > 0) rollback transaction RETURN -1 END else begin if (@@TRANCOUNT > 0) commit transaction; if @RowCountVar = 0 begin SELECT @message = 'ReadCode ' + @code + ' - no actualizado'; EXEC master..xp_logevent 60000, @message, informational; return -1; end else begin set @commFlag = (Select CommissioningFlag from Code where CodeId = @codeId); SELECT @message = 'ReadCode ' + @code + ' - flag ' + CAST(@commFlag as varchar(2)) + ' - return 0'; EXEC master..xp_logevent 60000, @message, informational; return 0; end end There isn't any transaction on the stored procedure. Maybe the table is locked by an insert or another process. After stored procedure execution I do a select for that row and it has the previous value on CommissioningFlag column. I'm sure this stored procedure works because it updates nearly 98% of the rows. But there is a 2% that it isn't updated. XACT_ABORT is not set. Do you why an Update doesn't update a row but it doesn't return an Error? I've been investigating what it is happening and I have seen something very interesting: Row number 5 hasn't been updated. This is the error. My program first update row 1, then row 2, etc. Each update triggers 0.15s. Update between row 4 and row 6 gets 0.30s (more or less). It seems that row 5 is updated in 0.15s without any delay but it isn't stored the change in database.

I have discovered a strange apparent bug in MS Access and would like to know if others know it already and hopefully how to get around it. Trying to count records based on known column categories. One particular set of records can't be saved or output because of apparently one or more records in that range. They appear to have some corrupt character which is generating a false error message: "Another user is currently using this record" or words to that effect. Is there some obscure character (combination) that can cause Access to lock a record? It is physically impossible that anyone else actually has access to the DB. There could conceivably be all kinds of strange characters in there because the data come from a wide variety of sources. I think I have narrowed it down to a personal name field but this is not a final verdict...

The following query : select (buffer_descriptors.datacache) as 'Data Cache (MB)', case when buffer_descriptors.datacache > 4096 then (((buffer_descriptors.datacache)/4096)*300) else '300' end as 'Min Page Life Expectancy', perf_mon.ple_value as 'Actual Page Life Expectancy (sec)' from ( select (count(*)*8/1024) AS 'datacache' from sys.dm_os_buffer_descriptors where page_type in ( 'INDEX_PAGE' ,'DATA_PAGE' ) ) as buffer_descriptors, ( SELECT cntr_value as ple_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy' AND OBJECT_NAME LIKE '%Buffer Manager%' ) as perf_mon Is generating a SOS_SCHEDULER_YIELD resource wait and a drain on memory > blocking_session_id : 0 > wait_type : SOS_SCHEDULER_YIELD The client hasn't noticed any particular problems and the server appears relatively quiet. Any thoughts as to why a SELECT on dm_os_buffer_descriptors might cause a drain on memory and a SOS_SCHEDULER_YIELD wait ? **Update 11:39** Running a CHECKPOINT and removing buffers DBCC DROPCLEANBUFFERS resolved the blocking issue. Still don't understand why though. **Update 18:46** The blocking occured again so I took Jon's advice and set the ISOLATION level to READ UNCOMMITTED. This worked and allowed the query above to complete. Afterwards, I went hunting around for exclusive locks in dm_tran_locks but didn't find any. Running the dm_os_buffer_descriptors query a second time in READ COMMITTED didn't hang. Here is the query used to view active locks SELECT SessionID = s.Session_id, resource_type, DatabaseName = DB_NAME(resource_database_id), c.client_net_address, request_mode, request_type, host_name, program_name, client_interface_name, login_name, nt_domain, nt_user_name, s.status, last_request_start_time, last_request_end_time, request_status, request_owner_type, objectid, dbid, a.number, a.encrypted , a.blocking_session_id, a.text FROM sys.dm_tran_locks l JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id JOIN sys.dm_exec_connections c ON c.session_id = s.session_id LEFT JOIN ( SELECT * FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) ) a ON s.session_id = a.session_id Should I be able to see what's blocking os_buffer_descriptors using dm_tran_locks? If not, what should I use?

Off late, I've been facing a lot of row lock contentions. The table in contention seems to be a particular table. This is generally what happens - - Developer 1 starts a transaction from Oracle Forms front end screen - Developer 2 starts another transaction, from a different session using the same screen ~5 minutes in, the front end seems unresponsive. Checking sessions shows row lock contention. The "solution" that everyone throws around is to kill sessions :/ As a database developer - What can be done to eliminate row lock contentions? - Would it be possible to find out which line of a stored procedure is causing these row lock contentions - What would be the general guideline to reduce/avoid/eliminate such problems which coding? If this question feels too open-ended/insufficient information please feel free to edit/let me know - I'll do my best to add in some additional information. ----- The table in question is under a lot of inserts and updates, I'd say it's one of the most busiest tables. The SP is fairly complex - to simplify - it fetches data from various tables, populates it into work tables, a lot of arithmetic operations occur on the work table and the result of the work table is inserted/updated into the table in question. ---- The database version is Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit. The flow of logic is executed the same order in both the sessions, the transaction isn't kept open for too long ( or at least I *think* so), and the locks occur during active execution of transactions. ----- **Update:** The table row count is larger than I expected, at about 3.1 million rows. Also, after tracing a session I found that couple of update statements to this table are not utilizing the index. Why is it so - I'm not sure. The column referenced in the where clause is indexed. I'm currently rebuilding the index.

One of my queries is running in to a Row Lock Contention. I've tracked down which table is causing that - so now what's the next step ? Which session is causing this table to get locked ? How do I remove the lock ? I'm using Oracle 10g