locked
Why do I get these errors while trying to execute a view in the ssms? RRS feed

  • Question

  • I am trying to execute a view that I created in the SSMS with SS 2005 Express.  This view was created successfully.  So, I don't understand why it couldn't be found.  I used the "use" keyword in the execute query to point it to the correct db.   I get the following error's when i execute it.  

    Msg 945, Level 14, State 2, Line 1 

        Database 'passwords' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

    Msg 2812, Level 16, State 62, Line 2

        Could not find stored procedure 'Organizations_vw'.

    These are the errors below in the errorlog mentioned in the first error above.  I don't know what they mean.

    2009-05-06 11:16:36.10 spid12s     SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due                                                       to some database maintenance or reconfigure operations.
    2009-05-06 11:16:36.10 spid12s     SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to                                                       some database maintenance or reconfigure operations.
    2009-05-06 11:16:36.10 spid12s     SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due                                                       to some database maintenance or reconfigure operations.


    The first error mentions a insufficient memory or disk space.  I think there should be plenty of both.  The db is one table and only has two records.  

    Any help would be greatly appreciated.

    • Moved by Jonathan KehayiasMVP Wednesday, May 6, 2009 10:10 PM Moving to Database Engine Forum (From:Transact-SQL)
    Wednesday, May 6, 2009 4:04 PM

Answers

All replies

  • What is the view doing?

    It may be possible that you ran out of disk space? Check the space left for the data & log files. It may be possible that the files are trying to grow but there is no space left to auto grow?


    select * from sys.databases

    What is the state_desc column say for your db?



    >>These below messages say that the procedure cache is flushed. What actions are being done on the db right now?  Are you restoring new db's on the server. Look for other messages in the error log.

    2009-05-06 11:16:36.10 spid12s     SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due                                                       to some database maintenance or reconfigure operations.
    2009-05-06 11:16:36.10 spid12s     SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to                                                       some database maintenance or reconfigure operations.
    2009-05-06 11:16:36.10 spid12s     SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due                                                       to some database maintenance or reconfigure operations.

    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Wednesday, May 6, 2009 8:48 PM
  • The view is simply returning the contents of one table that has two records with 5 fields.  It is very simple.  I filled the table with two records so I can get the connectivity going in an app.

    I checked the sys.databases table like you suggested.  The state_desc column says "RECOVERY_PENDING".  

    These are the the values I used when I scripted the db.  Which I believe defaults to MB.

    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5)

    I know it is small, but it's only going to hold relatively few and short strings(varchars).  The mdf and ldf files are still at the original size.  They have about five times that size to go before reaching the max file size.  


    I am not intentionally restoring any dbs on the server.  Right now, I don't think anything is being done.  I found this in the error log.  I don't know if it is helpful.   It looks like somem type of recovery was done.  I didn't initiate it though.                                     

                                                       Clearing tempdb database.
                                                       Starting up database 'tempdb'.
                                                       Recovery is complete.


    Any thoughts?



    Wednesday, May 6, 2009 9:46 PM

  • Did you tried to delete the log files with extension .ldf or tried to mess around with it?

    The status shows that it has to do some kind of recovery to bring it to an consistent state. hang on, let me bring some help and move to disaster recovery forum.






    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Wednesday, May 6, 2009 10:01 PM
  • This sounds to me like your database may have AutoClose turned on for it.  Check your database options and see if this is the case.  If it is, turn AutoClose OFF and while your in there check AutoShrink and disable that as well if it is turned on.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Sankar Reddy Wednesday, May 6, 2009 11:29 PM
    Wednesday, May 6, 2009 10:14 PM
  • Sankar,   I didn't mess around with the ldf file. 

    Jonathan,  I ran the "select * from sys.databases" query. The AutoClose is set to "1".  I assume this is on.  Is this correct?  Coincidentally, it is  the same as most of the other dbs on my machine.  Auto Shrink is set to "0".  

    Does "0" mean auto shrink is off?  How do I turn these off and on?

    I'm curios why this stuff is happening but it seems easier to drop the db and redo it.  Of course,  I might have the same issues again.

    Any thoughts?





    Wednesday, May 6, 2009 11:17 PM
  • Yes, that means AutoClose is turned on, and you will continue to encounter problems like this while it is on because SQL Server shuts down the database when it is idle and then has to reopen it on the first connection which is where you are getting your problems.  To change this you need to run:

    ALTER DATABASE [DatabaseName] SET AUTO_CLOSE OFF WITH NO_WAIT

    You can do this for all databases on the server in one swoop with the following code:

    sp_msforeachdb 'ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'


    It will throw a few errors for tempdb, and master, but it will change the others as specified.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by Sankar Reddy Wednesday, May 6, 2009 11:29 PM
    Wednesday, May 6, 2009 11:22 PM
  • Jonathan figured it out for you.

    Run this command:

    ALTER DATABASE DBNAME SET AUTO_CLOSE OFF

    By default for SQL Express databases, this option is turned on. That means when the last connection is closed, the database is closed and when a new connetion tries, then the databse is opened again. This flushes the procedure cache on the server and thats why you are seeing those messages in the error log.

    Something to remember today for me!


    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Wednesday, May 6, 2009 11:23 PM
  • Yes, AutoClose is a pretty evil option to have turned on, just like AutoShrink.
    http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.
    Wednesday, May 6, 2009 11:41 PM
  • Thanks for the advice.  I ran the query you suggested and it turned off AUTO_CLOSE on most of the dbs.  IT didn't turn it off for the one I wanted, which was strange.  I then ran the query specifying that db and it came back succesfull.  I checked it by running the sys.databases query which returned a 0, so it is now off.
    Aut shrink is also off.

    I just looked at the log it shows that the dbs spun up to perform the qury to turn "AUTO_CLOSE" off.  The one that I have been having all of this trouble with had an error which might explain why it didn't take the query to turn off the "AUTO_CLOSE".  The error follows:

    Starting up database 'passwords'.     Error: 5173, Severity: 16, State: 1.

    I am still having trouble with this db though.  I wonder if something else is going on.  I inserted some stuff into the db yesterday but do not see it when I query it today.  The table is empty.  Another strange thing is that when I look at the db in the object explorer, I see the db with the cylinder beside it but no + sign.  It doesn't allow me to traverse the structure to see the table, procs, and views in the table.  When I query the db though it shows the columns in the table I created but with no data.  

    Any ideas about why this is?   Does this go back to the the state_desc column saying "RECOVERY_PENDING"?  Do I need to do some type of recovery?

    Thanks for your efforts.



    Thursday, May 7, 2009 12:51 PM
  • Open your SQL Server Error Log and look for the corresponding entry for Error 5173.  Immediately following the error, it will tell you the specific file for the database that it is having trouble with.  This error is usually caused when the log file (databasename_log.ldf generally) is deleted for being to large.  When this occurs the database won't be able to Recover because the log is required to recover the database and roll transactions forward or backwards to bring the database to a consistent state.  Your best option is to restore from backup in this case, but if you don't have a backup, you can follow the information on Paul Randal's answer on this post:

    http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/189be01f-23de-48b0-96cc-8f1292c13c54

    Also look at his blog posting:

    http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Thursday, May 7, 2009 1:01 PM