Listar Trace Flags ativas

Precisa conferir quais trace flags estão ativas no seu SQL Server?
Execute a consulta abaixo na sua instância de SQL Server:

DBCC TRACESTATUS()

O retorno será parecido com o exemplo abaixo:

tflags01

Maiores detalhes do que significa cada coluna podem ser encontrados na documentação oficial no link a seguir: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-tracestatus-transact-sql?view=sql-server-2017

 
Alternativamente você também pode conferir as trace flags configuradas utilizando o SQL Server Configuration Manager, navegando nas propriedades da instância e conferindo a aba “Startup Parameters”, conforme exemplo abaixo:

tflags02

 

Não sabe o que é uma trace flag?
Confira na documentação oficial da Microsoft no link abaixo:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

Movendo a database tempdb

Precisando mover a tempdb de disco ou diretório?

Basta apenas seguir estes 4 passos.

  • Confira os nomes lógicos dos arquivos e seus respectivos caminhos;
SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
  • Para cada arquivo da tempdb altere o seu caminho. Se você tem mais arquivos configurados para a sua tempdb altere o local para cada um destes arquivos também utilizando o mesmo modelo dos comandos abaixo.
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'NovoCaminho\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'NovoCaminho\templog.ldf')
  • Reinicie sua instância de SQL Server;
  • Confira se os arquivos da tempdb já estão utilizando o novo caminho configurado.
SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')

Referências:

 

Conferir a utilização de recursos específicos da edição Enterprise do SQL Server

Para conferirmos a utilização de recursos específicos da edição Enterprise do SQL server é muito simples.

Podemos utilizar a DMV sys.dm_db_persisted_sku_features para listar os recursos que são específicos de acordo com a edição do SQL Server. Uma database que utiliza um destes recursos não pode ser movida para uma edição que não os suporte.

select *
from sys.dm_db_persisted_sku_features

Nas imagens abaixo temos 3 exemplos de recursos de edições específicas do SQL Server: Compression, ColumnStoreIndex e Partitioning.

É isso galera. Simples, rápido e fácil. Espero que tenham gostado.

Resolvendo o alerta Network Binding Order da instalação do SQL Server no Windows Server 2016

Precisando ajustar a ordem de prioridade das interfaces de rede no Windows Server 2016 para remover o alerta da instalação do SQL Server e não encontrou a antiga interface gráfica para realizar este ajuste?

Está no lugar certo.

Screen Shot 2017-08-24 at 02.14.03

Com a ajuda dos comandos abaixo capture o GUID da sua interface de rede que você quer colocar como preferencial.


ipconfig /all

Screen Shot 2017-08-24 at 02.21.30


wmic nicconfig get description, SettingID

Screen Shot 2017-08-24 at 02.22.01

Agora para editar a ordem das interfaces você deve navegar até a chave abaixo do registro do Windows com a ferramenta regedit:


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Linkage\

Agora edite o valor Bind do tipo REG_MULTI_SZ desta chave deixando a interface prioritária em cima, conforme exemplo abaixo:

Screen Shot 2017-08-24 at 02.28.14

Agora execute novamente a instalação do SQL Server e esta pendência terá sido resolvida.

Screen Shot 2017-08-24 at 02.30.12

É isso galera. Espero que tenham gostado!

O que é uma Table variable?

O que é uma table variable (variável do tipo table) no SQL Server?

É uma variável como qualquer outra com um tipo de dados especial (table) que pode ser usado para armazenar um result set qualquer.

Como criar/declarar uma table variable?


DECLARE @produto TABLE
(
id int,
valor money
)

Como consultar uma table variable?

SELECT id, valor FROM @produto;

Quando usar?

Utilize variáveis do tipo TABLE apenas para armazenar poucos registros (Até 100 registros de preferência).

Curiosidades

  • Não possuem estatísticas;
  • Não podem ser alteradas depois de criadas;
  • Não são afetadas por ROLLBACK, pois elas não suportam transações;
  • Não existe garantia que estarão residentes apenas em memória. Sob pressão de memória as páginas pertencentes a uma variável do tipo TABLE podem ser transferidas para a database tempdb;
  • Em consultas que façam junções em uma table variable considere utilizar o hintRECOMPILE para fazer com que o otimizador use a cardinalidade correta para ela.

Referências:

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql

Como transferir logins e senhas entre instâncias do SQL Server

Simples.

Basta criar as stored procedures sp_hexadecimal e sp_help_revlogin na database master do servidor de origem com a ajuda do código abaixo:

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END</pre>
SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT '' WHILE (@@fetch_status -1) BEGIN IF (@@fetch_status -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' END ELSE BEGIN -- SQL Server authentication -- obtain password and sid SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT -- obtain password policy state SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' IF ( @is_policy_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked END IF ( @is_expiration_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked END END IF (@denylogin = 1) BEGIN -- login is denied access SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) END ELSE IF (@hasaccess = 0) BEGIN -- login exists but does not have access SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) END IF (@is_disabled = 1) BEGIN -- login is disabled SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' END PRINT @tmpstr END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO
<pre>

Importante: Sempre consulte o código mais atualizado destas procedures e leia todas as observações e detalhes direto no KB original da Microsoft: https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

Agora no servidor de origem conectado na database master execute o comando abaixo:


EXEC sp_help_revlogin

Pronto. O resultado deste comando será o script pronto de todos os logins e senhas da sua instância de SQL Server mantendo os SIDs e senhas originais.

Agora no servidor de destino apenas execute o script gerado com a ajuda da procedure sp_help_revlogin.

Por hoje é isso pessoal. Espero que tenham gostado.

Como estimar o tempo de backup e restore no SQL Server

Já passou mais de 1 hora, seu BACKUP ou RESTORE ainda não finalizou e você está querendo uma estimativa do tempo de conclusão?

Fácil.

Basta executar o comando abaixo na sua instância de SQL Server utilizando a DMV (Dynamic Management View) dm_exec_requests para retornar as colunas percent_complete e estimated_completion_time e a DMF (Dynamic Management Function) dm_exec_sql_text para retornar o comando/TSQL de BACKUP/RESTORE executado.

SELECT
session_id as SPID,
command, s.text AS Query,
start_time,
percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
GO

Retorno:

Screen Shot 2017-08-24 at 03.08.44

Pronto! Até a próxima.