Mostrando entradas con la etiqueta SqlServer. Mostrar todas las entradas
Mostrando entradas con la etiqueta SqlServer. Mostrar todas las entradas

Last Backup Report

SELECT T1.name AS [Database]
   , ISNULL(T2.physical_device_name, 'DANGER!!!') AS [Backup]
   , T2.backup_size AS [Size]
   
, T2.backup_start_date AS [StartDate]
   
, T2.backup_finish_date AS [FinishDate]
FROM master.sys.sysdatabases T1
LEFT JOIN
(
   
SELECT T2.database_name
       
, T1.physical_device_name
       
, T2.backup_size
       
, T2.backup_start_date
       
, T2.backup_finish_date
   FROM msdb.dbo.backupmediafamily T1
   
INNER JOIN msdb.dbo.backupset T2 ON T1.media_set_id = T2.media_set_id
   
INNER JOIN
   
(
       
SELECT database_name, MAX(backup_finish_date) AS backup_finish_date
       
FROM msdb.dbo.backupset
       
WHERE TYPE = 'D'
       
GROUP BY database_name
   
) T3 ON T3.database_name = T2.database_name
        AND T3.backup_finish_date = T2.backup_finish_date
) T2 ON  T2.database_name = T1.name
ORDER BY T2.backup_finish_date DESC, T1.name 

SQLServer Connection Monitor

SELECT T1.session_id AS [SessionId]
  
, T1.host_process_id AS [HostPid]
  
, DB_NAME(T3.dbid) AS [Database]
  
, T1.HOST_NAME AS [Host]
  
, T1.program_name AS [ApplicationName]

   , T1.login_name AS [UserName]
  
, T2.client_net_address AS [IP]
  
, T1.cpu_time AS [Cpu]
  
, T1.memory_usage AS [Ram]
  
, T4.Connections AS [Connections]
  
, T4.net_transport AS [NetTransport]
  
, T2.net_packet_size AS [NetPacketSize]
  
, '[' + DB_NAME(T3.dbid) + '].['
    
+ object_schema_name(T3.objectid, T3.dbid) + '].['
    
+ OBJECT_NAME(T3.objectid, T3.dbid) + ']' AS [ObjectName]
  
, T3.TEXT AS [Query]

FROM sys.dm_exec_sessions T1
INNER JOIN sys.dm_exec_connections T2 ON T2.session_id = T1.session_id
CROSS APPLY sys.dm_exec_sql_text(T2.most_recent_sql_handle) T3
INNER JOIN
(
  
SELECT T1.dbid, T1.spid, T2.net_transport, COUNT(T1.dbid) AS [Connections]
  
FROM sys.sysprocesses T1
  
INNER JOIN sys.dm_exec_connections T2 ON T1.spid = T2.session_id
  
GROUP BY dbid, T1.spid, T2.net_transport

) T4 ON T4.spid = T1.session_id
ORDER BY Connections DESC, Cpu DESC, Ram DESC

Update SQLServer Identity Column


DECLARE @NewId INT = 23

DECLARE @TableName VARCHAR(MAX) = 'Database.Schema.TableName'

DBCC CHECKIDENT (@TableName, RESEED, @NewId)

 

Fix SQLServer Database marked as Suspect or Corrupted

ERROR: Database 'SenenDB' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. You can bring it online using the following steps:

--Reset the suspect flag
EXEC sp_resetstatus 'SenenDB'

--Set the database to emergency mode so that it becomes read only and not accessible to others
ALTER DATABASE SenenDB SET EMERGENCY

--Check the integrity among all the objects
DBCC CheckDB ('SenenDB')

--Set the database to single user mode
ALTER DATABASE SenenDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Repair the errors
DBCC CheckDB ('SenenDB', REPAIR_ALLOW_DATA_LOSS)

--Set the database to multi user mode, so that it can now be accessed by others
ALTER DATABASE SenenDB SET MULTI_USER

REPLACE INTO

-- Anteriormente en Sql Server

IF EXISTS(SELECT 'HOLA' FROM MiTabla WHERE Llave1 = @Llave1 AND LlaveN = @LlaveN)
 UPDATE MiTabla SET Valor1 = @Valor1, ValorN = @ValorN
 WHERE Llave1 = @Llave1 AND LlaveN = @LlaveN
ELSE
 INSERT INTO MiTabla(Llave1, LlaveN, Valor1, ValorN)
 VALUES (@Llave1, @LlaveN, @Valor1, @ValorN)

-- Ahora con Sql Server 2008

MERGE MiTabla AS T1
 USING (SELECT @Llave1, @LlaveN) AS T2 (Llave1, LlaveN)
 ON (T1.Llave1 = T2.Llave1 AND T1.LlaveN = T2.LlaveN)
WHEN MATCHED THEN
 UPDATE SET Valor1 = @Valor1, ValorN = @ValorN
WHEN NOT MATCHED THEN
 INSERT (Llave1, LlaveN, Valor1, ValorN)
 VALUES (@Llave1, @LlaveN, @Valor1, @ValorN);

