← maurobernal.com.ar

devops Mauro Bernal

Etiqueta: mssql

  • Cambiar el puerto del MS SQL Server

    Primero que nada recordemos que el motor de Microsoft corre bajo los siguientes puertos:

    TCP:1433

    UDP:1433

    Seguido a este te dejamos este post donde te contamos como funciona la seguridad de MS SQL (https://maurobernal.com.ar/blog/blog/consejos-para-asegurar-tu-ms-sql-server/)

    Entendiendo esta ya podemos empezar a cambiar la configuración de nuestro motor, para cambiar su puerto predeterminado. Lo primero que debes hacer es buscar el «SQL Server Configuration Manager» (o administrador de configuración de SQL Server) y buscar la opción de «SQL Server Network Configuration» (Configuración de Red) \ Protocols for MOTOR ,

    y una vez seleccionado buscas la configuración de TCP/IP y le das doble clic. En la nueva ventana emergente ya puedes cambiar el puerto como te mostramos en la imagen.

    Con esto sería suficiente. Solo toca ahora reiniciar el servicio. Puedes hacerlo también desde la misma aplicación.:

    PD: No te olvides de abrir el puerto en tu firewall. Te dejo una ayuda para hacerlo desde CMD. Por ejemplo si elegiste el 11433:

    netsh advfirewall firewall add rule name="SQL Puerto alternativo" dir=in localport="11433" protocol=tcp action=allow

    Si quieres verificar, puedes usar tu SQL Management Studio ( https://maurobernal.com.ar/blog/blog/mssql/como-especificar-el-puerto-en-el-sql-server-management-studio)

    Deja tus comentarios si te son útiles estos consejos…

  • Funciones Analíticas con SQL Server

    En los siguientes artículos que iré creando resumiremos el uso de las siguientes funciones analíticas incorporadas a partir de SQL Server 2012

     

    Estas son:

    CUME_DIST (Transact-SQL) LEAD (Transact-SQL)
    FIRST_VALUE (Transact-SQL) PERCENTILE_CONT (Transact-SQL)
    LAG (Transact-SQL) PERCENTILE_DISC (Transact-SQL)
    LAST_VALUE (Transact-SQL) PERCENT_RANK (Transact-SQL)

  • Realizar backup con TSQL

    Realizar backup de tus bases con TSQL en MS SQL Server

    Hoy veremos una manera simple de realizar un respaldo de todas nuestras bases de datos mediante un pequeño script.

    Script para Backup

    --------------------------------------------------
--Variables
DECLARE @Base_Datos VARCHAR(50) -- Nombre de la Base de Datos  
DECLARE @Ruta_Archivos VARCHAR(256) -- Ruta para los archivos 
DECLARE @Nombre_A VARCHAR(256) -- Nombre del Backup  (1era Parte)
DECLARE @Nombre_B VARCHAR(20) --  Nombre del Backup (2da Parte)
 
-- 1-Ubicación de los backups
SET @Ruta_Archivos = 'C:\Temp\'  
 
-- Nombre del Archivo (2da Parte) _YYYYMMDD
SELECT @Nombre_B = (SELECT '_'+CONVERT(VARCHAR(20),GETDATE(),112) +'.BAK') 
 
DECLARE Cursor_Backup CURSOR READ_ONLY FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
 
OPEN Cursor_Backup   
FETCH NEXT FROM Cursor_Backup INTO @Base_Datos   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @Nombre_A = @Ruta_Archivos + @Base_Datos +  @Nombre_B   
   BACKUP DATABASE @Base_Datos TO DISK = @Nombre_A  
 
   FETCH NEXT FROM Cursor_Backup INTO @Base_Datos   
END   

 
CLOSE Cursor_Backup   
DEALLOCATE Cursor_Backup

    Resultado de correr el script

    realizar backup con tsqlQue sigue…

    Se puede optimizar el script para agregar nuevas opciones de backups, como así también programar el mismo.

  • Obtener el ultimo día del mes con SQL Server

    Como obtener el último día del mes con TSQL en SQL Server

    Anteriormente habíamos visto como podíamos obtener:

    Primer y último día de Mes Anterior, Actual y Siguiente.

    Si te interesa ( y trabajas con una versión anterior a MS SQL Server 2012), les dejo el tutorial anterior:

    https://maurobernal.com.ar/blog/blog/t-sql/primer-y-ultimo-dia-del-mes-con-sql-server-y-otros/

    Pero en esta oportunidad veremos otra variante para obtener el último día del mes: actual, mes anterior y mes siguiente.

    Usando la funtión EOMONTH()

    A partir de Microsoft SQL Server 2012 se agregó la función EOMONTH()

    https://docs.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql

    La misma permite obtener el último día del mes

    Veamos un ejemplo:

    --GetDate() -> 14 Nov 2017

--Último día del mes Actual
select EOMONTH(getdate()) as UltDiaMesActual

--Último día del mes Anterior
select EOMONTH(getdate(),-1) as UltDiaMesAnterior

--Último día del mes Próximo
select EOMONTH(getdate(),1) as UltDiaMesProximo

    Esto nos dará como resultado:

    usando_eomonth_ejemploCon un poco de ingenio podemos obtener un poco más

    A partir del código anterior, con un poco de ingenio, y con ayuda de la función DateAdd()

    https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql

    Si le agregamos un día podemos obtener el primer día del mes anterior, mes actual y mes próximo

    --GetDate() -> 14 Nov 2017

--Primer día del Mes Anterior
select dateadd(d,1,EOMONTH(getdate(),-2)) as PrimerDiaMesAnterior

--Primer día del Mes Actual
select dateadd(d,1,EOMONTH(getdate(),-1)) as PrimerDiaMesActual

--Primer Dia del Mes Siguiente
select dateadd(d,1,EOMONTH(getdate())) as PrimerDiaMesSiguiente

    El resultado sería el siguiente:

    eomoth para primer dia del mes

     

    No se olviden de comentar si les sirvió, !!! Y cualquier cosa que necesiten hacer es posible con un poco de paciencia…

  • Eliminar Repetidos con TSQL

    Eliminar Repetidos con TSQL y la función Row_Number()

    Hoy vamos usar row_number(), una subconsulta, comando IN para buscar y eliminar registros repetidos.

    En esta nueva etapa me he propuesto compartir cada uno de los scripts que siempre son de utilidad cuando se trabaja con bases de datos.

    Como siempre son con TSQL para MS SQL Server.

    Esta vez veremos como eliminar registros repetidos de una tabla, solo dejando 1 registro.

    El secreto está en el uso del comando ROW_NUMBER

    https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

     

    Cargaremos una tabla de ejemplo MYTABLA

    IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('myTable'))
BEGIN;
    DROP TABLE [myTable];
END;
GO

CREATE TABLE [myTable] (
    [myTableID] INTEGER NOT NULL IDENTITY(1, 1),
    [Documento] VARCHAR(13) NULL,
    [Nombre] VARCHAR(255) NULL,
    PRIMARY KEY ([myTableID])
);
GO


INSERT INTO myTable([Documento],[Nombre]) VALUES('1695071197799','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630121141299','Harlan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1637061077799','Armand');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1653081722499','Chadwick');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1695020909099','Fletcher');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614102485499','Garth');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1624071487999','Cedric');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1678010341799','Prescott');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1695071197799','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630121141299','Harlan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1637061077799','Armand');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1653081722499','Chadwick');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1691081716399','Harding');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1641101376799','Jakeem');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630020717199','Carlos');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627101503999','Edan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636051993199','Samson');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627041338699','Wylie');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1661032884599','Brennan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1625120186899','Brett');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1642100764499','Ira');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1624052909599','Kyle');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656062403199','Jordan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656020101399','Alden');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1657080142799','Tanek');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1678122145299','Fulton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1684010635799','Reese');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1613011065899','Grant');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630012756499','Jakeem');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1618071462499','Flynn');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615052518299','Benjamin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1691052832399','Wylie');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1669013054199','Tucker');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1612110409199','Laith');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614110752199','Tad');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1699041337299','Chaney');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1650112954899','Jeremy');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1647052552299','Adam');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1643041374799','Denton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1642081947099','Knox');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1643020793199','Vance');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1632020546699','Thaddeus');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1670032188999','Keegan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615102660699','Clark');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1693053059399','Hedley');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1661082842999','Beau');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636081862699','August');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1608070833799','Oscar');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1687121074299','Leo');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1655122056599','Griffith');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614010191199','Andrew');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1668121357799','Abdul');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1691081716399','Harding');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1641101376799','Jakeem');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630020717199','Carlos');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627101503999','Edan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636051993199','Samson');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627041338699','Wylie');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1661032884599','Brennan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1625120186899','Brett');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1642100764499','Ira');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1602091000299','Jordan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1602060613699','Guy');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1649101947099','Bradley');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1692080607499','Basil');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1645013071199','Bruce');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1677121204299','Chancellor');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1668110667099','Price');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1644072576599','Driscoll');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1616101911399','August');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1680021349199','Wallace');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1652052495999','Emery');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1692050724999','Talon');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614111114799','Avram');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1670010342799','Hiram');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636022785999','Brody');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609033047099','Stuart');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1669122235799','Moses');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1626080826599','Leo');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1670010510999','Micah');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1676043021599','Jonah');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1621032365499','Len');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1658020481699','Amir');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1611032912899','Len');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1638071718599','Ray');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1676120168199','Kenyon');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1688050847099','Sawyer');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1692051561099','Malcolm');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1664101012499','Nolan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682121754399','Lucius');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1633112425699','Alvin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1606040846399','Ralph');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1610061348699','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1649120964999','Malachi');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609121778799','Kermit');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1649111157999','Wade');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1625051934199','Simon');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1698060420799','Donovan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1607101359699','Isaiah');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1648100677099','Avram');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1668081567999','Grant');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1613042285999','Keefe');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1626042455399','Joshua');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1676081014999','Amir');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1643042339599','Conan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1695051340499','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1610012015699','Akeem');

     

    Esta pequeña tabla de ejemplo tiene la siguiente estructura:

    eliminar repetidos con tsql

    Buscando repetidos

    Lo primero que haremos es buscar los que tienen el campo DOCUMENTO repetido usando la función Row_Number(). Esta nos devolverá un nuevo campo que llamaremos ORDEN, e indicara las veces que se repite un campo

    select *,ROW_NUMBER() over ( PARTITION BY Documento ORDER BY myTableID) as Orden 
