SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Stairway to Biml 9 – Refactoring a Simple Custom Biml Framework
Free eBook: Performance Tuning with SQL Server Dynamic Management Views
From the SQL Server Central Blogs - How Many Bytes Per Character in SQL Server: a Completely Complete Guide
From the SQL Server Central Blogs - Upgrading SQL Server 2017 Containers to 2019 non-root Containers with Data Volumes – Another Method

Question of the Day

Expanding the data frame

The Voice of the DBA

Proactive Performance Tuning

There was an interesting post from Erik Darling recently about a way to prevent anyone from creating an index with a low fill factor. Essentially, Erik posted code for a DDL trigger that would fire on the CREATE or ALTER INDEX events and if there is a value for the fillfactor < 80, this will roll back the creation. As Erik notes, however, this doesn't prevent the index from being created. Rather, it just rolls it back, which is an issue in and of itself. I wish we had a "before" DDL trigger for this.

Ultimately, changes that we want to make to our systems need to be tested and evaluated early on. We would want changes like this to be made in a dev environment, tested in QA or a Load Testing system, and then deployed to production. We could write tests to ensure that changes are made within known boundaries that won't substantially impact the performance of the system, but that's not reality. Even if we had a well designed and followed process, we rarely have the ability to properly load test our application to completely understand the impact of changes like a fill factor alteration.

The point, though, is that we want to be proactive and prevent issues like this from causing us problems. Rather than wait until this causes us issues with our production system, we want to try and educate other DBAs that might not know better. While there are lots of controls in SQL Server, there aren't a lot that prevent someone with sysadmin or serveradmin from changing settings that might cause issues.

My wish is that the SQL Server would include controls that lock down certain settings and choices to prevent alterations from any single administrator. I'd really like to have some sort of two user authentication for certain changes built into the platform. A way for an admin to request a change, and another admin to approve it. That, along with the ability to limit actions separately from a rollback, which becomes a less and less viable option as data volumes grow.

SQL Server has dramatically improved as a platform, with more capabilities, enhancements, and maturity with each version. There is still a long way to go, and a recognition that administration might need to become more of a team process, just like development, would be a good next step.

Steve Jones - SSC Editor

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

Featured Contents

Stairway to Biml 9 – Refactoring a Simple Custom Biml Framework

Andy Leonard from SQLServerCentral.com

This level of the Stairway to Biml examines how to refactor your Biml into an easier to maintain format.

Free eBook: Performance Tuning with SQL Server Dynamic Management Views

Additional Articles from Redgate

Dynamic Management Views (DMVs) are a significant and valuable addition to the DBA's troubleshooting armory, laying bare previously unavailable information regarding the under-the-covers activity of your database sessions and transactions.

From the SQL Server Central Blogs - How Many Bytes Per Character in SQL Server: a Completely Complete Guide

Solomon Rutzky from SQL Quantum Leap

A Completely Complete Guide to how many bytes are used to represent characters in the T-SQL string datatypes… Continue reading How Many Bytes Per Character in SQL Server: a...

From the SQL Server Central Blogs - Upgrading SQL Server 2017 Containers to 2019 non-root Containers with Data Volumes – Another Method

aen from Anthony Nocentino's Blog

Yesterday in this post I described a method to correct permissions when upgrading a SQL Server 2017 container using Data Volumes to 2019’s non-root container on implementations that use...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Expanding the data frame
I have this data frame of data: >>> df BillMonth BillingAmount 0 May 300 1 June 450 2 July 600 3 August 900 I have imported numpy as np and pandas as pd. I want to convert this to daily data for each month. My goal here is to get 30 rows for each existing row and then edit the data frame later to reflect the days. Which of these lines will expand my data frame to 30 rows for each month?
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)

Numeric and Decimal

What's the difference between the numeric and decimal datatypes?

Answer: No difference, they are synonyms for each other.

Explanation: There is no difference between the decimal and numeric types. They are synonyms of each other. Ref: decimal and numeric - https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-2017

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

migrate package siis from 2008 to 2107 - good evening I need to migrate packages ssis 2008 to 2017 I am looking for a method to export my packages that are on the 2008 server on the net I find this command who has a feedback experience USE MSDB SELECT 'EXEC XP_CMDSHELL ''DTUTIL /SQL ' + NAME +' /COPY FILE; D:\SQLDBAExperts\Packages\' + NAME+'.DTSX' […]

Whose account the job runs under? - I have a sql agent job that is called by a cmd file remotely using an admin account for example I will call it OrchestrorService account. In SQL agent job history I can see the job was invoked by that account. But the job step actually is a SSIS package. And the step is run […]

