Databases
StackOverflow2013
Postsdbo
POSQL Server 2008 Job error 1204 Lock
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server 2008 Job error 1204 Lock
    primarykey
    data
    text
    <p>I'm having some trouble with some ancient database i have to maintain. It's been around for ages (No, i did not design it, no, it's not possible to make any changes on it)</p> <p>it goes like this: There are 2 tables, "Documents" and "Versions". Documents is a pretty simple table, just a primary key, a varchar storing the document name and the user who created that document.Versions has a foreign key to the document it belongs to, an image field where the actual document is stored (mainly word documents and pdfs), the extension, and another field that keeps the version number.</p> <p>Whenever the application (an ANCIENT VB6 application) consumes a document, a new version is generated.</p> <p>Every night, a job is run on the database, in order to delete all versions except the 5 latest ones of each document. This has been working like, forever. </p> <pre><code>DELETE FROM t_ad_Versions WHERE VersionNumber &lt; dbo.MaxVersion(codDocument)-4 </code></pre> <p>Problem is, eventhough everyday the versions table has this job running in order to discar the oldest entries, the database is reaching an alarming size (Currently 300+ GB).</p> <p>In order to reduce said size, someone eventually realized that there's no need to keep versioning pdf elements. So, i was commanded to apply a simple modification: </p> <p>the job will delete older versions of word docs, preserving the latest 5 versions. For any other kind of file, ALL versions except the latest one will be deleted. So, i changed the stored procedure the job is associated to to this:</p> <pre><code>DELETE FROM t_ad_Versions WHERE VersionNumber &lt; dbo.MaxVersion(codDocument)-4 AND extension LIKE 'do%'; DELETE FROM t_ad_Versions WHERE VersionNumber &lt; dbo.MaxVersion(codDocument) AND extension NOT LIKE 'do%'; </code></pre> <p>However, after applying the change, the following day the job history notified the following error</p> <p>"The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. [SQLSTATE HY000] (Error 1204). The step failed."</p> <p>I'm kinda lost now, any ideas?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLynn Crumbling
    1. USHobbes
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COHow long does run this query? (SELECT COUNT(*) FROM t_ad_Versions WHERE VersionNumber < dbo.MaxVersion(codDocument)-4 AND extension LIKE 'do%') If it takes too long to execute then during DELETE some rows are changes and it fails. In other words there is no enough time for DELETE statement to lock rows while non of them is changed by another transaction.
      singulars
      1. POSQL Server 2008 Job error 1204 Lock
      1. USsqlint
    2. COProbably tried to delete too many things at once. see http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4419eab8-7e35-492f-9a12-0c56932d702a/sql-jobs-cannot-obtain-a-lock-resource-at-this-time
      singulars
      1. POSQL Server 2008 Job error 1204 Lock
      1. USJacob Goulden
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload