Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Featured Script
The Voice of the DBA
 

Daily Coping Tip

Share a happy memory with someone who means a lot to you

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.

Your Best Day

I was watching Scrubs recently, for the fourth of fifth time, as a way to pass some time during quarantine. I love that show, and it's been nice company for me many nights while I'm cooking dinner for the family as they finish up ranch chores outside. One of the episodes from season 4 is called "My Best Moment", where each of the main characters thinks about their best day in medicine. As a comedy, you can imagine that some of these aren't what some of us would think of as a best day at work, but it's an entertaining episode.
 
I was reminded of this recently when I saw a question about your worst mistake as a DBA. There are some interesting things people think about when they consider making a conscious decision they come to regret. Since this reminded me of that Scrubs episode, I wanted to turn this around.
 
Today I'm wondering what was your best day as a DBA or software developer might have been.
 
It could be that solved a problem, or did something that impressed people. It could be that everything went your way. Maybe your best day was an unexpected time away from work where no one contacted you or nothing went wrong.
 
For me, I think my best days have been in crisis. One day that stands out was when I had a consulting client call me as I was on the way to New York. I was in the airport when they called about a problem performing what I would have thought was a simple restore. Developers had run a backup before performing a software upgrade, but when that failed, they couldn't get the database back to the previous state. They were an application service provider for their clients and were panicking a bit.
 
I couldn't connect and had to query them for information over the phone, picturing the situation in my mind. I managed to help them realize that there were multiple backup files needed, track them down, and then structure the restore statement. I managed to help them, finishing up the call as I was boarding. It was a busy day, where I needed to focus on other things for my day job, but landing in NY and hearing they had successfully restored their system for a client, made that a great day.
 
I'd like to think I have more good days than bad ones in this business, and it's one reason I've enjoyed working with data for so long. I hope you have some good stories to pick from when you've had a good day in this field.

Steve Jones - SSC Editor

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

 
  Featured Contents

How Bad are Bad Page Splits – The Rest of the Story

Mike Byrd from SQLServerCentral

In Part 1 of this article we looked at a specific use case (probably exaggerated) that gave us an idea about how CPU and IO performance might be affected by a bad page split. We continue this analysis looking at what really happens to the data on the leaf pages of a clustered index with […]

Tempdb – Here’s a Problem You Didn’t Know You Have

Additional Articles from SimpleTalk

Because of its many roles, tempdb is the source of performance issues in SQL Server, and there are recommended configuration changes to mitigate this. In this article, Fabiano Amorim demonstrates another issue with tempdb performance that you probably didn’t know about.

Easily manage SQL Server licensing with SQL Monitor 10.1

Additional Articles from Redgate

SQL Monitor 10.1 now gives you a comprehensive overview of your SQL Server licensing, so you know which licenses are in use, and which versions are on which servers. This simplifies license auditing and lets you unearth misconfigurations that could potentially cost you money. The new version also integrates with Microsoft Teams and Splunk, and supports complex Active Directory environments.

From the SQL Server Central Blogs - Azure Data Studio SQL Notebook for Diagnostic Queries

Sheldon Hull from Sheldon Hull

Diagnostic Queries
Glenn Berry has long been known for producing the definitive diagnostic query set for various SQL Server versions. Between his amazing work and my favorite Brent Ozar First...

From the SQL Server Central Blogs - VM firmware matters for Windows Secure Boot

kleegeek from Technobabble by Klee from @kleegeek

The Register is reporting that future versions of Windows Server OS is going to require the TPM 2.0 chip and Secure boot enabled by default. Secure boot is quite...

 

  Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Querying Configurations

I can query various instance setting with sp_configure, but can I get these same settings in a DMV?

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)

Adding a new column in Python

I have a dataframe in Python that looks like this:

>>> import pandas as pd
>>> salessdata = {'SaleDate':['1 Jun 2020', '2 Jun 2020', '3 Jun 2020'],'SaleAmount':[100,200,300],'NumItems':[1,4,7]}
>>> sales = pd.DataFrame(salessdata, columns = ['SalesDate', 'SaleAmount', 'NumItems'])

I want to add a new column that will calculate the average cost per item, based on dividing the SaleAmount by the NumItems. Which of these will do that for me?

Answer: sales['AvgCostPerItem'] = sales['SaleAmount'] / sales['NumItems']

Explanation: We can add a new column just by referencing it in a formula.

>>> sales['AvgCostPerItem'] = sales['SaleAmount'] / sales['NumItems']
>>> sales
  SalesDate  SaleAmount  NumItems  AvgCostPerItem
