* 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.

http://crmjobeditor.codeplex.com/

The MSDN blog gives a good overview

http://blogs.msdn.com/b/crminthefield/archive/2012/04/26/avoid-performance-issues-by-re-scheduling-crm-2011-maintenance-jobs.aspx

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,
run_status,
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

SELECT
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]
FROM
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