Run SQL Server BLOG – Page 4 – Just another Select of Ms SQL DBA site

10th July 202110th July 2021

Install SSMS without Azure Data Studio

Starting with SSMS 18.7, SSMS installs a system version of Azure Data Studio by default. If an equal or greater system version of Azure Data Studio stable or insiders is already present on the workstation compared to the included version of Azure Data Studio, the installation of Azure Data Studio by SSMS is skipped. The Azure Data Studio version can be found in the release notes.

If you do not want to install ADS (Azure Data Studio) use the SSMS installer with the command line flag DoNotInstallAzureDataStudio=1.

SSMS-Setup-ENU.exe /Passive DoNotInstallAzureDataStudio=1

1	SSMS-Setup-ENU.exe /Passive DoNotInstallAzureDataStudio=1

10th July 202110th July 2021

SSISDB update failed during the SQL CU or SP udpate

Reason: the cu found a SSIDB and wanted to upgrade it.
It failed because SSIS component is not installed to the server.

It needs: Cannot bulk load. The file “C:\Program Files\Microsoft SQL Server\150\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll” does not exist or you don’t have file access rights.
Yes it is missing , because SSIS component installed.

Without the ssisdb the sql server was not able to start.

It recommended to reinstall the master. Do NOT reinstall the master.

My solution was:

Install (add ) the Integration Service component.
Start the server with 902 flag (admin command prompt)

net start MSSQL$SVAL26T /T902

1

net start MSSQL$SVAL26T /T902

create a missing SQL login and connect to hte SSISD

CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'anything', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE SSISDB
GO

ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]

CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'anything', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE SSISDB

ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]

Reason:

If you don’t create SSISDB locally (you attach by migration), the SQL login will missing.

Help:

After installing Integration service it gave enough hint:

2021-07-10 14:12:47.73 spid10s Cannot find the login ‘##MS_SSISServerCleanupJobLogin##’, because it does not exist or you do not have permission.

2021-07-10 14:12:47.73 spid10s Error: 912, Severity: 21, State: 2.

2021-07-10 14:12:47.73 spid10s Script level upgrade for database ‘master’ failed because upgrade step ‘ISServer_upgrade.sql’ encountered error 15151, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

SUM: Please check if there is a SSIDB in the SQL server before install SP or CU.

9th July 2021

Find and list read and writeable user databases which are only in local instance or in primary AG group

List database names, which are in AO AG:

SELECT name FROM sys.sysdatabases where dbid>4 and name  in(
SELECT  DB_NAME(drs.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
and drs.replica_id = ars.replica_id
where ars.role_desc='PRIMARY' 
OR ars.role_desc='SECONDARY'
)

SELECT name FROM sys.sysdatabases where dbid>4 and name in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY'

OR ars.role_desc='SECONDARY'

)

