SQL server

SELECT * FROM database.schema WITH(NOLOCK) ORDER BY 1 DESC

-- lock simulation on table dbo.test - 1 minute
BEGIN TRAN  
SELECT TOP (1) 1 FROM dbo.test t WITH (TABLOCKX)
WAITFOR DELAY '00:01:00'
ROLLBACK TRAN  
GO

SqlCmd -H 127.0.0.1 -E -Q "BACKUP DATABASE [database_name] TO DISK='c:\<folder>\<name>.bak'"

SqlCmd -E -S 127.0.0.1 -Q "RESTORE DATABASE [database_name] FROM DISK='c:\<folder>\<name>.bak' WITH REPLACE"

ALTER DATABASE database_name
ADD FILE
( NAME = tablespace_name,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\tablespace_name.ndf',
  SIZE = 5MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
TO FILEGROUP FILEGROUP_NAME;

SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName
FROM    sys.sysprocesses
WHERE dbid > 0 
--and loginame = 'loginname'
GROUP BY dbid, loginame;

GO
BEGIN TRY  
    -- normal execution
END TRY  
BEGIN CATCH  
    -- catch execution
END CATCH;  

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
FROM   sys.dm_os_performance_counters
WHERE  Rtrim([object_name]) LIKE '%Buffer Manager'
       AND counter_name = 'Database Pages';

;
WITH src
     AS (SELECT database_id,
                db_buffer_pages = Count_big(*)
         FROM   sys.dm_os_buffer_descriptors
         --WHERE database_id BETWEEN 5 AND 32766
         GROUP  BY database_id)
SELECT [db_name] = CASE [database_id]
                     WHEN 32767 THEN 'Resource DB'
                     ELSE Db_name([database_id])
                   END,
       db_buffer_pages,
       db_buffer_MB = db_buffer_pages / 128,
       db_buffer_percent = CONVERT(DECIMAL(6, 3), db_buffer_pages * 100.0 /
                                                  @total_buffer)
FROM   src
ORDER  BY db_buffer_mb DESC; 

USE <schema>;

go

;
WITH src
     AS (SELECT [Object] = o.NAME,
                [Type] = o.type_desc,
                [Index] = COALESCE(i.NAME, ''),
                [Index_Type] = i.type_desc,
                p.[object_id],
                p.index_id,
                au.allocation_unit_id
         FROM   sys.partitions AS p
                INNER JOIN sys.allocation_units AS au
                        ON p.hobt_id = au.container_id
                INNER JOIN sys.objects AS o
                        ON p.[object_id] = o.[object_id]
                INNER JOIN sys.indexes AS i
                        ON o.[object_id] = i.[object_id]
                           AND p.index_id = i.index_id
         WHERE  au.[type] IN ( 1, 2, 3 )
                AND o.is_ms_shipped = 0)
SELECT src.[object],
       src.[type],
       src.[index],
       src.index_type,
       buffer_pages = Count_big(b.page_id),
       buffer_mb = Count_big(b.page_id) / 128
FROM   src
       INNER JOIN sys.dm_os_buffer_descriptors AS b
               ON src.allocation_unit_id = b.allocation_unit_id
WHERE  b.database_id = Db_id()
GROUP  BY src.[object],
          src.[type],
          src.[index],
          src.index_type
ORDER  BY buffer_pages DESC; 

DBCC SHRINKDATABASE (<schema>, 10)

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB desc

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
	i.name AS IndexName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, i.Name, p.Rows
ORDER BY 
    TotalSpaceMB desc

BEGIN TRANSACTION 
 UPDATE <schema>.<table> SET <field> = <field> WHERE <field> = 2 
 WAITFOR DELAY '00:00:05'
 UPDATE <schema>.<table> SET <field> = <field> WHERE <field> = 2 
COMMIT TRANSACTION

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
  BEGIN
		
	 BEGIN TRANSACTION
     
		-- Delete some small number of rows at a time
		 DELETE TOP (1000)  <schema>.<table> WHERE <id> < <value>;
		 SET @Deleted_Rows = @@ROWCOUNT;

		 print  'del rows';
		 print @Deleted_Rows;

	 COMMIT TRANSACTION
	 --CHECKPOINT
END

SELECT  dest.text, deqs.last_execution_time
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE   deqs.last_execution_time > 'yyyy-MM-dd hh:mm:ss'
        AND dest.text LIKE '%<schema>.<table>%';

exec sp_who2;

-- you can get SPID performing exec sp_who2
dbcc inputbuffer(SPID);