How to view all Powershell commands on a server

Question: How can I view all Powershell commands on a server? I've installed multiple Powershell modules for various processes , and have lost track of what's available. Also , I'd like to expand my range of Powershell command usage Answer: The Powershell cmdlet - Get-Command - returns all the Powershell commands on a server. There are a number of different filters and parameters you can use with Get-Command. Using Get-command - you'll quickly find is one of the most useful Powershell cmdlets. Along with 2 other Powershell cmdlets - which I consider to be the most powerful - read more... Read more →

Should Developers manage database read status

A programmer asked me if he can include the a function to change the database status out of READ_ONLY , add some data and then put it back into READ_ONLY. These are the basic commands ALTER DATABASE database-name SET READ_ONLY ALTER DATABASE database-name SET READ_WRITE There are some considerations for deciding if a Developer should be able to include as part of an ETL process , the capacity to change the READ STATE of a SQL database 1) Requires ALTER permission on the database. This is an elevated privilege - and may break the organisations sql server security policy 2)... Read more →

How to read and parse the Microsoft SQL Server extended event log file xel file

Question: I'm trying to read the Microsoft SQL Server Extended Event Log File - xel file for the SQL Server Extended Events system_health extended event. Just opening it up in Notepad - produces human unreadable output . How can I read the Extended Event Log File output file , using t0-sql? Answer: Microsoft have provided various methods and SQL functions which can read the Extended Event Log File file . There are multiple options. It is possible to list out the records within the Log file - this option will return a recordset, as xml output. You may want to... Read more →

How to import JSON file into a SQL recordset with OPENJSON

Question: I have a JSON file loaded daily onto a drive . I need to set up a process to read the JSON file and translate into a SQL recordset. Eventually - it will be added to a SQL table. Is it possible to read a simple JSON file using T_SQL ? Answer: It is possible to read a json file using T-SQL.There are a number of different methods. By using the OPENROWSET functionality , ISJSON and OPENJSON function you can quickly read the file , check if the JSON is valid and then unpack the JSON into a SQL... Read more →

How to troubleshoot SPN and SQL Server Windows return code: 0x2098, state: 15

Question:I see this error in the SQL Server Error Logs . How can I troubleshoot this issue? The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/myserver.net:50234 ] for the SQL Server service.Windows return code: 0x2098, state: 15. The start up account for SQL Server is a dedicated start up account Answer: As a starting point let's look at what MSDN has to say " To use Kerberos authentication with SQL Server requires both the following conditions to be true: The client and server computers must be part of the same Windows domain, or... Read more →

How to troubleshoot Alter Database Failed SQLSTATE 42000 Error 3023

Question: I received an alert for a regular backup failure . A closer inspection of the sql server agent error logs displayed this error message: Executed as user: myUser. Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. [SQLSTATE 42000] (Error 3023) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). Answer: There are different reasons this error message is generated. The error message says that "ALTER DATABASE ADD FILE and encryption changes must be serialized". This... Read more →

Service Broker needs to access the master key in the database - Error-28054

Question : I found this logged in the SQL Server Error Logs, during some Service Broker troubleshooting. Service Broker needs to access the master key in the database 'MyDatabase'. Error code:32. The master key has to exist and the service master key encryption is required. Error: 28054, Severity: 11, State: 1. What does it signify? Service Broker is working ok on the server, but this error appears on some days. Answer: As a first step , run this sql code , which will signify if there is a master key for the database Select name, is_master_key_encrypted_by_server from sys.databases Where name... Read more →