Local databases , which are outside of AO AG:

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
SELECT  DB_NAME(drs.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
and drs.replica_id = ars.replica_id
where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

Writeable databases : primary db or only in local instance

    SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
    SELECT  DB_NAME(drs.database_id) as name
    FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
    JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
    JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
    and drs.replica_id = ars.replica_id
    where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

    UNION

    SELECT name FROM sys.sysdatabases where dbid>4 and name  in(
    SELECT  DB_NAME(drs.database_id) as name
    FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
    JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)
    JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id
    and drs.replica_id = ars.replica_id
    where ars.role_desc='PRIMARY' 
    --OR ars.role_desc='SECONDARY'

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY')

UNION

SELECT name FROM sys.sysdatabases where dbid>4 and name in(

SELECT DB_NAME(drs.database_id) as name

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id)

JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id

and drs.replica_id = ars.replica_id

where ars.role_desc='PRIMARY'

--OR ars.role_desc='SECONDARY'

2nd July 2021

Find limited SQL database files and log files

How to list limited database file size in a SQL server?

SELECT DB_NAME(database_id) AS DatabaseName,
[Name] AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB, (size*8) as SizeKB 
--, SUM(size*8) OVER (partition by database_id) as [Total Size in KB]
,* 
FROM sys.master_files
where  not (max_size = -1 or   max_size = 2684354561 )   -- (268435456 * 8000 = 2147483648000 = 2TB)
--  and DB_NAME(database_id) not in ('master','tempdb','model')
order by DatabaseName

SELECT DB_NAME(database_id) AS DatabaseName,

[Name] AS Logical_Name,

Physical_Name, (size*8)/1024 SizeMB, (size*8) as SizeKB

--, SUM(size*8) OVER (partition by database_id) as [Total Size in KB]

FROM sys.master_files

where not (max_size = -1 or max_size = 2684354561 ) -- (268435456 * 8000 = 2147483648000 = 2TB)

-- and DB_NAME(database_id) not in ('master','tempdb','model')

order by DatabaseName

20th April 2021

Subquery inside FROM table VS JOIN

Just an example for the same solution and results:

SELECT countries.local_name, lang_num
FROM countries,
  ( Select code, count(*) as lang_num
    From languages
    Group by code
    ) AS subquery
Where countries.code = subquery.code
Order by lang_num desc;

GO

SELECT  countries.local_name, count(*) AS lang_num
FROM countries
INNER JOIN languages
ON countries.code = languages.code
GROUP By languages.code, countries.local_name
ORDER BZ lang_num desc;

SELECT countries.local_name, lang_num

FROM countries,

( Select code, count(*) as lang_num

From languages

Group by code

) AS subquery

Where countries.code = subquery.code

Order by lang_num desc;

SELECT countries.local_name, count(*) AS lang_num

FROM countries

INNER JOIN languages

ON countries.code = languages.code

GROUP By languages.code, countries.local_name

ORDER BZ lang_num desc;

15th April 202114th April 2023

In memory OLTP database with Defender VirusScan setting

If you do not set the recommended VirusScan settings for SQL you can see some overhead.

In-memory OLTP SQL

I switched off the Windows Defender VirusScan between 1:29 – 1:40
Pages/sec in Memory and Disk reads is less wit turned off VirusScan.

28th January 202127th January 2021

Oversizing a column in a table – finding varchar(max) and text in MsSQL

It is very commmon when a table have one or more to wide columns.
Sometimes developers choose not supported columns, for example, text type in MS SQL database.
I always looking for text, ntext, varchar(max) and nvarchar(max) :

SELECT TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
 -- ORDINAL_POSITION,
  IS_NULLABLE,DATA_TYPE,  CHARACTER_SET_NAME, COLLATION_NAME
  ,CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
DATA_TYPE= 'text' 
or DATA_TYPE='ntext' 
or  (DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH =-1) 
or (DATA_TYPE='nvarchar' and CHARACTER_MAXIMUM_LENGTH =-1)
order by TABLE_NAME,DATA_TYPE
-- and TABLE_NAME ='table

SELECT TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,

-- ORDINAL_POSITION,

IS_NULLABLE,DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME

,CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE

DATA_TYPE= 'text'

or DATA_TYPE='ntext'

or (DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH =-1)

or (DATA_TYPE='nvarchar' and CHARACTER_MAXIMUM_LENGTH =-1)

order by TABLE_NAME,DATA_TYPE

-- and TABLE_NAME ='table

After it, I check the (n)varchar and (n)char columns.

I am finding the longest string.

-- varchar type
SELECT max(len(oszlopnev)) from table_name

-- SELECT max(datalength(code)) from table_name --you can see nvarchar use the double size

--text type:
SELECT max(datalength([oszlopnev])) FROM [tablename]

-- varchar type

SELECT max(len(oszlopnev)) from table_name

-- SELECT max(datalength(code)) from table_name --you can see nvarchar use the double size

--text type:

SELECT max(datalength([oszlopnev])) FROM [tablename]

27th January 202127th January 2021

Index fragmentation with table row count and index creation and modification date

You can often check the index defragmentation. It is useful if you start checking with the biggest table, because the biggest effect is there.
I like to check the indexes with the creation and modification date. If you rebuild the index your index modification date will be updated.

USE UserDB     -- with modifydate -order by rowcount
GO

SELECT s.[name] +'.'+t.[name]  AS table_name
 ,i.NAME AS index_name
 ,index_type_desc
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
 ,record_count AS table_record_count
 ,create_date
 ,modify_date
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
ORDER BY record_count desc, avg_fragmentation_in_percent DESC

USE UserDB -- with modifydate -order by rowcount

SELECT s.[name] +'.'+t.[name] AS table_name

,i.NAME AS index_name

,index_type_desc

,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent

,record_count AS table_record_count

,create_date

,modify_date

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips

INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]

INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)

ORDER BY record_count desc, avg_fragmentation_in_percent DESC

If you want to see for all database you can replace the

DB_ID()

with

NULL

in the script.

16th January 20217th February 2021

Python and MS SQL connection

Install ODBC driver for MS SQL>
pip install pyodbc
Downloading pyodbc-4.0.30-cp39-cp39-win_amd64.whl (67 kB)
|████████████████████████████████| 67 kB 745 kB/s
Installing collected packages: pyodbc
Successfully installed pyodbc-4.0.30

Python query:

import pyodbc 
# Some other example server values are
# server = 'localhost\sqlexpress' # for a named instance
# server = 'myserver,port' # to specify an alternate port
server = 'tcp:192.168.0.13' 
database = 'AdventureWorks2017' 
username = 'dbuser' 
password = 'strongpassword' 
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = conn.cursor()
cursor.execute('SELECT  TOP(5) [FirstName]  ,[MiddleName]  ,[LastName] FROM [AdventureWorks2017].[Person].[Person]')
for row in cursor:
    print(row)

import pyodbc

# Some other example server values are

# server = 'localhost\sqlexpress' # for a named instance

# server = 'myserver,port' # to specify an alternate port

server = 'tcp:192.168.0.13'

database = 'AdventureWorks2017'

username = 'dbuser'

password = 'strongpassword'

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

cursor = conn.cursor()

cursor.execute('SELECT TOP(5) [FirstName] ,[MiddleName] ,[LastName] FROM [AdventureWorks2017].[Person].[Person]')

for row in cursor:

print(row)

27th December 202027th December 2020

Download SQL PASS content

As announced on December 17, 2020, PASS saddened to share that, due to the impact of COVID-19, PASS is ceasing all regular operations, effective January 15, 2021. PASS will maintain access for members until January 15th. Please read the announcement from the PASS Board for more information.
The PASS encourage you to take full advantage of any access that you have to PASS content between now and January 15, 2021.

You can download them in the SQLSatruday website by manually:

sqlsaturday SQL Pass — SQLSsaturday – SQL Pass web page presentation download

Or you can download by script.
I created a simple script in python, which can download the files which is not required authentication. Why python? I started to study it two month ago, so I must have a real practice.

# created by Gábor Bodnár 2020-12-24
# you can download SQLSaturday session files, which does not req authentication
# order from the newest to older files in batch . The latest is: 28874
# https://www.sqlsaturday.com/SessionDownload.aspx?suid=28865
# https://www.sqlsaturday.com/SessionDownload.aspx?suid=16007
# https://www.sqlsaturday.com/SessionDownload.aspx?suid=28134 (you can download from logged in site)

import sys
import os
import ntpath
import wget
from shutil import move
from colorama import init
init()


# set from hinumber to lonumber  separeted folde by step, basefolder the directory on your local computer
hinumber=28874
lonumber=24000
step=1000
basefolder='C:\\SQLSatruday\\presentations\\'

# download routine

for i in range(hinumber, lonumber,-1):
    print('i:',str(i))
    if i % step ==0 or i==hinumber:
        print(i)
        subdir=str(i)
        downloadfolder=basefolder+subdir
        print('basefolder:',basefolder)
        print('subdir:',subdir)
        print('downloadfolder',downloadfolder)
        if not os.path.exists(downloadfolder):

            os.makedirs(downloadfolder)
    
    file_base_url='https://www.sqlsaturday.com/SessionDownload.aspx?suid='
    file_url =  file_base_url+ str(i)
    file_name = wget.download(file_url,out=downloadfolder)

    directory= os.path.dirname(file_name)
    origpath=os.path.realpath(file_name)
    filenev=os.path.basename(file_name)
    
    print(' origpath:',origpath)
    print(' Filenev:',filenev)
    strfilenev=str(filenev)
    if  strfilenev=='SessionDownload.aspx' or strfilenev == 'SessionDownload (1).aspx' :
        print('\033[31m' + str(i) + '\033[0m' )
        f= open( basefolder+"\\download_"+subdir+"_url_error.txt","a")
        f.write(file_url+'\n')
        f.close()

    ujfilenev=str(i)+'_'+str(filenev)
    ujutvonal=directory+'\\' +ujfilenev
    move(origpath,ujutvonal)
    print('new downloaded file:',ujutvonal)

# created by Gábor Bodnár 2020-12-24

# you can download SQLSaturday session files, which does not req authentication

# order from the newest to older files in batch . The latest is: 28874

# https://www.sqlsaturday.com/SessionDownload.aspx?suid=28865

# https://www.sqlsaturday.com/SessionDownload.aspx?suid=16007

# https://www.sqlsaturday.com/SessionDownload.aspx?suid=28134 (you can download from logged in site)

import sys

import os

import ntpath

import wget

from shutil import move

from colorama import init

init()

# set from hinumber to lonumber separeted folde by step, basefolder the directory on your local computer

hinumber=28874

lonumber=24000

step=1000

basefolder='C:\\SQLSatruday\\presentations\\'

# download routine

for i in range(hinumber, lonumber,-1):

print('i:',str(i))

if i % step ==0 or i==hinumber:

print(i)

subdir=str(i)

downloadfolder=basefolder+subdir

print('basefolder:',basefolder)

print('subdir:',subdir)

print('downloadfolder',downloadfolder)

if not os.path.exists(downloadfolder):

os.makedirs(downloadfolder)

file_base_url='https://www.sqlsaturday.com/SessionDownload.aspx?suid='

file_url = file_base_url+ str(i)

file_name = wget.download(file_url,out=downloadfolder)

directory= os.path.dirname(file_name)

origpath=os.path.realpath(file_name)

filenev=os.path.basename(file_name)

print(' origpath:',origpath)

print(' Filenev:',filenev)

strfilenev=str(filenev)

if strfilenev=='SessionDownload.aspx' or strfilenev == 'SessionDownload (1).aspx' :

print('\033[31m' + str(i) + '\033[0m' )

f= open( basefolder+"\\download_"+subdir+"_url_error.txt","a")

f.write(file_url+'\n')

f.close()

ujfilenev=str(i)+'_'+str(filenev)

ujutvonal=directory+'\\' +ujfilenev

move(origpath,ujutvonal)

print('new downloaded file:',ujutvonal)

The scripts create a subfolder for every 1000 files, if it stops (I had problem with two special filename, you can rewrite the hinumber and lownumber and continue)

Be aware!
So the SQL Pass is a very large community. If you want to download around 3500 pdf , 4000 ppt and 8000 zip files with a lot of stuff, you need at least 50 GB disk space.
I am sure this big community will survive in a new form.

Enjoy the knowledge of SQL Server community!