SQL Prompt 10 - database is locked

GarySBRGarySBR Posts: 1 New member
in SQL Search
Hello.  Once in a while my SSMS session will get taken over by SQL Prompt getting "database is locked" errors.  Opening a new query tab will hang SSMS for about 30 or 60 seconds, the Red Gate error dialog will pop up with "database is locked" (sometimes multiple lines of it in the one dialog), clicking through the pop up will open the query tab.  Trying to close SSMs gracefully will not work, as every attempt to close leads to the freeze, delay then the popup...I have to kill the ssms process.

Which database is Prompt having trouble with...a db on the instance or its own internal db?  I haven't found anything about database is locked in the Prompt log files.

Thanks!
Tagged:
· Share on Twitter

Answers

  • dkim1999dkim1999 Posts: 85 Bronze 2
    Hi Gary,

    Thank you for reaching out and I apologize that you are running into issues with SQL Prompt.

    What version of SQL Prompt are you currently using?
    If you are on the latest version, can you try downgrading to a previous version to rule out any environmental issue? https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.12.1.28146.exe

    Kind Regards,
    David Kim
    Product Support Engineer

    · Share on Twitter
  • BevBev Posts: 6 New member
    I'm now getting this issue as well, currently using the most recent version 10.12.2.28458
    · Share on Twitter
  • bgarberbgarber Posts: 1 New member
    I am also getting this error starting today and also running the most recent version 10.12.2.28458
    · Share on Twitter
  • GrimfellowGrimfellow Posts: 4 New member
    same as well, running the newest version
    · Share on Twitter
  • dkim1999dkim1999 Posts: 85 Bronze 2
    Hi there,

    The development team is currently looking at this issue.

    In the meantime, the workaround for this issue is to downgrade to a previous version. You can download it here: https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.12.1.28146.exe

    Kind Regards,
    David Kim
    Product Support Engineer

    · Share on Twitter
  • dkim1999dkim1999 Posts: 85 Bronze 2
    Hi Gary,

    Thank you for waiting.

    While the development team has not been able to reproduce this issue, they have made a speculative change that might fix the issue.

    Can you please try using the following download link that I will send to you through your email?
    It should be from david.kim@red-gate.com.

    Thank you!
    David Kim
    Product Support Engineer



    · Share on Twitter
  • Thilo_MuehlbergerThilo_Muehlberger Posts: 1 New member
    A Solution:

    1. Close SSMS

    2. Open the latest SQL Prompt log file in %localappdata%\Red Gate\Logs\SQL Prompt 10 and search for the error message "database is locked"
    Sample result is as follows:

    code = Busy (5), message = System.Data.SQLite.SQLiteException (0x800007AF): database is locked
    database is locked
       at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
       at System.Data.SQLite.SQLiteDataReader.NextResult()
       at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
       at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
       at RedGate.SqlPrompt.TabHistory.DocumentStore.MarkDocumentOpen(Int64 documentId)
       at RedGate.SqlPrompt.TabHistory.Monitoring.TabWatcher.<>c__DisplayClass14_0.<OnDocumentOpened>b__0()
       at RedGate.SqlPrompt.UsageReporting.ErrorReporting.ErrorReporterWithUI.Do(Action action)

    Result: it is verified that the error has something to do with SQL Prompt 10 due to the error message could be found. It is clear now that the error message was raised by SQLite

    3. Move to folder %localappdata%\Red Gate\SQL Prompt 10
    There are two SQLite databases, SavedTabs.db and SqlHistory.db

    4. Create a backup of each, for example with Ctrl+C, Crt+V of each file

    Note: a backuped SQLlite file have no locks anymore

    5. Delete or rename each of SavedTabs.db and SqlHistory.db

    6. Rename the backuped files to SavedTabs.db and SqlHistory.db

    7. Start SSMS



    · Share on Twitter
  • JoDouglassJoDouglass Posts: 5 Bronze 1
    I've just had the same thing happen - in my case, the errors started when I closed a tab. When I opened the Tab History I noticed that while the tab had closed, my Tab History still showed it as open. I then kept getting errors if I switched tab, or tried to open Tab History again - and then I just couldn't open Tab History at all. I also could quit SSMS, I had to force quit it.

    I checked the logs, and found that this was the first error logged (in the sqlprompt-sqlhistory log file):
    2022-08-17 12:11:51.332 +01:00 [Information] Tab history file detected without sql history file. Starting conversion.
    2022-08-17 12:11:51.334 +01:00 [Information] Converting tab history file 'C:\Users\(username)\AppData\Local\Red Gate\SQL Prompt 10\SavedTabs.db' (105921536 bytes) to sql history file 'C:\Users\(username)\AppData\Local\Red Gate\SQL Prompt 10\SqlHistory.db'
    2022-08-17 12:11:51.703 +01:00 [Error] Error processing operation: code = Constraint (19), message = System.Data.SQLite.SQLiteException (0x800027AF): constraint failed
    UNIQUE constraint failed: Queries.CurrentPath

    In the other log file (sqlprompt) I then have various database locked errors after that point - this is the first one:
    2022-08-17 12:12:24.764 +01:00 [Error] Report error dialog UI shown
    code = Busy (5), message = System.Data.SQLite.SQLiteException (0x800007AF): database is locked database is locked

    Possibly relevant - the tab I was trying to close was quite an old tab that I'd pulled out of my Tab History, made a change to, and then tried to close. The full sqlprompt-sqlhistory log (below) references Upgrades.TabHistoryMigration and Upgrades.LegacyDatabaseUpgrader. I recently switched laptop, and I installed the latest version of SQLPrompt, Googled how to move the tabs across, and did so. Everything has been working fine for over a week, but maybe it's related?
    2022-08-17 12:11:51.703 +01:00 [Error] Error processing operation: code = Constraint (19), message = System.Data.SQLite.SQLiteException (0x800027AF): constraint failed
    UNIQUE constraint failed: Queries.CurrentPath
       at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
       at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
       at System.Data.SQLite.SQLiteDataReader.NextResult()
       at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
       at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
       at
     RedGate.SqlPrompt.SqlHistory.Storage.Upgrades.TabHistoryMigration.TabHistoryDataMigrator.MigrateDocument(SQLiteCommandWrapper command, Document document)
       at RedGate.SqlPrompt.SqlHistory.Storage.Upgrades.TabHistoryMigration.TabHistoryDataMigrator.InsertDocuments(SQLiteConnectionWrapper connection, IEnumerable`1 documents)
       at RedGate.SqlPrompt.SqlHistory.Storage.Upgrades.TabHistoryMigration.TabHistoryDataMigrator.MigrateData(String tabHistoryDatabasePath, String sqlHistoryDatabasePath)
       at RedGate.SqlPrompt.SqlHistory.Storage.Upgrades.LegacyDatabaseUpgrader.Upgrade(String tabHistoryDatabasePath, String sqlHistoryDatabasePath)
       at RedGate.SqlPrompt.SqlHistory.Storage.Store.Initialize(String tabHistoryPath, String sqlHistoryPath)
       at RedGate.SqlPrompt.SqlHistory.Server.OperationProcessor.Process(OperationType operationType, IMessage message)
       at RedGate.SqlPrompt.SqlHistory.Server.OperationQueue.ProcessQueue(Object sender, DoWorkEventArgs e)
    2022-08-17 12:11:51.703 +01:00 [Error] Error processing operation: System.NullReferenceException: Object reference not set to an instance of an object.
       at RedGate.SqlPrompt.SqlHistory.Storage.Store.SetStarredState(String path, Boolean starred)
       at RedGate.SqlPrompt.SqlHistory.Server.OperationProcessor.Process(OperationType operationType, IMessage message)
       at RedGate.SqlPrompt.SqlHistory.Server.OperationQueue.ProcessQueue(Object sender, DoWorkEventArgs e)


    · Share on Twitter
  • Rob_IQRob_IQ Posts: 17 Bronze 5
    I just got this error today with the latest version 10.12.4.
    Once it started, I could reset the service to unlock the files and view tab history. However, this kept happening most times I opened a tab and Every Time I tried to add a Star to a tab. It would hang a few seconds and then show the error. 
    I had to kill SSMS when closing since that also threw the error which blocked a clean shutdown.
    I uninstalled and downgraded to 10.12.1 and so far, it's been ok - (at least for 30 min so far)

    Has any progress in addressing this issue been made?
    · Share on Twitter
  • kwilkwil Posts: 1 New member
    I was experiencing exactly this issue on 10.12.4.29949, where every action in SSMS causes SQL Prompt to display the "database is locked" message after a 30-second stall. For now, I simply unchecked the "Enable tab history" box in the SQL Prompt options, and that gets everything going again. 

    It isn't a root-cause fix, but I can tell you for sure that this is related to Tab History.
    · Share on Twitter
Sign In or Register to comment.