Common causes of SQL Server BACKUP LOG Failures

There are some repeat questions I get from Backup or Operations staff - when they receive alerts for a BACKUP LOG failure. There are multiple reasons it could be happening ranging from media failure , human error, software failure ,and SQL Server database status issues e.g OFFLINE Let's leave those to one side for a moment and assume they are all ok . The two main reasons are; 1) the initial database FULL BACKUP not taken and 2) the database in SIMPLE RECOVERY mode 1) Initial FULL BACKUP not executed when database is in FULL RECOVERY mode Msg 4214, Level... Read more →

The mysterious case of Pseudo SIMPLE RECOVERY model and the transaction log explosive growth

I created a database to host a data set for some sql performance testing. The default Recovery Model was set to FULL. The test data load insert was executed and it was expected to take about 40 mins. About 15 mins into the the test data load insert a scheduled full backup started. Up until the full backup started the transaction log growth rate was minimal. At the end of the FULL BACKUP - the transaction log started growing at a much quicker rate - with the same type of transactions. What is going on here? Initially I was scratching... Read more →

How to trace SQL Server Backup

Question: I'm trying to understand for educational purposes , what are the exact steps the SQL Server Backup is taking to progress through a SQL Server backup. Is there a way or method to trace the steps taken to complete a backup? Answer: Before I progress with outlining a method - I want to set a WARNING that this trace flag should only be used in guidance with experienced support staff - and only on non production systems. Thoroughly test on non-production first and ensure consulting is involved Using the 3004 and 3605 DBCC TRACEON (3004 , 3605 , -1)... Read more →

SQL Server Linked Server and MongoDB BI Connector

Step 1 - Ensure MongoDB BI Connector is installed and configured . This will support the access to the MongoDB server. Notes on MongoDB BI Connector Step 2 - Install MongoDB ODBC Driver for BI Connector - provides connectivity between a SQL client and the MongoDB Connector for BI More details on https://docs.mongodb.com/bi-connector/master/reference/odbc-driver/ Step 3 - Create the System DSN You'll need the following base information to connect to the MongoDB BI Connector Server Name\Port User. Password Read further for full details of the Create System DSN options Step 4 - Create the Linked Server This is some sample code... Read more →

How to list computer shares with Powershell and WMI

Question: How can I list shares from a server using Powershell and WMI ? I've been tasked with auditing servers and identifying shares which are not authrorized Answer: The two main methods I use are 1) Get-Smbshare and 2) get-WmiObject -class Win32_Share. Get-Smbshare examples --list all SMB shares on a server Get-Smbshare --list all SMB shares on a specific server Get-Smbshare -CimSession "my_server_name get-WmiObject -class Win32_Share examples --list all share on the current server get-WmiObject -class Win32_Share --list all shares on a remote server get-WmiObject -class Win32_Share -computer my_computer Read more on Powershell and server management How create a drive... Read more →

How to use Database Tuning Advisor via command line

Question: Is it possible to use SQL Server Database Tuning Advisor through the command line? Answer: On a server with SQL Server installed you can start the SQL Server Database Tuning Advisor In the command line type "dta".That will return something like : Microsoft (R) SQL Server Microsoft SQL Server Database Engine Tuning Advisor command line utility Version 14.0.800.90 ((SQL_Main).170711-1339) Copyright (c) 2015 Microsoft. All rights reserved. Additionally - you can use : dta -? and that will give you extra usage details along with descriptions per switch Usage: DTA.EXE [-S ServerName[\Instance]] [-U LoginId] [-P Password] [-E] [-d DatabaseName] [-D... Read more →

How to fix Exception caused by space in text for Powershell form

Question: I'm building a Powershell form GUI utilising the System.Windows.Forms API. I'm getting an error when passing text value back to be processed from a text box defined by : $CommentsTxtBox = New-Object System.Windows.Forms.TextBox Exception calling "ExecuteReader" with "0" argument(s): "Incorrect syntax near 'mytext'." When I then take the space out of the Comments value – i.e change “New Value” to “NewValue” I get the New value generated message. So the space within the text value is generating the error How can I fix? Answer: The quick answer is to place ' ' around the incoming value where you are... Read more →