BogdanM88

declare
	--Maximum size in MB for the object in question
	@ObjectSizeMB numeric (16,3) = NULL,
	--Maximum number of rows in the section
	@row_count numeric (16,3) = NULL;
	;with st AS(
	select DISTINCT
	obj.[object_id]
	, obj.[create_date]
	, OBJECT_SCHEMA_NAME(obj.[object_id]) as [SchemaName]

BogdanM88

with info as
	(SELECT
		ps.[object_id],
		ps.database_id,
		ps.index_id,
		ps.index_type_desc,
		ps.index_level,
		ps.fragment_count,
		ps.avg_fragmentation_in_percent,
		ps.avg_fragment_size_in_pages,

BogdanM88

#checks if the Outputs folder exists. If it doesn’t, the script creates it and returns its full path
function CheckAndCreateFolder($rootFolder, [switch]$Outputs)
{
    $location = $rootFolder

    #set the location based on the used switch
    if($Outputs -eq $true)
    {
        $location += "\Outputs"
    }

BogdanM88

if ($returnCode -notin (100, 101))
     { #an error is encountered
       $logPath = "$outsLoc\DataOutput_error.txt"

       $message >> $logPath
       clear-content $logPath
       $message = "`r`n $returnCode - An error is encountered"

       #output file is opened when an error is encountered
       Invoke-Item "$logPath"

BogdanM88

#define the tool’s location, date stamp variable and the tool’s parameters 
$toolLocation   = "C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com"
$dateStamp = (Get-Date -Format "Mmddyyyy_HHMMss")

#output log file path
$logPath = "$outsLoc\DataOutput_$dateStamp.txt"

$Params = "/datacompare /compfile:""D:\DataSync\Project\test_DB_1vstest_DB_2.dcomp"" /log:""$logPath"""
$sync = " /sync"

BogdanM88

#checks if the Outputs folder exists. If it doesn’t, the script creates it and returns its full path
function CheckAndCreateFolder($rootFolder, [switch]$Outputs)
{
    $location = $rootFolder
 
    #set the location based on the used switch
    if($Outputs -eq $true)
    {
        $location += "\Outputs"
    }

BogdanM88

(collapsable block)
select f.[database_name]
	,[backup_type]=
	case f.[type]
	when 'D' then 'Full'
	when 'L' then 'Log'
	when 'I' then 'Diff'
	end
	,f.[backup_finish_date]
	,round(cast(f.backup_size as float),1) as [backup_size, mb]

BogdanM88

(collapsable block)
select spid
	,rtrim(db_name([dbid])) as [db]
	,rtrim([status]) as [status]
	,rtrim([loginame]) as [loginame]
	,blocked
	,rtrim([program_name]) as [program_name]
	,waittime
	,rtrim(waitresource) as [waitresource]
	,cpu

BogdanM88

(collapsable block)
select substring(t.[text], (q.[statement_start_offset]/2)+1, ((
		case when (q.[statement_end_offset]=-1) then datalength(t.[text]) else q.[statement_end_offset]
		end -q.[statement_start_offset])/2+1)) as [query_txt]
	,q.[execution_count]
	,cast(q.[total_elapsed_time]*1.0/1000000 as float) as [total_elapsed_time]
	,cast(q.[last_elapsed_time]*1.0/1000000 as float) as [last_elapsed_time]
	,cast(q.[min_elapsed_time]*1.0/1000000 as float) as [min_elapsed_time]
	,cast(q.[max_elapsed_time]*1.0/1000000 as float) as [max_elapsed_time]
	,q.[total_logical_reads]

BogdanM88

(collapsing block)
with tbl as (
	select database_id
	,round(cast(sum(case when ([type]=0) then [size] end) * 8.0/1024 as float), 2) as data_size
	,round(cast(sum(case when ([type]=1) then [size] end) * 8.0/1024 as float), 2) as log_size
	,round(cast(sum([size]) * 8.0/1024 as float), 2) as total_size
	from sys.master_files
	group by [database_id]
)
select d.database_id