* You are viewing the archive for September, 2014

Maintaining CRM 2011 Job Schedules

The CRM 2011 background job schedule can be changed with this tool from CodePlex.


The MSDN blog gives a good overview


List out jobs and last run times on SQL 2013

Generate a table of jobs and their last run times on SQL Server 2013.

use msdb
;WITH jobhistory as (
SELECT job_id,
last_run_time = max(dbo.agent_datetime(run_date, run_time))
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0
AND run_status = 1
GROUP BY job_id, run_status)

SELECT … Continue Reading

List SQL 2013 Table Sizes and Row Counts

Tabulate the table sizes and row counts in a SQL 2013 DB

s.name + ‘.’ + t.Name AS [Table Name],
t.object_id as [Object Id],
part.rows AS [Total Rows In Table – Modified],
CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3))
AS [Table’s Total Space In GB]
SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id
INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id
AND idx.Index_id = part.Index_id
INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id
INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id
INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id
WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255
GROUP … Continue Reading