DBCC SQLPERF(logspace)

----------------------------------------------------------------------------------------------

DECLARE @sql_command varchar(1024)
DECLARE @logtable TABLE
(
   [Database Name] VARCHAR(1000) NULL,  
   [Log Size (MB)] numeric,
   [Log Space Used (%)] numeric,
   [Status] VARCHAR(128) NULL  
)

SELECT @sql_command = 'dbcc sqlperf (logspace)'

INSERT INTO @logtable EXEC (@sql_command)

select * from @logtable where [Database Name] = 'RFDB'

SELECT /* ignore this */ command,
            r.session_id, r.blocking_session_id,
            s.text,
            start_time,
            percent_complete, 
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

DECLARE @whotable TABLE
(
   SPID INT,  
   Status VARCHAR(1000) NULL,  
   Login SYSNAME NULL,  
   HostName SYSNAME NULL,  
   BlkBy SYSNAME NULL,  
   DBName SYSNAME NULL,  
   Command VARCHAR(1000) NULL,  
   CPUTime INT NULL,  
   DiskIO INT NULL,  
   LastBatch VARCHAR(1000) NULL,  
   ProgramName VARCHAR(1000) NULL,  
   SPID2 INT,
   REQUESTID INT
)
 
INSERT INTO @whotable EXEC sp_who2
 
select * from @whotable order by BlkBy desc

export

bcp <database.schema.tablename> out c:\raytemp\tablename.out -n -T -S”<server,port>”

import

bcp <database.schema.tablename> in c:\raytemp\tablename.out -n -T -S”<server,port>”

DECLARE @objtable TABLE
(
dbname VARCHAR(100) NULL,
SizeMB INT
)

insert into @objtable
exec sp_msforeachdb
‘use [?];
SELECT ”?”, MAX(TotalSpaceMB) as MaxObjSizeMB
FROM (
SELECT
t.NAME AS TableName,
i.name as indexName,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB
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
WHERE
t.NAME NOT LIKE ”dt%” AND
i.OBJECT_ID > 255 AND
i.index_id <= 1 AND ''?'' <> ”TEMPDB”
GROUP BY
t.NAME, i.object_id, i.index_id, i.name ) as Tab’

select * from @objtable order by SizeMB desc

https://www.brentozar.com/archive/2009/08/backup-log-with-truncate-only-in-sql-server-2008/

USE [master]
GO
 
DECLARE @dbName SYSNAME
DECLARE @sqlCmd VARCHAR(MAX)
 
SET @sqlCmd = ''
SET @dbName = ''
 
SELECT   @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) +
         CHAR(13)
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = @dbName
 
PRINT @sqlCmd
 
--Uncomment below line to kill
--EXEC (@sqlCmd)

select * from (
SELECT
[UserName] = CASE princ.[type]
WHEN ‘S’ THEN princ.[name]
WHEN ‘U’ THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE princ.[type]
WHEN ‘S’ THEN ‘SQL User’
WHEN ‘U’ THEN ‘Windows User’
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,–perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
–database user
sys.database_principals princ
LEFT JOIN
–Login accounts
sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
–Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
–Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
princ.[type] in (‘S’,’U’)
UNION
–List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
[UserName] = CASE memberprinc.[type]
WHEN ‘S’ THEN memberprinc.[name]
WHEN ‘U’ THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE memberprinc.[type]
WHEN ‘S’ THEN ‘SQL User’
WHEN ‘U’ THEN ‘Windows User’
END,
[DatabaseUserName] = memberprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,–perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
–Role/member associations
sys.database_role_members members
JOIN
–Roles
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
–Role members (database users)
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
–Login accounts
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
–Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
–Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
–List all access provisioned to the public role, which everyone gets by default
SELECT
[UserName] = ‘{All Users}’,
[UserType] = ‘{All Users}’,
[DatabaseUserName] = ‘{All Users}’,
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,–perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
–Roles
sys.database_principals roleprinc
LEFT JOIN
–Role permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
–Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
JOIN
–All objects
sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
–Only roles
roleprinc.[type] = ‘R’ AND
–Only public role
roleprinc.[name] = ‘public’ AND
–Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
) T
where T.DatabaseUserName like ‘%HQU-PlatFin-DBAdmin%’

SELECT * FROM
(
SELECT TOP 10000 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) AS [Text],
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time/1000 total_worker_time_in_ms,
qs.last_worker_time/1000 last_worker_time_in_ms,
qs.total_elapsed_time/1000 total_elapsed_time_in_ms,
qs.last_elapsed_time/1000 last_elapsed_time_in_ms,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time_in_ms DESC
) T

select (LEN(@string)-LEN(REPLACE(@string,@strToFind,'')))/LEN(@strToFind)

-- Read the errorlog with a stored procedure

EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO

Or look in the Configuration Manager  

SQL Server Network Configuration/Protocols/IP Addresses/TCP Port