0       NaN         100         1      100.000000
1       NaN         200         4       50.000000
2       NaN         300         7       42.857143
>>>

Ref: 3 ways to add new columns to a Pandas Dataframe - https://cmdlinetips.com/2019/01/3-ways-to-add-new-columns-to-pandas-dataframe/

Discuss this question and answer on the forums

 

Featured Script

Post SQL Server Installation steps with PowerShell

iLearnSQL from SQLServerCentral

I bet, each DBA would change some settings after the SQL Server installation as per their company standard . I wrote this PowerShell script for one of my client who has below standards: Port should be 1433, static Disable SQL telemetry* & SQLWriter services Change recovery model to FUll for all databases except tempdb and […]

# Chaning ports
'Loading SQLPS environment'
Import-Module SQLPS -DisableNameChecking -Force

'Initializing WMI object and Connect to the instance using SMO'
($Wmi = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $env:COMPUTERNAME)

($uri = "ManagedComputer[@Name='$env:COMPUTERNAME']/ ServerInstance[@Name='$env:COMPUTERNAME']/ServerProtocol[@Name='Tcp']")

# Getting settings
($Tcp = $wmi.GetSmoObject($uri))

$Tcp.IsEnabled = $true
($Wmi.ClientProtocols)

$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties

'Setting IP Properties'
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value="1433"

'Review properties'
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties

'Disable Dynamic Ports'
#($uri = "ManagedComputer[@Name='$env:COMPUTERNAME']/ ServerInstance[@Name='$env:COMPUTERNAME']/ServerProtocol[@Name='TcpDynamicPorts'].value=""")
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties['TcpDynamicPorts'].value=""

'Save properties'
$Tcp.Alter()

'Review properties'
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties

#Restart SQL Services
#Restart-Service -Force mssql$*

# Disabling SQLWriter and SQLtelemetry* services
Get-Service SQLWriter, SQLtelemetry* | Stop-Service -PassThru -Force | Set-Service -StartupType disabled | write-output

# Changing Recovery Model to FULL for all databases except tempdb and Master
$Server="localhost"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Server
$SMOserver.Databases | where-object {$_.Name -ne "tempdb" -and $_.Name -ne "master"} | select Name, RecoveryModel | Format-Table
$SMOserver.Databases | where-object {$_.Name -ne "tempdb" -and $_.Name -ne "master"} | foreach {$_.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full; $_.Alter()}
$SMOserver.Databases | where-object {$_.Name -ne "tempdb" -and $_.Name -ne "master"} | select Name, RecoveryModel | Format-Table

# Assigning sysadminpermissions to NT AUTHORITYSYSTEM
$Svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "localhost"
$svrole = $svr.Roles | where {$_.Name -eq 'sysadmin'}
$svrole.AddMember("NT AUTHORITYSYSTEM")

# If the computer has less than 8GB of physical memory, allocate 80% of it to SQL Server and leave 20% for the OS and other applications
# If the computer has more than 8GB of physical memory, assign 30% to OS and 70% to SQL Server

Function Get-ComputerMemory {
$mem = Get-WMIObject -class Win32_PhysicalMemory | Measure-Object -Property Capacity -Sum
return ($mem.Sum / 1MB);
}

Function Get-SQLMaxMemory {
$memtotal = Get-ComputerMemory
$min_os_mem = 2048 ;
if ($memtotal -le $min_os_mem) {
Return $null;
}
if ($memtotal -ge 8192) {
$sql_mem = $memtotal * 0.7
} else {
$sql_mem = $memtotal * 0.8 ;
}
return [int]$sql_mem ;
}

Function Set-SQLInstanceMemory {
param (
[string]$SQLInstanceName = "localhost",
[int]$maxMem = $null,
[int]$minMem = 0
)

if ($minMem -eq 0) {
$minMem = 1024
}
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object Microsoft.SQLServer.Management.Smo.Server($SQLInstanceName)
if ($srv.status) {
Write-Host "[Running] Setting Maximum Memory to: $($srv.Configuration.MaxServerMemory.RunValue)"
Write-Host "[Running] Setting Minimum Memory to: 1024"

Write-Host "[New] Setting Maximum Memory to: $maxmem"
Write-Host "[New] Setting Minimum Memory to: 1024"
$srv.Configuration.MaxServerMemory.ConfigValue = $maxMem
$srv.Configuration.MinServerMemory.ConfigValue = 1024
$srv.Configuration.Alter()
}
}

$MSSQLInstance = "localhost"
Set-SQLInstanceMemory $MSSQLInstance (Get-SQLMaxMemory)

# Create TempFiles