from mytable

    eliminar repetidos con tsql y row_number()

    PARTITION BY Documento le indicamos que la partición sea el campo documento

    ORDER BY myTableID le indicamos el criterio para enumerar. Es decir en orden ascendente por ID del campo. Si tuviésemos un campo de tipo fecha (Datetime) podríamos ordenar por mas nuevo, o viceversa

     

    Buscando los repetidos

    Lo primero que haremos es buscar los repetidos usando una subconsulta

    Select myTableID from
(select *,ROW_NUMBER() over ( PARTITION BY Documento ORDER BY myTableID) as Orden 
from mytable) SubConsulta1
where Orden>1

    Eliminiar repetidos TSQL 31 repetidosEsta consulta devuelve los ID de los repetidos: 31 registros para ser mas especificos

    Eliminado los repetidos

    Ahora los eliminamos usando el comando IN

    begin transaction eliminacion1
delete from mytable where 
mytableID in 
(

Select myTableID from
(select *,ROW_NUMBER() over ( PARTITION BY Documento ORDER BY myTableID) as Orden 
from mytable) SubConsulta1
where Orden>1
)

commit transaction eliminacion1

     

     

     

    Espero que les haya sido útil.

     

    El comando Row_Number() permite trabajar con los registros de forma masiva pudiendo identificar la N aparición.

    Mas adelante veremos como eliminar / actualizar:

    -El último registro de una serie de registros

    -El primero, etc…

     

     

     

     

  • Validar CBU Argentina

    Validar – CBU Número de Cuenta – Argentina

    Siguiendo con estas funciones de TSQL para MS SQL Server, en esta ocasión es para validar los 22 dígitos del CBU.

    La Clave Bancaria Uniforme (CBU) se compone de 22 dígitos

    https://es.wikipedia.org/wiki/Clave_Bancaria_Uniforme

    CBU Clave Bancaria Uniforme

     

    CREATE FUNCTION [dbo].[ValidarCBUdigito] 
