Something happened to the SQL Server 2017 database size recently. I was 400MB and in one day it doubled its size.
When exactly and why it happened nobody knows. The size of each table remains almost the same, but the size of MDF file is doubled.
Shrink did not help.
I decided to try to script the database and after that to run the script to get a new copy of the database.
I created the script for entire database. The size of the file is little bit less than 2GB.
I detached the original database, made a copy of MDF and LDF files and tried to open the script file in the MSSQL management studio.
After 20-30 seconds I've got this message:
I clicked OK, management studio started to change its cursor from normal to hourglass back and force every 5-6 seconds. The title bar adds and removes "Not Responding".
I've been waiting for about 25 minutes and got a message:
I have 64GB RAM, very fast NVMe drive, i9 14 core processor. So, my PC is very powerful and cannot work with 2GB sql file.
What is the proper way to create the database from the script? If MSSQL management studio can create the script within 1-2 minutes, then why it doesn't want to do the opposite process?
Schema and data. I also tried to split the file using online service. I created 10 files, and tried to run them one by one. First of all the first file took more than 10 minutes and finished with a big number of errors.
As I understand my file is very small comparing with sizes other users report in different places. And having a small file I have no tool to script and restore with no errors.
The reason was found. Query Store General/Operation Modes settings were Off, then somebody changed them to Read write with Stale Query Treshold = 30 days. Then probably somebody ran some queries the result of which was added to the size of MDF file. We couldn't find who and why did that change.
Change settings back and purging Query Data restored the normal size of the MDF file.
* The Best Reasons to Target Windows 8
Learn some of the best reasons why you should seriously consider bringing your Android mobile development expertise to bear on the Windows 8 platform.