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 →