( 
-- Add the parameters for the function here 
@block VARCHAR(100) 
) 
RETURNS TINYINT 
AS 
BEGIN 
DECLARE @subblock VARCHAR(100) 
DECLARE @posicion INTEGER 
DECLARE @digito CHAR(1) 
DECLARE @Pond CHAR(4) 
DECLARE @largo INTEGER 
DECLARE @suma INTEGER 

SELECT @suma = 0 
SELECT @largo = LEN ( @block ) 
SELECT @digito = SUBSTRING ( @block , @largo , 1 ) 
SELECT @subblock = SUBSTRING ( @block , 1 , @largo - 1 ) 
SELECT @Pond = '9713' 
SELECT @posicion = 1 

WHILE @posicion <= @largo - 1 
BEGIN 
SELECT @suma = @suma + CONVERT( INTEGER , SUBSTRING ( @subblock , @largo - @posicion , 1 )) * CONVERT( INTEGER , SUBSTRING ( @pond ,CASE (@posicion % 4) WHEN 1 THEN 4 WHEN 2 THEN 3 WHEN 3 THEN 2 WHEN 0 THEN 1 END , 1 ) ) 
SELECT @posicion = @posicion + 1 
END 


if @digito = RIGHT ( convert( varchar, 10 - ( @suma % 10 )) , 1 ) 
RETURN 1 

