Moving from an execute as owner approach to a signed procedure approach

July 17, 2019

The following can be used as a template for signing procedures which removes the need for executing as owner and database chaining

–create a test login
CREATE LOGIN test WITH PASSWORD =’abc’, CHECK_POLICY = OFF

GO

–create a test proc

CREATE PROC test_proc
AS
SELECT * FROM sys.dm_exec_query_memory_grants

GO

GRANT EXEC ON test_proc TO PUBLIC

CREATE CERTIFICATE [CodeSigningCertificate]
ENCRYPTION BY PASSWORD = ‘Password123!’
WITH EXPIRY_DATE = ‘2099-01-01’,
SUBJECT = ‘Code Signing Cert’

CREATE LOGIN [CodeSigningLogin] FROM CERTIFICATE [CodeSigningCertificate]

GRANT VIEW SERVER STATE TO [CodeSigningLogin]
ADD SIGNATURE TO test_proc
BY CERTIFICATE [CodeSigningCertificate]
WITH PASSWORD = ‘Password123!’;

–Clean Up

/*
DROP PROC dbo.test_proc
DROP LOGIN [CodeSigningLogin]
DROP CERTIFICATE [CodeSigningCertificate]
DROP LOGIN test
*/

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

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

reclaim LOB data from an empty table

April 4, 2019

EXECUTE sys.sp_spaceused @objname = N’schema.table’, @updateusage = ‘true’

Shrinking database files

October 22, 2018

Prevent New Data from Being Written to a File

The database file will not shrink in a timely fasion while there is data in the file. So remove it. On a daily basis tables our tables are dropped and recreated. The fastest way to remove data is to let automatic process drop tables and not allow the processes to recreate the tables on the file we are trying to shrink.We can make a file readonly by using DBCC SHRINKFILE (‘filenname’,EMPTYFILE), as long as the command is running it will 1. not allow new data to be written to the file 2. slowly move data out of the file. The command frequently fails with a deadlock allowing data to be written to the file so by using the following work in procedure we can tell the proc to restart on failure.

CREATE PROC [dbo].[emptyfile_new]
@dbase VARCHAR(100) ,
@log_file_name VARCHAR(1000)
AS
SET DEADLOCK_PRIORITY 10;

BEGIN TRY

