So, you have a multi-terabyte database and a SQL Server full backup. Just imagine that you do something that wrecks the data in one table (and of course, that has never happened), the only secure copy is within a backup file that will take several hours to restore and you don’t have the time or the space for such an act. Continue reading
I need to install a new PowerShell module onto a server that has no access to the Internet, so I am unable to use the Install-Module command.
Continue reading
Background
Whilst reading up on the finer details of High Availability Groups (HA Groups) I have discovered that it can, under certain circumstances, use Snapshot Isolation Level and therefore, row versioning. This has implications with your monitoring and maintenance tasks.
Continue reading
Because one of the nodes in our AG was having Windows updates applied, it was necessary to suspend data movements for all databases involved in the AG, on that server. Upon my return (after the maintenance was completed) I was unable to connect to the server, with a message that didn’t really match the circumstances – or so I believed.
Attempting to connect to the server, I was faced with the response below:
Unable to connect to server
“The target database, <db name>, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. (Microsoft SQL Server, Error: 876)”
This was a bit of a puzzle, as I knew there were databases suspended, which is why I needed to connect to the server and this node of the AG was definitely set for read-only access.
The clue is at the start of the error message, where it says ‘target database’. Having looked at my connection properties it says ‘default’.
Shows which database will be used for this connection
So, I decided to select a specific database, in case this was the issue. Within the drop-down list for ‘Connect to database’ I choose to browse the server, to see the list of databases.
Cannot select a database to connect to
Whereupon the same error message appears, making it appear that I can’t change this setting at all.
Cannot select a database to connect to
However, just because I can’t select a database name, it doesn’t mean I can’t just type a name straight into that field. In the ‘Connect to database’ I type ‘master’, hit return and the connection works fine – I’m logged into the server.
Now that I’m in the server, if I look at my login properties, the ‘Default database’ entry had the name of one the databases within the AG. Which of course means that if there is ever an issue with that database then I won’t be able to connect. Pretty much what the error message was trying to tell me.
So, I have made sure that my ‘Default database’ is set to a system database, master is an obvious choice. If master is unavailable I will have larger issues.
Set default db to master
Ensuring that my connections all default to master will remove this issue, when connecting to AG servers. I usually do default to master but never had a reason for that – now I have.
Introduction
Continuing my series of XML articles I’m going to move from XML RAW to XML AUTO with a handful of examples that share features between the two. Continue reading
Introduction
Most systems that I have worked with make use of the XML data type. I don’t find it to be the most intuitive data type to work with but most aspects of it can be built upon from a few basic examples. This will be part of a developing series that will cover XML and JSON. Continue reading
Having attempted to start an old test instance from SQL Server Configuration Manager, I received the wonderful error “The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details”
Error From Configuration Manager
More helpful than some message you can get – at least it tells me where to look.
So, where is the error log for SQL Server? As I rarely need to access it and always forget how to find it, this small article has been written. I write these articles for myself – if anybody else finds them useful then that’s a bonus.
Configuration Manager is where the startup parameters can be seen, for the instance in question. The ‘-e’ parameter is the location of the error logs is stored.
Showing the location of the error log
So, looking in the file ‘ERRORLOG’, in the given location I can see that the creation of TempDB failed, due to a permissions issue (access denied).
Permissions issue with TempDB
To resolve this issue I checked the folder and the existing TempDB files were set to read-only. Who knows how that happened but the solution was easy enough. Having removed those files (because TempDB is re-created every time) the instance was able to start correctly and all is good in my world.
Of course, this won’t be the solution to every problem but at least you now know where to look for more information.
Recent Comments