RETURN 0 
END

     

  • Cuantos VLF es demasiado?

    Entendiendo que es VLF

    De acuerdo al tamaño que tenga nuestro archivo de LOG (.ldf) es importante controlar los VLF (Virtual Log File).

    Los VLF, son los archivos virtuales en que se divide nuestro log, y un exceso de los mismos puede provocar una reducción del rendimiento.

    Verificando los VLF

    • Verificar cuantos VLF tenemos en nuestra base
    • De manera invidual

     

    DBCC LOGINFO
    • De forma masiva
    --variables to hold each 'iteration'  
declare @query varchar(100)  
declare @dbname sysname  
declare @vlfs int  
  
--table variable used to 'loop' over databases  
declare @databases table (dbname sysname)  
insert into @databases  
--only choose online databases  
select name from sys.databases where state = 0  
  
--table variable to hold results  
declare @vlfcounts table  
    (dbname sysname,  
    vlfcount int)  
  
 
 
--table variable to capture DBCC loginfo output  
--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version 
 
declare @MajorVersion tinyint  
set @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))-1) 

if @MajorVersion < 11 -- pre-SQL2012 
begin 
    declare @dbccloginfo table  
    (  
        fileid smallint,  
        file_size bigint,  
        start_offset bigint,  
        fseqno int,  
        [status] tinyint,  
        parity tinyint,  
        create_lsn numeric(25,0)  
    )  
  
    while exists(select top 1 dbname from @databases)  
    begin  
  
        set @dbname = (select top 1 dbname from @databases)  
        set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '  
  
        insert into @dbccloginfo  
        exec (@query)  
  
        set @vlfs = @@rowcount  
  
        insert @vlfcounts  
        values(@dbname, @vlfs)  
  
        delete from @databases where dbname = @dbname  
  
    end --while 
end 
else 
begin 
    declare @dbccloginfo2012 table  
    (  
        RecoveryUnitId int, 
        fileid smallint,  
        file_size bigint,  
        start_offset bigint,  
        fseqno int,  
        [status] tinyint,  
        parity tinyint,  
        create_lsn numeric(25,0)  
    )  
  
    while exists(select top 1 dbname from @databases)  
    begin  
  
        set @dbname = (select top 1 dbname from @databases)  
        set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '  
  
        insert into @dbccloginfo2012  
        exec (@query)  
  
        set @vlfs = @@rowcount  
  
        insert @vlfcounts  
        values(@dbname, @vlfs)  
  
        delete from @databases where dbname = @dbname  
  
    end --while 
end 
  
--output the full list  
select dbname as 'Base', vlfcount  'Cant'
from @vlfcounts  
order by vlfcount desc

    En base a la cantidad deberemos seguir el siguiente criterio

    • Menos de 1 MB, ignorar este caso.
    • Hasta  64 MB: 4 VLFs, cada uno de 1/4 del tamaño
    • De 64 MB a 1 GB: 8 VLFs, cada uno de 1/8 del tamaño
    • Mas de 1 GB: 16 VLFs, cada uno  de 1/16 del tamaño

     

    Si obtenemos una cantidad mayor tenemos que:

    • Verificar el tamaño del LOG.
    • Reducir el LOG(*).
    • Crear de nuevo el LOG con un tamaño inicial igual al existente antes de reiniciarlo.

    (*) será conveniente hacer un backup del mismo

     

    -- Detecting and reducing VLFs in SQL Server 2008
