Question: What is the Difference Among DECIMAL, FLOAT and NUMERIC Datatype? Let us learn more.
SQL SERVER – FIX Error 18456, Severity: 14, State: 58. Login failed for user
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 Auditing and Recovery With ApexSQL Log 2016
ApexSQL Log is a powerful SQL Server transaction reader for forensic auditing and rollback of malicious and/or unintended changes for Microsoft SQL Server. It is an ideal cost solution for recovery, row changes and/or before-after auditing. ApexSQL Log uses technology which enables it to read online transaction log files or transaction log backup files in order to create auditing results including before and after views of the data as well as the full row history of all changes.
From the recovery standpoint, ApexSQL Log provides sophisticated undo/redo capabilities which enable it to create TSQL scripts which can be used to completely reverse or replay all or only selected transactions read from the online transaction log file and added transaction log backups.
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.
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.