SQLServerCentral.com Community Update for March 16, 2020

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Backing Up Some Log

Featured Script

Orphaned Users Search and Destroy

The Voice of the DBA

The WFH Challenges

The daily protocol for some of the world is changing rapidly these days. The COVID-19 pandemic is affecting many people. Many of us working in tech are blessed that we have the ability to work remotely, and lots of companies have started to allow working from home (WFH) to rapidly grow in the last month. I worry about those that don't have the option, and I hope they find ways to cope.

I've been working from home for 17 years. My wife did it for large tech companies for almost 20 years. It's not hard, but it's not simple. Kendra gave us three tips last week, and I'm adding a few more since

I ran across a piece in the NYT from a writer that's been forced to work at home more due to the COVID-19 pandemic. It notes that working at home isn't a panacea, and there are creativity losses from separation. I tend to agree, which is why I normally go to the Redgate offices a few times a year.

I will say that the switch to working from home is an adjustment. Alice Goldfuss has a long post on this, and there are a few thoughts from Andrew Pruski on his first two years. If you're interested, Eugene Meidinger also talks a bit about working for himself at home, and the loneliness of the first three months. Mala also has some resources that might prove helpful.

If I think back, I remember a lot of the challenges of being at home and not going to an office. I spent over a decade going to an office and starting to work at home was hard. My wife had been doing it for 7 or 8 years at that point and sharing an office was hard. She was always on the phone, which was louder than the open office plan I'd had at JD Edwards. Or she was gone, and I was lonely. I'd go for walks with the dog or hit the gym most days. I learned that I had a kitchen, but I was less prepared for lunch because I didn't have to make it and take it anywhere.

At one point I took a part time consulting gig to get out of the house. That gave me more purpose and satisfaction, but it meant my days were more busy and I struggled to separate work from home. I still struggle slightly with this, but I have learned to balance life out. If I need a break on a Tuesday, that's fine. If I need to work on a Saturday to catch up, that's fine. Sometimes I actively make that trade, especially during ski season.

Working from home is a change, and it's disruptive. I mostly work on solo projects, but I do need to collaborate and find the Slack, Teams, and video calls are helpful. I do have a separate office, and I've had to explain to family that working is working, and they should not just interrupt me at any time. I have to learn to take breaks, and I have to learn to build a routine. You will too, and it's something I'd recommend. Take advice from others and try something for a week. See how it works for you, remembering you have to build some habits and try them out. Don't abandon something immediately, and don't think that the way you start working this week is something you're stuck with forever.

Stay safe during this time and work to keep yourself mentally health until the world gets back to normal activity.

Steve Jones - SSC Editor

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

Featured Contents

Combining many crossing intervals

Bogdan SAHLEAN from SQLServerCentral

This article demonstrates how the geometric functions in SQL can be use to solve the Islands and Gaps problem.

Overview of Azure Cache for Redis

Additional Articles from SimpleTalk

Caching data is one way to improve application performance. In this article, Chandra Kudumla explains a service for caching data found in Azure called Azure Cache for Redis.

Moving on from Volunteer Projects

Kathi Kellenberger from SimpleTalk

I can’t remember a time when I was not involved in side-projects, usually as a volunteer but also writing books or creating courses. New projects are like bright, shiny objects, and it’s easy to get involved in too many of them.

From the SQL Server Central Blogs - Improved VMware vCenter Performance Metric Logging

kleegeek from Technobabble by Klee from @kleegeek

By default, the vSphere vCenter deployment contains a virtual appliance where the VMware management and orchestration platform, vCenter, resides and runs from. The default performance metric statistics collection interval...

From the SQL Server Central Blogs - Extended Events: system_health and a Long Running Query

Grant Fritchey from The Scary DBA

Wouldn’t it be great to just quickly and easily take a look at your system to see if you had any queries that ran for a long time, but,...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Backing Up Some Log
I have this code: DECLARE @n NVARCHAR(MAX) = 'Northwind_Prod' SELECT @n = @n + '_' + CAST(CAST(SYSDATETIME() AS DATE) AS nvarchar(20)) + '.trn' BACKUP LOG @n TO DISK = 'e:\northwindprod.trn' What happens when I run this code?
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 SQLCMD GO

I am writing a SQLCMD script in SSMS with SQLCMD mode. I need to have a batch separator in my script, and am planning to use GO. What options do I have for the batch separator on it's own line?

Answer: GO can be by itself or preceded by two exclamation points (!!)

Explanation: GO can be by itself or preceded by two exclamation points (!!). Ref: Edit SQLCMD Scripts with Query Editor - https://docs.microsoft.com/en-us/sql/ssms/scripting/edit-sqlcmd-scripts-with-query-editor?view=sql-server-ver15

