(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:

image

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 (1)

  1. Adrian says:

    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

Skip to main content