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...
PowerShell / SQL Server / Check SQL Server current Update Status and send Email Report
Posted by Barac in Powershell, SQL Server on Mar 1st, 2018 | 2 comments
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...
SQL Server / DB Backup Size Last Month / Email Alert
Posted by Barac in SQL Server on Mar 1st, 2018 | 0 comments
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...
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’....
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)),...
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 | 5 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. ...
Microsoft SQL Server 2017 Updates / RTM (14.0.1000.169)
Posted by Barac in Updates on Jan 8th, 2018 | 0 comments
SQL SERVER 2017 RTM Update Version: MSSQL 2017 RTM CU-29 SECURITY UPDATE, Build: 14.0.3445.2, KB: KB5014553, Release Date: June 2022, Download: https://support.microsoft.com/en-us/topic/kb5014553-description-of-the-security-update-for-sql-server-2017-cu29-june-14-2022-024a90f1-1173-4ade-9c18-816ee7150458; Update Version: MSSQL 2017 RTM CU-29, Build: 14.0.3436.1, KB: KB5010786, Release Date: March 2022, Download: https://support.microsoft.com/en-us/topic/kb5010786-cumulative-update-29-for-sql-server-2017-8b3b4122-ed46-4d33-9d80-256c513ae42e;...
Microsoft SQL Server 2016 Updates / RTM (13.0.1601.5) / SP1 (13.0.4001.0 or 13.1.4001.0) / SP2 (13.0.5026.0 or 13.2.5026.0) / SP3 (13.0.6300.2 or 13.3.6300.2)
Posted by Barac in Updates on Jan 7th, 2018 | 2 comments
SQL SERVER 2016 SP3 Update Version: MSSQL 2016 SP3 AZURE CONNECT FEATURE PACK SECURITY UPDATE, Build: 13.0.7016.1 / 13.3.7016.1, KB: KB5015371, Release Date: June 2022, Download: https://support.microsoft.com/en-us/topic/kb5015371-description-of-the-security-update-for-sql-server-2016-sp3-azure-connect-feature-pack-june-14-2022-d809657e-15a9-48fe-bd19-a8864ac5d3a4; Update Version: MSSQL 2016 SP3 AZURE CONNECT FEATURE PACK, Build: 13.0.7000.253 / 13.3.7000.253, KB: KB5014242, Release Date: June 2022, Download:...
Microsoft SQL Server 2014 Updates / RTM (12.0.2000.0) / SP1 (12.0.4100.1 or 12.1.4100.1) / SP2 (12.0.5000.0 or 12.2.5000.0) / SP3 (12.0.6024.0 or 12.3.6024.0)
Posted by Barac in Updates on Jan 6th, 2018 | 0 comments
SQL SERVER 2014 SP3 Update Version: MSSQL 2014 SP3 CU-4 SECURITY UPDATE, Build: 12.0.6439.10 / 12.3.6439.10, KB: KB5014164, Release Date: June 2022, Download: https://support.microsoft.com/en-us/topic/kb5014164-description-of-the-security-update-for-sql-server-2014-sp3-cu4-june-14-2022-f3400be9-b2d3-4873-ae58-15e6e0cc686e; Update Version: MSSQL 2014 SP3 CU-4 SECURITY UPDATE, Build: 12.0.6433.1 / 12.3.6433.1, KB: KB4583462, Release Date: January 2021, Download:...
Microsoft SQL Server 2012 Updates / RTM (11.00.2100) / SP1 (11.0.3000.0 or 11.1.3000.0) / SP2 (11.0.5058.0 or 11.2.5058.0) / SP3 (11.0.6020.0 or 11.3.6020.0) / SP4 (11.0.7001.0 or 11.4.7001.0)
Posted by Barac in Updates on Jan 5th, 2018 | 0 comments
SQL SERVER 2012 SP4 Update Version: MSSQL 2012 SP4 SECURITY UPDATE, Build: 11.0.7507.2 / 11.4.7507.2, KB: KB4583465, Release Date: January 2021, Download: https://support.microsoft.com/en-us/topic/kb4583465-description-of-the-security-update-for-sql-server-2012-sp4-gdr-january-12-2021-9e49671a-4f8e-1a71-ecc9-d9902d06242d; Update Version: MSSQL 2012 SP4 SECURITY UPDATE, Build: 11.0.7493.4 / 11.4.7493.4, KB: KB4532098, Release Date: February 2020, Download:...
About Me
My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.
read moreAuckland SQL Group
MSDN PROFILE
SQL SERVER UPDATES
- Microsoft SQL Server 2022 Updates / Release Candidate (RC 1) Evaluation Edition (16.0.950.9)
- Microsoft SQL Server 2019 Updates / BETA (15.0.1000.34) / RTM (15.0.2000.5)
- Microsoft SQL Server 2017 Updates / RTM (14.0.1000.169)
- Microsoft SQL Server 2016 Updates / RTM (13.0.1601.5) / SP1 (13.0.4001.0 or 13.1.4001.0) / SP2 (13.0.5026.0 or 13.2.5026.0) / SP3 (13.0.6300.2 or 13.3.6300.2)
- Microsoft SQL Server 2014 Updates / RTM (12.0.2000.0) / SP1 (12.0.4100.1 or 12.1.4100.1) / SP2 (12.0.5000.0 or 12.2.5000.0) / SP3 (12.0.6024.0 or 12.3.6024.0)
- Microsoft SQL Server 2012 Updates / RTM (11.00.2100) / SP1 (11.0.3000.0 or 11.1.3000.0) / SP2 (11.0.5058.0 or 11.2.5058.0) / SP3 (11.0.6020.0 or 11.3.6020.0) / SP4 (11.0.7001.0 or 11.4.7001.0)