Discuss this question and answer on the forums

Featured Script

Orphaned Users Search and Destroy

slesicki from SQLServerCentral

This script will find all orphaned users on all databases on an instance, and generate a script to drop each user. This script does take into account users without logins. Simply execute, copy the values from the DropScript column to a new SSMS window and execute. Use with caution.

---------------------------------------------------------------------------------------------------
--Author: Patrick Slesicki
--Date: 06/26/2018
--Purpose: To find orphans on an instance and generate a script to drop them.
--Notes: Use with caution. MS has users without logins for some databases.
--History:
--mm/dd/yyyy Init Description
------------ ---- ---------------------------------------------------------------------------
--03/06/2020 PLS Major revision using sids as join fields rather than names.
---------------------------------------------------------------------------------------------------
DECLARE @SQL AS nvarchar(2000) =
N'USE [?]
INSERT INTO #Orphan
(
DBName
,IsReadOnly
,UserName
,UserType
,DropScript
)
SELECT
DB_NAME()
,(SELECT is_read_only FROM sys.databases WHERE name = DB_NAME())
,dp.name
,dp.type_desc
,CASE
WHEN (SELECT is_read_only FROM sys.databases WHERE name = DB_NAME()) = 0 THEN ''USE '' + QUOTENAME(DB_NAME()) + ''; DROP USER '' + QUOTENAME(dp.name) + '';''
WHEN (SELECT is_read_only FROM sys.databases WHERE name = DB_NAME()) = 1
THEN ''USE master; ALTER DATABASE ''
+ QUOTENAME(DB_NAME()) + '' SET READ_WRITE WITH NO_WAIT; USE ''
+ QUOTENAME(DB_NAME()) + ''; DROP USER '' + QUOTENAME(dp.name)
+ ''; USE master; ALTER DATABASE '' + QUOTENAME(DB_NAME()) + '' SET READ_ONLY WITH NO_WAIT;''
ELSE NULL
END
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE
dp.principal_id > 4
AND dp.type IN (''G'', ''S'', ''U'')
AND dp.name NOT IN(
''MS_DataCollectorInternalUser''
,''AllSchemaOwner''
,''vssfadmin''
,''TFSWITDDLADMIN''
,''smsdbuser_ReadOnly''
,''smsdbuser_ReadWrite''
,''smsdbuser_ReportSchema''
)
AND dp.name NOT LIKE ''NT %''
AND sp.sid IS NULL;
';
---------------------------------------------------------------------------------------------------
--Drop the temp table if it exists and create the temp table
---------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#Orphan') IS NOT NULL
DROP TABLE #Orphan;
CREATE TABLE #Orphan
(
DBName nvarchar(128)
,IsReadOnly bit
,UserName nvarchar(128)
,UserType nvarchar(60)
,DropScript nvarchar(4000)
);
---------------------------------------------------------------------------------------------------
--Execute the dynamic sql statement
---------------------------------------------------------------------------------------------------
EXEC sys.sp_MSforeachdb @command1 = @SQL;
---------------------------------------------------------------------------------------------------
--Get results
---------------------------------------------------------------------------------------------------
SELECT
DBName
,IsReadOnly
,UserName
,UserType
,DropScript
FROM #Orphan
ORDER BY
DBName
,UserName;
---------------------------------------------------------------------------------------------------
--Cleanup
---------------------------------------------------------------------------------------------------
DROP TABLE #Orphan;
GO

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

Missing dumps during unexpected server reboot - One of my critical database server had an unexpected reboot followed by server hung issue but sadly we are unable to find any related errors in the event logs or SQL logs . Also there was no dumps generated . I recollect there is a way that the dumps can be generated later as well […]

SQL Server 2016 - Administration

Finding databases with no dependencies - Hello experts, This is more of a conceptual question - I'm pretty sure I can do all of the legwork once I get pointed in the right direction. How does one go about finding which databases on a server have no dependencies? I realize this may not be totally possible because some developer might have […]

Weird issue with data transfer to Oracle - Hello all... had an odd issue crop up and was wondering if anyone had ideas on what might have caused it... We have a nightly job that copies data from one of our local databases (SQL Server 2016) to an Oracle database at our corporate headquarters. The setup is pretty basic - we have a […]

SQL Server 2016 - Development and T-SQL

