CogKnowHow

Product:

Microsoft SQL server 2016

Issue:

A rollback of a deadlock is hung on itself. You have try to kill a process that was hung, but not it is stuck in suspended state.

Suggested solution:

Go to SSMS – Activity Monitor – sort on the Command column to find the rollback processes. Note down the ID

Check if this process is doing anything with this command:

select percent_complete, * from sys.dm_exec_requests where session_id = 69  -- change to your id

If the values does not change from 0%, then the process is most likely not doing anything.

From the result of above statement, you can of wait_resource column find out what table is creating the lock.

wait_resource = “KEY: 40:844424931901440 (7210abc) ” =  Database_Id, HOBT_Id

The first number is the database – use this SQL query to find what:

SELECT     name    FROM    sys.databases    WHERE      database_id=40;

The second number is the table that gives the issue – use this SQL query in the database to find where:

SELECT 

    sc.name as schema_name, 

    so.name as object_name, 

    si.name as index_name

FROM sys.partitions AS p

JOIN sys.objects as so on 

    p.object_id=so.object_id

JOIN sys.indexes as si on 

    p.index_id=si.index_id and 

    p.object_id=si.object_id

JOIN sys.schemas AS sc on 

    so.schema_id=sc.schema_id

WHERE hobt_id = 844424931901440;

If you can not find the other blocking process and stop it, and a recreate of a index does not help, then your option is to restart the SQL server service in Microsoft Windows.

Obtain some downtime on the SQL server, and restart the service in hope it will solve the deadlock.

https://www.mssqltips.com/sqlservertip/6307/how-to-stop-and-start-sql-server-services/

More information:

LCK_M_S

https://www.brentozar.com/archive/2014/03/happens-issue-kill/

https://linuxhint.com/sql-server-kill-spid/

https://littlekendra.com/2016/10/17/decoding-key-and-page-waitresource-for-deadlocks-and-blocking/

Product:
Microsoft SQL server 2016

Issue:

Get a warning when you create a table with a primary key.

Warning! The maximum key length for a clustered index is 900 bytes. The index ‘xyz’ has maximum length of 8024 bytes. For some combination of large values, the insert/update operation will fail.

CREATE TABLE [fun_table]

