Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Treat everyone you interact with as though they are a friend

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

The Hidden Trigger

This editorial was originally published on 18 Apr 2016. It is being re-run as Steve is on vacation.

I ran across a post recently where someone asked about using a trigger for a particular issue. There were quite a few responses, but one person brought up the fact that triggers have various downsides, all of which I agreed with. However I didn't see one mentioned, which is a big one with me.

Triggers are hidden.

It's not that triggers are hidden from DBAs by default, after all, there's a triggers folder under each table in the Object Explorer, but how often do you open that folder? It's just not obvious when a trigger exists on a table. The use of triggers is too uncommon, and it's easy to forget they exist, even if you're the person that added them to a database.

This is really my one big complaint about triggers. I can't easily determine when a trigger is firing, which sometimes leads to issues. I've troubleshot strange things in SQL Servers, only to realize hours later that a trigger is firing based on some action.

Perhaps it's me, but do many of you think about triggers first when something strange happens in SQL Server? I think I've gotten better over the years at looking for them, but I still get stymied and waste time trying to debug issues. I really wish that a "triggers" tab would appear in SSMS, maybe after the Execution Plan tab, whenever a trigger fired. It could have a copy of inserted and deleted, or even just the trigger code. That alone might clue me in quicker.

I think triggers can be valuable in database systems, but because they seem to be so rarely used, I've grown to distrust and dislike them over the years.

Steve Jones - SSC Editor

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

 
 Featured Contents

The Basics of PowerShell Day By Day

FrankDolan77 from SQLServerCentral

A basic introduction to PowerShell for those DBAs familiar with T-SQL.

Using Simple Linear Regression to Make Predictions

Additional Articles from MSSQLTips.com

The first part of this article focuses on using an R program to find a linear regression equation and the second part is devoted to simple linear regression analysis.

From the SQL Server Central Blogs - What Agent Jobs are Running??

gbargsley from GarryBargsley

Do you run SQL Agent Jobs in your environment?  Do you know what is running at any given time?  Most people do not know what is currently running if...

From the SQL Server Central Blogs - Unboxing SQL Clone 2020 for a PoC

Steve Jones - SSC Editor from The Voice of the DBA

When I got a new machine, I had SQL Clone on my old machine, in a few VMWare VMs. I decided to move this to my new machine, so...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Backing up a File for an offline filegroup

I have a database on SQL Server 2019 that has two filegroups. In the Archive filegroup, I have three files: FGArchive1, FGArchive2, and FGArchive3. I take Archive 1 offline with this code: 
ALTER DATABASE FGTest MODIFY FILE (NAME = N'FGArchive1', OFFLINE)
GO
What happens if I try to run a backup of this file with this code? 
BACKUP DATABASE [FGTest] FILE = N'FGArchive2' 
TO  DISK = N'D:\SQLBackup\FGARchive2.bak' 
WITH NOFORMAT, INIT,  NAME = N'FGTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
 

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)

Filtering the Dataframe in Python

I have a dataframe in Python, sales, that I created with the pandas library. I have a field in the dataframe called 'State'. I want to get only the rows from the dataframe that have a value of "New York" for the row. Which code should I run?

Answer: sales.loc[sales['State'] == 'New York']

