Archive for June, 2019

Mapping temp tables to users

June 28, 2019

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0,
LEN(path)-CHARINDEX(‘\’, REVERSE(path))+1) + ‘\Log.trc’
FROM sys.traces
WHERE is_default = 1;

SELECT DISTINCT
o.name,
o.OBJECT_ID,
o.create_date,
gt.NTUserName,
gt.HostName,
gt.SPID, (8*p.reserved_page_count)/1024.0 size_mb
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
JOIN tempdb.sys.objects AS o WITH (NOLOCK) ON gt.ObjectID = o.OBJECT_ID
INNER JOIN tempdb.sys.dm_db_partition_stats p WITH (NOLOCK) ON p.object_id = o.object_id AND p.index_id IN (0,1)
WHERE gt.DatabaseID = 2

Advertisements

Unable to shrink database log

June 10, 2019

Database was in simple mode, no transactions to be written.

Receiving the following errors after tatting about trying to shrink the log prior to a backup.

“Cannot shrink log file because total number of logical log files cannot be fewer than 2.”
“Cannot shrink log file because requested size is larger than the start of the last logical log file”

detach database, rename the transactioon log.

Attach the database, dont use the gui, script it from the gui and delete reference to the log.

A new log will be generated. Boom.

xp_cmdshell

June 5, 2019

will run using the sql service credentials

xp_cmdshell ‘whoami’ to check this

if not an admin will run using the proxy account – check security -> ##xp_cmdshell_proxy_Account##

working folder can be found xp_cmdshell ‘dir’ likely to be system32