Top SQL Queries to Safeguard JIRA (2021-06-30)

SQLServerCentral Newsletter for June 30, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Top SQL Queries to Safeguard JIRA]( - [SQL Server transaction log architecture]( - [Testing Flyway Migrations Using Transactions]( - [From the SQL Server Central Blogs - When Read Committed Snapshot Goes Bad – Version Store Filling TempDB]( - [From the SQL Server Central Blogs - Long names vs short names (or aliases)]( Question of the Day - [The R function scope]( The Voice of the DBA  Daily Coping Tip Make a list of joys in your life over the last 30 days. 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. Patching Challenges In my career, I've had to manage many production database instances. In fact, there was a time when two of us were patching hundreds of instances (600?) when MS released patches. That wasn't too often back then, unlike the every other month(ish) schedule that we have with 2017/2019 and the Cumulative Updates. My process was often to test patches on QA/Test servers and then start to roll out the patches to production. We didn't quite follow what [Brent Ozar recommends](, in that we often patched development servers later. We didn't control those, and unless production was patched, we couldn't get developers to patch their systems. We didn't patch DR servers first, but that's a good idea. It's one I think makes a lot of sense. We did patch secondaries first, ensuring that if there were issues here, we wouldn't impact production, and these days with Availability Groups, hitting the secondary replicas, especially read-only ones, is a good idea. The big thing for me wasn't so much the type of servers, but in having a series of rings to roll out the patches in groups. We used automated processes (no one wants to click next-next-next) and while we might patch a lot of servers, we never wanted to patch them all. We typically had 3 rings. Ring 1 was test servers, and ring 2 was most of production. Ring 3 was any production servers that got an exemption from initial patching. There were times when some process was important and couldn't be interrupted or a client needed a few more weeks to test. We'd let them delay a month, but not longer. I think it's important to have a strategy, and as Brent notes, also a protocol for how you handle things. I've often depended on our normal backup processes, especially in large environments, and the patches tended to stop the SQL services, so I don't know how important it is to stop client apps, but think about it. One note about backing out changes is that containers make this a lot easier. If you move to production containers (linux, HA challenges, features missing, etc.) you can swap out an updated container with a new (or old) patch level as needed. There are caveats here, and certainly I'd start implementing this in a dev area first to understand the implications, but I expect over time containers will make patch deployment and rollback much easier. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Top SQL Queries to Safeguard JIRA]( Vasanth Kumar Makam from SQLServerCentral Troubleshooting Performance Problems is not very easy process and can be complicated when it comes to identifying problems with JIRA application. [SQL Server transaction log architecture]( Additional Articles from SimpleTalk The transaction log file for a SQL Server database critical for for maintaining database integrity. In this article, Greg Larsen explains SQL Server transaction log architecture. [Testing Flyway Migrations Using Transactions]( Additional Articles from Redgate When you are using Flyway, how can you test your database migration script first to make sure it works exactly as you intended before you let Flyway execute it? From the SQL Server Central Blogs - [When Read Committed Snapshot Goes Bad – Version Store Filling TempDB]( david.fowler 42596 from SQL Undercover We all had those calls, it’s the middle of the night and we get that phone call that a customer is getting error messages due to their tempDB being... From the SQL Server Central Blogs - [Long names vs short names (or aliases)]( Kenneth.Fisher from SQLStudies I remember when I was first learning to code there was always the argument of how long a name should ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  The R function scope I have this function in R. f <- function(i) { g <- function(j){ j+k } k <- 4 i * g(i) } I then run this code: > k <- 10 > f(4) What is returned? 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 CURRENT_TIMESTAMP I have a CREATE TABLE statement and I use a default for a column with the CURRENT_TIMESTAMP value. My table looks like this: CREATE TABLE defaulttest2 ( myid INT , mydate DATETIME2(7) DEFAULT CURRENT_TIMESTAMP) GO What is the datatype that is returned by CURRENT_TIMESTAMP? Answer: datetime Explanation: CURRENT_TIMESTAMP returns a datetime data type, without time zone data. Ref: CURRENT_TIMESTAMP - [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 [Business Use changes - when to delete disabled jobs]( - Over the past 5 years with my employer, I have created a number of stored procedures, jobs, etc as I built interfaces for outside vendors. As the vendors have changed, and we entered into an affiliation agreement with a larger organization, many of the jobs are now disabled and the tables and SPs are no […] SQL Server 2017 - Development [Seeking input on Best Method to Join on First or Last Row in a 1:M Join]( - I have included the DDL to re-create the 2 tables in this examples. I can't post the actual DDL so these are abbreviated versions of the 2 tables I'm working with. The relationship between PERSON and PERSON_HISTORY is 1:M. What I seek is the most effective/best method for joining these 2 where I get only […] [SQL Server query results sort order with totals]( - I am trying to sort by the ID with the highest total. I can sort by ID and then get the total for each member but I cannot seem to get it to sort by id and total. Below is what I have and what I want. On what I have I am doing Any suggestions on […] SQL Server 2016 - Administration [Argument syntax for SSIS Execute Process Task Editor]( - Hi, I'm trying to modify my SSIS packages to use sftp and since it cannot be done using the FTP Manager Connection within SSIS, I am calling a PowerShell script to connect to our sftp server and move the files as needed. The PowerShell script works okay and copies the file with no errors, but […] [Handling reports of slow-running procedures]( - Hello experts, Does anyone know of a page or document that outlines a process for user reports of "slow-running procedures"? This issue is pretty frustrating for me because SQL activity is so dynamic that it's not like easier problems of missing permissions, low drive space, etc. Each time I get a request to look into […] SQL Server 2016 - Development and T-SQL [Multiple deadlocks occuring on simultaneous deletions from temporal table]( - We have a stored procedure which is attempting to delete 1-2 rows at a time from a temporal table overnight. The stored procedure is executed several times simultaneously (around 10-15 threads) by parallel processes, and so attempts may be made to delete 30 rows simultaneously by different SPIDs, for example. DELETE FROM dbo.Table WHERE ID […] [Does Every BEGIN require an END]( - Does every instance of BEGIN within your code require an END? I've seen more than one piece of code, usually a Stored procedure made by the vendor who provides our accounting software, that has more instances of BEGIN than END. I always believed that every instance of BEGIN required an END. Is that not the […] [help on execute table variable into one table]( - I have created table variable as below , just want to that result into table as last line is not working please help on that, Select * into table_temp from execute (@query) how to make sure this query like select * into table_2 from table_1  DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols […] SQL Server 2019 - Administration [UniqueIdentifier as Primary Key, non-clustered index, fragmentation issue]( - I've been working a little bit with a few tables here, they've got "CreatedDate" as clustered Index and and Id (UniqueIdentifier) as Primary Key. The Id's are generated from code and are non-sequential. The problem(?) is that the Id-PK index gets heavily fragmented almost instantly even after a defrag-session. I can't say I've identified any […] SQL Server 2019 - Development [DB logins permission script out]( - Hi, Can anyone share or point me to get -DB logins permissions in a single script. Not using use dbname. I could see all script is having use db, since i have lot of db. [Unable to connect to Remote SQL Server Application from C# Application]( - Hi there We have a remote SQL Server instance on a VM , that we can connect to successfully from SQL Server Management Studio on my local machine. However I am having problems connecting to this database from a c# application Now my connection string is as follows: string ConnectionString = "server=METRODEMO\\SQLEXPRESS;Database=Logger;Trusted_Connection=True;"; I have also […] SQL Server 2008 - General [Foreign key for Xml Datatype]( - Is foreign key constraint possible in xml data type? OR Is it possible to mention multiple Foreign keys in a single cell? For ex: If xml has 2 rows, 2 ids will be given in main col with some separator, if xml has 3 rows then 3 keys so on. Can it be done? [Checkdb error]( - While executing DBCC CHECKDB i am getting following error "Check terminated. A failure was detected while collecting facts. Possibly tempdb". Checked for estimated tempdb following was the result. Estimated TEMPDB space needed for CHECKALLOC (KB) 19762 Estimated TEMPDB space needed for CHECKTABLES (KB) 1 Auto growth is enabled for tempdb still error repeats. what is […] Powershell [table script to dump to csv with foreign key order]( - Can PS be used to dump the SQL DDL to csv but do it in the order which would used to reload an empty database.. Tables have lots of foreign key constraints . I would like to produce a file that I could use to rebuild an empty database, but foreign keys are causing me […] Oracle [Need Help with Pivot / Unpivot SQL]( - Hi Can any one help me with query to transfer from position1 to position2 as shown below ?    [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -