1

I am working on a SQL Server 2008 R2 server.

On this server are two instances (let's call them A and B).

A replicates one of it's databases to B using a transactional replication.

This replication however became inactive and the snapshot deleted after some maintenance occurred.

I have reinitialized the subscribers and have successfully created a new snapshot but when I start synchronizing, I get the following error:

The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
(Source: MSSQLServer, Error number: 1204)

I have killed the connections to database B that aren't Administrator (login of sa) and that aren't running. This didn't work and I am still getting the error.

I'm not very knowledgeable when it comes to this stuff and I don't have a DBA to talk to so any help is greatly appreciated as I am running out of ideas fast.

Thanks in advance!

Edit: Here is some more info after talking with Razzle Dazzle.

DBCC OPENTRAN - This finds no open transactions on both the provider and the subscriber.

sp_who - This shows that very few (under 40) connections are going for both provider and subscriber. The only connection(s) that are in a status other than either 'Background' or 'Sleeping' is the connection(s) that I have to the server via SSMS.

DBCC MEMORYSTATUS -

Publisher: Publisher Memory Status

Subscriber: Subscriber Memory Status

Lt Muffin360
  • 31
  • 6
  • You should first investigate if there is any blocking going on as the inability to obtain a lock resource indicates something else already has one on the asset in question. Use something like a sp_who or some other active session query to see if there are blocking chains among the active SPIDs on the intance. You should also check to see if there is currently an open transaction using DBCC OPENTRAN as that could also cause issues if it's big enough and been long running. – Razzle Dazzle Aug 13 '15 at 19:46
  • Thanks for the suggestion Razzle Dazzle. I went ahead and looked on both the publisher and subscriber for Open Transactions and active SPIDs. No Open Transactions were found and there isn't an SPID that is in a state other than sleeping or background that isn't the SSMS connection that I am making to the server. Hopefully this helps some? – Lt Muffin360 Aug 13 '15 at 20:05
  • The next thing you may want to check on is the memory usage of SQL Server. Both on the OS to ensure the instance is getting the amount of RAM it needs to function well and internally since acquiring locks requires pages in memory. – Razzle Dazzle Aug 13 '15 at 20:17
  • Is this the DBCC MEMORYSTATUS or are you talking about something else? Just want to make sure I understand so I can get the correct information out there. – Lt Muffin360 Aug 13 '15 at 20:23
  • DBCC MemoryStatus is certainly a good way to get a look at memory usage in SQL Server. You could even do something like suggested in this article for a brief glance at whether or not you may need additional resources: https://www.simple-talk.com/sql/database-administration/why-is-that-sql-server-instance-under-stress/ – Razzle Dazzle Aug 13 '15 at 20:52
  • I have added in the DBCC MEMORYSTATUS from both the provider and the subscriber in an edit to the original question. However, I tried out several of the sys. queries and none of them worked because the database doesn't like the sys. call. Is there a way to fix that or is there anything else that would be helpful to have? – Lt Muffin360 Aug 13 '15 at 21:46

0 Answers0