Currently Browsing: SQL Server

PowerShell / SQL Server Backup / Cannot find type Microsoft.SqlServer.Management.Smo.Backup

PowerShell / SQL Server Backup / Cannot find type Microsoft.SqlServer.Management.Smo.Backup
In one of my previous posts, I show you how to use PowerShell to manage your SQL Server database backups and how to install the SQL Server PowerShell module. To be able to manage SQL Server Database Backups you will need to load Microsoft.SqlServer.SmoExtended assembly. If not you can end up with the following error. PS>TerminatingError(New-Object): “Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: verify that the assembly containing this type is loaded.” new-object : Cannot find type [Microsoft.SqlServer.Management.Smo.Backup]: verify that the assembly containing this type...
read more

PowerShell / SQL Server / Cannot find type Microsoft.SqlServer.Management.Smo.Server

PowerShell / SQL Server / Cannot find type Microsoft.SqlServer.Management.Smo.Server
Some user complained that they cannot use my PowerShell script published on TechNet https://gallery.technet.microsoft.com/Use-PowerShell-to-Backup-3bb0a397 It is a PowerShell script to Backup all user databases to Azure Storage. They experienced the following error: 8/14/2018 11:18 AM – Setting Folders PS>TerminatingError(New-Object): “Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.” new-object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is...
read more

SQL Server / Missing Indexes Query / DMV / Email Alert

SQL Server / Missing Indexes Query / DMV / Email Alert
This is an example of missing index query (DMV) of the database within email alert, we already had examples how you can use sp_send_dbmail stored procedure from msdb database to send backup size email daily,  long-running queries report and how to send missing indexes (Cached Plans) 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...
read more

SQL Server / Query Optimization / TempDB Page Splits / Table Spool (Lazy Spool)

SQL Server / Query Optimization / TempDB Page Splits / Table Spool (Lazy Spool)
Recently I had a customer with a really slow query execution and a case of large page splits number on the temporary database. In this example, I tried to replicate the similar issue on my local SQL instance using StackOverflow database. You can download this StackOverflow DB from Brent Ozar web page, just follow the instructions How to Download the Stack Overflow Database via BitTorrent   Firstly I created an Extended Event session to follow up if the page splits occur. DBCC DROPCLEANBUFFERS CREATE EVENT SESSION [PageSplits] ON SERVER ADD EVENT sqlserver.page_split ADD TARGET...
read more

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

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

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

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

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

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

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

« Previous Entries