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”

How to deploy SSIS package to SQL Server 2012 SSISDB Catalog

In this post, I’m going to present how to deploy SSIS package to SQL Server 2012 integration services catalog. Create SSIS catalog After step 1 you can see SSIS catalog name called “SSISDB”  created. Right click and create a folder “TestSSISPackages”     Now create a visual studio SSIS integration sample package. For the purpose ofContinue reading “How to deploy SSIS package to SQL Server 2012 SSISDB Catalog”

Database size report for multiple database on multiple servers

As a DBA, you are responsible for monitoring the growth of the databases on multiple servers in different environments. There are many ways we can achieve this, my requirement was to use linked servers to get the databases size of all the linked servers and email the report to DBA group. Here is the script i haveContinue reading “Database size report for multiple database on multiple servers”

Enable Instant File Initialization is easy in SQL Server 2016

Starting from SQL Server 2005, Microsoft introduced a feature called Instant File Initialization. By default, when you create a database, add a new data file, increase the size of an existing file,  in SQL Server first initialized by filling the files with zeros. File initialization causes these operations to take longer. However, when data is written toContinue reading “Enable Instant File Initialization is easy in SQL Server 2016”