A community of more than 1,600,000 database professionals and growing

Featured Script

ReplicationDeltaSelect

The Voice of the DBA

Template Configuration

One of my goals this year is to really spend some time learning more about Extended Events (XE). I'm somewhat embarassed that almost a decade after their introduction in SQL Server 2008, I have a fairly rudimentary grasp of the system. I've watched talks from Jonathan Kehayias and Erin Stellato and others. I recognize the value in a lightweight system, but for some reason I haven't done much with XE.

As part of my learning, I'm going back to the basics with videos, articles, and documentation. In one section, I ran across the templates and how they can be used to greatly simplify the creation of new sessions. I'm a bit fan of templates, and I love the ones I use for T-SQL in SQL Prompt. I know templates are incredibly useful in plenty of other areas.

XE is more complex system. The verbiage, the complexity of filtering, and the variety of targets make this a very flexible system, but also one that can overwhelm people. When do you use the histogram target v the ring buffer? What events make the most sense to solve or diagnose your system?

Today I wonder if some of you out there can share some knowledge. Are there built in templates that you find useful for certain situations? Have you created your own that speed up your analysis of an issue? If so, it would be great if you could share some code and explain why you find certain session settings useful. If you find some templates to be problematic, perhaps share that.

As I'm learning, I think that it's likely I'll have my own set of events and settings that I lean on heavily. Since I can save these on an instance, and not just a workstation, that's a huge improvement over trace. These sessions can be shared with other DBAs in my organization, which is helpful and handy. Maybe one of you will give me a new template that I can add to my toolbox.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.7MB) podcast or subscribe to the feed at iTunes and Libsyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps

Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. Download your free trial

The industry standard for comparing and deploying SQL Server database schemas

Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial

Featured Contents

Autogenerating SSIS file import packages using Biml

Johan Åhlén from SQLServerCentral.com

Tired of building tons of similar SSIS packages for importing files into your data warehouse? Learn how to autogenerate them using Biml. More »

An Easier Way of Pivoting Data in SQL Server

There are several methods to pivot, or transpose, data in T-SQL, but most are difficult to understand and write. Phil Factor demonstrates how to take advantage of JSON support, introduced with SQL Server 2016, to create a function that makes pivoting data simple. More »

Right-sizing for a cloud service

When it’s time to purchase new resources for one or more applications, the non-scientific approach of guesstimating the requirements can be expensive, whether for on-premises capital expenditures or cloud-based services. More »

From the SQLServerCentral Blogs - tSQLt Course

Ed Elliott from SQLServerCentral Blogs

In the last half of 2017, I decided to run a tSQLt course and went down the route of putting... More »

From the SQLServerCentral Blogs - Settings Goals Professionally

Brian Kelley from SQLServerCentral Blogs

I was reminded of this topic after yesterday’s #SQLChat. Have you set professional goals for yourself? If so, have you... More »

Question of the Day

Today's Question (by Steve Jones):

I see I have a guest user in my database, but in SSMS the user has a red x next to the name. How do I enable this user in the database for others to map to if they don't have a user account?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Security.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Expert Scripting and Automation for SQL Server DBAs

Automate your workload and manage more databases and instances with greater ease and efficiency by combining metadata-driven automation with powerful tools like PowerShell and SQL Server Agent. Automate your new instance-builds and use monitoring to drive ongoing automation, with the help of an inventory database and a management data warehouse. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Evgeny Garaev):

What are the minimal prerequisites for creation of a temporal table in a SQL Server 2016 database?

Answer:

A temporal table must have a primary key defined
A temporal table must have two datetime2 columns
SYSTEM_VERSIONING must be ON

Explanation:

There are 3 minimal requrements for temporal tables:

Primary key must me defined
You need to have 2 datetime2 colums to track changes in the table
SYSTEM_VERSIONING must be ON

The history table can be created for you by MS SQL Server database engine automatically.

Ref - click here

» Discuss this question and answer on the forums