([the_name] [nvarchar](100) NULL,

[the_value] [sql_variant] NOT NULL,  -- will give the error

 CONSTRAINT [PK_thevalue] PRIMARY KEY CLUSTERED 

([the_value] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Suggested solution:

Change the sql_variant to a nvarchar() instead if you are going to use it in a index.  This is a warning that if you store more than 900 bytes in the column the_value, it will be truncated at the index/primary key.

More information:

From the internet – but this facts change with what version of Microsoft SQL server you are using.

As a general rule, you should avoid using SQL Server’s sql_variant data type. sql_variant is limited:

• Variants can’t be part of a primary or foreign key in SQL server before 2005.
  
• Variants can’t be part of a computed column.
• Variants won’t work with LIKE in a WHERE clause.
• OLE DB and ODBC providers automatically convert variants to nvarchar(4000)

To avoid problems, always explicitly convert sql_variant data types as you use them.

But some people claim that SQL_VARIANT is a fair bit faster than going trough VARCHAR conversions for your columns.

Warning! The maximum key length is 900 bytes. The index has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.

https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver16

https://www.visualbasicplanet.info/sql-server-2000/sqlvariant.html

https://stackoverflow.com/questions/13823130/are-there-any-benefits-to-using-sql-variant-over-varchar-in-sql-server

https://stackoverflow.com/questions/9039455/should-i-use-sql-variant-data-type

Product:

Microsoft Windows 2019 server

Issue:

When setting up a new server, where you want to create the same shared folders as on a existing server. How do you easy transfer the folder setup?

Solution:

You can check the existing file shares on your old server by go to control panel – administrative tools – Computer Management.

Under Shared folders – Shares, can you se the existing file shares on the server. By dubbleclick on a share, you can see permissions and other settings.

Most of this are stored in the Windows Registry. At [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Shares] branch.

To move the shares to other server, start REGEDIT program from start menu, on the old server.

Go to branch HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Shares.

Right click shares and select Export.

Save it in a file, called shares.reg.

Copy the file shares.reg over to the new Windows server where you want the shares to be created.

Login to the new server as local administrator.

Double click on the shares.reg file.

Click YES to the question if it should update the server with this values.

Click OK.

Then you must restart the server or the server service. Go to a command prompt as administrator.

Enter this to restart the server service;

Net Stop Server

Net Start Server

Then you should have the file share. On the new Windows server, go to the folder that you want to share, and right click and select properties.

Under Sharing tab and Advanced Sharing button, you should now see the same setup as the old other server.

Please use $ after share name when you create them, then the file share is hidden and will not show up when someone browse the network. You need to know the share name and have access to it to be able to connect to it from other computer.

More Information:

https://www.tgrmn.com/web/kb/item133.htm

Product:

Microsoft Windows 2019 server

Issue:

The new hard disk is empty, but file manager still show it contain some files. When you go to the drive it is empty in file manager.

File manager show that only a part of the disk is free space.

Solution:

Some person have moved files to the recycle bin. The recycle bin is empty for you, but the other user on the windows server have a not empty recycle bin – he should empty the recycle bin, then the hard drive will show 100% free space.

Files are not removed from windows until the recycle bin is empty, so if you try to clean a hard disk, you need to empty the recycle bin also when you are done deleting files.

Product:

Microsoft SQL server 2019

Issue:

How do i log if a table is deleted or truncated in SQL server databases?

Solution:

Create a audit log file with this SQL command:

USE master

CREATE SERVER AUDIT [Truncate_Audit]
TO FILE
(FILEPATH = N'c:\temp\logs'
,MAXSIZE = 1 GB
,MAX_ROLLOVER_FILES = 20
,RESERVE_DISK_SPACE = OFF
)
WITH
(QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

ALTER SERVER AUDIT [Truncate_Audit] WITH (STATE = ON)

Then to make all database that start with letter A to be monitor run below SQL command:

DECLARE @command varchar(500)
SELECT @command = 'IF ''?'' LIKE ''A%'' BEGIN USE ?
EXEC(''Create database audit specification [DatabaseAuditSpecification-Truncate]
FOR SERVER AUDIT [Truncate_Audit]
ADD (Delete on database::[?] by [dbo])
WITH (STATE=ON)'')END'

EXEC sp_MSforeachDB @command

Now, do some add and delete of a table in your database to create some action to log.

Then to see the log enter this SQL command:

SELECT event_time, succeeded, object_id, object_name,session_server_principal_name,server_principal_name,server_instance_name,database_name,statement,file_name,audit_file_offset
FROM fn_get_audit_File ('c:\temp\logs\Truncate_Audit_*.sqlaudit',null,null) order by event_time DESC

You have to adjust the SQL code for your needs.

More Information:

SQL Server auditing with Server and Database audit specifications

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-ver15  

https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/

https://sqlship.wordpress.com/2011/06/30/how-to-capture-delete-and-truncate-t-sql-using-database-audit-specification/

Product:

Microsoft SQL server 2016

Issue:
How do i add a AD group to all databases on the SQL server as read only access?

Solution:

Adjust below script – replace AD\DBAteam with your domain\group and run it in SQL query.

Use master
GO

DECLARE @dbname VARCHAR(50) 
DECLARE @statement NVARCHAR(max)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR 
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution','SSISDB') 
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @dbname 
WHILE @@FETCH_STATUS = 0 
BEGIN 

SELECT @statement = 'use '+@dbname +';'+ ' CREATE USER [AD\DBATeam] 
FOR LOGIN [AD\DBATeam] ; EXEC sp_addrolemember N''db_datareader'', 
[AB\DBATeam] '

exec sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname 
END 
CLOSE db_cursor 
DEALLOCATE db_cursor

The script will work until it reaches a database that is offline – there it will stop and not process any databases below that.

The users login must first be in the database;

CREATE LOGIN [AD\DBATeam] FROM WINDOWS

More information:

https://www.mssqltips.com/sqlservertip/3541/grant-user-access-to-all-sql-server-databases/

https://www.guru99.com/sql-server-create-user.html

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql?view=sql-server-ver15

Move or copy SQL Logins by assigning roles and permissions