Differences

This shows you the differences between two versions of the page.

--- db:mssql [2021/09/16 17:22] – [MSSQL rebuild all indexes in database] admin
+++ db:mssql [2021/11/03 09:53] (current) – admin
@@ Line 2: / Line 2: @@
 ===== MSSQL. Info =====
+  * https://habr.com/ru/post/316064/ - информация по mssql
+  * mssql video traning http://www.sqlservervideos.com/video/sql2528-log-files/
+  * https://sqlserver-kit.org/ru - каталог инструменты,
   * https://my.visualstudio.com/Downloads/Featured?mkt=ru-ru - скачивание дистрибутивов
-  * https://habr.com/ru/post/316064/ - информация по mssql
+  * https://docs.microsoft.com/ru-ru/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15 - SSMS - studio
   * https://sqlserverbuilds.blogspot.com/ - site with version / update
   * [[https://www.microsoft.com/en-us/download/details.aspx?id=44278|MSSQL 2008 SP4]]
   * mssql update pack https://support.microsoft.com/en-us/topic/kb968382-how-to-obtain-the-latest-service-pack-for-sql-server-2008-8b8113f9-dc3e-4f79-fecc-e1b8d96dd6fe
-  * mssql video traning http://www.sqlservervideos.com/video/sql2528-log-files/
   * sp_command help https://irfanworld.wordpress.com/tag/sp_helpdb/
 ===== MSSQL. Docker =====
 Docker mssql [[https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver15&pivots=cs1-bash|Mssql docker]] \\
-<code bash>
+<code bash> # пробросить mnt/volume для WSL и жестких дисков + нестандартный порт
-sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<YourStrong@Passw0rd>" \
+sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=passw0rd_123" \
-   -p 1433:1433 --name sql1 -h sql1 \
+ -p 1434:1433 --name sql1 -h sql1 -v /mnt:/mnt/volume \
-   -d mcr.microsoft.com/mssql/server:2019-latest
+ -v /opt/docker/mssql/isoj:/var/opt/mssql/data
+ -d mcr.microsoft.com/mssql/server:2019-latest
 </code>
@@ Line 23: / Line 25: @@
 sudo docker exec -it sql3 "bash"
 /opt/mssql-tools/bin/sqlcmd -S 172.31.28.155,1434 -U SA -P "passw0rd_123"
+-- включить mssql agent
+sp_configure 'show advanced options', 1;
+GO
+RECONFIGURE;
+GO
+sp_configure 'Agent XPs', 1;
+GO
+RECONFIGURE
+GO
 </code>
@@ Line 49: / Line 61: @@
 <code SQL>
 -- # Backup Restore/Mssql
+-- get backup set
+SELECT TOP 100
+  name,
+  type, *
+FROM
+  msdb..backupset
+WHERE 1=1
+ -- and database_name like '%adm%'
+ORDER BY
+backup_set_id DESC
 -- delete database
@@ Line 63: / Line 86: @@
 # backup full
-/*
+/* */
 USE WAdmIsk;
 BACKUP DATABASE WAdmIsk TO DISK = 'F:\Backups\temp\WAdmIskFull.bak' WITH FORMAT, MEDIANAME = 'SQLServerBackups', NAME = 'Full Backup of WAdmIsk';
@@ Line 99: / Line 122: @@
 </code>
 ++++
+++++ --- MSSQL. turn on agent|
+<code SQL>
+-- Get data for mssql
+sp_configure 'show advanced options', 1;
+GO
+RECONFIGURE;
+GO
+sp_configure 'Agent XPs', 1;
+GO
+RECONFIGURE
+GO
+</code>
+++++
+++++ --- SCRIPT - for archive all files except ZIP|
+<code BASH>
+Function Zip
+{
+	Param
+	(
+		[string[]]$toBeZipped
+	)
+    if ($null -eq $toBeZipped) {return}
+	$CurDir = Get-Location
+	Set-Location "C:\program files\7-zip\"
+	foreach ( $f in $toBeZipped)
+        {
+        echo "$(Get-Date -Format o) zip file $f "
+	   .\7z.exe A -tzip "$f.zip" $f | Out-Null
+        }
+	Set-Location $CurDir
+}
+Function ZipFiles
+{
+	Param
+	(
+		[string]$zipFile
+		,
+		[string[]]$toBeZipped
+	)
+	$CurDir = Get-Location
+	Set-Location "C:\program files\7-zip\"
+	.\7z.exe A -tzip $zipFile $toBeZipped | Out-Null
+	Set-Location $CurDir
+}
+# Code
+# get-childitem $path -recurse -exclude *.cs,*.tt,*.xaml,*.csproj,*.sln,*.xml,*.cmd,*.txt
+$bckpdir=$args[0]
+if ($null -eq $bckpdir){
+ echo "$(Get-Date -Format o) no directory in param in start"
+ exit 1
+}
+$CheckFile = Test-Path -Path $bckpdir
+if (!($CheckFile))  {
+ echo "$(Get-Date -Format o) $bckpdir  directory not exist "
+ exit 1
+}
+echo "$(Get-Date -Format o) --- start compress $bckpdir"
+$files = Get-ChildItem $bckpdir\*.* -exclude *.zip | Where-Object {$_.CreationTime -lt ((Get-date).adddays(+1))}
+Zip $files
+foreach ( $f in $files)
+{
+ If(Test-Path "$f.zip")
+   {
+     Remove-Item $f
+     echo "compress ok Delete $f"
+   }
+}
+echo  "$(Get-Date -Format o) === stop"
+</code>
+++++
+++++ --- SCRIPT - for archive ZIP all files except last one for sort in time|
+<code BASH>
+Function Zip
+{
+  Param
+  (
+    [string[]]$toBeZipped
+  )
+    if ($null -eq $toBeZipped) {return}
+  $CurDir = Get-Location
+  Set-Location "C:\program files\7-zip\"
+  foreach ( $f in $toBeZipped)
+        {
+        echo "$(Get-Date -Format o) zip file $f "
+     .\7z.exe A -tzip "$f.zip" $f | Out-Null
+        }
+  Set-Location $CurDir
+}
+Function ZipFiles
+{
+  Param
+  (
+    [string]$zipFile
+    ,
+    [string[]]$toBeZipped
+  )
+  $CurDir = Get-Location
+  Set-Location "C:\program files\7-zip\"
+  .\7z.exe A -tzip $zipFile $toBeZipped | Out-Null
+  Set-Location $CurDir
+}
+# Code
+# get-childitem $path -recurse -exclude *.cs,*.tt,*.xaml,*.csproj,*.sln,*.xml,*.cmd,*.txt
+$bckpdir=$args[0]
+if ($null -eq $bckpdir){
+ echo "$(Get-Date -Format o) no directory in param in start"
+ exit 1
+}
+$CheckFile = Test-Path -Path $bckpdir
+if (!($CheckFile))  {
+ echo "$(Get-Date -Format o) $bckpdir  directory not exist "
+ exit 1
+}
+echo "$(Get-Date -Format o) --- start compress $bckpdir"
+$files = Get-ChildItem $bckpdir\*.* -exclude *.zip | sort -Property CreationTime
+if ($files -is [array])
+{
+$files = $files[0..($files.Count - 2)]
+Zip $files
+foreach ( $f in $files)
+{
+ If(Test-Path "$f.zip")
+   {
+     Remove-Item $f
+     echo "compress ok Delete $f"
+   }
+}
+}
+else
+{
+   echo  "$(Get-Date -Format o) return is not array - only one file $files"
+}
+echo  "$(Get-Date -Format o) === stop"
+</code>
+++++
+++++ --- SCRIPT - clear age files in directory |
+<code BASH>
+$bckpdir=$args[0]
+if ($null -eq $bckpdir){
+ echo "$(Get-Date -Format o) no directory in param in start"
+ exit 1
+}
+$numdays=$args[1]
+if ($null -eq $numdays -or -not $numdays -match '^[0-9]+$' -or $numdays -lt 3 ){
+ echo "$(Get-Date -Format o) no days in integer or less than 3 in param "
+ exit 1
+}
+$CheckFile = Test-Path -Path $bckpdir
+if (!($CheckFile))  {
+ echo "$(Get-Date -Format o) $bckpdir  directory not exist "
+ exit 1
+}
+echo "$(Get-Date -Format o) --- start clear $bckpdir keep $numdays days"
+$files = Get-ChildItem –Path $bckpdir | Where-Object {($_.CreationTime -lt (Get-Date).AddDays(-$numdays))}
+if ($null -eq $files){
+ echo "$(Get-Date -Format o) === stop no files to delete"
+ exit 0
+}
+echo $($files| Format-Table  -HideTableHeaders)
+$files | Remove-Item
+</code>
+++++
 ===== MSSQL.bulk copy to file =====
@@ Line 143: / Line 368: @@
 ===== MSSQL rebuild all indexes in database =====
+<code SQL >-- Short way
+Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'</code>
 ++++ Rebuild indexes|
 <code SQL>
 --https://stackoverflow.com/questions/32505775/rebuild-all-indexes-in-a-database
+--check fragmentation
+SELECT dbschemas.[name] as 'Schema',
+dbtables.[name] as 'Table',
+dbindexes.[name] as 'Index',
+indexstats.avg_fragmentation_in_percent,
+indexstats.page_count
+FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
+INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
+INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
+INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
+AND indexstats.index_id = dbindexes.index_id
+WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'
+ORDER BY indexstats.avg_fragmentation_in_percent desc
+-- process fragmentation
+use ISOJ;
 DECLARE @TableName VARCHAR(255)
@@ Line 160: / Line 404: @@
 SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
 EXEC (@sql)
+DECLARE @msg VARCHAR(200) = 'process index completed. '+@TableName;
+    RAISERROR(@msg, 1, 1) WITH NOWAIT;
+    WAITFOR DELAY '00:00:01';
 FETCH NEXT FROM TableCursor INTO @TableName
 END
@@ Line 171: / Line 418: @@
 https://www.sqlservercentral.com/blogs/setting-up-a-server-side-trace
+===== MSSQL. syncronize tables =====
+++++Check syncrhonize|
+<code SQL>
+-- Sector
+select 'cappeal',
+year(case when c.date is null then co.date else c.date end) as  year,
+count(*) as cnt,
+sum(case when co.id is null then 1 else 0 end) as cntForLoad
+from ISOJ2018.dbo.cAppeal c
+full outer join isoj.dbo.cAppeal co on co.id=c.id
+-- where co.id is null
+group by rollup(year(case when c.date is null then co.date else c.date end)
+);
+</code>
+++++
+++++ Syncronize tables|
+<code SQL>
+USE ISOJ;
+  ---  synchronize tables
+    DECLARE @DATABASE NVARCHAR(255)
+    DECLARE @TABLE NVARCHAR(255)
+    DECLARE @cmd NVARCHAR(1000)
+ -- '[' + table_catalog + '].[' + table_schema + '].[' +  table_name + ']' as tableName
+ DECLARE DatabaseCursor CURSOR READ_ONLY FOR
+   SELECT top 2000 table_name  as tableName
+	   FROM [ISOJ].INFORMATION_SCHEMA.TABLES
+	   WHERE table_type = 'BASE TABLE'
+    OPEN DatabaseCursor
+    FETCH NEXT FROM DatabaseCursor INTO @TABLE
+    WHILE @@FETCH_STATUS = 0
+    BEGIN
+	FETCH NEXT FROM DatabaseCursor INTO @TABLE
+  --set @TABLE = 'cAppealSource'
+       SET @cmd = '
+INSERT INTO [isoj].[dbo].['+@TABLE+']
+select top 1000000 *
+from ISOJ2018.[dbo].['+@TABLE+'] c
+where c.id in (
+	select c.id
+	from ISOJ2018.[dbo].['+@TABLE+'] c
+	left join isoj.[dbo].['+@TABLE+'] co on co.id=c.id
+	where co.id is null
+)
+order by c.DateModified asc;
+	   '
+          BEGIN TRY
+             PRINT @TABLE -- uncomment if you want to see commands
+             EXEC (@cmd)
+          END TRY
+          BEGIN CATCH
+             PRINT '---'
+             PRINT @cmd
+             PRINT ERROR_MESSAGE()
+             PRINT '---'
+          END CATCH
+ END
+    CLOSE DatabaseCursor
+    DEALLOCATE DatabaseCursor
+GO
+</code>
+++++
+===== TSQL example =====
+++++ TSQL example|
+<code SQL>
+use ISOJ;
+ DECLARE @Database NVARCHAR(255)
+    DECLARE @Table NVARCHAR(255)
+    DECLARE @cmd NVARCHAR(1000)
+    DECLARE DatabaseCursor CURSOR READ_ONLY FOR
+    SELECT name FROM master.sys.databases
+    WHERE name IN ('ISOJ')  -- databases
+    AND state = 0 -- database is online
+    AND is_in_standby = 0 -- database is not read only for log shipping
+    ORDER BY 1
+    OPEN DatabaseCursor
+    FETCH NEXT FROM DatabaseCursor INTO @Database
+    WHILE @@FETCH_STATUS = 0
+    BEGIN
+       SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
+       table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
+       -- create table cursor
+       EXEC (@cmd)
+       OPEN TableCursor
+       FETCH NEXT FROM TableCursor INTO @Table
+       WHILE @@FETCH_STATUS = 0
+       BEGIN
+          BEGIN TRY
+             SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
+             PRINT @cmd -- uncomment if you want to see commands
+             EXEC (@cmd)
+          END TRY
+          BEGIN CATCH
+             PRINT '---'
+             PRINT @cmd
+             PRINT ERROR_MESSAGE()
+             PRINT '---'
+          END CATCH
+          FETCH NEXT FROM TableCursor INTO @Table
+       END
+       CLOSE TableCursor
+       DEALLOCATE TableCursor
+       FETCH NEXT FROM DatabaseCursor INTO @Database
+    END
+    CLOSE DatabaseCursor
+    DEALLOCATE DatabaseCursor
+GO
+</code>
+++++