SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

High Deadlock Priority

The Voice of the DBA

Gigging for a Career

This editorial was originally published on Oct 24, 2017. It is being re-run as Steve is away at the Data Community Summit.

There are some people that like working for an organization, sacrificing some compensation and flexibility for stability and security. Others enjoy the chance to experience constantly changing environments and a rich variety of projects at the expense of regularly searching for new work. Neither is necessarily better or worse than the other, and these aren't polar opposites. In the real world, each includes some of the advantages and disadvantages of the other.

For many of us that work with data, we realize there are some advantages to understanding the business meanings and implications of the information derived from data. Indeed, those that tend to work with the same types of data become fluent and comfortable manipulating and discussing how analysis, transformation, and more relate to that type of data. This leads me to think that someone that works for a company or stays within a particular industry might be preferred as a data professional for organizations in that space. Maybe this is someone that an organization wants to hire and retain over time.

I ran across a piece that discusses the Gig Economy from the perspective of data professionals. Their view is that many data professionals would rather work on interesting projects, and analyze data that is interesting to them, rather than being stuck with a single organization. Certainly from the organization's view, having very skilled professionals available for project work means less costs in training, benefits, hiring, etc. While the cost per day might be high, there is no need for an ongoing, or at least not a constant, commitment.

I do think that many of the changes in technology have made it possible for talented workers to find plenty to keep them busy and earn a very good living. However, there's a cost in spending time looking for projects on a regular basis. It takes not only time, but mental strength and a desire to be a bit of a salesperson and marketing professional. Some people may find long term clients that call them over and over, which isn't that different from working for an organization unless you can interleave a number of clients together on a regular basis. However, most people that work at "gigs" are regularly spending time looking for work.

I would postulate that most of us prefer some amount of security and stability, preferring to work for an organization for a period of time. While we may change jobs at times, it's often at a lower pace than those that might prefer to work in the Gig Economy

Steve Jones - SSC Editor

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

Featured Contents

Azure Data Studio - A First Look

Kenneth Igiri from SQLServerCentral

See a first look at Azure Data Studio from Kenneth Igiri and read about his impressions.

It’s Probably Time to Upgrade Your Cloud VMs

Additional Articles from Brent Ozar Unlimited Blog

If you’ve been in Azure or Amazon for a few years, you’re probably on old, slow hardware.

Security in the cloud: Whose responsibility is it?

Additional Articles from Redgate

While the cloud is recognized as more secure than on-premises servers and infrastructures, it does come with the often talked about shared responsibility model. Cloud providers are responsible for security ‘of’ the cloud, while their clients are responsible for security ‘in’ the cloud. Find out more in this Q&A with Dustin Dorsey

From the SQL Server Central Blogs - Tips, ideas, and recommendations on your technical blog

Will Assaf from SQL Tact

This technical blog is hardly the ideal, but I'll use this space here to summarize some of the guidance that my colleague Randolph West and I presented at a...

From the SQL Server Central Blogs - About those Read-Only Replicas

sqlrunner from SQLRunner

In a post, I wrote last month I showed how you can take advantage of your replicas in SQL Sever by adding a single attribute to your connection string...

SQL Server 2022 Administration Inside Out

Site Owners from SQLServerCentral

Dive into SQL Server 2022 administration and grow your Microsoft SQL Server data platform skillset. This well-organized reference packs in timesaving solutions, tips, and workarounds, all you need to plan, implement, deploy, provision, manage, and secure SQL Server 2022 in any environment: on-premises, cloud, or hybrid, including detailed, dedicated chapters on Azure SQL Database and Azure SQL Managed Instance.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	High Deadlock Priority
Deadlock priorities range from -10 to 10. If I run this code, what is my deadlock priority? SET DEADLOCK_PRIORITY HIGH
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)

The Database Transaction

What happens if I run this?

BEGIN TRANSACTION
CREATE DATABASE newdb
ROLLBACK TRANSACTION

Answer: An error occurs

Explanation: The CREATE DATABASE transaction runs in autocommit mode and cannot be in a transaction. Ref: CREATE DATABASE - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=sql-server-ver16&tabs=sqlpool

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

SQL2017 DBMirroring Query store not working on mirrored databases - I have noticed on our SQL2017 mirrored databases Query store is not working. ( Moving to AGs is not an option. ) Settings in use: ALTER DATABASE [MyMirroredDb] SET QUERY_STORE = ON GO ALTER DATABASE [MyMirroredDb] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 100, […]

SQL Server 2016 - Administration

