SQL Invalid Check

danfountain

Hi
When i do a Invalid check on a database it seems to get half way through the stored procs then gives this error:

Login failed when attempting to find invalid objects in DATABASE.
To find invalid objects, your user must have:
• access to the database
• the VIEW DEFINITION permission granted on the database

I am a sysadmin on this sql server, and to be safe i have also granted view definition ALL.

Yet i am still getting this error on one database. Other databases on this same instance seem to work fine.

Could you assist?

Dan

---

ADDITIONAL

I have just noticed its actually generating an error and the above is a red herring. Here is the error:

17 Jul 2014 13:05:22,508 [1] ERROR RedGate.SQLPrompt.SSMSUI.Tabs.InvalidObjects.ObjectErrorList - Error fetching list of invalid objects
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at RedGate.InvalidObjects.ScriptExecutionValidator.GetErrorsForObject(IDbObjectWithUnadornedCreateScript objectToTest)
at RedGate.InvalidObjects.DatabaseValidator.GetErrors(IDatabaseCandidate database, CancelSignal cancelSignal, ProgressMeter progressMeter, IDbObjectValidator[] validators)
at RedGate.InvalidObjects.DatabaseValidator.GetErrors(ConnectionProperties connectionProperties, IMetadataCacheOptions cacheOptions, CancelSignal cancelSignal, ProgressMeter progressMeter)
at RedGate.SQLPrompt.SSMSUI.Tabs.InvalidObjects.ObjectErrorList..?(CancelSignal , ProgressMeter )
at RedGate.SQLPrompt.CommonUI.Utils.ReplaceableBackgroundWorker.<>c__DisplayClassf`1.<RunCancellableTask>b__b(Object param0)
ClientConnectionId:b20edece-6237-41b1-81ee-a376e468a5b2


This shouldnt time out - the server has no issues.

Dan

Aaron L

Hi Dan,

I think you're correct in that the first error is a red herring and it's actually the timeout, but it's pretty strange that only that one database would time out and not the others. Perhaps it has a stored procedure that uses a synonym to another server?

To help figure out what's causing it, you can try increasing your log level by editing:

%localappdata%\Red Gate\SQL Prompt 6\LoggingConfiguration.xml

And change the <root> level from "WARN" to "INFO"
Running find invalid objects again should log something like "Checking validity of <objectname>" which might help narrow down the object causing the issue.

Thanks,
Aaron.