-- Glenn Berry 
-- June 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- Switch to your database
USE ngmetadata;
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Check individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]
FROM sys.database_files;

-- Step 1: Compressed backup of the transaction log (backup compression requires Enterprise Edition in SQL Server 2008)
BACKUP LOG [ngmetadata] TO  DISK = N'N:\SQLBackups\ngmetadataLogBackup.bak' WITH NOFORMAT, INIT,  
NAME = N'ngmetadata- Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1;
GO

-- Step 2: Shrink the log file
DBCC SHRINKFILE (N'ngmetadata_log' , 0, TRUNCATEONLY);
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Step 3: Grow the log file back to the desired size, 
-- which depends on the amount of write activity 
-- and how often you do log backups
USE [master];
GO
ALTER DATABASE ngmetadata MODIFY FILE (NAME = N'ngmetadata_log', SIZE = 8GB);
GO

-- Switch back to your database
USE ngmetadata;
GO

-- Check VLF Count for current database after growing log file
DBCC LogInfo;

     

  • Nivel de Fragmentacion de una base de dato

    Nivel de Fragmentación

    Uno de los principales tópicos que se debe verificar es el crecimiento de los archivos que componen una base de datos, como así también el nivel de fragmentación de los índices que la componen.

    Asegurar el mejor rendimiento de una base de datos en MSSQL Server puede ser una tarea labiosa.

    Verifica que bases de datos crecieron de manera automática

    Cuando alguno de los archivos que componen la base (mdf y ldf) se queden sin espacios, y tiene permitido autocrecer (autogrowth) detienen la consulta en curso,  y autoincrementa su tamaño, y continua con la operación inicial. Esto si ocurre muy seguido puede provocar que se reduzca el rendimiento de la base.
    Una forma de detectarlo es consultando el log del SQL Server:

    SELECT
DatabaseName as Base,
[FileName] as Archivo,
CASE EventClass
			WHEN 92 THEN 'Datos'
			WHEN 93 THEN 'Log'
END  as Tipo,
CONVERT(VARCHAR(20), EndTime - StartTime, 114) as TiempoDemora,
StartTime as FechaInicio,
EndTime as FechaFin
 

FROM (
SELECT pt = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1
) p
CROSS APPLY sys.fn_trace_gettable(pt, DEFAULT)
WHERE EventClass IN (92, 93)
ORDER BY StartTime DESC

    AutogrowthDe acuerdo al resultado será la base en la que deberemos trabajar:

    • Si aparece la TEMPDB la opción mas recomendable es: Crear un archivos de Datos (.MDF) y un archivo de LOG( ldf) por cada procesador que tiene el servidor. Y el tamaño inicial total de la base debe ser el actual + 10% . Y luego irlo ajustando a medida que se vaya necesitando.

    Verifica el nivel de fragmentación de los indices

    El segundo punto importante es verificar que nivel de fragmentación tienen cada uno de los indices que componen la base de datos. Este punto puede provocar que nuestra base de datos tenga un tamaño mayor que el real. Y provoque que las consultas se demoren un poco mas de lo que deberian:

    Para corroborarlo podemos usar el siguiente script:

    IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