function Invoke-Sqlcmd2
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
[Parameter(Position=1, Mandatory=$false)] [string]$Database,
[Parameter(Position=2, Mandatory=$false)] [string]$Query,
[Parameter(Position=3, Mandatory=$false)] [string]$Username,
[Parameter(Position=4, Mandatory=$false)] [string]$Password,
[Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600,
[Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15,
[Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile,
[Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow"
)

if ($InputFile)
{
$filePath = $(resolve-path $InputFile).path
$Query = [System.IO.File]::ReadAllText("$filePath")
}

$conn=new-object System.Data.SqlClient.SQLConnection

if ($Username)
{ $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
else
{ $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }

$conn.ConnectionString=$ConnectionString

#Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
if ($PSBoundParameters.Verbose)
{
$conn.FireInfoMessageEventOnUserErrors=$true
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
$conn.add_InfoMessage($handler)
}

$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
switch ($As)
{
'DataSet' { Write-Output ($ds) }
'DataTable' { Write-Output ($ds.Tables) }
'DataRow' { Write-Output ($ds.Tables[0]) }
}

} #Invoke-Sqlcmd2

function Set-TempDbSize
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$false)] [Int16]$maxFileCount = 16,
[Parameter(Position=1, Mandatory=$false)] [Int32]$maxFileInitialSizeMB = 1024,
[Parameter(Position=2, Mandatory=$false)] [Int32]$maxFileGrowthSizeMB = 10240,
[Parameter(Position=3, Mandatory=$false)] [Int32]$fileGrowthMB = 100,
[Parameter(Position=4, Mandatory=$false)] [float]$coreMultiplier = 1.0,
[Parameter(Position=5, Mandatory=$false)] [switch]$outClipboard
)

#get a collection of physical processors
[array] $procs = Get-WmiObject Win32_Processor
$totalProcs = $procs.Count
$totalCores = 0

#count the total number of cores across all processors
foreach ($proc in $procs)
{
$totalCores = $totalCores + $proc.NumberOfCores
}

#get the amount of total memory (MB)
$wmi = Get-WmiObject Win32_OperatingSystem
$totalMemory = ($wmi.TotalVisibleMemorySize / 1024)

#calculate the number of files needed (= number of procs)
$fileCount = $totalCores * $coreMultiplier

Write-Host "$fileCount"
if ($fileCount -gt $maxFileCount)
{
$fileCount = $maxFileCount
}

#calculate file size (total memory / number of files)
$fileSize = $totalMemory / $fileCount

if ($fileSize -gt $maxFileInitialSizeMB)
{
$fileSize = $maxFileInitialSizeMB
}

Write-Host "$fileSize","$fileCount"

#build the sql command
$command = @"

declare @data_path varchar(300);

select
@data_path = replace([filename], '.mdf','')
from
sysaltfiles s
where
name = 'tempdev';

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = {0}MB , MAXSIZE = {1}MB, FILEGROWTH = {2}MB );
"@ -f $fileSize, $maxFileGrowthSizeMB, $fileGrowthMB

for ($i = 2; $i -le $fileCount; $i++)
{
$command = $command + @"
declare @stmnt{3} nvarchar(500)
select @stmnt{3} = N'ALTER DATABASE [tempdb] ADD FILE ( NAME = N''tempdev{3}'', FILENAME = ''' + @data_path + '{3}.mdf'' , SIZE = {0}MB , MAXSIZE = {1}MB, FILEGROWTH = {2}MB )';
print @stmnt{3}
exec sp_executesql @stmnt{3};

"@ -f $fileSize, $maxFileGrowthSizeMB, $fileGrowthMB, $i
}

Write-Host "$fileSize","$maxFileGrowthSizeMB","$fileGrowthMB"

if ($outClipboard)
{
$command | clip
return "";
}
else
{
return $command
}

# remove ndf files

$command = "
DECLARE @ndfname nvarchar(100)
declare @queryNdf nvarchar(100)
use tempdb
DECLARE c1 CURSOR
FOR

select name from sysfiles
where filename like '%ndf%'

OPEN c1

FETCH NEXT FROM c1
INTO @ndfname

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @ndfname
use tempdb
--DBCC SHRINKFILE (@ndfname, EMPTYFILE)

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE ( 'ALL')

--DBCC SHRINKFILE (@ndfname, EMPTYFILE)
use master

SET @queryNdf = 'ALTER DATABASE tempdb REMOVE FILE [' + @ndfname + ']'

PRINT @queryNdf
Exec (@queryNdf)

FETCH NEXT FROM c1
INTO @ndfname

END

CLOSE c1
DEALLOCATE c1

return $command

}

