SQL SERVER - Database Files Initial Size Changing Automatically - SQL Authority with Pinal Dave

One of my clients reported an interesting issue and I was needed to wear detective cap to solve it. The mystery, for which they hired me was as below for Database Files Initial Size Changing Automatically.

Pinal,
We see an interesting issue, and we believe there is something not right with our environment. As per best practices from one of your blog, we have configured the initial file size of the database files to big enough for next 1 year data growth.

We are noticing that the file size is not getting set and it changes to smaller size every night.  Would you be able to suggest us something?

SQL SERVER - Database Files Initial Size Changing Automatically databaseshrink-800x281

MY INVESTIGATION

I asked them if there is any maintenance plan which runs every night and does the shrink of the database, like below?

SQL SERVER - Database Files Initial Size Changing Automatically shrink-auto-01

And they said that they have no maintenance plan doing such activity.

Here are the various data points I looked at.

  1. SELECT * FROM database_files
  2. SELECT * FROM databases
  3. sp_helpdb SAPDB

When I looked at sys.databases, I could see is_auto_shrink_on was set to 1.

You can run below query in you production database and make sure auto shrink is set to 0.

SELECT is_auto_shrink_on, * FROM sys.databases

ROOT CAUSE

As you can see above, we concluded that the behavior which they saw was because Auto Shrink property was set to ON for many databases. Since this was one of an active database, auto shrink was kicked as compared to other databases. This article discusses the use of both Auto options[1].

Reference: Pinal Dave (https://blog.sqlauthority.com[2])

References

  1. ^ Auto options (support.microsoft.com)
  2. ^ https://blog.sqlauthority.com (blog.sqlauthority.com)

Comments

Post a Comment

Popular posts from this blog

New Article Posted: Seven Things You Should Do in Your AMS

Given how advanced and thorough the modern association management software (AMS) system has become, there are many opportunities for your organization to leverage it. Here are seven ways I believe every association should be using their AMS. Click here to read the rest of the article . Of course, if you’re already on my  announcements list , you would have received notice of this new article right in your email box. Not signed up yet?  Click here to sign up . Go to the article
Read more

10 Common Mistakes Biblical Counselors Sometimes Make, Part 8

Image
A Word from Bob: You’re reading Part 8 of a 10-part blog series on 10 Common Mistakes Biblical Counselors Sometimes Make . Mistake #1: We Elevate Data Collection Above Soul Connection . Mistake #2: We Share God’s Eternal Story Before We Listen Well and Wisely to Our Friend’s Earthly Story . Mistake #3: We Talk at Counselees Rather Than Exploring Scriptures with Counselees . Mistake #4: We Practice Half-Biblical Counseling If We Address Sin but Neglect Suffering . Mistake #5: We Fail to Follow the Trinity’s Model of Comforting Care . Mistake #6: We Tend to View People One-Dimensionally . Mistake #7: We Devalue Emotions Rather Than Seeing Emotions as God’s Idea . Mistake #8: We Minimize the Complexity of the Soul-Body Interconnection Because we are soul physicians and soul care-givers, our tendency is to focus on matters of the soul. Most biblical counselors are not trained in the medical field, so we wisely avoid offering medical guidance. Yet, that appropr...
Read more