[RESOLVED] Corrupt SQL Server

[Shaggy Hiker]

I have a Surface Pro 2 (yeah, a 2, really) that I have both SQL Server 2012 and 2017 running on. I haven't used it in several months, but fired it up today to work on something. The program I was working on (which is on a different computer) started having some weird problems with SQL Server actions, so I went back to the Surface Pro to check on some things. What I found was that if I selected the top 1000 rows, it worked fine (as far as I could tell), but if I tried to edit top 200 rows, I would either get a message saying that there was an attempt to write to protected memory which isn't allowed, or else SQL Server Management Studio would quit without any message.

I certainly think the SQL issues with the program and the DB are probably related, but I'm not quite sure what to make of it overall. Prior to the SQL issues, the program had properly written a few records (perhaps three to each of three different tables). It seems unlikely that writing records could have corrupted SQL Server in some fashion. It also seems unlikely that this is just a SSMS issue, though I didn't test it (by attempting to execute some SQL statement, which would still be in SSMS).

I'm inclined to say that the Surface Pro is failing, or perhaps just a sector on the HD. I suppose it must be fairly old, by now. Are there any other alternatives that are worth checking?

[OptionBase1]

You say that you have SQL 2012 and SQL 2017. Are both exhibiting this behavior? Are there any other databases hosted in the problematic SQL version that you can test with to see if they are behaving strangely as well?

[Shaggy Hiker]

Those are good tests. I'll try them out tomorrow. There are only two databases in the 2017 version, and possibly only one in 2012, which was offline anyways.

[Zvoni]

Are there any other outside/remote SQL-Servers you could connect to (via SSMS)?
If yes, and SSMS on your Surface still fails/crashes.....

[digitalShaman]

I'd think the first thing to do is check database integrity: https://www.google.com/search?q=check+mssql+database
potentially followed by chkdsk and mem test.

[sapator]

Can you, is there an option to check the SQL logs?
Also I had crash issues with a PC with low memory on SQL edit. If I was trying to Edit a high amount of data the SQL would crash. Not sure about your specs or the breaking point or if that is the problem tho .

[Shaggy Hiker]

The DB size is trivial compared to the amount of memory available, and nothing else is running on that computer, so that's not likely to be the issue.

I wasn't aware of the integrity test, though I'm not surprised that one exists. I'll try that out, too.

[Shaggy Hiker]

There are two databases in that installation. Both of them exhibit the same problem. So far, I have yet to find any issues. Chkdsk found nothing, and the database integrity check said that all was well wit the DB. I then tried a repair install for SQL Server 2017, which went off without issue. After that, I tried a select, and it worked, then I tried an edit...and SSMS tried to put up an error message, then just quit to desktop.

So, while I didn't try the memtest, that's the only one of those I didn't try (since that appears to require a download or two, to be certain). There weren't any issues detected...and yet failure, still.

Very odd.

[Zvoni]

There are two databases in that installation. Both of them exhibit the same problem. So far, I have yet to find any issues. Chkdsk found nothing, and the database integrity check said that all was well wit the DB. I then tried a repair install for SQL Server 2017, which went off without issue. After that, I tried a select, and it worked, then I tried an edit...and SSMS tried to put up an error message, then just quit to desktop.

So, while I didn't try the memtest, that's the only one of those I didn't try (since that appears to require a download or two, to be certain). There weren't any issues detected...and yet failure, still.

Very odd.

The same with a „remote“ server? See my post above

[Shaggy Hiker]

Nevermind, it was all a read herring. I should have gotten a clue...or a clupeid.

I thought I had all of my computers updated on SSMS, but that one was still running SSMS2012. I feel it likely that I did use SSMS2012 on that SQL Server installation in the past, which may be the case, since lots of stuff did work. It is reasonable that I never tried to edit records on that computer, which could explain why I never encountered the issue before. Alternatively, perhaps something changed. The former seems more likely than the latter, as nothing seems likely to have changed on that computer (especially since it was mostly off for the last several months).

In any case, that was the issue. Using an older version of SSMS with a newer version of SQL Server will result in this misleading message...if you do the right thing.

Of course, there was a reason I was using SSMS on that computer, and the issue I had run into has nothing to do with this solution, so I'm back to the original issue, but that's a matter for a different time...and it might have gone away by coincidence, since it could be the quirky issues I've had from time to time with that Surface Pro on this network.

[sapator]

Be aware that using 2012-14 with a 202xx version and try to update will just crash the server and restart it.
I have databases on many sql versions and the good ol FAST 2014 will crush when I just forget and try to edit a record on something something version (it's not per year any more so, something something).