SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Finding Free Space

The Voice of the DBA

The Redgate Way

Recently Matt Hilbert, from Redgate Software, wrote a piece on our blog about our journey to DevOps. It's a great read, summing up some of the things that we've learned in our journey across the last decade. Matt is a great writer, and it's worth a few minutes of your time to check it out and think about all the things that we've been through.

I've known some people at Redgate for 18+ years, and I've worked there for 12, so I've had the chance to see quite a few changes. When I started, teams worked for long periods of times, in what is really a waterfall methodology. They went through long phases of development, testing, and beta releases, with Brad McGehee and myself having plenty of time to learn a product and then know it would be stable for a year or so.

That slowly started to change, as Matt describes, with teams moving to new methods of building software, experimenting and learning. The Prompt team was one of the most ambitious, working in pair programming and finding ways to release almost on demand. Over time, other teams caught up and built some amazing processes. We even had two teams working on some products, with alternating two week sprint cycles to allow them to release every week. That was an impressive coordination of software development teams.

Even today, I'm constantly impressed by teams. They aren't all always rock stars, but they often exceed my expectations. I will see some slowdowns when teams change their people, process, or tooling, but then they will leap forward. The Data Masker team has been impressive lately, with some fantastic productivity improvements being added to the software. I apologized recently for not taking the Data Catalog team seriously for almost a year, but they have done more than I ever imagined with that tool in the last six months.

We do continue to improve our software development skills at Redgate. We do release often, but really, we try to also improve the quality of our software, improve the skills of our developers, while working to retain talented individuals and help them enjoy their chosen career. We still produce bugs, we never get everything done as fast as sales, marketing, or even me, would like, but I do think that the last ten years has been an incredible growth as set of software development teams. Now our challenge is more closely aligning all teams to work in a loosely coupled, but tightly integrated fashion.

DevOps is really a better way to build software for most organizations and teams. It often doesn't change a lot about the actual code we write, but it does get developers, infrastructure staff, and management to rethink how we work, especially how we work together. That's the hardest part to get through to many customers. This isn't an install-a-tool-and-things-are-great system. Tools do help, but your attitude, your focus, and your willingness to work as a team are more important.

Read about our journey. We've taken 1,000 steps, but there is more for us to learn, change, and implement. Think about how you would want to change things at your company, and maybe pass this along to your manager. It's not easy, and it might not be quick, but it's an incredible journey. It's also very much worth the time and effort you put into it.

Steve Jones - SSC Editor

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

Featured Contents

Verify your SQL Server database backups

Steve Rezhener from SQLServerCentral

Introduction A popular joke about DBAs and database backups goes like this. There are two types of DBAs: DBAs that do backups DBAs that will do backups The joke is only stating the obvious – lack of database backups might be the end of a DBA career and/or the supported business. Unfortunately, that joke fails […]

Large estate monitoring at BMW

Additional Articles from Redgate

Managing 100s of business-critical servers across multiple continents became a struggle for BMW. SQL Monitor provided a fast and powerful solution that allowed them to scale to their business needs. Availability has since increased to 100% and doubled the speed of delivery. Learn how here.

How to Think Like the SQL Server Engine: Using Statistics to Build Query Plans

Additional Articles from Brent Ozar Unlimited Blog

Every index has a matching statistic with the same name, and each statistic is a single 8KB page of metadata that describes the contents of your indexes. Stats have been around (and been mostly the same) for forever, so this is one of the places where SQL Server’s documentation really shines: Books Online has a ton of information about statistics. You honestly don’t need to know most of that – but if you wanna make a living performance tuning, there’s a ton of great stuff in there.

From the SQL Server Central Blogs - Event Notifications Example

SQLPals from Mission: SQL Homeostasis

Event notifications are kinda like a trigger in the sense that they respond to specific event, specifically in response to DDL statements and SQL Trace events.

The major difference between...

From the SQL Server Central Blogs - What the ???? T-SQL Tuesday #120

Kenneth.Fisher from SQLStudies

Hey, it’s T-SQL Tuesday again! I almost forgot about it what with Summit and all. Wayne Sheffield (blog|twitter) is our ... Continue reading

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Finding Free Space
I have a few files in a filegroup on my SQL Server 2017 database. I want to query the amount of free space in a particular file. Where do I find this information?
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)

Missing Data

I have this data in a text file:

Steve,DEN,LHR,5
Kendra,PDX,LHR,4
Grant,BOS,LHR,
Steve,DEN,SYD,

I have imported the numpy module as np and now want to read this data into an array using that module. However, I need values for the missing items in the last two rows. My code looks like this:

>>> flight = np.genfromtxt('e:\\Documents\\flight.csv',delimiter=',',xxxx=7,dtype=("|S10","|S10","|S10",int))

What parameter do I put in place of the xxxx to get the missing values to default to 7?

Answer: filling_values

Explanation: Default and missing are not parameters. Missing_values will put a -1 in the place for those values. The filling_values parameter will replace each with a 7.