-- Algo que MySql tiene desde hace varios años

REPLACE INTO MiTabla(Llave1, LlaveN, Valor1, ValorN)
VALUES (@Llave1, @LlaveN, @Valor1, @ValorN)

Exportar privilegios

use MyDataBase

GO

SELECT T1.state_desc
    , T1.permission_name
    , 'ON'
    , '[' + OBJECT_SCHEMA_NAME(T1.major_id) + '].[' + OBJECT_NAME(T1.major_id) + ']'
    , 'TO'
    , USER_NAME(T1.grantee_principal_id)
FROM sys.database_permissions T1
JOIN sys.database_principals T2 ON T2.principal_id = T1.grantee_principal_id
WHERE T1.class_desc = 'OBJECT_OR_COLUMN'
ORDER BY 2, 6, 4

Change Data Capture (CDC)

use MyDataBase

GO

EXEC sys.sp_cdc_enable_db

GO

EXEC sys.sp_cdc_enable_table
     @source_schema = 'SchemaDeLaTabla'
   , @source_name = 'NombreDeLaTablaAuditada'
   , @role_name = 'dbo'
   , @supports_net_changes = 1

GO

DECLARE @inicio binary(10), @fin binary(10)
SELECT @inicio = sys.fn_cdc_get_min_lsn('SchemaDeLaTabla_NombreDeLaTablaAuditada')
SELECT @fin = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_net_changes_SchemaDeLaTabla_NombreDeLaTablaAuditada(@inicio, @fin, 'all') 
SELECT * FROM cdc.fn_cdc_get_all_changes_SchemaDeLaTabla_NombreDeLaTablaAuditada(@inicio, @fin, 'all')
more info: http://msdn.microsoft.com/en-us/library/bb522489.aspx

Limpiar la lista de servidores recientes del SSMS 2008

Para borrar el cache de la lista de servidores a los que te conectaste recientemente en el SSMS (SQL Server Management Studio 2008) necesitas borrar/renombrar el archivo:

%APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

Y listo =)

Shrink Log Files

DECLARE @script nvarchar(4000)
SET @script = '
IF ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'')
BEGIN
    USE [?]
    DECLARE @tsql nvarchar(4000) SET @tsql = ''''
    DECLARE @logFile int
    DECLARE LogFiles CURSOR FOR
        SELECT fileid FROM sysfiles WHERE status & 0x40 = 0x40
    OPEN LogFiles
    FETCH next FROM LogFiles INTO @logFile
    WHILE @@fetch_status = 0
    BEGIN
        SET @tsql = @tsql
            + ''DBCC SHRINKFILE(''
            + cast(@logFile AS varchar(5))
            + '', 1)'' + CHAR(13) + CHAR(10)
        FETCH next FROM LogFiles INTO @logFile
    END
    CLOSE LogFiles
    DEALLOCATE LogFiles
    SET @tsql = @tsql
        + ''BACKUP LOG [?] WITH TRUNCATE_ONLY''
        + CHAR(13) + CHAR(10) + @tsql
    EXEC(@tsql)
END'
EXEC sp_msforeachdb @script

Limitar conexiones concurrentes

CREATE TRIGGER LimitarConexiones
ON ALL SERVER WITH EXECUTE AS 'loginName'
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN()= 'loginName'
    AND (
        SELECT COUNT(*)
        FROM sys.dm_exec_sessions
        WHERE is_user_process = 1
        AND original_login_name = 'loginName'
    ) > 3
    ROLLBACK;
END;

Corregir Error 15023

USE MiDataBase
GO
EXEC sp_change_users_login 'Report'
-- Si el procedimiento regresa algún valor ejecutar el siguiente:
GO
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'LoginName'

Auditar cambios en tu Database

CREATE TRIGGER [NombreDelTrigger]
ON DATABASE
FOR DDL_PROCEDURE_EVENTS, DDL_FUNCTION_EVENTS, DDL_TRIGGER_EVENTS, DDL_TABLE_EVENTS, DDL_VIEW_EVENTS, DDL_INDEX_EVENTS
AS
BEGIN
   INSERT INTO LogTable(fecha,objeto,host,usuario,accion)
   VALUES(GETDATE()
   ,EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
   ,HOST_NAME()
   ,SYSTEM_USER
   ,EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')
   )
END

Consulta recursiva para mostrar sumatorias

SELECT T1.FECHA, T2.VALOR
FROM TABLA T1
INNER JOIN (
   SELECT T3.FECHA,
   (
      SELECT SUM(ISNULL(T4.VALOR,0))
      FROM TABLA T4
      WHERE T4.FECHA BETWEEN '2008-12-1' AND T4.FECHA
      AND T3.FECHA >= T4.FECHA
   ) AS VALOR
   FROM TABLA T3
   WHERE T3.FECHA BETWEEN '2008-12-1' AND '2008-12-31'
) T2 ON T2.FECHA = T1.FECHA