SQLServerCentral.com Community Update for May 30, 2025

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

A Basic Data API Builder ADD

The Voice of the DBA

Deleting a Database

This editorial was originally published on May 12, but a newsletter snafu didn't send it out. It is being re-scheduled for today.

Who among us has deleted a production database?

I'd hope it's very few of you that have done this in your career. I'm sure a few of you have deleted (or truncated or updated all rows for) a table in production. I've done that a few times, but fortunately, I've been able to recover the data quickly. I had this happen in SQL 6.5 and was grateful I could start a single-table restore before my phone rang.

Here's another question: which of you has had a storage admin delete or remove some remote storage and cause you database problems? Has anyone had that happen in their environment? I haven't had this in production, but I have had this happen to test systems, and I was very irate with the storage people when it did. After that, I'm sure they were very cautious about changing any configuration for database servers. I'm also sure that also contributed to my struggles in getting more space promptly as well, so I'm not sure I came out ahead in that situation.

A hospital system had issues after this happened to them: engineers deleted critical storage that connected to a database system. Fortunately, no services have stopped, and no patients are missing services, at least as far as we know. Everything has to be moving slower, and that might mean that staff is spending more time on "downtime procedures", i.e. paper, than focusing on patients. Knowing a few medical professionals, this means they're more stressed and working harder to be sure patients aren't affected. Sucks for them, and I doubt the hospital compensates them for an engineer's mistake.

I haven't heard about this happening in a long time, and I'm surprised by that. Almost all storage these days for server systems is remote, especially in the cloud, and it would be easy to click the wrong button or select the wrong disk when re-configuring a system and remove critical storage. Maybe we've gotten better at popping up warnings that slow people down and prevent mistakes.

Or maybe we don't delete disks and only add them to database systems

In any case, I hope they can recover things quickly and easily. If you've seen this, let me know. If you haven't, here's a reminder that this could happen. You should be sure your backups are running AND you can perform a test restore.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

Sentiment Analysis & Key Phrase Detection

williamsnoori from SQLServerCentral

Learn how you can conduct a sentiment analysis on data loaded into an Excel spreadsheet.

Power BI Mistakes to Avoid – Part 2

Additional Articles from MSSQLTips.com

What advice would you give to someone new to Power BI? What are common Power BI mistakes to avoid? In this article, we will discuss several Power BI mistakes to avoid to help you navigate this powerful tool effectively.

From the SQL Server Central Blogs - Why Data Silos Hurt Your Business Performance

Chris Yates from The SQL Professor

Businesses rely on data for decision-making and strategy, but data silos hinder productivity. These silos lead to ineffective decisions from incomplete data, wasted resources through redundant efforts, and missed...

From the SQL Server Central Blogs - KDA: Echoes of Deception - Case 1

Zikato from StraightforwardSQL

Another KDA case: Digitown’s utility bills suddenly doubled for no good reason. With the election coming up, I got pulled in to figure out what went wrong. I’ve got...

Learn Microsoft Fabric: A practical guide to performing data analytics in the era of artificial intelligence

Site Owners from SQLServerCentral

A step-by-step guide to harness the power of Microsoft Fabric in developing data analytics solutions for various use cases

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	A Basic Data API Builder ADD
What parameters are required for the dab add command in the Data API Builder?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Removing a File

I attempted to use ALTER DATABASE to remove a file from my filegroup in SQL Server 2022. There are 4 files in this filegroup, but I don't need them all.

However, I received a message that the file was not empty. How do I empty this file so it can be removed?

Answer: Use DBCC SHRINKFILE with the EMPTYFILE parameter

Explanation: DBCC SHRINKFILE with the EMPTYFILE parameter is the best way to do this. Ref: DBCC SHRINKFILE - https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver16#arguments

Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 - Development and T-SQL

Question on SQL Query with NOT condition - I have a record, in those the three columns where firstname / last name and full name is empty. ID = 198765 FirstName = empty LastName = empty FullName = empty Flag = 0 I am trying to pull the record with ID 198765 out from select query where first and last or full name […]

SQL Server 2019 - Administration

Deadlocks and Backups - Need help - Hey everyone, please excuse my ignorance, I'm just .NET developer and have used SQL for years, but as someone pulling and manipulating data, but not much in the realm of administration or in this case troubleshooting deadlocks. I've tried to get my higher ups to get a db expert but it seems this task has […]

