One of the most common and searched SQL Server failure is around “Logins”. Login failed for user has always been tricky and interesting to troubleshoot. I don’t think there is a silver bullet to easily troubleshoot the same. With every version of SQL Server, there are enhancements made in error reporting, which try to make life on DBA’s easy. In previous version of SQL, whenever there is a login failed, it would print message in the SQL ERRORLOG along with the state of login failed. It was difficult for a DBA to find the cause further and it all boiled down to doing a number of permutations and combinations of resolution over the internet. Let us see how to FIX Error 18456.
SQL SERVER – Huge Transaction Log (LDF) for Database in Availability Group
One of my blog readers posted on Facebook asked following question about Huge Transaction Log:
Hi Pinal,
We are using AlwaysOn availability group for our production database. The database is fairly large in size as its around 260 GB. The database transaction log file grows around 2.5gb every day. Over the weekend the DBA team noticed that the Log file for this database bloated up to around 230 GB and then seems to hold steady. While troubleshooting I noted an error that the rebuild Index failed due to “Availability_Replica”. Here is the error which we saw in ERRORLOG
SQL SERVER – JSON Support for Transmitting Data for SQL Server 2016 – Notes from the Field #114
[Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about JSON Support for Transmitting Data for SQL Server 2016. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively interesting topic for database experts. Read the experience of SQL SERVER – JSON from Kathi in her own words.
SQL SERVER 2016: Creating Simple Temporal Table
Temporal tables are a new feature that was introduced in SQL Server 2016. A temporal table gives application developers to view the state of data at a point in time. Temporal tables can also be used for logging purposes if required. I wanted to explore how this feature can be used. The documentation has been interesting and I went ahead in trying out how this can be enabled.
One of the most used use case for this feature would be to find the data values in a past date. This can now be easily configured in SQL Server 2016. Let us go ahead and create a database for use and then create a table with the Temporal history database created by default / automatically.
SQL SERVER – How to Connect Using NT AUTHORITY \ SYSTEM Account?
Sometimes it is needed to connect to SQL Server using System account. Don’t literally ask me if this is a valid scenario in first place. Someone just pinged to say – they want to do it. I was personally not sure why, but they had their own internal requirement to do the same. Let us learn about in this blog post about how to connect using NT AUTHORITY \ SYSTEM Account?
It is not possible to provide windows credential in SSMS and they are always grayed out. My exploration is always to keep trying to find a solution to such typical use cases. I was able to find that PSExec can be used to achieve this. I am a big fan of some of the tools from sysinternals. These are handy and quite small footprint of the servers. One of the tools I have day-in day-out is ZoomIt while doing presentations.
MySQL – How to Generate Random Number
In MySQL, UUID() function returns Universal Unique Identifier that generates 36 characters long value which is 5 part hexadecimal numbers. If you want to generate random password, you can make use of this function which generate random number.
Returns the string 85aeb064-8f73-11e5-85ef-02fcc4101968 (Note that this is random, when you execute you will get different value). As the total length is 36, you can make use of the result to get a random password with any length.
SQL SERVER – Install Error – The /UIMode setting cannot be used in conjunction with /Q or /QS
Doing repetitive tasks is something everyone hates. I am sure many of us have resorted to ingenious techniques to figure out doing the task efficiently. There is no standard tool or technique that everyone uses. Recently I was asked to install a SQL instance with same configuration on 10 servers. Who would like to log in to all those servers, run setup.exe and go through the same setup wizard? Luckily, the SQL Server setup allows to create a configuration file. The very thought of extracting it as configuration is something that made me jump out of my seat. Let us learn about Install Error – The /UIMode setting cannot be used in conjunction with /Q or /QS.