Explanation: The loc() function will let you slice the dataframe. Including the column for the dataframe in here with an == and the value will return the rows you need. Ref: loc() - https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.loc.html

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 2017 - Administration
How to get drive space using Queries on SQL Server hosted on Linux - Hi, I need to get the free drive space details on SQL Server using queries, this server is hosted on a Linux Server. Require a query to give drive free space details. Thanks
SQL Server 2017 - Development
Unable to debug a sproc - I'm trying to debug an SP in visual studio 2019 (what a palava! bring back this ability in SSMS please!!) Anyways. I have created a connection to the DB and tested successfully. Now I add a script to the project and this contains my exec SPname code. Next I select execute with debugger and […]
SQL Server 2016 - Administration
Cannot insert duplicate key in object - In table tUSERI primary key was added in the column [Email]. I deleted a user abc@gmail.com from tUSER. When I try to add the same user abc@gmail.com got the following error. What causes the error? Please help clean and update the table tUSER. Violation of PRIMARY KEY constraint 'PK_tuser_2'. Cannot insert duplicate key in object […]
Much lines from soms in audit - I had the requirement to put on auditing. I made the server specifications, put in a file, and read the file info a database only the security officer can read. But now he sees too much. Every action in ssms is put in the database. The activity monitor is one of the worst. Can I […]
Availability groups - licencing considerations - Hi all   I'm trying to my facts in a row before I go to managers with this one.   We have one copy of our production data (from the software vendor) on server A (I know, it's the only copy and I've been shouting about that forever). What we want to do is set […]
Folders in C:\Users - are they really needed? - I have some time on my hands (sadly) and am using some of it to clean up my computer. The computer in question runs Windows 7 (no, 'upgrading' to Windows 10 is not an option) and I have used it for many years. Over time, I have installed at least three versions of MS SQL, […]
SQL Server 2016 - Development and T-SQL
Flattening XML - I'm sure this has been asked a 1000 times before, but every example I've seen works great, but doesn't seem to translate well into my XML format - I have no control over the XML I have the following XML: DECLARE @xml XML = '
[…]
Column Level Collation Japanese_90_CI_AS_WS - Having issues getting the column level collation to function. The default collation of the database is SQL_Latin1_General_CP1_CI_AS. Inserted the following characters below ??? into the table and render as ??? as expected. I altered the column collation to Japanese_90_CI_AS_WS and yet to get it to render properly. I am able to get the characters to […]
Is there a tool to automate SQL tuning and review? - I am looking for a tool that reviews and tunes SQL queries automatically. Does Microsoft or a 3rd party vendor have such a tool? Ideally such a tools could be used to automate this process fully. If it could perform standards checking that would be even better. Any ideas?
Development - SQL Server 2014
Previous row where condition exists - Hello, I have a table of data in which I've identified some duplicate records.  I need to pull all of the duplicate records for someone to review.  My problem is that I'm only able to pull one of the duplicates and not both.  The sample below just includes those values which are actually matching (duplicates) […]
SQL 2012 - General
Why aren't the backups working? - You've heard of "the accidental DBA", right? Well, I'm the accidental TFS admin. I have a bit more knowledge of TFS, than the average developer, ergo when the former TFS admin left, I got stuck with it. But my question is more about SQL Server, than TFS. Our TFS backups haven't worked in over a […]
SQL Server 2019 - Development
Is there a version of SQL Server that does automatic SQL tuning? - Is there a version of SQL Server that does automatic SQL tuning? I am also looking for automatic index creation based on queries executed. I know the query store from SQL Server 2016. I am sure that many things have changed since 2016. Any suggestions?
SQL Azure - Development
Create a User in Azure SQL Database - I need to create a user in Azure SQL Database and map the user to a specific database. does anyone have the syntax to make this happen? The GUI is restricted and you cant do it as you would in SSMS on-prem as you cant view the properties. Also I am after a decent script […]
General Cloud Computing Questions
Career advancement in cloud - I am a SQL server administrator with 5 years of pure DBA experience. I am planning to get trained into cloud like Azure or AWS or any other cloud technology where there is a future growth. Can someone guide me which patch to take, is it Azure or AWS. Since i am an administrator i […]
Analysis Services
Random excessive CPU consumption - Hi all. I'm in a bit of a quandary with one of our Analysis Services servers, and I'm hoping you may have some suggestions on how to solve our issue. Basically we have 7 Cube servers, all with identical system specs. Startup log: (8/6/2020 11:23:20 AM) Message: Service stopped. (Source: \\?\R:\Analysis\Log\msmdsrv.log, Type: 1, Category: 289, […]
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -