SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

The Log File Structure

The Voice of the DBA

Daily Coping Tip

Make a list of things you are looking forward to

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.

Clownpocalypse and DR

Today we have a guest editorial from Grant Fritchey as Steve is on holiday. This editorial was originally published on 24 Oct 2016.

You plan and plan in preparation for the zombiepocalypse. You lay in weapons. You get food and water supplies built up. You take training in survival skills, martial arts, sword & firearms. You build a fence on the property and put in an alarm system along with your generator and a well. You are ready for the zombies to arrive. Heck, you’re kind of bored waiting for the zombies to arrive. What do you get? Clowns.

Clowns?

Clowns.

Here’s the good news, all that preparation, funny enough, still applies to clowns. You’re still ready for a siege, but instead of shambling, rotting corpses, it’s a horde of clowns, all coming out of a single car. The training, the supplies, the weapons, they’re all still going to be useful.

What happened to the Data Platform you ask? Here’s the thing, you don’t know what your disaster will actually be. Yeah, you might have severe database corruption caused by extra-solar radiation (same stuff you thought would cause the zombies) affecting your disks. All the backups and practice restores in preparation for that corruption will serve you well when it happens. What if it doesn’t. What if someone just turns off the SAN? Did all that preparation go to waste? Not at all. You’re still prepared.

Whether it’s a flood from a hurricane, or a flood from bad maintenance in the bathroom of the floor above your server room, the preparations you’ve made are still useful. Yes, we’re all very disappointed that it’s a clownpocalypse instead of a zombiepocalypse. The good news is, regardless of the source of the trouble, the preparations made for it are still going to be useful. Same thing goes for all the work you do in setting up a proper disaster recovery processing for the data you manage.

No clowns were harmed during the composing of this bit of whimsy. The same can’t be said of zombies.

Grant Fritchey

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

Featured Contents

Great Power(Shell)...Great Responsibility

Cláudio Silva from SQLServerCentral

-WhatIf you could see what would happen if you ran a command?

SQL Server Synonyms

Additional Articles from SimpleTalk

SQL Server synonyms can be used to permanently alias database objects in the same or another database. In this article, Greg Larsen demonstrates how to use synonyms and the many reasons to consider using them.

From the SQL Server Central Blogs - A table is like a library : T-SQL Tuesday #131

Kenneth.Fisher from SQLStudies

Rob Volk (blog|twitter) is our host for TSQL Tuesday this month and wants us to Explain a database subject like ... Continue reading

From the SQL Server Central Blogs - QDS Forced Plans - Gotchas and Limitations

cjsommer from cjsommer.com

Released with SQL Server 2016, Query Data Store was a game changer for performance tuning.

Right from Microsoft’s website: The SQL Server Query Store feature provides you with insight...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The Log File Structure
What is the physical structure of a transaction log file?
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)

Setting Affinity

I want to set an affinity mask in SQL Server 2019. What sp_configure setting would I choose to alter to make this change?

Answer: affinity64 mask

Explanation: There is an affinity mask option, but this is only for 32 bit systems. SQL Server 2019 is only available as a 64 bit applications. NOTE: This option should not be used with sp_configure. Instead ALTER SERVER CONFIGURATION is the way to change this. Ref:

affinity64 mask Server Configuration Option - https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/affinity64-mask-server-configuration-option?view=sql-server-ver15
ALTER SERVER CONFIGURATION - https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-server-configuration-transact-sql?view=sql-server-ver15
Hardware and Software Requirements - https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-ver15?view=sql-server-ver15

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

Set permisson user allow connect with application, don't allow connect with ssms - Hello, guys. I use .net to code, can i set permisson a sql user only can connect in connection string in .net code, and not allow that user login with ssms ?

Maint Plan Failing after 'sa' Disabled - I have 4 Maint Plans, 1 of which fails because 'sa' is disabled, but I don't see 'sa' specified in the plan. It rebuilds indexes, updates statistics, deletes old backups, then takes new backups. The other 3 Maint Plans just run DIFFS and T-Log backups. All 4 Scheduled Job Owners are a Windows account. On […]

SQL Server 2016 - Administration