GO
CREATE TABLE #temp (
db SYSNAME DEFAULT DB_NAME(),
flname SYSNAME,
size_after_growth DECIMAL(18,2),
size DECIMAL(18,2),
space_used DECIMAL(18,2),
growth INT,
is_percent_growth BIT,
PRIMARY KEY CLUSTERED (db, flname)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
SELECT '
USE [' + name + ']
INSERT INTO #temp (flname, size_after_growth, space_used, size, growth, is_percent_growth)
SELECT
name
, CASE WHEN is_percent_growth = 1 THEN size * (1 + (growth / 100.)) ELSE size + growth END * 8. / 1024
, space_used = FILEPROPERTY(name, ''SpaceUsed'') * 8. / 1024
, size = size * 8. / 1024
, CASE WHEN is_percent_growth = 1 THEN growth ELSE growth * 8. / 1024 END
, is_percent_growth
FROM sys.database_files'
FROM sys.databases
WHERE [state] = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
EXEC sys.sp_executesql @SQL
SELECT
db
, flname
, size_after_growth
, size
, space_used
, CAST(growth AS VARCHAR(10)) + CASE WHEN is_percent_growth = 1 THEN ' %' ELSE ' MB' END
FROM #temp
WHERE (is_percent_growth = 0 AND growth < 50)
OR (is_percent_growth = 1 AND growth < 5)
OR (size - space_used < 20)

     

    Para correguirlo podemos ejecutar el siguiente script por cada una de nuestras bases de datos:

    DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT '
ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
CASE WHEN s.avg_fragmentation_in_percent > 30
THEN 'REBUILD'
ELSE 'REORGANIZE'
END + ';
'
FROM (
SELECT
s.[object_id]
, s.index_id
, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
WHERE s.page_count > 128 -- > 1 MB
AND s.index_id > 0 -- <> HEAP
AND s.avg_fragmentation_in_percent > 5
GROUP BY s.[object_id], s.index_id
) s
JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.objects o ON o.[object_id] = s.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
PRINT @SQL
EXEC sys.sp_executesql @SQL

     

     

  • Contar días hábiles entre una fecha

    Como contar días entre fechas

    La siguiente función para SQL Server permite contar la cantidad de días que existen entre un rango de fechas sin contar Sábados y Domingos.

    CREATE FUNCTION [dbo].[DiasHabiles](@FechaInicio DATETIME,@FechaFin DATETIME) 
 RETURNS integer 
 AS 
 BEGIN

            DECLARE @DiasNormales INT	--Con esta variable calculamos cuantos dias "normales" hay en el rango de fechas 
            DECLARE @DiasHabiles INT  --Con esta variable acumulamos los dias totales 
            DECLARE @Contador INT		--Esta variable nos sirve de contador para saber cuando lleguemos al ultimo dia del rango 
            DECLARE @Aux_Fecha DATETIME	--Esta variable es la que comparamos para saber si el dia que esta calculando es sábado o domingo*

            /*Esta par de variables sirven para comparar las dos fechas, si son iguales, la funcion nos regresa un 0*/ 
            DECLARE @Aux_FechaInicio VARCHAR(10) 
            DECLARE @Aux_FechaFin VARCHAR(10) 

            --Inicializamos algunas variables 

			SET @DiasNormales = 0 
            SET @DiasHabiles = 0 
            SET @Contador=0 

            --Calculamos cuantos dias normales hay en el rango de fechas  
            SELECT @DiasNormales = DATEDIFF(DAY,@FechaInicio,@FechaFin) + 1 

            /*Ordenamos el formato de las fechas para que no importando como se proporcionen se comparen igual*/ 

            SELECT @Aux_FechaInicio = (SELECT CAST((CAST(datepart(dd,@FechaInicio)AS 
                                   VARCHAR(2))+'/'+ CAST(datepart(mm,@FechaInicio)AS 
                                   VARCHAR(2))+'/'+CAST(datepart(yy,@FechaInicio)AS VARCHAR(4))) as 
                                   varchar(10))) 
            SELECT @Aux_FechaFin = (SELECT CAST((CAST(datepart(dd,@FechaFin)AS 
                                   VARCHAR(2))+'/'+ CAST(datepart(mm,@FechaFin)AS VARCHAR(2))+'/'+ 
                                   CAST(datepart(yy,@FechaFin)AS VARCHAR(4)))as varchar(10))) 


            --Se comparan las dos fechas 

            IF @Aux_FechaInicio <>@Aux_FechaFin 
			BEGIN 
				/*Si la diferencia de fechas es igual a dos, es porque solo ha transcurrido un dia, asi que solo se valida que no vaya a marcar dias de mas*/ 
				IF @DiasNormales = 2 
				BEGIN 
					SELECT @DiasHabiles = 1 
				END 
				ELSE 
				BEGIN 
					WHILE @Contador < @DiasNormales 
					BEGIN 
						/*Se Iguala la fecha a que vamos a calcular para saber si es sabado o domingo en la variable @Aux_Fecha sumandole los dias que marque el contador, el cual no debe ser mayor que el numero total de dias que hay en el rango de fechas*/ 
						SELECT @Aux_Fecha = @FechaInicio + @Contador 
						/*Utilizando la funcion datepart con el parametro dw que calcula que dia de la semana corresponde una fecha determinada, determinados que no sea sabado (7) o domingo (1)*/ 
							IF ((datepart(dw,@Aux_Fecha) <> 1) and (datepart(dw,@Aux_Fecha) <> 7) ) 
							BEGIN
								/*Si no es sabado o domingo, entonces se suma uno al total de dias que queremos desplegar*/ 
								SELECT @DiasHabiles = @DiasHabiles + 1 
							END 
						--Se suma un dia mas al contador 
						SELECT @Contador = @Contador + 1 
					END 
				END 
			END 
            ELSE 
            BEGIN 
            --Si fuese cierto que las fechas eran iguales se despliegue cero 
            SELECT @DiasHabiles = 0 
            END 

            --Al finalizar el ciclo, la funcion regresa el numero total de dias 
            RETURN(@DiasHabiles) 