>>> flight = np.genfromtxt('e:\\Documents\\flight.csv',delimiter=',',filling_values=7,dtype=("|S10","|S10","|S10",int))
>>> flight
array([(b'Steve', b'DEN', b'LHR', 5), (b'Kendra', b'PDX', b'LHR', 4),
       (b'Grant', b'BOS', b'LHR', 7), (b'Steve', b'DEN', b'SYD', 7)],
      dtype=[('f0', 'S10'), ('f1', 'S10'), ('f2', 'S10'), ('f3', '<i4')])

Ref: numpy.genfromtxt() - https://docs.scipy.org/doc/numpy/reference/generated/numpy.genfromtxt.html

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 - Development

Update - W. Average Query - I need help with UPDATE Query. I have this data in tables and want to update weighted average in table tblData3 - (TValue * BPrice) .... / SUM(TValue) create table tblData1 ( IDate date, PID varchar(20), SID varchar(20), TValue float ) insert into tblData1 values ('10/15/2019','4567','ABC',4567.34) insert into tblData1 values ('10/15/2019','4567','ABC',5678.34) insert into tblData1 values […]

SQL Server 2016 - Administration

Suspended session - Hi, Application service is going to hung state and when we check the database, there is a connection in suspended state and it is running forever until we restart the service. And the wait event is ASYNC_NETWORK_IO. The query running is very small select query, fetching couple of hundred rows. and there is no information […]

XP_send mail fail - We migrated from sql 2005 to sql 2016 recently in one of the job we are getting error Executed as user: ' 'Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed. SQL agent has sysadmin and database reader role access on msdb how to solve the issue […]

Minimum memory - Hi All , Should we adjust the minimum memory per query (in KB) in SQL ? The default is 1024 KB and I plan to change it into 2048 KB , Would it help boosting SQL performance ? Also if change it into 2048 KB , should I change Minimum server memory (in MB) into […]

Monitor / Alert PII fields - Best 3rd party tool? - I have been tasked with finding a new tool for Database Activity Monitoring. We have previously used Mcafee DAM tool but that is ending and we are frantically searching for a replacement. If anyone has any experience recently with a 3rd party Data Monitoring / Activity Monitoring tool please let me know. Thanks […]

Trigger store procedure with a file in SQL Server - Hi, Will receive a JSON file from an application on to D drive D:\tmp Whenever I receive that JSON file, a stored procedure has to execute taking that JSON file as input file? How do I automate it?

Is it possible get from Query Store historical ~ CPU % usage by each query ? - Is it possible get from Query Store historical ~ CPU % usage by each query in 1 hr range? Many places share code where "avg cpu utilization " or max cpu utilization" extracted from query store (https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/sql-database/sql-database-monitoring-with-dmvs.md) -- Top 15 CPU consuming queries by query hash -- note that a query hash can have many […]

missing storage space - Hi I have a particular drive on one of my sql servers, it's gotten very full - there are no log files involved, this is purely data within the filegroup (primary) there are only 2 tables (everything else is in other filegroups) dtproperties sysdiagrams These are tiny - when I do a dbcc shrinkfile, I […]

Administration - SQL Server 2014

How does DBCC CHECKDB use tempdb? - I would like to know the working principle of DBCC regarding the tempdb. I want to know why it uses tempdb for what purpose. I didn't find any advanced article about that so maybe someone can shortly explain or at least share links for the article about that.

SQL 2012 - General

Analysis services - I have disk space is getting filled up with Analysis OLAP and log location where it has extensions of .db Does it mean they are analysis services databases? I am planning to delete older dbs on analysis services do you see any issues? Please advise?

SQL Server 2012 - T-SQL

Need help with a Query - Hi, Below I have code that takes in 3 parameters. Now, sometimes, as you can imagine one of these can have a value, or only two, or all three may be populated. I am trying to find a way to write the code below so that this will work, but so far it has not. […]

T-SQL (SS2K8)

Running Totals by Date and Account - I'm trying to develop T-SQL (SQLServer 2008 R2) logic for calculating running totals for all accounts each day. The database columns available are Date, AccountNo and Amount. I need a report to list dates in the first column and each accounts running total in succeeding columns to the right. I would like all dates listed in column […]

SQL Azure - Administration

Does blocking has affect on High DTU utilization ? - Hi, Does blocking has affect on High DTU utilization ? (some people advocate that Spike in DTU might be caused by high number of blocking processes , or long term blocking) DTU definition select convert(decimal(18, 0),AVG([avg_DTU_percent])) from (SELECT (SELECT Max(v) FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v)) AS [avg_DTU_percent] FROM sys.dm_db_resource_stats WHERE end_time>=DATEADD(MINUTE, -10, GETUTCDATE())) […]

Reporting Services

SSRS Enterprise vs Standard Edition - I need to install SSRS for report development and deployment. I don't work with it myself and I haven't had much luck trying to find the information I need. My boss would like me to install SSRS 2019 Enterprise Edition. It looks like there is just one download for SSRS 2019. Here are the questions […]

Integration Services

Flat file source to destination table is getting only top 1 record into table - I have a package where i have flat file source with 4 columns and the source file will be in this format 123456|20130701|AWD|WS1035575|20170201|VCDF|MA1064844|20110001|NHS|AS19474754|20160401|EFV7|LK First column holds -123456 Second column holds -20130701 Third column holds -AWD Fourth column holds -WS it repeats …... before for each loop i have a script task where the files […]

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.