Procedure Error - has no parameters and arguments were supplied - DECLARE @FROMDATE SMALLDATETIME, @TODATE SMALLDATETIME, @SERVER_NAME NVARCHAR(150) SELECT a.ServerName,a.DatabaseName,a.DatabaseSize,b.DatabaseSize,a.TrackDate as Todate_Size,a.TotalSize,b.TrackDate as FromDate_Size ,b.TotalSize, (a.TotalSize-b.TotalSize) AS GrowthSizeDiff FROM (SELECT ServerName, DatabaseName , DatabaseSize , LogSize , TotalSize , TrackDate =(CONVERT (DATE, TrackDate, 103)) FROM Database_Size_Tracking WHERE servername = @SERVER_NAME AND (CONVERT (DATE, TrackDate, 103)) =(CONVERT (DATE, @TODATE, 103))) A, (SELECT ServerName, DatabaseName , DatabaseSize , […]

count(*) locking a table? - Hello, Why would select count(*) from a table encounter a lock? The table in question had initial bulk insert (append records) and then update happens on those new records. In the meantime I queried the total rows - count(*) and it locked the whole thing. Eventually I had to kill the query with count(*). Did […]

Administration - SQL Server 2014

SQL Server CPU utilization - Hi there, i am using this script to get the cpu utilization. Unfortanetly I get wrong results. The NonSQLCPUUtilization is always negative. It's a SQL Server 2014 with CU4. We have an other SQL Server 2014 with CU4 where it works correctly. Any hints are appreciated. Regards select top 10 id, SQLServerCPUUtilization, 100 - SystemIdle […]

Configuring SQL Replication using Remote Distributor: Create Publication Error - Dear Team, I am currently on SQL server 2014 R2 and I have been trying to replicate my LIVE database to a report server using a remote Distributor. Currently, i am getting the error as shown below: TITLE: New Publication Wizard ------------------------------ SQL Server could not create publication 'XXDB_PUB'. ------------------------------ ADDITIONAL INFORMATION: An exception occurred […]

Encrypted Log Shipping - I've found a number of articles on encrypting backups and transaction log backups, but all these are based on manually generated code to include the encryption details in the backup command. Is there anything out there covering how Log Shipping can be enabled to include encryption, and if so what version of SQL has this […]

SQL 2012 - General

Fulltext: modify system stoplist - Hello there, I have a customer that wishes to remove a stop word from the fulltext stoplist. Unfortunately, currently we are only using the system stoplist. Yes, I know I can create a custom stoplist, rebind it to the indexes etc. A colleaghue came with the idea of just deleting the word from the sys.fulltext_system_stopwords. […]

String extraction - SQL 2012 - Hi, I need to make changes to the below to only extract '2002' as the result. select REPLACE(REPLACE(SUBSTRING('CKnovelty2002_pouch-5AR-OneSize',1,CHARINDEX('-','CKnovelty2002_pouch-5AR-OneSize',1) - 1),'CKnovelty',''),'CKflower','') At the moment, I am getting '2002_pouch' as the result which is incorrect. The expected output is '2002'. Can somebody please help in this regard ? Many thanks.

SQL Server 2019 - Administration

Need replacement for Microsoft RDCManager - which is being deprecated March 2020 - Due to vulnerability, Microsoft is deprecating RDCMan (LINK listed below) Does anyone have a recommendation on a replacement RDCMan? I have over 200 SQL Servers (VM's & Physcial's) that I connect to... and I have them neatly defined in RDCManager by PROD, STAGE, TEST, DEV and by Application System. Looking for a replacement for RDCMan […]

SQL Server 2019 - Development

top cqu sql how to tune - there is a function in legacy code view below and the ind query shows up as top sql. Any idea how to remove the function and replace it with adhoc sql. Individual t-sql SELECT @Type = coalesce(@Type + ', ', '') + type_desc from _dataDictionary_type where type_id IN (select type from _TYPE_MAPPING WITH (NOLOCK) where […]

SQL Server 2008 Performance Tuning

Optimise/ speedup query - Below query is used for inserting and updating the tables in the SQL Server database. The XQuery is slow while executing in SSMS for first time.I am using SQL Server 2008 R2. The total time taken for 500 queries is 20 to 40 seconds. How can I optimise this query to speed up the execution? […]

SSRS 2016

PowerBI Paginated Reports vs SSRS reports - I have a reportserver where I am currently deploying both PowerBI desktop reports, and SSRS rdl reports. We're setting up standards for the team for our PowerBI reporting to match up to our SSRS standards (logos/header/footer elements/color schemes/etc.). Searching online has led us to the conclusion that adding header/footer is best served by using PowerBI […]

Integration Services

extract all data with week - Good morning all I have a table that contains the history of a table over 1 year old he asked me to extract one file per week so we total I must have 54 files Who can help me with this need please

This email has been sent to newsletter@newslettercollector.com. 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.