END

GO

     

    Su uso es muy simple:

    Select dbo.DiasHabiles('2015/07/13','2015/07/31') as DiasHabiles,DateDiff(d,'2015/07/13','2015/07/31') as DiasCorridos

     

     

    Resultado_contar_fechas

    En este ejemplo se puede ver la variación que existe entre la función DateDiff y nuestra función.

  • Averiguar el Puerto del SQL Server

    ¿Cómo saber en que puerto está corriendo nuestro motor SQL Server? Primero que nada es conveniente entender porque es que no esta en su puerto default. Si eres novato en el tema, seguramente tienes la instalación estándar del motor de Microsoft, y nunca te has preguntado porque debería cambiarlo de puerto. Si es tu caso te dejo este artículo donde te contamos como es la seguridad del MS SQL Server y que cosas debes tener en cuenta. (https://maurobernal.com.ar/blog/blog/consejos-para-asegurar-tu-ms-sql-server/)

    Para aquellos que entendemos el porqué debemos cambiarlo, y no sabemos como hacerlo, te dejo este otro artículo. (https://maurobernal.com.ar/blog/blog/mssql/cambiar-el-puerto-del-ms-sql-server)

    Y para aquellos que sabemos lo anterior, pero por diferentes motivos nos toca implementar una aplicación, y no sabemos en que puerto está corriendo, a continuación te damos una idea de como averiguarlo.

    Averiguar el Puerto en el que está funcionando el SQL Server:

    Por defecto el motor SQL Server trabaja en los siguientes puertos de manera predeterminada:

    • TCP:1433
    • UDP:1433

    Pero puede ocurrir que esto al tener varias instancias, o por otros motivos que no vienen al caso, no sean los puertos en los que está funcionando nuestro Motor. Para averiguar el puerto podemos utilizar los siguientes 3 métodos:

    1. Registros de errores de lectura de SQL Server
    2. Usando el Administrador de configuración de SQL Server (SQL Server Configuration Manager )
    3. Usando el visor de sucesos (o Eventos) de Windows (Windows Application Event Viewer)

    1-Registros de errores de lectura de SQL Server:

    USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on' 
GO

    port_number

    Los parámetros que puede utilizar con XP_READERRRORLOG se mencionan a continuación para su referencia:

    1.  1. Valor de los archivos de registro de errores que quieran leer: 0 = actual, 1 = Archivo # 1, 2 = Archivo # 2, etc …
    2. Tipo de archivo: 1 o NULL = log de ​​errores, 2= registro de Agente SQL 
    3. Cadena de búsqueda 1: Primer cadena de texto que se quiere buscar
    4. Cadena de búsqueda 2: Segunda cadena de texto a buscar
    5. Fecha de Inicio para la búsqueda
    6. Fecha Fin para la búsqueda
    7. Orden de los Resultados : N’asc’ = Ascendente, N’desc’ = Descendiente

    2-Usando el Administrador de Configuración del SQL Server:

    Se debe ir a INICIO \ Todos los Programas \ Microsoft SQL Server 200X \ Configuration Tools \ SQL Server Configuration Manager

    configuration_manager_port

     3-Usando el Visor de Eventos de Windows:

    Se deben filtrar los eventos cuyo ID es 26022

    visor_port_sql

    visor_port_sql_number

    Video Explicando todo este proceso

Tags

tsql (27)mssql (26)sql (20)devops (20)dotnet (18)docker (15)performance (14)contenedores (11)dotnet10 (10)linux (9)csharp (8)microservicios (7)angular (7)angular21 (7)sql server (6)issabel (6)docker-compose (6)typescript (6)mysql (5).NET (5)