(SQL) Tip of the Day: Cannot Scale Because Files Are Too Large

Today’s Tip…

It is rare, but sometimes when you are trying to scale a SQL Azure database to a lower tier you can get an error stating your database or files are too large. A quick fix is:

The first query checks the sizes of the database and physical files, the last two shrink the files of your database and it’s transaction log respectively.

Comments

• Anonymous
  May 19, 2016
  I think each time is provided a piece of code that can affect adversely a database's performance should be also posted a warning. Not warning on the adverse effects is like putting a hammer in a child's hands in a room full with mirrors. Shrinking files can cause considerable index fragmentation. See Paul Randals' post on this topic: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/On the other side there are situations in which a shrink can help alleviate a problem, like in the above case. Then it's advisable also to rebuild/reorganize indexes. See TechNet documentation: https://technet.microsoft.com/en-us/library/ms189858(v=sql.110).aspx