DECLARE @sql VARCHAR(MAX) = 'USE ' + @dbase + '; DBCC SHRINKFILE (N''' + @log_file_name + ''' , EMPTYFILE)';

EXEC (@sql);

END TRY

BEGIN CATCH

SELECT ERROR_MESSAGE();
EXEC test.dbo.emptyfile_new;

END CATCH;

Leave the procedure running for the file you want to empty, if you want to empty multiple file, run the procedure multiple times in different sessions using different parameters.

Columnstore Indexes

You can not use shrinkfile on a file that contains a table with a columnstore index on it, move any table to a new temporary filegroup. Make sure to transfer any compression setting, indexes, keys, constraints.

You can not use shrinkfile on a file that contains a columnstore index. Move any columnstore indexes to a new tempory filegroup.

How much Data is in your file

SELECT FILEPROPERTY(‘filename’,’SpaceUsed’)

This will return the number of pages in your file. Multiply by 8 to get the number of kb.

What data is contained in my file

We can see how many pages of data each table/index has in the database using the following code:

USE DATABASE_NAME

select object_id,index_id,allocation_unit_type_desc,COUNT(*) c
into #a
from sys.dm_db_database_page_allocations(DATABASE_ID,NULL,NULL,NULL,'limited') where allocated_page_file_id = DATABASE_FILE_NUMBER
group by object_id ,index_id,allocation_unit_type_desc

Select b.name,c.name,d.name
,CASE WHEN a.index_id IN (0,1) THEN 'alter table '+ c.name + '.' + b.name+ ' rebuild'
WHEN a.index_id > 1 THEN 'ALTER INDEX ' + d.name + ' on '+ c.name + '.' + b.name + ' rebuild'
END
INNER JOIN sys.objects b ON a.object_id = b.object_id
INNER JOIN sys.schemas c ON c.schema_id = b.schema_id
INNER JOIN sys.indexes d ON d.index_id = a.index_id AND d.object_id = a.object_id

Move Remaining Data

heaps and clustered indexes can be moved using ALTER TABLE X.X REBUILD

Other indexes can be moved using ALTER INDEX x ON x.x REBUILD

If these tables or indexes contain lob data then rebuilding will not move this instead copy data to a holding table, truncate the original table and move it back to the original table, while the emptyfile command is running.

You can identity Lob data on the file using the above SQL allocation_unit_type_desc field.

Check Page Contents

Occasionally you might get an error that where you are told a page cannot be moved, use the following to check the page contents

DBCC TRACEON (3604)
DBCC PAGE ('DATABASE_NAME',DATABASE_FILENUMBER,PAGE,1) WITH TABLERESULTS

Switch the final parameter for a 3 to actual data on the page

You may be told that a file can not be shrunk as it is being shrunk by another process – even though it is not. In this case increase the file size by a small amount such as 10MB and retry your shrink.

xp_cmdshell and file paths with spaces

May 24, 2018

Not sure why but xp_cmdshell has issues with spaces in file paths. For example the following 7zip command results in an error:

EXEC xp_cmdshell ‘”c:\Program Files\7-Zip\7z” a “c:\test folder\test.zip” “c:\test folder\x.bmp’

‘c:\Program’ is not recognized as an internal or external command,operable program or batch file.

One workaround to this is to ensure that the qualified path is not at the beginning of the command. For example the following will work as expected

EXEC xp_cmdshell ‘cd.. && “c:\Program Files\7-Zip\7z” a “c:\test folder\test.zip” “c:\test folder\x.bmp’

So the work around here was to insert cd.. && at the beginning of the command string.

passing temp tables between stored procedures

May 23, 2018

Its not widely known that you can pass temp tables between stored procedures.

The basic idea is that you create a parent procedure which contains the temp table, and one or more child procedures that reference the table. The parent procedure will then call the child procedures after the temp table has been created.

A basic example can be seen below:

USE test
GO

CREATE PROC dbo.pass_temp_table_between_stored_procs

AS

SELECT GETDATE() x INTO #temp

EXEC test.dbo.pass_temp_table_between_stored_procs1
EXEC test.dbo.pass_temp_table_between_stored_procs2

GO

CREATE PROC dbo.pass_temp_table_between_stored_procs1
AS
SELECT * FROM #temp

GO

alter PROC dbo.pass_temp_table_between_stored_procs2
AS

UPDATE #temp SET x = x+1

SELECT * FROM #temp

GO

EXEC dbo.pass_temp_table_between_stored_procs

jobs/schedules/steps views

May 2, 2018

CREATE VIEW jobschedules

AS

SELECT SJ.name as job_name
, SJ.enabled as is_job_enabled
, SS.enabled as is_schedule_enabled
/*, ISNULL(SJ.description, ”) as job_desc*/
, SS.name as schedule_name
, CASE freq_type
WHEN 1 THEN ‘Occurs on ‘ + STUFF(RIGHT(active_start_date, 4), 3,0, ‘/’) + ‘/’ + LEFT(active_start_date, 4) + ‘ at ‘
+ REPLACE( RIGHT(CONVERT(varchar(30),
CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime) /* hh:mm:ss 24H */
, 9), 14), ‘:000’, ‘ ‘) /* HH:mm:ss:000AM/PM then replace the :000 with space.*/
WHEN 4 THEN ‘Occurs every ‘ + CAST(freq_interval as varchar(10))
+ ‘ day(s) ‘
+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 8 THEN
‘Occurs every ‘ + CAST(freq_recurrence_factor as varchar(10))
+ ‘ week(s) on ‘
+
REPLACE( CASE WHEN freq_interval&1 = 1 THEN ‘Sunday, ‘ ELSE ” END
+ CASE WHEN freq_interval&2 = 2 THEN ‘Monday, ‘ ELSE ” END
+ CASE WHEN freq_interval&4 = 4 THEN ‘Tuesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&8 = 8 THEN ‘Wednesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&16 = 16 THEN ‘Thursday, ‘ ELSE ” END
+ CASE WHEN freq_interval&32 = 32 THEN ‘Friday, ‘ ELSE ” END
+ CASE WHEN freq_interval&64 = 64 THEN ‘Saturday, ‘ ELSE ” END
+ ‘|’, ‘, |’, ‘ ‘) /* get rid of trailing comma */

+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 16 THEN
‘Occurs every ‘ + CAST(freq_recurrence_factor as varchar(10))
+ ‘ month(s) on ‘
+ ‘day ‘ + CAST(freq_interval as varchar(10)) + ‘ of that month ‘
+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 32 THEN
‘Occurs ‘ +
CASE freq_relative_interval
WHEN 1 THEN ‘every first ‘
WHEN 2 THEN ‘every second ‘
WHEN 4 THEN ‘every third ‘
WHEN 8 THEN ‘every fourth ‘
WHEN 16 THEN ‘on the last ‘
END
+ CASE freq_interval WHEN 1 THEN ‘Sunday’
WHEN 2 THEN ‘Monday’
WHEN 3 THEN ‘Tuesday’
WHEN 4 THEN ‘Wednesday’
WHEN 5 THEN ‘Thursday’
WHEN 6 THEN ‘Friday’
WHEN 7 THEN ‘Saturday’
WHEN 8 THEN ‘day’
WHEN 9 THEN ‘weekday’
WHEN 10 THEN ‘weekend’
END

+ ‘ of every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ month(s) ‘
+ CASE freq_subday_type
WHEN 1 THEN ‘at ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
WHEN 2 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ second(s)’
WHEN 4 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ minute(s)’
WHEN 8 THEN ‘every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ hour(s)’
ELSE ” END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ‘ between ‘+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
+ ‘ and ‘
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘))
ELSE ”
END
WHEN 64 THEN ‘Runs when the SQL Server Agent service starts’
WHEN 128 THEN ‘Runs when the computer is idle’

END as [Description]
, CASE freq_type
WHEN 1 THEN ‘One Time’
WHEN 4 THEN CASE freq_subday_type WHEN 1 THEN ‘Daily’
WHEN 2 THEN ‘Second-ly’
WHEN 4 THEN ‘Minutely’
WHEN 8 THEN ‘Hourly’
ELSE ”
END
WHEN 8 THEN ‘Weekly’
WHEN 16 THEN ‘Monthly’
WHEN 32 THEN ‘Monthly, relative to freq_interval’
WHEN 64 THEN ‘Runs when the SQL Server Agent service starts’
WHEN 128 THEN ‘Runs when the computer is idle’
END As [Freq_Type]
, CASE freq_subday_type
WHEN 1 THEN ‘At the specified time’
WHEN 2 THEN ‘Every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ Second(s)’
WHEN 4 THEN ‘Every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ Minute(s)’
WHEN 8 THEN ‘Every ‘ + CAST(freq_subday_interval as varchar(10)) + ‘ Hour(s)’
ELSE ”
END AS freq_subday_type

, CASE
WHEN freq_type = 4 /* (daily) */ THEN ‘Every ‘ + CAST(freq_interval as varchar(10)) + ‘ Day(s)’
WHEN freq_type = 8 /* (weekly) */ THEN
REPLACE( CASE WHEN freq_interval&1 = 1 THEN ‘Sunday, ‘ ELSE ” END
+ CASE WHEN freq_interval&2 = 2 THEN ‘Monday, ‘ ELSE ” END
+ CASE WHEN freq_interval&4 = 4 THEN ‘Tuesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&8 = 8 THEN ‘Wednesday, ‘ ELSE ” END
+ CASE WHEN freq_interval&16 = 16 THEN ‘Thursday, ‘ ELSE ” END
+ CASE WHEN freq_interval&32 = 32 THEN ‘Friday, ‘ ELSE ” END
+ CASE WHEN freq_interval&64 = 64 THEN ‘Saturday, ‘ ELSE ” END
+ ‘|’, ‘, |’, ‘ ‘) /* get rid of trailing comma */
WHEN freq_type = 16 THEN ‘On Day ‘ + CAST(freq_interval as varchar(10)) + ‘ of Every Month’
WHEN freq_type = 32 /* (monthly) */ THEN ‘Every ‘ +
CASE freq_interval WHEN 1 THEN ‘Sunday’
WHEN 2 THEN ‘Monday’
WHEN 3 THEN ‘Tuesday’
WHEN 4 THEN ‘Wednesday’
WHEN 5 THEN ‘Thursday’
WHEN 6 THEN ‘Friday’
WHEN 7 THEN ‘Saturday’
WHEN 8 THEN ‘Day’
WHEN 9 THEN ‘Weekday’
WHEN 10 THEN ‘Weekend day’
END
ELSE ”
END as [freq_interval]
, CASE WHEN freq_type = 8 /* (weekly) */ THEN ‘Every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ Week(s)’
WHEN freq_type = 16 /* (monthly) */ THEN ‘Every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ Month(s)’
WHEN freq_type = 32 /* (monthly relative) */ THEN ‘Every ‘ + CAST(freq_recurrence_factor as varchar(10)) + ‘ Week(s)’
ELSE ”
END as freq_recurrence_factor
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘)) as starttime
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_end_time as varchar(10)), 6), 3, 0, ‘:’ ), 6, 0, ‘:’ ), 8) as datetime), 9), 14), ‘:000’, ‘ ‘)) as endtime
, STUFF(STUFF(active_start_date, 5, 0, ‘/’), 8, 0, ‘/’) as schedule_start_date
, STUFF(STUFF(active_end_date, 5, 0, ‘/’), 8, 0, ‘/’) as schedule_end_date
FROM msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.sysjobschedules SJS
ON SJ.job_id = SJS.job_id
INNER JOIN msdb.dbo.sysschedules SS
ON SJS.schedule_id = SS.schedule_id

CREATE VIEW jobs

AS
SELECT a.name job_name ,
a.enabled is_job_enabled ,
description job_description ,
start_step_id start_at_step ,
b.name category_name ,
c.name job_owner_name ,
CASE notify_level_email
WHEN 0 THEN ‘never’
WHEN 1 THEN ‘when the job succeeds’
WHEN 2 THEN ‘when the job fails’
WHEN 3 THEN ‘when the job completes’
END notify_level_email ,
d.name notify_email_operator ,
date_created job_created_date ,
date_modified job_modifed_date
FROM msdb..sysjobs a
LEFT JOIN msdb..syscategories b ON b.category_id = a.category_id
LEFT JOIN msdb..sysusers c ON c.sid = a.owner_sid
LEFT JOIN msdb..sysoperators d ON d.id = a.notify_email_operator_id;
GO

CREATE VIEW jobsteps

AS
SELECT b.name job_name,
step_id job_step_number,
step_name job_step_name,
subsystem job_step_type,
command job_step_command,
CASE on_success_action
WHEN 1 THEN ‘go to next step’
WHEN 2 THEN ‘quit the job reporting success’
WHEN 3 THEN ‘quit the job reporting failure’
WHEN 4 THEN ‘go to step’
ELSE NULL
END on_success_action,
on_success_step_id ,
CASE on_fail_action
WHEN 1 THEN ‘go to next step’
WHEN 2 THEN ‘quit the job reporting success’
WHEN 3 THEN ‘quit the job reporting failure’
ELSE NULL
END ON_failure_action,
on_fail_step_id ,
database_name ,
retry_attempts ,
retry_interval retry_interval_mins,
output_file_name ,
CASE last_run_outcome
WHEN 0 THEN ‘failed’
WHEN 1 THEN ‘succeeded’
WHEN 2 THEN ‘retry’
WHEN 3 THEN ‘cancelled’
WHEN 5 THEN ‘unknown’
END last_run_outcome,
LEFT(oa1.last_run_duration, 2) + ‘:’ + SUBSTRING(oa1.last_run_duration, 3, 2) + ‘:’ + SUBSTRING(oa1.last_run_duration, 5, 2) last_run_duration,
last_run_retries ,
last_run_date ,
LEFT(oa1.last_run_time, 2) + ‘:’ + SUBSTRING(oa1.last_run_time, 3, 2) + ‘:’ + SUBSTRING(oa1.last_run_time, 5, 2) last_run_time,
c.name proxy_name
FROM msdb..sysjobsteps a
INNER JOIN msdb..sysjobs b ON b.job_id = a.job_id
LEFT JOIN msdb..sysproxies c ON c.proxy_id = a.proxy_id
OUTER APPLY ( SELECT RIGHT(‘000000’ + CAST(last_run_duration AS VARCHAR(100)), 6) last_run_duration ,
RIGHT(‘000000’ + CAST(last_run_time AS VARCHAR(100)), 6) last_run_time ) oa1;

Create a copy of a database without any data

April 10, 2018

Create dacpac

install:

Microsoft® SQL Server® Data-Tier Application Framework (June 30 2016)

https://www.microsoft.com/en-us/download/details.aspx?id=53013

C:\Program Files\Microsoft SQL Server\130\DAC\bin>sqlpackage /Action:Extract /SourceDatabaseName:”games” /SourceServerName:SERVERNAME /TargetFile:”C:\dac\games.dacpac”

create new database from dacpac

within management studio

right click databases folder
deploy data tier application
follow the GUI instructions
configure database