I have a stored procedure which runs a dynamic SQL query against a view. For some reason the sql maintains a schema table lock on one of the tables in the view after completion of the stored proc. This happens intermittently.
Then when my DB Maintenance Plan tries to run its optimizations job it hangs. I ran profiler when the optimizations job was running and found that it hangs up when it tries to reindex the locked table.
How can I make sure that the lock is released when the stored procedure completes? Or at least before the Maintenance Plan runs.