New SQL Server 2016 Truncate Table With Partitions

I have a table with different partitions and would like to truncate rows in the particular partitions in a table rather than truncating whole table. How can we do this? This task is fairly easy in sql server 2016. Microsoft introduced a new clause called “With Partitions” in truncate table command. Let’s see how it works. ForContinue reading “New SQL Server 2016 Truncate Table With Partitions”

Fix – Tempdb path was wrongly updated

You have Moved the tempdb to other drive and forgot to add the name of the MDF and LDF at the end of the file path, hence the error Error: 5123, Severity: 16, State: 1. Also, unable to restart the SQL Server. Don’t be panic. We can fix this. In this example I’m going to move the tempdb without providingContinue reading “Fix – Tempdb path was wrongly updated”

Database Mirroring = SQL Server 2016 AlwaysON Basic Availability Groups

Database Mirroring was one of the widely used feature since SQL Server 2005 until Microsoft announces Always On availability groups. Database Mirroring feature has been listed as deprecated ever since Availability groups have been introduced from SQL Server 2012. SQL Server 2016 BOL states Database Mirroring feature will be removed in a future version of MicrosoftContinue reading “Database Mirroring = SQL Server 2016 AlwaysON Basic Availability Groups”

Availability Groups Monitoring Alerts

You have setup the availability groups. How would know that the availability groups are running fine? All the good DBA’s always want to be notified via email when there are any issues related database servers not just only availability groups. In order to do this, you would need a alerts setup so that you canContinue reading “Availability Groups Monitoring Alerts”

Forgot SQL Server “SA” password? Don’t Worry

Hi Rama, Need your help. I forgot the “SA” password and I am unable to access to SQL Server via windows/sql authentication.  Is there any way we can recover the “SA” account? Hello Vamshi, Yes, we can as long as you are able to RDP and you are a built in administrator to your windows box.Continue reading “Forgot SQL Server “SA” password? Don’t Worry”

Powershell Script to Add New Database to Existing Availability Groups

One of the common tasks for DBA to add the new database to existing availability groups. This can be done fairly easy via SSMS. The requirement is how to deploy the new database from Team Foundation Server (TFS) to SQL Server Alway on AG listener and automatically add this database to Availability groups. Here is theContinue reading “Powershell Script to Add New Database to Existing Availability Groups”

SSIS Error – The Binary code for the script is not found

Application team reported me that one of the SSIS package is failing after recent changes. It was failing with message “Binary Code for the script is not found” I have been asked to troubleshoot the issue. I have opened the SSIS package in SQL Server Data tools and try to run the package from SSDT.Continue reading “SSIS Error – The Binary code for the script is not found”

Query Store – Key feature in SQL Server 2016

As a DBA, how many times you hear about a user reporting that a query suddenly running more slowly than in the past? SQL Agent job usually takes 3 hours , but this is running more than 12 hours, Could you please investigate? What would you do in this case? There are number of commonContinue reading “Query Store – Key feature in SQL Server 2016”

The report server cannot open a connection to the report server database

One of the student asked me while connecting to report server url, he was prompted the error as “The report server cannot open a connection to the report server database” Looking at the error, it states that the report server database is not accessible by the report server. Let us see the SQL Server report server configurationContinue reading “The report server cannot open a connection to the report server database”

Backup and Restore Database without non clustered indexes

In this post, I’m going to demonstrate how to take backup of database without considering non clustered indexes. Before describing how to eliminate index data from backups, it’s first important to point out that a non clustered index contains simply a copy of the rows from its associated table, sorted differently by the index’s keys toContinue reading “Backup and Restore Database without non clustered indexes”