A Fine Slice Of SQL Server
Monica Rathbun shows how to use the Steps Recorder in Windows:
Windows PSR “Problem Steps Recorder”. It’s a nifty tool that helps you trouble shoot a computer problem by recording step by step what the user is doing.
How to:
Go to Start
Windows Accessories
Steps Recorder or Problem Steps Recorder depending on Windows version
Select Start Record (App will popup)
That’s a new one on me; click through to check it out.
Comments closedKenneth Fisher explains why you might get “out of space” errors when taking compressed backups:
Compressing your backups has very few downsides. It’s usually faster (the additional time for compression is less than the time saved by reduced IO) and of course, the backups are smaller. I have run into a few issues, however.
On one occasion there was 75gb free on a drive, the last full backup was only 50gb and the database had not grown significantly in size. Interestingly there was an error when we tried to run a backup. Not enough disk space.
Read on for the explanation as well as the solution.
Comments closedCody Konior tests how long messages in the error log can be:
Stupid question… what’s the schema of a table with sys.sp_readerrorlog output? Well you might be surprised if you’re used to using nvarchar(max) or nvarchar(2048).
There’s a datetime (modern datetime2(3)) obviously. ProcessInfo is either “Server” or “spidxxxy” where xxx is an int (max of 11 characters including minus) and y is an optional single character suffix. But as for the text…
Let’s try to max it out!
Moral of the story: keep those error messages as short as possible while still being meaningful.
Comments closedShane O’Neill explains what the $error variable does:
Now ignoring the fact that you already know what is wrong, this tells me that there is either something wrong with the
$Databasevariable, the$sqlvariable or the syntax statement. Maybe even something else though!
This is not helpful and I’m going to have a bad time.I encountered this lately and thanks to Chrissy LeMaire ( b | t ), I was introduced to the
$errorvariable.
Shane also has an interesting side note around error colors.
Comments closedDan Guzman goes into some detail about error handling in SQL Server:
T-SQL and ADO.NET data access code must work in concert with one another to ensure SQL errors are detected in application code. The T-SQL constructs used in multi-statement batches can affect if and how when errors are reported by ADO.NET during batch execution. I’ll start by citing core T-SQL error handling objectives, which can be summarized as:
1) Ensure a multi-statement T-SQL batch doesn’t continue after an error occurs.
2) Rollback transaction after errors.
3) Raise error so that the client application is aware a problem occurred.
Read the whole thing.
Comments closedDave Mason troubleshoots an annoying connectivity error message:
Ah! An “SSPI” error. I was certain I knew what the problem was. It had to be a failure to properly register the SPN. I was prepared to run a few SetSPN.exe commands to fix the problem and be on my way. To my surprise, the SPN *was* registered correctly, as I discovered in the Log File Viewer:
Click through for the solution.
Comments closedJeff Pries explains how to troubleshoot various Reporting Services configuration errors:
After installing SQL Server Reporting Services (SSRS), are you receiving an Error 404, Error 400, “Invalid Request” error, or “Bad Connection” error on first visiting the SSRS web portal (the error message seems to vary based on version, browser, and whether accessing via http/https or /reports vs /reportserver) ?
I’ve run into this a few times so I’m listing the steps I’ve used to fix it. For me, the root cause of this error has been the SSRS Configuration Wizard automatically configuring SSRS to use HTTPS, but assigning an invalid machine SSL Certificate. The fix is to self-generate a new and valid SSL certificate for the SSRS website to use.
Jeff then provides step-by-step instructions.
Comments closedKyle Kingsbury takes a new look at MongoDB:
In April 2015, we discussed stale and dirty reads in MongoDB 2.6.7. However, writes appeared to be safe; update-only workloads with majority write concern were linearizable. This conclusion was not entirely correct. In this Jepsen analysis, we develop new tests which show the MongoDB v0 replication protocol is intrinsically unsafe, allowing the loss of majority-committed documents. In addition, we show that the new v1 replication protocol has multiple bugs, allowing data loss in all versions up to MongoDB 3.2.11 and 3.4.0-rc4. While the v0 protocol remains broken, patches for v1 are available in MongoDB 3.2.12 and 3.4.0, and now pass the expanded Jepsen test suite. This work was funded by MongoDB, and conducted in accordance with the Jepsen ethics policy.
Mongo has grown up when it comes to data integrity, though be sure you’re using the v1 replication protocol.
Comments closedRobert Davis looks at messages in the error log related to memory-optimized tables:
The server on which we are running in-memory OLTP is a really hefty server with 128 logical cores and 1.5 TB of RAM (1.4 TB allocated to SQL Server). We are limiting in-memory’s memory usage with Resource Governor, which also makes it easy to see how much it is using. Needless to say, even with a limited percentage of 1.4 TB of RAM is still a lot of memory. The highest I have seen in-memory usage for this one database reach at peak activity levels is ~43 GB. In production, when the heavy in-memory OLTP processes complete, I see the system reclaim the in-memory buffers pretty quickly, though not completely. During a normal day, I often see the in-memory memory usage hovering between 1 and 3 GB even when there is virtually no traffic.
When testing in-memory on a dev server that only I was using before deploying to production, I noticed that the memory usage would stay at whatever high level it reached. This makes me believe that in-memory buffers are cleaned up and reclaimed as needed, and if not needed, they just hang around as in-memory buffers. And it appears that some of the buffers end up hanging around. Perhaps they wouldn’t if the server was memory starved. I have not tested that theory.
It’s a conjecture, but seems pretty solid. Also worth reiterating is that they’re warnings, not errors.
Comments closedMichael Swart talks about doomed transactions:
So the procedure was complicated and it used explicit transactions, but I couldn’t find any TRY/CATCH blocks anywhere! What I needed was a stack trace, but for T-SQL. People don’t talk about T-SQL stack traces very often. Probably because they don’t program like this in T-SQL. We can’t get a T-SQL stack trace from the SQLException (the error given to the client), so we have to get it from the server.
Michael shows how to get stack trace information and provides some advice on the process (mostly, “don’t do what we did”).
Comments closed