Currently Browsing: SQL Server

SQL Server / Query Optimization / Merge Join Operator / Sort Operator

Posted by in SQL Server on May 30th, 2018 | 0 comments
SQL Server / Query Optimization / Merge Join Operator / Sort Operator
Recently we had really nice meetup gathering here in Auckland. Was a beautiful evening, there were 20 people talking about SQL server, was perfect. One of the topics was “Merge Join Operator” For this demo I used SQL Server 2014 Developer Edition. As a database I used StackOverflow, you can download this DB from Brent Ozar web page, just follow the instructions How to Download the Stack Overflow Database via BitTorrent In this example I will use two tables Badges (25 million rows) and Users (8 million rows) and I will join them on Userid (Badges) and Id (Users) columns. We have just clustered indexes...
read more

SQL Server / Long Running Queries / Email Alert

Posted by in SQL Server, SQL Tips and Tricks on May 4th, 2018 | 1 comment
SQL Server / Long Running Queries / Email Alert
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...
read more

SQL Server / Query Optimization / Memory Grant Warning / Excessive Grant

Posted by in SQL Server on Apr 21st, 2018 | 0 comments
SQL Server / Query Optimization / Memory Grant Warning / Excessive Grant
Recently we had really nice meetup gathering here in Auckland. Was a beautiful evening, there were 20 people talking about SQL server, was perfect. One of the topics was “Bad estimates and Memory Grant Warning” For this demo I used SQL Server 2014 Developer Edition. As a database I used StackOverflow, you can download this DB from Brent Ozar web page, just follow the instructions How to Download the Stack Overflow Database via BitTorrent   In the previous post we talked about using string function in the WHERE clause and bad estimates within query plan. Here is one example how wrong estimates can...
read more

SQL Server / Query Optimization / String functions in the WHERE clause and bad estimates

Posted by in SQL Server on Apr 19th, 2018 | 2 comments
SQL Server / Query Optimization / String functions in the WHERE clause and bad estimates
Recently we had really nice meetup gathering here in Auckland NZ. Was a beautiful evening, there were 20 people talking about SQL server, was perfect. One of the topics was “String functions in the where clause and bad estimates” For this demo I used SQL Server 2014 Developer Edition. As a database I used StackOverflow, you can download this DB from Brent Ozar web page, just follow the instructions How to Download the Stack Overflow Database via BitTorrent   Using string function in the WHERE clause can cause bad estimates. I will query TAB Badges (has just clustered index on ID...
read more

SQL Server / Initialize Secondary Database / Generate a full backup of the primary database using SSMS transaction log shipping GUI issue

Posted by in SQL Server, SQL Tips and Tricks on Mar 13th, 2018 | 0 comments
SQL Server / Initialize Secondary Database / Generate a full backup of the primary database using SSMS transaction log shipping GUI issue
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....
read more

PowerShell / SQL Server / Check SQL Server current Update Status and send Email Report

Posted by in Powershell, SQL Server on Mar 1st, 2018 | 2 comments
PowerShell / SQL Server / Check SQL Server current Update Status and send Email Report
Script Download: The script with usage example is available for download from https://gallery.technet.microsoft.com/Use-PowerShell-to-check-05ca591f Summary: Check SQL Server Version and the current patch level for all servers you specified. As well check latest patches/updates available for installed SQL Server version and send email with results. Description: SQL Server Instance Update Status PowerShell script which can be invoked remotely from another PC trough the command line, with PowerShell or executed remotely through task scheduler adding servers names. Script is checking server registry...
read more

SQL Server / DB Backup Size Last Month / Email Alert

Posted by in SQL Server on Mar 1st, 2018 | 0 comments
SQL Server / DB Backup Size Last Month / Email Alert
This is one simple example how you can use sp_send_dbmail stored procedure from msdb database to send DB backup size daily. I used folowing arguments to achive 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, nvarchar(255)), @body_format (Is the format of the message body, varchar(20)) More information about this system stored procedure you can find here...
read more

SQL Server / SQL Server Management Studio could not delete the Secondary server / Secondary not available

Posted by in SQL Server, SQL Tips and Tricks on Feb 20th, 2018 | 0 comments
SQL Server / SQL Server Management Studio could not delete the Secondary server / Secondary not available
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...
read more

SQL Server / Missing Indexes Query / Cached Plans / Email Alert

Posted by in SQL Server, SQL Tips and Tricks on Feb 15th, 2018 | 1 comment
SQL Server / Missing Indexes Query / Cached Plans / Email Alert
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,...
read more

SQL Server / The target principal name is incorrect. Cannot generate SSPI context

Posted by in SQL Server, SQL Tips and Tricks on Jan 12th, 2018 | 5 comments
SQL Server / The target principal name is incorrect. Cannot generate SSPI context
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...
read more

« Previous Entries Next Entries »