One of the blog which I wrote earlier was also on Login failed for user with state 58. Let us see how to FIX Error 18456.
After reading that blog, one of the blog reader contacted me and told that she is getting state 6. I thought this error are very common and is important to demystify some of the reasons for the same. I have asked completed error message to assist him. Here is the message she shared:
Error: 18456, Severity: 14, State: 6.
As an independent database consultant, I often face a challenge when analyzing the performance of SQL Server instances, which I see for the first time in my life. In many cases, this may seem a difficult task, because in order to fix something you must first understand the database structure. A great many of the companies do not document their database. And even if documentation exists, it is rarely updated. As a result, we have the following situation – documentation is either obsolete, or does not reflect the actual state of things. Let understand SQL SERVER – View Dependencies.
The last time when I wrote about ColumnsStore FAQ, one of the readers had an interesting question. The question was around how to identify the traits of using a column store inside an execution plan. The very thought got me thinking. I was thinking where to start. I remembered that ShowPlan XML was a great way to see some of these attributes. The very next question that came up, what should one look forward to inside a ShowPlan XML.
Question: What is the Difference Among DECIMAL, FLOAT and NUMERIC Datatype? Let us learn more.
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.
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
[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.