Featured Script

ReplicationDeltaSelect

Patrick Slesicki from SQLServerCentral.com

Select a database context.

Enter a name for the vaiable @SubscriberServerName.

Enter a name for the vaiable @PublicationName.

Execute.

Copy the desird record(s) from the SQLStatement column and execute in seperate window.

Use with caution especially when running against larger production systems.

Please let me know if you find issues or have suggestions for improvemnt.

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 : SQL Server 2017 - Administration

2017 Standard Edition Listener limit - Is there a limit to the number of listeners that can be created using SQL Server Standard? I'm working with a...

SQL Server 2017 : SQL Server 2017 - Development

Average number of records per hour per datetime range - Hi, I have a DB with PATIENT_ID, ARRIVAL_DATE, DEPARTURE_DATE, and DEPARTMENT. I need to find the average number of patients in...

SQL Server 2016 : SQL Server 2016 - Administration

Automation of refresh database from Prod to Dev using power shell script - Hi experts, Can someone please post a good power shell script to automate database refresh on Dev from Prod? Thanks.

Need to setup PULL subscription in a 2-node SQL 2016 Enterprise AlwaysOn Environment - These instructions did not work for me: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replication-subscribers-and-always-on-availability-groups-sql-server Deployment included: Using an e

Data Copy for reporting purposes - Hi! @ present we have a reporting server which is for internal and external users..they run regular reports. - we keep the...

Memory Optimized FileGroups - In the SQL 2016 documentation it states that you must create a new filegroup called Memory_Optimized_Data ut when I open...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Help me with for Xml path() - Hi all, drop table #temp drop table #statistics create table #temp ( ID int, varchar(100), BATCH_NO int, Xml_name varchar(50)) ...

Count(*) with LIKE - I am working on converting an Excel formula to Excel. I have successfully moved two other Excel reports to SQL...

SSIS Permission issue with reading file from SharePoint page - I have an SSIS package that uses a script task WebRequest object to download a file from a Sharepoint page. ...

SQL Server 2014 : Administration - SQL Server 2014

Backup of DB while it is configured with log shipping - I have a script which I use to take the backup of our DBs to the cloud. I am just...

Need Recommendations for Document Database - We're in the process of a data migration from one LOB tool to another over this weekend. Part of the...

SQL Server 2012 : SQL 2012 - General

Decimal place in SQL - I have a dumb question, I have values like 0054880, 0054885 0054890 and I need values like 5488.000,5488.500,5489.000 How can I do...

SQL Server 2012 : SQL Server 2012 - T-SQL

How to distinct values and adding row numbers while keeping original order? - How can i get a distinct list of the lcc_codes below removing null lcc_codes and with a row number while...

Enter maximum value on the basis of 2 independent tables. - Hi Folks, I stuck in a puzzle, please suggest a way to resolve this. I have 2 below Tables in my database. Tab_A...

Two tables that they have foreign keys pointing to each other - Hi, I have an unusual problem in that I have to remove records from two tables that they have foreign keys...

SQL Server 2008 : SQL Server 2008 - General

Upgrade - Planning to upgrade from SQL Server 2008 SP1 to SQL Server 2008 R2 SP3. The database on the server are...

Missing the Control Flow tasks in the Toolbox - Hi! I am on the Control Flow tab. In my Toolbox, I only see Maintenance Plan Tasks and General. There is nothing...

Reporting Services : Reporting Services

Indenting wrapped text - This one is probably very simple, but I'm not sure. I am using SSRS 2005, and I already have a...

Database Design : Virtualization

Creating a Hyper-V "template" for multiple production servers - Goal: To create a "template" of a VM (in Hyper-V) that could be "cloned" for multiple production servers. Problem: Renaming "Template"...

Microsoft Access : Microsoft Access

Migrated DB to 2016, having ODBC Access problem - Thanks in advance for any clues on this problem. I'm migrating several SQL Server databases from SQL2005 to SQL2016. One...

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.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.