SQL Server Transactional Replication-Snapshot Initialization for existing REPL - Hi, This is Syed JB, I have an existing Transactional Replication. Unfortunately, for some reasons Replication has been removed for some time. But, now, we have to rebuild the Transactional Replication. Here is the problem, Subscriber has the historical data and for this I can’t push the snapshot and without the initialization, subscriber subscription […]

SQL 2012 - General

SQL Scripts automatically invoked - When detecting the processes in the SQL Server 2012, I found there was a SQL scrit executed automatically automatically many times, the following as attachment, do you know when the script will be executed and when it will be executed? thanks a lot!

SQL Server 2019 - Administration

Log shipping time_since_last_backup - I am using SQL server 2019, I have a database setup transaction log shipping on Server A, and secondary on Server B. I have a need to setup another database to also use transaction log shipping, also primary db is on ServerA and secondary is on Server B. When I setup the Alert monitor instance […]

SQL replication error. - Hi I´m having two SQL server cluster ( lets say A and B. ) with a number of instances ( lets say A1, A2,.. , B1,B2...) in two different sites. I want to replicate A\A1 database Test to server B\B1 database Test and server B\B1 database test2 to server A\A1 database Test2 ( hope […]

Sorry posted in admin section - Sorry posted in admin section.

SQL Server 2019 - Development

Find a word from string - Hi All I would like to get rows wherever it has "restricted" . If I write condition with like '%restricted%' it is showing me all. create table #tbl_datafile_list (Autogrow varchar(500)) go insert into #tbl_datafile_list values ('64.0 MB, unrestricted growth'), ('By 10 percent, restricted growth to 500000.0 MB'), ('By 10 percent, unrestricted growth'), ('1.0 MB, unrestricted […]

Has SQL Server Introduced a way for Converting HTML to non-HTML stored as VARCHA - If a table stores in a column of type VARCHAR the textual equivalent of an html page, does SQL Server have some but in function or process for converting that into non-html text, just regular text with carriage returns and the like? I've seen a few custom solutions for this but noting native to SQL […]

SQL Azure - Administration

IX fragmentation and Updating Stats - Though its Azure MI Business critical , I see fragmentation, Any script to identify which IX's need to be rebuilt ,as Frag is over 50%. I will add REBUILD WITH (ONLINE=ON) as we cannot have downtime , Its a 24 x 7 DB. I see failed inserts on dynatrace every now and then, possibly as […]

Good Azure cost calculator - Azure calculator doesn't give a good estimate as it cannot differentiate between HA/ DR and primary replica. I have looked around , I couldn't find a good way to come up with an estimate, wondering how others have done this before?

shrink data file with low priority - so total size of DB on Azure is 1 TB, but when I add all tables ( Data file ) and log file the total size is 100 GB. So I need to reclaim the 900 GB ( release storage to the OS ) . MS recommends running DBCC shrinkfile with wait at low priority […]

Reporting Services

HSTS - need to enable in SSRS 2022 - Hi, My sys admin says I must enable HSTS in SSRS to pass penetration tests. Using this as guide: Enabling HSTS and selecting most secure ciphers and protocols for HTTPS for ConfigMgr I change the custom header property and restarted the ssrs service. Problem is my sysadmin is saying that is still not passing the HSTS […]

Analysis Services

Product Hierarchy in a Matrix with 8+ measures on Values-- Perfomance - I'm not new to PowerBI, but I'm new to this problem. I'm working for a company that's creating a PowerBI solution on Azure for a retail company. They have a proper star schema (Sales fact in the middle, and then these dimensions: Product (hierarchy, 8 levels), Date, Store Hierarchy. And then they have like 8 […]

SQL Server 2022 - Administration

None of the IP addresses configured for the availability group listener - Hi , Receiving below error when trying to add listener to AG. Any suggestions please. ALTER AVAILABILITY GROUP xxxx ADD LISTENER N'xxxx' ( WITH IP ( ('xx.xx.xx.xx', 'xxx.xxx.xxx.xxx') ), PORT=1433 ); Error: Msg 19456, Level 16, State 0, Line 1 None of the IP addresses configured for the availability group listener can be hosted […]

SQL Server 2022 - Development

How to Efficiently Find and Remove Duplicates in a Large SQL Database? - I have a large SQL database with millions of records, and I've identified duplicate entries. What's the most efficient way to find and remove these duplicates without compromising database performance or data integrity? Are there any best practices or SQL queries that can help in this situation?" This question addresses a common database maintenance task […]

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.