Currently Browsing: SQL Tips and Tricks
Azure / SQL Server / Azure SQL Database Disaster Recovery / Orphaned Users / Different SIDs
Posted by Barac in Powershell, SQL Server, SQL Tips and Tricks on Dec 5th, 2018 | 0 comments
If you are using Azure SQL Database with geo-replication or auto failover group and if you are using logins and users rather than contained users you can end up with the following error when you try to connect to the secondary/failover database after failover occured. If you already created a login on the failover server using SQL command -- ====================================================================================== -- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database --...
Azure / SQL Server / Rename an Azure SQL database
Posted by Barac in Azure, SQL Tips and Tricks on Nov 16th, 2018 | 0 comments
We had one customer who was moving his Azure SQL Database from Elastic Pool to Standalone Database. At the same time, he wanted to change database name as well. Azure database was part of an existing failover group (two elastic pools) and geo-replicated to the new future Azure SQL Server. Changing the database name is not allowed if the database is part of the replication If the database is not part of replication we can use two different approaches to rename it, using: SQL Server Management Studio or Transact-SQL Note: This refers just to users databases USING SQL Server Management...
Azure / SQL Server / This location is not available for subscription
Posted by Barac in Azure, Powershell, SQL Tips and Tricks on Oct 22nd, 2018 | 0 comments
During deployment of Azure SQL Server in Canada East location (new Azure Subscription) I got the following error: Using Azure Portal Using Powershell At the same time if I list all available azure location for the current subscription I can see that Canada East is there Get-AzureRmLocation | select displayname The reason is that Canada East region is not activated for my new Azure Subscription. To resolve this issue just contact the Azure support to have this enabled for you. You can do this for free using support page on your Azure Portal. Open a new support request ...
SQL Server / ALTER INDEX / CREATE INDEX Progress
Posted by Barac in SQL Monitoring, SQL Server, SQL Tips and Tricks on Oct 12th, 2018 | 1 comment
Creating a new and altering existing indexes on the big tables can be time-consuming. There is a simple way to see the progress of the CREATE / ALTER INDEX command. Since SQL Server 2014 we can use sys.dm_exec_query_profiles DMV to monitor real-time query progress. sys.dm_exec_query_profiles (SQL SERVER 2014 and above) “Monitors real-time query progress while the query is in execution. For example, use this DMV to determine which part of the query is running slow. Join this DMV with other system DMVs using the columns identified in the description field” The first thing we need to...
SQL Server / Long Running Queries / Email Alert
Posted by Barac in SQL Server, SQL Tips and Tricks on May 4th, 2018 | 0 comments
This is example of long running queries of database within email alert, we already had example how you can use sp_send_dbmail stored procedure from msdb database to send backup size email daily, and how to send missing indexes report via email. This is just another use case of sp_send_dbmail stored procedure. I used following arguments to achieve this @profile_name (name of an existing Database Mail profile, type sysname, default NULL), If you are not sure about your profile account use the following script, it shows all the accounts for all profiles in the instance. EXECUTE...
SQL Server / Initialize Secondary Database / Generate a full backup of the primary database using SSMS transaction log shipping GUI issue
Posted by Barac in SQL Server, SQL Tips and Tricks on Mar 13th, 2018 | 0 comments
Several time, using different SQL Server versions I end up having a problem with initializing secondary database using SSMS transaction log shipping GUI (mainly with large databases). I got errors similar to: System.Data.SqlClient.SqlError: Cannot open backup device ‘\\network_path\trnls\db01.bak’. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo) Indicating that you don not have enough permissions. Even after I confirmed all permissions are OK, and I already have another DB using same network location successfully, still end up with same error....
SQL Server / SQL Server Management Studio could not delete the Secondary server / Secondary not available
Posted by Barac in SQL Server, SQL Tips and Tricks on Feb 20th, 2018 | 0 comments
Most of the time it is very easy to remove secondary server from your log shipping configuration. But what happens if secondary server is not available. We had this case on one of our testing environment, we had secondary server on the cloud and after a month server lease expired. When we tried to remove that server from our log shipping configuration we end up with following errors. Error details As you can see from error details SQL Server Management Studio could not delete the Secondary server ‘SQLNODE2’. (Microsoft SQL Server Management Studio) Object reference not...
SQL Server / Missing Indexes Query / Cached Plans / Email Alert
Posted by Barac in SQL Server, SQL Tips and Tricks on Feb 15th, 2018 | 1 comment
This is example of missing indexes of database within email alert, we already had example how you can use sp_send_dbmail stored procedures from msdb database to send backup size email daily, and this example is just to show use case with different scripts. I used following arguments to achieve this @profile_name (name of an existing Database Mail profile, type sysname, default NULL), @recipients (Is a semicolon-delimited list of e-mail addresses to send the message to, varchar(max)), @body (Is the body of the e-mail message, nvarchar(max)), @subject (Is the subject of the e-mail message,...
SQL Server / The target principal name is incorrect. Cannot generate SSPI context
Posted by Barac in SQL Server, SQL Tips and Tricks on Jan 12th, 2018 | 2 comments
One of our old SQL servers was running under the local system context. Then we decided to change the account that the SQL service runs under, and we created domain service account with basic domain user permissions. Eventually, we end up with following error trying to access our SQL Server remotely. SQL Server SPN Creation To run SQL Server service you can use Local System account, local user account or a domain user account. If you are using Local System account to run your SQL Service the SPN will be automatically registered. Nevertheless, if you are using domain account to run SQL Server...
SQL Server / Instant File Initialization
Posted by Barac in SQL Server, SQL Tips and Tricks on Dec 11th, 2017 | 0 comments
Instant file initialization reclaims used disk space without filling that space with zeros. What those this means for our SQL Server data files? Data file grow needs to be completed immediately. Lets do a simple test. I will create additional tempdb files with fixed 10GB size without Instant file initialization enabled for the SQL Server service account. /* Adding additional tempdb files */ USE [master]; GO ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'D:\MSSQL\TempDB\tempdev2.ndf' , SIZE = 10GB , FILEGROWTH = 0); ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev3',...
About Me
My name is Zoran, currently living in Auckland, New Zealand. I am a big data enthusiast with more than 10 years of hands-on experience in database administration. Also, I am a Certified Microsoft Trainer (MCT) and Certified Microsoft Database Administrator (MCSA, MCSE) with a Master‘s degree in Information Technology.
read moreAuckland SQL Group
MSDN PROFILE
