Mssql

  • add local (non-AAD) user to Azure SQL Server
  • connect to Azure SQL server with SSMS
  • right-click on SQL Server name and select new query

  • execute the query below to create a login and user on server level security context with public permission (connect only)

    sql CREATE LOGIN <USERNAME> WITH PASSWORD = '<strong password>' GO CREATE USER <USERNAME> FOR LOGIN <USERNAME> GO - right-click on the database you need to allow the user to access and select new query - execute the query below to create the user in the security context of the database and attach to appropriate role

    sql CREATE USER <USERNAME> FOR LOGIN <USERNAME> GO EXEC sp_addrolemember N'db_datawriter', N'<USERNAME>' GO - add AD user or group to Azure SQL Database (no need to add user to server, only database level)

sql create user [AZURE-AD-USER-OR-GROUP] from external provider

  • stop all SQL services on server

powershell $sqlservice = Get-Service | where DisplayName -Like "SQL Server*" | select Name foreach ($sql in $sqlservice) { Set-Service -Name "$($sql.Name)" -StartupType Disabled; Stop-Service -Name "$($sql.Name)" -Force } - show recent failed logins on SQL instance

sql EXEC sp_readerrorlog 0, 1, 'Login failed'

  • DBCSHRINKDATABASE may not be a good idea

  • when SQLSERVER Max memory is set to 0 and SQL service cannot start

  • use the -f switch with sqlserver.exe to run instance in a minimal single user/admin mode

    batch sqlserver.exe -f -s<INSTANCENAME> - then connect with SSMS and change the max memory on the instance

  • query to find database index fragmentation

sql 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() ORDER BY indexstats.avg_fragmentation_in_percent desc

  • query to find empty space in database

sql USE [database name] GO SELECT [TYPE] = A.TYPE_DESC ,[FILE_Name] = A.name ,[FILEGROUP_NAME] = fg.name ,[File_Location] = A.PHYSICAL_NAME ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT) /128.0) ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100) ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id order by A.TYPE desc, A.NAME;

  • to rebuild database index after shrink:
  • connect to database with SSMS
  • expand database and expand Indexes

  • right-click on the index and select Rebuild and then Reorganize

  • show progress of currently running backup jobs

sql USE master GO SELECT command AS Command, d.name AS Name, percent_complete AS PercentComplete, start_time AS StartTime, GETDATE() AS CurrentTime, DATEADD(second,estimated_completion_time/1000, GETDATE()) AS EndTime, DATEDIFF(minute, start_time, GETDATE()) AS MinsRunning, estimated_completion_time/1000/60 AS MinsToGo FROM sys.dm_exec_requests req INNER JOIN sys.sysdatabases d ON d.dbid = req.database_id WHERE req.command LIKE '%BACKUP%' - when backing up TDE encrypted databases, add maxtransfersize to avoid lengthy backup job

sql MAXTRANSFERSIZE = 1048576