Unable to Join Databases to AG - I have a stand alone SQL 2008 R2 SQL Server that I'm migrating to a 3 node AG. At various times during the testing of the restore process to get the databases onto the new SQL Server 2017 servers that are on CU16 I run into a scenario where one or more databases will join […]

Using Storage DeDupe for AlwaysOn Availability Groups - I have a Sr. Sysadmin here who wants to turn on storage dedupe for the three nodes that will be host AG's because he really wants space savings. The setup is that I actually have 3 sets of 3 nodes. Each set of three nodes will be hosting databases from what is now a single […]

SQL Server 2017 - Development

sqlcmd - Unable to pass in exact file path as an input variable to :out - Hi there I have a routine which outputs an ID generated from a stored procedure into a text file as follows: :!!if exist \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt del \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt --:connect localhost\SQL2008R2 :out \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt select Ltrim(Rtrim(@StoredProcedureRunTrackerID)) Now i wanted to change this , so that the path for :out is not hardcoded and passed in as a variable, ie […]

SQL Server 2016 - Administration

Is there such a thing as database file bloating? - Hello, I'm still fairly new to SQL Server. We have a production database at our company that has grown from about 40gb to 100gb over the course of about five years. What I want to better understand is if the data file can become "bloated" with nonexistent data, or if the file size is an […]

Transacation Replication Push subscription error message - I have transactional replication setup with push subscriptions. When I expand publication and right click subscriber to "view synchronization status" it is giving this error message below, but replication monitor does any error and I am able to replicate data. An error occurred while attempting to access the subscription. (View Synchronization Status) ------------------------------ To synchronize, […]

Package error - when i ran the package through sql agent job it got failed but i did not find any information in job history Executed as user: . Started: 11:59:55 AM Finished: 12:00:43 PM Elapsed: 47.281 seconds. The package execution failed. The step failed. How can we trouble shoot

Extended events - MY EXTENDED EVENT SCRIPT: CREATE EVENT SESSION [CLogins] ON SERVER ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1) ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE (([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%SELECT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DELETE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INSERT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CREATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DROP%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%RENAME%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TRUNCATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%COMMENT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MERGE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CALL%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%EXPLAIN PLAN%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%LOCK TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%GRANT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%REVOKE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEXES%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEX%')) […]

SQL Server 2016 - Development and T-SQL

Issue with appending date and timestamp to file name - I have an SSIS expression where it appends 20191125155915.xlsx to filename @[User::FilePath] + "\\" + "DBAFullPro_"+(DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) +RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2)+ RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2)+ RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2)+RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)+".xlsx" I am using file system task to rename original file name to _timestamp. […]

The data types varchar and datetime2 are incompatible in the add operator. - In the procedure i have date column as datetime2 declare @date datetime2 = null, @CaptureError varchar(100) when i am logging this variable like below SET @CaptureError= @CaptureError + ' ,Date: ' + coalesce(@Date, 'NULL') this is getting error as data types varchar and datetime2 are incompatible in the add operator. what is the workaround for […]

Development - SQL Server 2014

Table join question -- multiple recods in one table - This is an employment application database. I have a foreign key table named Application_Locations that has 2 columns UserID and LocationID. This is a many to many relationship i recently had someone ask me to write a report that would produce results from a zip code search. They then asked me to list the […]

SQL 2012 - General

Join Query, two tables, two databases? - Is that possible? To have two databases with one query? If so the query below should populate some data! If not, how can I accomplish the task? SELECT JobSheet.dbo.JobHrs.JobNo, JobSheet.dbo.JobHrs.ActualHrs, JobSheet.dbo.JobHrs.ReworkHrs, TimeReporting.dbo.TimeData.Time, TimeReporting.dbo.TimeData.Rework FROM JobSheet.dbo.JobHrs INNER JOIN TimeReporting.dbo.TimeData ON JobSheet.dbo.JobHrs.JobNo = TimeReporting.dbo.TimeData.JobNo WHERE JobSheet.dbo.JobHrs.JobNo = '19-0228B'

SSDT

Using variables in multiple packages - I have a SSIS Solution built in Visual Studio Community 2019 using SSDT. The solution contains about 15 packages. I am using 5 variables (created in the first package) that I would like to use in all of the other packages. Is there a way to make a variable usable in other packages within the […]

Expression won't evaluate for property - I am attempting to parameterize the three properties for checkpoints in a SSDT 2017 SSIS project and keep receiving this error for the CheckpointUsage property: Error loading: The result of the expression "@[$Project::parCheckpointUsage]" on property cannot be written to the property. The expression was evaluated, but cannot be set on the property. I have 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.