RING_BUFFER_RESOURCE_MONITOR - IndicatorsPool? - Hi all, i've been looking into an odd issue with possible plan cache memory starvation (SQL couldn't generate a query plan prior to timeout for unchanged procs that happily compliled previously) , and spotted quite frequent RESOURCE_MEMPHYSICAL_LOW events. IndicatorsProcess and IndicatorsSystem are both 0, but IndicatorsPool is 2, and I can't find any information around […]

Always On Availability Groups Issue - Hi, I see couple of below errors for 2 sql server instances i.e. primary and secondary. When I see the dashboard, it displayed the state as critical and after few minutes it turned green (I mean the databases we are online and synchronized). I do not see any databases on both the instances in recovery […]

SQL Server 2016 - Development and T-SQL

A real challenge and I need suggestions on efficient ways to solve it - I have a need to determine the site of cancer given some complexities. Each cancer patient has up to 4 diagnosis codes with the first being the primary. I currently have a mapping table that is capable of mapping up to two values to a result value. Currently I only check the primary diagnosis to […]

Varchar to datetime - Hi, How can I convert SCHEDSTART varchar(25) '2020-06-07-12.30.00.00000' to datetime '2020-06-07 12:30:00.000' SELECT convert(datetime,(left([SCHEDSTART],10) +' ' + left(Right([SCHEDSTART],14), 12) ), 127) didnt work. Any help ?

Administration - SQL Server 2014

PBM - result_detail column in syspolicy_policy_execution_history_details - Hello, we use PBM to monitor last full and last log backup. PBM created, nothing special so far. We created a stored procedure and a job querying syspolicy_policy_execution_history joining syspolicy_policy_execution_history_details to get the result of the PBM and send it as e-mail. So far so good. But we want to include the expected result in […]

SQL Server 2012 - T-SQL

using parameter and not using parameter , the result is different - declare @YYMMDD DATETIME set @YYMMDD='2020-09-23 00:00:00.000' select CONVERT(VARCHAR(6), '2020-09-23 00:00:00.000', 112) , CONVERT(VARCHAR(6), @YYMMDD, 112) same value, when we use parameter to pass the value, and not using parameter, the result is not same, what is the reason ? thanks!

SQL Server 2019 - Administration

How does dns determine what ip to resolve to in an multisubnet AG? - When a SQL Listener is created on a Multi Subnet environment 2 x ip addresses are assigned. AG Name - SQLAG1 AG Listener Name - SQLAG1-LI 10.0.0.1 10.0.10.1 I have given the ip addresses above for example. In DNS there are 2 x entries for the SQLAG1-LI one for each of the IP addresses. A […]

SnapCenter Server Backup for SQL - Hello, do you have any experience with SnapCenter Server 4.3. Can we say that is a valid backup? can we use like a primary backup? I have some testing now, and I am afraid how is working with this web app. Thanks

I've lost the ability to connect to my SQL 2019 instance - On my home Windows 10 Professional desktop PC, I've got SQL Server 2019 Developer Edition installed. I thought I had mixed mode but am not so sure. A month or two ago I had problems with my Windows profile - it became corrupted. So, I disabled it and created a new Windows Profile. I thought […]

SQL Server 2019 - Development

\'DBMS_DATA_MINING\' is not a recognized CURSOR option - Is DBMS_DATA_MINING available in SQL Server 2019? This is error: 'DBMS_DATA_MINING' is not a recognized CURSOR option

Analysis Services

Where is the AdventureWorks 2008R2 Analysis Services project download? - I need the 2008R2 version because I want to climb the Stairway to MDX and in Level 1 it says: Consider working, therefore, with 2008R2, if at all possible: learning the basics of MDX is challenging enough for most that are new to it, without the additional distractions imposed by working with older releases. In […]

Integration Services

SSIS Package that updates two databases on the same SQL Server instance - Hi, I have a large script that is querying and updating two databases on the same SQL Server instance (one client database and the MSDB). I'd like to publish this script in an SSIS package, but when I do so it complains that I don't have authorization to update the MSDB database. I have created […]

SSIS package error on remote database - hello everyone, so i have created a SSIS package that i want it to run on another server, so example below: Server A is running the SSIS package, and the destination is Server B, that i want it to run under a certain account, like either the network service or system service, which has the […]

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.