Product:

Microsoft SQL server

Problem:

What version of SQL server is it?

The different tools to SQL server are often installed in folder C:\Program Files\Microsoft SQL Server\150, where the number represent the version of SQL server.

The Database default folder is C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA, where the number 15 represent the SQL version – in this example that is SQL server 2019.

Solution:

This page show the version numbers:

https://sqlserverbuilds.blogspot.com/2014/01/sql-server-internal-database-versions.html

SQL Server Version Internal Database Version Database Compatibility Level Supported Database Compatibility Levels
SQL Server 2022 950 160 ?
SQL Server 2019 CTP 3.2 / RC 1 / RC 1.1 / RTM 904 150 150,140,130,120,110,100
SQL Server 2019 CTP 3.0 / 3.1 902 150 150,140,130,120,110,100
SQL Server 2019 CTP 2.3 / 2.4 / 2.5 897 150 150,140,130,120,110,100
SQL Server 2019 CTP 2.1 / 2.2 896 150 150,140,130,120,110,100
SQL Server 2019 CTP 2.0 895 150 150,140,130,120,110,100
SQL Server 2017 868 / 869 140 140,130,120,110,100
SQL Server 2016 852 130 130,120,110,100
SQL Server 2014 782 120 120,110,100
SQL Server 2012 706 110 110,100,90
SQL Server 2012 CTP1
(a.k.a. SQL Server 2011 Denali)
684 110 110,100,90
SQL Server 2008 R2 660 / 661 100 100,90,80
SQL Server 2008 655 100 100,90,80
SQL Server 2005 SP2+
with VarDecimal enabled
612 90 90,80,70
SQL Server 2005 611 90 90,80,70
SQL Server 2000 539 80 80,70
SQL Server 7.0 515 70 70
SQL Server 6.5 408 65 65
SQL Server 6.0 406 60 60

Legend: ? = still investigating, RTM = Release to manufacturing, SPn = Service Pack n, CTP = Community Technology Preview (beta release).

 

To see the compatibility level on the database enter:

EXEC sp_helpdb;

Product:

Microsoft SQL server 2016

Visual Studio developing

Issue:

When deploy/publish a database change to a SQL server 2016 we get a error;

The remote script failed with exit code 1

The action Publish DacPac on server failed

Could not find the file: Microsoft.SqlServer.Dac.dll

Possible solution:

Check on the target SQL server if the needed files are installed in correct folder.

Depending on version of SQL server, the files are in folders like 120,130,140.

Folder 150 is for SQL server 2019.

For SQL server version 2016 the files should be in folder 130

C:\Program Files\Microsoft SQL Server\130\dac\bin

If the folder is missing, check what version of SQL server that is installed.

Can be that SQL server is installed, but the supporting tools like Microsoft SQL Server Data Tools and Microsoft SQL Server Data-Tier Applications Framework (x64) is installed, but at an different version.

Go to control panel – program and features – and check what is installed.

Download and install the correct version you need.

 

More information:

https://sqlserverbuilds.blogspot.com/2014/01/sql-server-internal-database-versions.html

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/be484b63-a6cc-4dac-a2c2-78a56ff5b502/where-is-the-microsoftsqlserverdacdll-that-includes-support-for-sql-server-2014?forum=ssdt

 

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

Product:

Microsoft SQL server 2016

Microsoft Windows 2012 server

Issue:

How add read access to all tables in all databases on one server to a user?

Solution:

Add a AD user as a login to the SQL server in SSMS. https://www.guru99.com/sql-server-create-user.html

In our example we use User3, but you change it to your login-name.

EXEC sp_MSforeachdb ' Use ?; Create user User3 for login User3; Grant select on database :: ? to User3 '

In the string after the sp_MSforeachdb  command, the character ? is replaced by each database.

Use ?, this will move focus to this database, and execute the command after to this database.

Create user User3 for login User3, adds the user name to the database and connect it with the login user.

If the user3 already is in the database, then above will not work.  Then run the command again without the Create user… part.

Grant select on database :: ? to User3, will give this user READ access to all object in the database. Even new tables and views.

The grant select on a database, will give that user full read access to the database tables and views.

More information:

https://www.mssqltips.com/sqlservertip/6702/sql-server-windows-authentication-with-users-and-groups/

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

https://www.techonthenet.com/sql_server/users/create_user.php

https://www.tutorialsteacher.com/sqlserver/grant-permissions-to-user

https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

https://www.mssqltips.com/sqlservertip/1476/how-to-read-log-file-in-sql-server-using-tsql/

https://dba.stackexchange.com/questions/117759/granting-select-access-to-all-tables-within-a-specific-database-in-ms-sql

To grant SELECT permissions on the whole database use this:

USE <MY_DATABASE>
GRANT SELECT ON DATABASE :: <MY_DATABASE> TO <MY_USER>

where

  • <MY_USER> is user
  • <MY_DATABASE> is database name

Granting permissions on schema doesn’t help, simple reason: if new schemas occur the user will not have permissions.

Granting permissions on all objects doesn’t for the same reason, the user will not have permissions on new objects created after GRANT event.

Product:
Microsoft SQL Server 2016

Microsoft Windows 2012

Issue:
How read the SQL agent jobs logs without being a sysadmin?

Solution:

Add the user to a AD group and add the AD group as a login in the SQL server. In our example we will instead use SQL login User3.

In SSMS go to Security – Logins – New Login.  https://www.guru99.com/sql-server-create-user.html  Create your login in SQL.

Go to your MSDB database under – Databases – System Databases – msdb – Security – Users – New User.
Add your User3 there to the msdb database.

Expand Membership and select SQLAgentReaderRole, then click OK.

Then that user can in SSMS see SQL Server Agent, and View History and properties of a SQL Agent Job.

With SQL script:

USE [msdb]
exec sp_addrolemember 'SQLAgentReaderRole', 'DomainName\GroupName'

 

https://zarez.net/?p=36

More Information:

https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?redirectedfrom=MSDN&view=sql-server-ver15

https://www.techonthenet.com/sql_server/users/create_login.php

https://www.tutorialsteacher.com/sqlserver/grant-permissions-to-user

https://www.vb-net.com/Sql/Index.htm

https://www.tutorialsteacher.com/sqlserver/indexes