SQL Server Maximum Columns per table

Question: What is the limit on SQL Server Maximum Columns per table? I have a very wide Excel worksheet and want to migrate the data to a SQL Server table - where we can then manage the data? Answer: The standard SQL Server table maximum columns limit is 1024. Tables that include sparse column sets include up to 30,000 column A Sparse column set combines the table's sparse columns and translates into a structured representation. The column set is not stored physically in a table. Read more on SQL Server tables Table sizes and percentages (SQL Server DBA) SQL Server... Read more →

How to get ODBC drivers using Powershell

Question: I had a question from a developer who was troubleshooting an application to SQL Server connection via a DSN using ODBC. They wanted the SQL Server ODBC Driver names and platform (32 bit|64 bit) used to connect . They will use this information to check application compatibility . Although i can just RDP onto the server and grab the information through the ODBC gui - how can I use Powershell to get these ODBC details? Answer: You can extract the ODBC Driver name and platform via the Powershell cmdlet - Get-OdbcDriver. There are various options including to run the... Read more →

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 →