0

Scenario: System in a VM in Azure using MVC and a SQL Database (not in the VM) working under normal conditions for 2 or 3 months. Suddenly, stored procedures called from my MVC web app or SQL Management Studio return Time Out. Queries like Select * from Table work perfect.

EDIT: Timeouts while executing Alter or Create SP queries happened too.

No proper solutions or explanations found.

Workaround: Restore old backup in a new SQL Database and change the connection string to the new Database. While the system is running in the backup, try to backup the database with issues (first close all connections to that DB like Management Studio). It may take some time and some retries. After the backup is done, restore it in a new DB and change back the connectionString. You will lose a few minutes of data and some downtime but you will have your system working again in Azure.

Any ideas about this issue in the Stored Procedures in Azure?

Ariel Erlijman
  • 362
  • 4
  • 15

1 Answers1

0

At first glance, this smells like a parameter sniffing issue; it is probably not related to Azure.

Check this thread for details on what the issue is, and how to resolve it: Parameter Sniffing (or Spoofing) in SQL Server

Community
  • 1
  • 1
Herve Roggero
  • 5,149
  • 1
  • 17
  • 11
  • Not sure if parameter sniffing / spoofing is applicable here. The thing is when the problem was present, I had timeouts executing Alter Stored Procedure or Create Stored procedure queries! I'll check around this issue and let you know. Nice to learn the concept. – Ariel Erlijman Sep 23 '13 at 14:16