#Set-TempDbSize -outClipboard
$query=Set-TempDbSize

Invoke-Sqlcmd2 -ServerInstance "localhost" -Query $query

More »

 

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
Failover, Patching and Hallengren Backups - I am using a replication tools that has been having issues the last couple of patch cycles.  I am doing some troubleshooting and I see a few issues.   When patching the AG servers a failover occurred.  To patch AG servers, should we reboot the secondary first and then the primary?  We never thought of […]
SQL Server 2017 - Development
JOIN Causing Duplicate Values - Dear Group: I am missing something and not sure what. Below, is the output and query when I do a simple select statement and these numbers are correct. SELECT A.ReportDate, A.Site, FORMAT(A.ReportDate, 'yyy') + '-' + FORMAT(A.ReportDate, 'MM') AS Report_Slicer, A.Pfolio, A.C_Type, SUM(A.Calls) AS Calls, SUM(A.HSec) AS HSec, SUM(A.Accept) AS Accept, SUM(A.Reject) AS Reject, SUM(A.ASec) […]
Incorrect syntax near the keyword 'unique'. - Hi All I am trying to create new table on sql server 2014 enterprise edition with constraint and unique non-cluster index and getting  "Incorrect syntax near the keyword 'unique'." msg. It runs ok on sql 2017 server.I tried to look into books online but link takes to 2019.here is my script EGIN TRAN BEGIN try […]
NPI Data - Has anyone ever imported the NPI data from the CMS website? I can't seem to get it to work.   https://download.cms.gov/nppes/NPI_Files.html
SQL Server 2016 - Administration
Finding when SQL login switched to disabled status - Hello experts, I just fixed an issue for a client where I discovered the SQL login in question was disabled. I forgot (my fault) to check the login data before re-enabling the login, so I think I lost the previous change date for the login. I did try to go back and restore backups of […]
Linked server -   Hi I have 2 servers A and B for which I need to setup a linked server. these are SQL servers. When I right click on server A and select the option create new linked server and I enter the new linked server name as B and in server B when I select the […]
Health check for SQL Server - We had ESXi host issue cause SQL Server VM's to reboot. So I looked at the logs and connectivity test and now running check db. Do you think I am missing anything here? Thanks in Advance!
SQL Server 2016 - Development and T-SQL
Create a customer calendar from its history - Hello, I would like to create a calendar from a history table for a customer, so that I can read the status for each day. Date        SupportlevelID 2020-12-29  4 2020-12-30  NULL 2020-12-31  NULL 2020-12-29  NULL But I only have a history table where I can find dates of changes. SupportlevelID | date […]
Administration - SQL Server 2014
error while setting up linked error - Hi I get the error Client and Server cannot communicate...they do not posses a common algorithm how to resolve this Regards  
Development - SQL Server 2014
SQL Server Trigger to prevent insertion of duplicate data in Table - Hello, I have a company table for some reason I can't create a constraint on table level. I am trying to use the following Trigger on my table to avoid duplicate records. when I try to insert the record I am getting following error. Please advise how to avoid duplicate record insert / Update using […]
SQL 2012 - General
value separate to text and unit not working when value have comma separated mult - I work on SQL server 2012 I face issue : I can't split Value column to Text Unit and Value Unit in case I have on Value column multiple value separated by comma . as example Value ValueUnit TextUnit 1.71V, 2.375V, 3.135V 1.71 V, 2.375V, 3.135V have issue when separate it to text and value […]
SQL Server 2019 - Administration
upgrade SSIS catalog got an error - I move a SSIS database and other user databases  from SQL server 2017 to a new server 2019. Other databases work fine. SSISDB does not work as expected. I restore the SSIS database and master key, successfully. But When I try to run database upgrade from SSMS by right click SSISDB Database upgrade I got […]
Analysis Services
Changing Analysis Service Directory - Hi Guys, I need to move the Analysis Services directory found at this location ...\MSAS14.POWERPIVOT Is this possible to do? I've had a read and am confused by some folk saying I have to re-install and others saying its a config option?   Cheers Alex
Integration Services
Deploy error after migrating database from 2017 to 2019 new server - I am working on to migrate databases from a SQL server 2017 to 2019 new SQL server. Other databases work fine. But I have errors when running packages in the SSIS catalog. So I though I  need to upgrade the packages in MS Data tool first. I open VS 2019 data tool, and changed the […]
COVID-19 Pandemic
Daily Coping 29 Jun 2020 - Today’s tip is to appreciate the joy of nature and the beauty in the world around you. http://voiceofthedba.com/2020/06/29/daily-coping-29-jun-2020/
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -