CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    90

    [RESOLVED] SQL Server Errors

    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:
    Name:  SQLError.png
Views: 160
Size:  8.3 KB
    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:
    Name:  SQLError2.png
Views: 128
Size:  6.0 KB
    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?

    Thank you
    Attached Images Attached Images  

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: SQL Server Errors

    When you say you created the 'script', what was in it? Just the db schema, or schema and data?

  3. #3
    Join Date
    Jan 2013
    Posts
    90

    Re: SQL Server Errors

    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.

    Thank you

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: SQL Server Errors

    Do you have backups of the database before it grew in size?

  5. #5
    Join Date
    Jan 2013
    Posts
    90

    Re: SQL Server 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.

    Thank you

  6. #6
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: SQL Server Errors

    Glad to hear you've got it solved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured