Uncategorised – Run SQL Server BLOG

18th May 202318th May 2023

VS Code and Git and Docker

Download a git project from github.
Create Visual Studio project workspae
Build containers

Commands in powershell (in the new empty project folder):

# download the code
git clone https://github.com/docker/welcome-to-docker

# enter to project folder
cd .\welcome-to-docker\

# open it in Visual Studio Code
code .

# build the container
docker build -t welcome-to-docker .

# yo can start it in Docker Desktop

# download the code

git clone https://github.com/docker/welcome-to-docker

# enter to project folder

cd .\welcome-to-docker\

# open it in Visual Studio Code

code .

# build the container

docker build -t welcome-to-docker .

# yo can start it in Docker Desktop

28th January 202315th April 2023

Dynamically generated code T-SQL code can cause trouble

A poorly written query can code trouble if the SQL server cannot predict a good execution plan because of too many type conversation and varchar max parameters:

After changing the code

30th July 2021

LOGMAN for SQL Server

en example for

logman.exe create counter SQLServerPerf -f bincirc -v mmddhhmm -max 500 -c "\Processor(*)\*" "\LogicalDisk(*)\*" "\Memory(*)\*" "\TCP\*" "\Network Interface(*)\*" "\PhysicalDisk(*)\*" "\Process(*)\*" "\SQLServer:Wait Statistics(*)\*" "\SQLServer:SQL Statistics\*" "\SQLServer:Resource Pool Stats(*)\*" "\SQLServer:Memory Manager\*" "\SQLServer:Memory Node(*)\*" "\SQLServer:General Statistics\*" "\SQLServer:Databases(*)\*" "\SQLServer:Buffer Manager\*" "\SQLServer:Access Methods\*" "\SQLServer:Transactions\*" "\SQLServer:SQL Errors\*" "\ProcessorPerformance\*" -si 00:00:10

logman.exe create counter SQLServerPerf -f bincirc -v mmddhhmm -max 500 -c "\Processor(*)\*" "\LogicalDisk(*)\*" "\Memory(*)\*" "\TCP\*" "\Network Interface(*)\*" "\PhysicalDisk(*)\*" "\Process(*)\*" "\SQLServer:Wait Statistics(*)\*" "\SQLServer:SQL Statistics\*" "\SQLServer:Resource Pool Stats(*)\*" "\SQLServer:Memory Manager\*" "\SQLServer:Memory Node(*)\*" "\SQLServer:General Statistics\*" "\SQLServer:Databases(*)\*" "\SQLServer:Buffer Manager\*" "\SQLServer:Access Methods\*" "\SQLServer:Transactions\*" "\SQLServer:SQL Errors\*" "\ProcessorPerformance\*" -si 00:00:10

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!

24th December 202027th December 2020

Install wget in Python

You don’t need to downloag and install wget.
Instead of it, you run the following in cmd (or in python):

python.exe -m pip install --upgrade pip
pip install wget

1 2	python.exe -m pip install --upgrade pip pip install wget

26th March 201926th March 2019

Adatbázis méret csökkentése – Shrink a Database

Adatbázis fájlok méretét ritkán csökkentjük, hiszen többnyire úgy is megtelik új adatokkal , indexekkel. Érdemes csökkenteni a méretet például ha nagyon sok adatot kipucoltunk az adatbázisból és csak nagyon sokára fog újra felhízni..

-- lényeg, hogy kis adagokban csökkentse a db-t mert soká tarthat és beterhel, valamint trunzactional  db is nő!!

DECLARE @DataFileName VARCHAR(255); 

DECLARE @starttime datetime
DECLARE @endtime datetime
SET @starttime =getdate()
SET @DataFileName = (SELECT name FROM sysfiles WHERE groupid = 1);
DECLARE @TargetSize INT; 

SET @starttime =getdate()
SET @TargetSize = ROUND(8 * (SELECT size FROM sysfiles WHERE groupid = 1) / 1024, 0) - 500;   -- Select current size and substract 500 MB
SELECT @DataFileName as datafiename,@TargetSize as targetsize;
EXEC ('DBCC SHRINKFILE (' + @DataFileName + ', ' + @TargetSize + ')');
SET @endtime =getdate()
SELECT DATEDIFF(millisecond,@starttime,@endtime) AS elapsed_ms;

-- lényeg, hogy kis adagokban csökkentse a db-t mert soká tarthat és beterhel, valamint trunzactional db is nő!!

DECLARE @DataFileName VARCHAR(255);

DECLARE @starttime datetime

DECLARE @endtime datetime

SET @starttime =getdate()

SET @DataFileName = (SELECT name FROM sysfiles WHERE groupid = 1);

DECLARE @TargetSize INT;

SET @starttime =getdate()

SET @TargetSize = ROUND(8 * (SELECT size FROM sysfiles WHERE groupid = 1) / 1024, 0) - 500; -- Select current size and substract 500 MB

SELECT @DataFileName as datafiename,@TargetSize as targetsize;

EXEC ('DBCC SHRINKFILE (' + @DataFileName + ', ' + @TargetSize + ')');

SET @endtime =getdate()

SELECT DATEDIFF(millisecond,@starttime,@endtime) AS elapsed_ms;

Az adatábzis méretet mindig óvatosan csökkentsük, lehet hogy 500 MB üres terület kivétele pillanatok alatt , pár másodperc alatt megtörténik, majd máskor az adatok áthelyezése miatt percekig fut akárcsak 1 MB átszervezése.

Javaslat: kisebb MB-ok csökkentésével kezdjük, majd növeljük a shrink méretet. Amikor elkezd nőni a végrehajtási idő , akkor csökkentsük a shrink méretet.

10th January 201916th January 2019

Adatbázis audit

DevOps környezetben vagy éles környezetben érdemes auditot beállítani. Saját érdekünkben érdemes tudni, hogy ki nyúlt bele és mit csinált!

CREATE SERVER AUDIT [Audit-20190110]
TO FILE
( FILEPATH = N'H:\Audit\'
,MAXSIZE = 100 MB
,MAX_FILES = 500
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 3000
,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit-20190110] WITH (STATE = ON)
GO

USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190110]
FOR SERVER AUDIT [Audit-20190110]
-- ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (BACKUP_RESTORE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_MIRRORING_LOGIN_GROUP),
-- ADD (LOGOUT_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (SERVER_OPERATION_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON)
GO

--Create the database Audit spec
CREATE DATABASE AUDIT SPECIFICATION [dbaudit]
FOR SERVER AUDIT [Audit-20190110]
ADD ( AUDIT_CHANGE_GROUP )
,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)
,ADD ( BACKUP_RESTORE_GROUP )
,ADD ( DATABASE_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP )
,ADD ( DATABASE_OBJECT_PERMISSION_CHANGE_GROUP )
,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )
,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP )
,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
,ADD ( DBCC_GROUP )
,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )
,ADD ( SCHEMA_OBJECT_CHANGE_GROUP )
,ADD ( SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP )
,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )
,ADD ( SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP )
-- 2012-newer allowed events
,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )
   ,ADD ( USER_DEFINED_AUDIT_GROUP )
   --,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )

WITH ( STATE = ON);

GO

SELECT event_time
,action_id
,session_server_principal_name AS UserName
,server_instance_name
,database_name
,schema_name
,object_name
,statement
FROM sys.fn_get_audit_file('H:\Audit*.sqlaudit', DEFAULT, DEFAULT)
-- WHERE action_id IN ( 'SL', 'IN', 'DR', 'LGIF' , '%AU%' )

CREATE SERVER AUDIT [Audit-20190110]

TO FILE

( FILEPATH = N'H:\Audit\'

,MAXSIZE = 100 MB

,MAX_FILES = 500

,RESERVE_DISK_SPACE = OFF

)

WITH

( QUEUE_DELAY = 3000

,ON_FAILURE = CONTINUE

)

ALTER SERVER AUDIT [Audit-20190110] WITH (STATE = ON)

USE [master]

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190110]

FOR SERVER AUDIT [Audit-20190110]

-- ADD (DATABASE_OBJECT_ACCESS_GROUP),

ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),

ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),

ADD (BACKUP_RESTORE_GROUP),

ADD (AUDIT_CHANGE_GROUP),

ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),

ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),

ADD (FAILED_LOGIN_GROUP),

ADD (DATABASE_MIRRORING_LOGIN_GROUP),

-- ADD (LOGOUT_GROUP),

ADD (DATABASE_CHANGE_GROUP),

ADD (DATABASE_OBJECT_CHANGE_GROUP),

ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_CHANGE_GROUP),

ADD (SERVER_OBJECT_CHANGE_GROUP),

ADD (SERVER_OPERATION_GROUP),

ADD (LOGIN_CHANGE_PASSWORD_GROUP),

ADD (SERVER_STATE_CHANGE_GROUP),

ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),

ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),

ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),

ADD (USER_CHANGE_PASSWORD_GROUP)

WITH (STATE = ON)

--Create the database Audit spec

CREATE DATABASE AUDIT SPECIFICATION [dbaudit]

FOR SERVER AUDIT [Audit-20190110]

ADD ( AUDIT_CHANGE_GROUP )

,ADD ( APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)

,ADD ( BACKUP_RESTORE_GROUP )

,ADD ( DATABASE_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP )

,ADD ( DATABASE_OBJECT_PERMISSION_CHANGE_GROUP )

,ADD ( DATABASE_PERMISSION_CHANGE_GROUP )

,ADD ( DATABASE_PRINCIPAL_CHANGE_GROUP )

,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )

,ADD ( DBCC_GROUP )

,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )

,ADD ( SCHEMA_OBJECT_CHANGE_GROUP )

,ADD ( SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP )

,ADD ( DATABASE_ROLE_MEMBER_CHANGE_GROUP )

,ADD ( SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP )

-- 2012-newer allowed events

,ADD ( FAILED_DATABASE_AUTHENTICATION_GROUP )

,ADD ( USER_DEFINED_AUDIT_GROUP )

--,ADD ( SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )

WITH ( STATE = ON);

SELECT event_time

,action_id

,session_server_principal_name AS UserName

,server_instance_name

,database_name

,schema_name

,object_name

,statement

FROM sys.fn_get_audit_file('H:\Audit*.sqlaudit', DEFAULT, DEFAULT)

-- WHERE action_id IN ( 'SL', 'IN', 'DR', 'LGIF' , '%AU%' )

25th September 201825th September 2018

Nyomtatott informatikai könyvek

Microsoft könyveknél baromi drága a szállítási költség Európába, hiába a “nagy” mcp-s kedvezmény. Több könyvnél már lehet számolgatni. Ms press könyveket Amazonon célszerű megrendelni , érdemes összehasonlítani az amazon.com, amazon.de és amazon.co.uk árait, majd hozzákalkulálni a fontosságtól függően a szállítás időtartamát.
Pro SQL Server on Linux
Apress kiadó: gyakori az ebook akció, drágák a nyomtatottak, de.. évente egyszer a Black Friday keretében napokon keresztül óriási akcióval ,12 eurós áron beszerezhető minden egyes könyv és mindezt ingyenes szállítás mellett. Ez a legszuperebb ajánlat, az ember már-már hitetlenkedik, hogy megéri e a kiadónak! Ott a trükk, hogy a célkontinensen, esetünkben Európában nyomtatják ki a könyvet és úgy postázzák! 🙂
Packtpub, Packt Publishing kiadó könyvei elsősorban az ebook eladásra koncentrálnak, gyakori az 10 euró körüli ebook “akció”, könyvek viszont drágák. Ennél a kiadónál viszont érdemes cupon kódokat keresni. Múlt héten rendeltem könyveket és 35%-os kedvezményes kódot találtam, ami az ingyenes szállítás mellet már nagyon jó! Működő kód (írd egybe,azaz kötőjeleket szedd ki): AI-NOW-PRINT-35 . SQL-es és Azuros könyvekre is adta a kedvezményt.

6th July 2018

Két lekérdezés eredményének összehasonlítása

Két lekérdezés különbsége:

select * from (select * from query1) as query1
except
select * from (select * from query2) as query2

select * from (select * from query1) as query1

except

select * from (select * from query2) as query2

Szükség esetén az order by elhagyandó a lekérdezésekben.

26th March 2018

Hello world!

Welcome to WordPress. This is your first post. Edit or delete it, then start writing!