Building Flame Diagram for MSSQL stored procedures - I wanted to share with you tools from my personal toolbox. If your code has many nested executions of stored procedures, you can benefit from building popular "flame diagram" of the execution time which is de facto standard for performance profiling. Here is step-by-step guide. Creation of a trace Start SQL profiler and select the […]

SQL Server 2019 - Development

Find Duplicate Rows - What’s the best way to find duplicate rows in a sql query? I heard there is a way where RC is used and it gives RC 1 for the first row and RC 2 for the duplicate row. But would like to know the syntax for it. I tried: select itemnumber , count (itemnumber ) […]

Is there an ISERROR equivalent in SS? - Hi everyone I have a SP that just failed. Format of SP is: Select symbol, date, calc1, calc2 from sub-query It is due to bad data so I need to update the SP so it handles the scenario better. I am proposing the following logic: CASE WHEN "calc1 has error" THEN NULL ELSE calc1 END […]

isnull, coalesce overhead - hi, one of our sister division's erp extract view is going thru at least one mod to do the equivalent of a coalesce on a column that if null brings down our dw load job. since they were doing that i asked if they could do the same on all columns that had never be […]

is there an elegant way for agent notification to show failure if any step fails - hi our corp dw load sqlagent job goes to the next step on the 14+ erp loads if they fail. but quits notifying of failure on any cube step at the end. most of the loads are ssis but there is some t-sql too. without making the job really ugly, is there an elegant way […]

Merge spans with Dates Logic - --for a given member if the startdate and endate is continous we need to keep in single record and if start date and end date is not continous i need to keep in separate record for a given member. drop table #test create table #test (ID int, startdate datetime, enddate datetime ) insert into […]

Analysis Services

Connect tabular model to excel via Power BI semantic model - I have a tabular cube, with myself included in one of the roles. I can connect to this tabular model via Excel successfully, and would now like to connect to a Power BI semantic model rather instead. However I get the attached error. I suspect there additional security needed to be defined, but unsure where. […]

Administration

Article for posting - Hello I sent you an article for posting: "Building the future with a react development company: an in-depth guide" Please reply to this email [email protected] regarding this article

SQL Server 2022 - Administration

Isolation Level in Azure SQL Database - Hello, Can you please clarify the following: In Azure SQL DB both ALLOW_SNAPSHOT_ISOLATION/READ_COMMITTED_SNAPSHOT are set to ON: ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON Is it sufficient to implement RCSI or I still need to add either to connection string or right to the top of the query: SET TRANSACTION ISOLATION LEVEL […]

SQL 2022 upgrade issues - We're about to upgrade our SQL 2017 Standard to 2022 standard. We have 1 customer who takes a backup and restores it to their 2019 instance (so they can run Power BI reports). We cannot postpone it, and they have asked if we could consider a BACPAC from our new 2022 to their 2019 version. […]

SQL Server 2022 - Development

Remove ISNULL FROM left join to make SARGAble - Hi, I'm trying to make the below query SARGable by removing the ISNULL function part of the join. (Part of join in italic) Any suggestions? SELECT ..... FROM dbo.historicsummary hs LEFT JOIN dbo.Status fs ON hs.ID = fs.ID AND hs.TimeStamp >= fs.ValidFrom AND hs.TimeStamp < ISNULL(fs.ValidTo, DATEADD(d, 1, SYSDATETIME()))

New lightweight pure‑T‑SQL unit‑testing framework (T‑TEST) — feedback welcome - Hi all, I’ve just open‑sourced T‑TEST, a tiny alternative to tSQLt that lives 100 % in T‑SQL—no CLR, no extra binaries. Why you might care one install.sql, < 10 KB of objects every proc in the tests schema is a test (auto‑discover by name convention) inline assertion functions (not SPs!) (test.assert_equals, test.fail, …) one-row recordset assertion (using […]

CAST(VARCHAR(n)) resolved performance issue? - Hi, I would love to hear if anyone can tell me why a CAST in a query can "resolve" this performance issue. Same symptom on test server with a copy of the database. weblab_reportlog = 207 850 195 rows Requisitions = 30 143 466 rows DBCC FREEPROCCACHE SELECT [LID], [Datestamp], [logmessage], [Reporttype], [UserID] FROM [dbo].[weblab_reportlog] […]

This email has been sent to [email protected]. To be removed from this list, please click here. If you have any problems leaving the list, please contact the [email protected]. This newsletter was sent to you because you signed up at SQLServerCentral.com.