Table Space updated again

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: March 28, 2014

Today we have another update for an age old script on this site.  You can find the last update to the script here.

This time we have a bit of a bug update.  The details of that bug are in the notes for the script.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
/*
Purpose:
To Evaluate table sizes combined with index space consumption to determine higher cost tables in 
terms of storage, resources and maintenance needs.
 
ModifiedDate	ModifiedBy	Description
2013-11-21		JB			Tables without Indexes had a Null Value in the Output
							Fixed the output to sum properly for the NULLS in absence of an index
 
*/
BEGIN TRAN
 
DECLARE @dbsize DECIMAL(19,2)
        ,@logsize DECIMAL(19,2)
 
SET NOCOUNT ON
 
/*
**  Summary data.
*/
BEGIN
        SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) / 128.0
                , @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN SIZE ELSE 0 END)) / 128.0
                FROM sys.database_files
 
END;
        WITH FirstPass AS (
                SELECT OBJECT_ID,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0,
                        UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0,
                        PageCnt = SUM(
                        CONVERT(DECIMAL(19,2),CASE
                                WHEN (index_id < 2)
                                        THEN (used_page_count)
                                ELSE lob_used_page_count + row_overflow_used_page_count
                                END
                        )) * 8/1024,
                        iPageCnt = SUM(
                        CONVERT(DECIMAL(19,2),CASE
                                WHEN (index_id > 1)
                                        THEN (used_page_count)
                                ELSE 0
                                END
                        )) * 8/1024,
                        RowCnt = SUM(
                        CASE
                                WHEN (index_id < 2)
                                        THEN row_count
                                ELSE 0
                        END
                        )
                FROM sys.dm_db_partition_stats
                WHERE 1 = 1
					--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
					AND index_id < 2
                GROUP BY OBJECT_ID
        ),IndexPass AS (
                SELECT OBJECT_ID,
                        iReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0
                        ,iUsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0
                        ,iPageCnt = SUM(
                        CONVERT(DECIMAL(19,2),CASE
                                WHEN (index_id > 1)
                                        THEN (used_page_count)
                                ELSE 0
                                END
                        )) / 128.0
                        ,RowCnt = SUM(
                        CASE
                                WHEN (index_id < 2)
                                        THEN row_count
                                ELSE 0
                        END
                        )
                FROM sys.dm_db_partition_stats
                WHERE 1 = 1 
					--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
					AND index_id > 1
                GROUP BY OBJECT_ID
        ),InternalTables AS (
                SELECT ps.OBJECT_ID,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0
                        ,UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0
                FROM sys.dm_db_partition_stats  ps
                        INNER Join sys.internal_tables it
                                ON it.OBJECT_ID = ps.OBJECT_ID
                                And it.internal_type IN (202,204,211,212,213,214,215,216)
                WHERE it.parent_id = ps.OBJECT_ID
					--And OBJECTPROPERTY(ps.object_id,'IsMSShipped') = 0
                GROUP BY ps.OBJECT_ID
        ),Summary AS (
                SELECT
                        OBJECT_NAME (F.OBJECT_ID) AS ObjName 
                        ,SCHEMA_NAME(o.schema_id) AS SchemaName
                        ,CASE WHEN OBJECTPROPERTY(F.OBJECT_ID,'IsMSShipped') = 1 
							THEN 'YES' 
							ELSE 'NO' 
							END AS IsMsShipped
                        ,NumRows = MAX(F.RowCnt)
                        ,ReservedPageMB = SUM(ISNULL(F.ReservedPage,0) + ISNULL(i.ReservedPage,0))
                        ,DataSizeMB = SUM(F.PageCnt)
                        --,IndexSizeMB = SUM(CASE WHEN (F.UsedPage + ISNULL(i.UsedPage,0)) > F.PageCnt
                        --                                THEN ((F.UsedPage + ISNULL(i.UsedPage,0)) - F.PageCnt) ELSE 0 END) ,-- Equivalent of max_record_size from sys.dm_db_index_physical_stats
						,IndexSizeMB = SUM(ISNULL(ip.iPageCnt,0))
                        ,UnusedSpace = SUM(CASE WHEN (F.ReservedPage + ISNULL(i.ReservedPage,0)) > (F.UsedPage + ISNULL(i.UsedPage,0))
                                THEN ((F.ReservedPage + ISNULL(i.ReservedPage,0)) - (F.UsedPage + ISNULL(i.UsedPage,0))) ELSE 0 END)
                                + (SUM(ISNULL(ip.iReservedPage,0)) - SUM(ISNULL(ip.iUsedPage,0)))
                        ,IndexReservedMB = SUM(ISNULL(ip.iReservedPage,0))
                        ,dbsizeMB = @dbsize
                        ,LogSizeMB = @logsize
                FROM FirstPass F
					INNER JOIN sys.objects o
						ON F.OBJECT_ID = o.OBJECT_ID
					LEFT Outer Join InternalTables i
                        ON i.OBJECT_ID = F.OBJECT_ID
                    LEFT OUTER JOIN IndexPass ip
						ON F.OBJECT_ID = ip.OBJECT_ID
                GROUP BY F.OBJECT_ID,o.schema_id
        ),TotalUnused AS (
				SELECT SUM(ISNULL(UnusedSpace,0)) AS UnusedSpace
						,SUM(ISNULL(reservedpageMB,0))+SUM(ISNULL(IndexReservedMB,0)) AS Usedr
					FROM Summary
		)
        SELECT ObjName,SchemaName,IsMsShipped,NumRows, ReservedPageMB, ISNULL(DataSizeMB,0) AS DataSizeMB, ISNULL(IndexSizeMB,0) AS IndexSizeMB
						,ISNULL(S.UnusedSpace,0) AS UnusedSpace, dbsizeMB, LogSizeMB
						,TU.UnusedSpace AS TotalTableFreeSpace 
                        ,dbsizeMB - TU.Usedr AS DataFileFreeSpace  
                        /*within 1.5gb on a 1.76tb database or .000085% variance or 99.999915% accuracy */
                        ,PercentofDBPhysFile = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / @dbsize) * 100
                        ,PercentofDBUsedSpace = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / (@dbsize - TU.UnusedSpace)) * 100
 
        FROM Summary S
			CROSS APPLY TotalUnused TU
        ORDER BY PercentofDBUsedSpace DESC
 
ROLLBACK

Table Hierarchy updated

Recently a need resurfaced to explore the foreign key tree (hierarchy/genealogy) as it related to a specific table within a database.  As I pulled out the script from the repository, I realized there was some unfinished work to be done.  But there was also some polish that needed to be added.  This is an update to the most recent posting of that script.  You can see several revisions in the series at this link or the group here.

Some of the changes involve formatting and and labeling.  I added a new column called “Direction” to help understand the relationship of the key to the table in question.  I also changed up the FKGenealogy (formerly called SortCol) to reflect the source table more accurately in the case when the key comes from an ancestor rather than a descendant.  The Level of the FK was also modified to help understand a little better how far away the ancestor was in relationship to the origin table.

A final adjustment also comes from the Genealogy attribute.  Ancestors were all starting at the wrong point in the lineage.  I adjusted that so the lineage can be seen from the point in the tree that the ancestor is related rather than as a root direct from the origin table.

All in all, this script should make more sense to the user than the previous versions.

DECLARE    @StartParentTable    VARCHAR(256);
 
SELECT @StartParentTable = 'tblCampaignMain';
 
WITH Posterity (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, FKGenealogy) AS (
  SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
          ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
          ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
          ,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
          ,0 AS FKLevel
          ,CAST('.'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))
      FROM sys.foreign_key_columns sfkc
          INNER Join sys.foreign_keys sfk
              ON sfkc.constraint_object_id = sfk.OBJECT_ID
      WHERE OBJECT_NAME(sfk.referenced_object_id) = @StartParentTable
  UNION All
  SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
          ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
          ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
          ,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
          ,FKLevel + 1
          ,STUFF(('.' + F.FKGenealogy + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
                      ),1,1,'')
      FROM sys.foreign_keys sfk
          INNER Join Posterity F
              ON OBJECT_NAME(sfk.referenced_object_id) = F.ChildTable
              And F.FKGenealogy NOT LIKE '%'+CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX))+'%'
          INNER Join sys.foreign_key_columns sfkc
              ON sfkc.constraint_object_id = sfk.OBJECT_ID
      WHERE OBJECT_NAME(sfk.referenced_object_id) <> @StartParentTable
          And sfk.referenced_object_id <> sfk.parent_object_id
), Ancestry (ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, FKGenealogy) AS (
  SELECT DISTINCT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
      ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
      ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
      ,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
      ,(FKLevel * -1)-1 AS FKLevel
      ,F.FKGenealogy + CAST(CAST(OBJECT_NAME(sfk.Parent_object_id) AS VARCHAR(MAX))+'.' AS VARCHAR(MAX))
  FROM Posterity F
      INNER Join sys.foreign_keys sfk
          ON F.ChildTable = OBJECT_NAME(sfk.parent_object_id)
          And F.ParentTable <> OBJECT_NAME(sfk.referenced_object_id)
      INNER Join sys.foreign_key_columns sfkc
          ON sfkc.constraint_object_id = sfk.OBJECT_ID
  UNION All
  SELECT OBJECT_NAME(sfk.referenced_object_id) AS ParentTable
      ,COL_NAME(sfkc.referenced_object_id,sfkc.referenced_column_id) AS ParentColumn
      ,OBJECT_NAME(sfk.Parent_object_id) AS ChildTable
      ,COL_NAME(sfkc.parent_object_id,sfkc.parent_column_id) AS ChildColumn
      ,F.FKLevel -1
      ,STUFF(('.' + FKGenealogy + CAST(CAST(OBJECT_NAME(sfk.referenced_object_id) AS VARCHAR(MAX)) + '.' AS VARCHAR(MAX))
                  ),1,1,'')
  FROM Ancestry F
      INNER Join sys.foreign_keys sfk
          ON F.ParentTable = OBJECT_NAME(sfk.parent_object_id)
      INNER Join sys.foreign_key_columns sfkc
          ON sfkc.constraint_object_id = sfk.OBJECT_ID
  WHERE F.ParentTable not in (SELECT ParentTable FROM Posterity)
      And sfk.referenced_object_id <> sfk.parent_object_id
      And F.ChildTable not IN (SELECT ChildTable FROM Posterity)
)
 
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, FKGenealogy
	,CASE 
		WHEN FKLevel > 0 THEN 'Descendant' 
		WHEN FKLevel = 0 THEN 'Root'
		END AS Direction
FROM Posterity
UNION All
SELECT ParentTable,ParentColumn, ChildTable,ChildColumn, FKLevel, FKGenealogy
	,'Ancestor' AS Direction
FROM Ancestry
  ORDER BY FKGenealogy ASC, Direction DESC
  OPTION (maxrecursion 500);

T-SQL Tuesday #050: Automating Database Restores

Comments: 1 Comment
Published on: January 14, 2014

Here it is time for the party of the month for the SQL Server acolytes and I was running a bit behind.  Why?  Well, that was due in part to me rushing around trying to do some of what this months topic is.  Some would call that pretty could timing.

TSQL2sDay150x150Hemanth.D (blog|twitter) is hosting the T-SQL Tuesday blogging party this month, and he wants all of us to post about Automation. As Hemanth.D put it on his blog in his own words:

You could write about, what options you would consider when automating something? Where do you draw the line? What are our preferred tools for automation? T-SQL, PowerShell, VBScript or Batch files(?) or maybe just share something that you automated in the last couple of years.

You can read the invite he posted here.

History

As Hemanth.D mentioned in his invitation, this is not the first time this topic has come up for TSQLTuesday.  As it would happen, I also participated in the first go around with my contribution about sizing databases on limited information.  You can read that here.

This time around, I have a little bit of a different topic to approach.  I hadn’t considered this until after having read that Wayne Sheffield wrote about his efforts to verify backup files via script (automation).  You can read what Wayne wrote at this link.

Having read that, it seemed painfully obvious to me that I should go ahead and write about my efforts to automate backup restores.  After all, if you are verifying the backup files existence, you might also want to test the backups to ensure they are good.  Besides, we all need to test our backups anyway, right?

Present

I have a few different methods I have used over the years to automate restores.  In one iteration, the restore operations were hard coded in a procedure that was scheduled to run on a nightly or weekly basis.  It probably was also just hard coded to be specific to a database.  That kind of setup is not super useful except for that one database.

With that in mind, I worked on several iterations to help create a script for myself that would automate the restores of any database, with any number of backup files, to a different server, and not have file paths/names/anything hard-coded.  Well – there ended up being one thing hard-coded but that can be changed easily enough.

I decided on a script that would read the backup path for the most recent backup file from the msdb database of the server where the database was backed up.  I do this via a linked server that can be created/destroyed in the proc or that can reside permanently (I prefer to leave it in place).  Take the filepath of that backup file and restore it to the destination server.  All of this via tsql.

Now a caveat with this solution is that the backup path works best if it is a UNC path.  Even if you are backing up the database to the local server, backing up to UNC means that the restore operation can just grab that path and not encounter errors due to drive mappings (e.g. backed up to D but the D on the restore server is the cd-rom drive).

What if you don’t want to restore the source database with the same name to the new server?  Well, that has also been considered and a parameter can be passed to the stored procedure to allow for a new database name.  What if the default file paths are different?  That consideration has been made too!  All of that said, more testing is always welcome.

The script can be evaluated from here.

Next steps:

With the script, the next things to do would be to create SQL Agent jobs to run the script on a routine basis.  Test the script and verify it.

User of this script assumes all risk.

Audit Configuration Changes

Do you know the last time a Server Property was changed on your instances of SQL Server?

Are you wondering when the setting for max degree of parallelism was changed?

Do you know who changed the setting?

In some environments there are a lot of hands trying to help mix the pot.  Sometimes more hands can make light work.  This is not always the case though.  More hands in the mix can be a troublesome thing.  Especially when things start changing and the finger pointing starts but nobody really knows for sure who made the change or when the change was made.

I know, that is why there is this concept called change management.  If you make a change to a setting, it should be documented, approved and communicated.  Unfortunately the process does not always dictate the work flow and it may be circumvented.  This is why auditing is a tool that is in place and should be in place – like it or not.

Auditing can be a very good tool.  Like many things – too much of a good thing is not a good thing.  Too much auditing can be more of a hindrance than help.  You don’t want to cause interference by auditing too many things.  You also don’t want too much data that the facts get blurred.  I hope that this script strikes more of a balance with just the right amount of data being captured to be of use.

The basic principle to auditing server configs is to find what values changes, when they were changed and by whom.  There are ancillary details that can be helpful in tracking the source of the change such as the hostname of the source computer making the change.  These are all things that we should capture.  But if a setting hasn’t changed – then we need not necessarily report that the setting was unchanged (it should go without saying).

So for this, I created a couple of tables and then a script that I can add to a job to run on a regular basis.  I can put the script in a stored procedure should I desire.  I’ll leave that to you as an exercise to perform.

USE AdminDB;
GO
SET NOCOUNT ON;
 
DECLARE @ConfigLastUpdateDate	DATETIME
		,@PreviousPollDate		DATETIME
		,@MaxPollDate			DATETIME
		,@PATH					NVARCHAR(260);
 
SELECT @PATH = REVERSE(SUBSTRING(REVERSE([PATH]), 
						CHARINDEX('\', REVERSE([path])), 260)) + N'LOG.trc'
	FROM sys.traces 
	WHERE is_default = 1;
 
IF OBJECT_ID('tempdb..#DBCCConfig') IS NOT NULL DROP TABLE #DBCCConfig;
	CREATE TABLE #DBCCConfig(
		[ParentObject] VARCHAR (100),
		[Object]       VARCHAR (100),
		[Field]        VARCHAR (100),
		[Value]        VARCHAR (100)); 
 
INSERT INTO #DBCCConfig (ParentObject, Object, Field, Value)
	EXECUTE ('DBCC CONFIG WITH TABLERESULTS');
 
WITH cte AS
(
SELECT cfgupddate = MAX(CASE WHEN t1.Field = 'cfgupddate' THEN t1.Value ELSE NULL END),
        cfgupdtime = MAX(CASE WHEN t1.Field = 'cfgupdtime' THEN t1.Value ELSE NULL END)
FROM #DBCCConfig t1
WHERE   Field IN ('cfgupddate', 'cfgupdtime')
)
SELECT  @ConfigLastUpdateDate = CONVERT(DATETIME,t3.configure_upd_dt)
FROM    cte t1
        CROSS APPLY (SELECT cfgupddate = DATEADD(DAY, CONVERT(INT, t1.cfgupddate), '1900-01-01')) t2
        CROSS APPLY (SELECT configure_upd_dt = DATEADD(ms, CONVERT(INT, t1.cfgupdtime)*3.3, t2.cfgupddate)) t3;
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigAudit')
	CREATE TABLE SysConfigAudit (
		configuration_id	int
		,name	NVARCHAR(256)
		,value	sql_variant
		,minimum	sql_variant
		,maximum	sql_variant
		,value_in_use	sql_variant
		,description	NVARCHAR(MAX)
		,is_dynamic	bit
		,is_advanced	BIT
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME)
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigChangeLog')
	CREATE TABLE SysConfigChangeLog (
		configuration_id	int
		,name	NVARCHAR(256)
		,CurrValue	SQL_VARIANT
		,PrevValue	SQL_VARIANT
		,description	NVARCHAR(MAX)
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME
		,PrevConfigUpdtDate	DATETIME
		,ChangeDate	DATETIME
		,ChangeBy	NVARCHAR(256)
		,HostName	NVARCHAR(256)
		,ChangeSPID	SQL_VARIANT
		,Changedata	NVARCHAR(2000)
		,ApplicationName NVARCHAR(256)
		,Severity INT
		,ERROR SQL_VARIANT
		,ChangeBySessionLogin	NVARCHAR(256))
IF NOT EXISTS (SELECT Name 
					FROM sys.key_constraints 
					WHERE name ='PK_SysConfigChangeLog' 
						AND OBJECT_NAME(parent_object_id) = 'SysConfigChangeLog')
BEGIN
ALTER TABLE dbo.SysConfigChangeLog ADD CONSTRAINT
	PK_SysConfigChangeLog PRIMARY KEY CLUSTERED 
	(
	configuration_id,
	ChangeDate DESC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
		ON Admin_Data
 
END
 
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
/* Write the configurations out to audit table 
*/
IF CONVERT(DATE,GETDATE()) <> @MaxPollDate
BEGIN
	INSERT INTO SysConfigAudit
			( configuration_id ,
			  name ,
			  value ,
			  minimum ,
			  maximum ,
			  value_in_use ,
			  description ,
			  is_dynamic ,
			  is_advanced,
			  PollDate,
			  LastConfigUpdtDate
			)
	SELECT configuration_id,name
			,value
			,minimum,maximum
			,value_in_use
			,description
			,is_dynamic,is_advanced
			,GETDATE(),@ConfigLastUpdateDate
		FROM master.sys.configurations;
END
 
/* Recast MaxPollDate */
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
SET @PreviousPollDate = ISNULL((SELECT TOP 1 PollDate 
									FROM dbo.SysConfigAudit 
									WHERE Polldate <> @MaxPollDate 
									ORDER BY PollDate DESC),'1/1/1900');
 
/*	A configuration has changed and a reboot has occurred 
	causing the updtdate to be written to the config block of the page 10
	The configuration may not be written to the page, but we will write it to
	the table anyway and then compare to the default trace file in all cases.
	If there are any values changed, then write those to the change log with
	the login of the person who changed the value
*/
 
BEGIN
 
WITH presel AS (
SELECT df.LoginName,df.TextData,df.StartTime,df.HostName,df.Severity,df.DatabaseName,df.SPID,df.ERROR
		,df.SessionLoginName,df.ApplicationName
		,SUBSTRING(df.TextData
					,CHARINDEX('''',df.TextData)+1
					,CHARINDEX('''',df.TextData
								,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1)
				AS ConfigName
		,ROW_NUMBER() OVER (PARTITION BY SUBSTRING(df.TextData
													,CHARINDEX('''',df.TextData)+1
													,CHARINDEX('''',df.TextData
															,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1) 
								ORDER BY df.StartTime DESC) AS RowNum
	FROM ::fn_trace_gettable( @path, DEFAULT )  df
	WHERE 1=1
		AND df.TextData LIKE '%Configuration option%'
		AND df.ApplicationName NOT IN ('SQLAgent - Initial Boot Probe','SQLAgent - Enabling/disabling Agent XPs')
		AND df.Severity IS NOT NULL
)
 
INSERT INTO SysConfigChangeLog (configuration_id,name,CurrValue,PrevValue,description,PollDate	
							,LastConfigUpdtDate	,PrevConfigUpdtDate	,ChangeDate,ChangeBy
							,HostName,ChangeSPID,Changedata,ApplicationName,Severity
							,ERROR,ChangeBySessionLogin)
SELECT CUR.configuration_id,cur.NAME,cur.VALUE AS CurrValue,Prev.VALUE AS PrevValue,cur.description
		,cur.polldate, cur.LastConfigUpdtDate,prev.LastConfigUpdtDate AS PrevConfigUpdtDate
		,df.StartTime AS ChangeDate,df.LoginName AS ChangeBy,df.HostName,df.SPID AS ChangeSPID
		,df.TextData AS ChangeData,df.ApplicationName,df.Severity,df.ERROR
		,df.SessionLoginName AS ChangeBySessionLogin
	FROM AdminDB.dbo.SysConfigAudit CUR
		INNER JOIN AdminDB.dbo.SysConfigAudit Prev
			ON CUR.configuration_id = Prev.configuration_id
			AND CUR.PollDate = @MaxPollDate
			AND Prev.PollDate = @PreviousPollDate
		CROSS APPLY presel  df
	WHERE df.RowNum = 1
		AND df.ConfigName = CUR.NAME
		AND CUR.VALUE <> Prev.VALUE	
		AND NOT EXISTS (SELECT configuration_id
							FROM SysConfigChangeLog
							WHERE Name = Cur.NAME
								AND CurrValue = CUR.VALUE
								AND PrevValue = Prev.VALUE
								AND ChangeDate = df.StartTime
								AND ChangeBy = df.LoginName)
		;
END
 
SELECT *
	FROM dbo.SysConfigChangeLog
	ORDER BY ChangeDate DESC;

Here I am trapping the config settings on a daily basis (as the script is written for now). I then cross reference the current settings against the previous settings.  Then I check the default trace to see if any of those settings exist in the trace file.

The default trace captures the data related to configuration changes.  On busy systems, it is still possible for these settings to roll out of the trace files.  For those systems, we may need to make some alterations to the script (such as running more frequently and changing the comparisons to account for smaller time intervals than a day break).

To ensure proper comparison between the audit tables and the trace file, note the substring function employed.  I can capture the configuration name and then join to the audit tables on configuration name.

This has proven useful to me so far in tracking who did what to which setting and when they did it.

I hope you will find good use for it as well.

Backup History

Categories: Corner, News, Professional, Scripts, SSC
Comments: 5 Comments
Published on: November 30, 2012

This is a quick rewrite of the script I just posted a few days ago.  The script had a few flaws if trying to run it on a SQL 2005 box.  I have updated it to run against SQL 2005 and up.

This version is done using Dynamic SQL to help achieve some of the functionality that I wanted but while allowing the flexibility of the different SQL Versions.

You can see the old script here, shown as a part of investigating why differential backups were failing to restore.

DECLARE @SQLVer SQL_VARIANT
		,@DBName VARCHAR(128)
		,@NumDays	SMALLINT
		,@SQL		VARCHAR(1024)
		,@WhereClause	VARCHAR(256)
 
SET @DBName = Null
;
SET @NumDays = 14
;
SET @SQLVer = CONVERT(INTEGER, PARSENAME(CONVERT(VARCHAR(20),SERVERPROPERTY('ProductVersion')),4));
 
SET @WhereClause = 'WHERE a.type IN (''D'',''I'')
		And a.backup_start_date > GETDATE()- ' + CAST(@NumDays AS VARCHAR)+''
IF @DBName IS NOT NULL
BEGIN
	SET @WhereClause = @WhereClause + '
		AND a.database_name = '''+ @DBName +''''
END
 
SET @SQL = '
SELECT a.database_name,a.backup_start_date
		,b.physical_device_name AS BackupPath
		,a.position
		,a.type
		,a.backup_size/1024/1024 AS BackupSizeMB
		,' + CASE 
			WHEN @SQLVer < 10 
				THEN '0'
				ELSE 'a.compressed_backup_size/1024/1024'
			END + ' AS CompressedBackMB
	FROM msdb.dbo.backupset a
		INNER JOIN msdb.dbo.backupmediafamily b
			ON a.media_set_id = b.media_set_id
	' + @WhereClause + '
	ORDER BY a.database_name,a.backup_start_date;'
 
--PRINT @SQL
EXECUTE (@SQL);

Running the above code, I get the following for one of my SQL instances.

Last Known Good CheckDB

Comments: 1 Comment
Published on: November 20, 2012

Diligent DBAs like to check their databases for a little thing called corruption.  Some of those DBAs are masterful at keeping records and logging that the database was checked and came up with a clean bill of health.

There are many different ways of logging this kind of activity.  Today I will share one such way to track when the last successful run of Checkdb happened.

First a little back story

A question came across twitter on the SQLhelp hashtag. The question was “Does restoring a database clear the dbi_dbccLastKnownGood value on the boot page?”

This question prompted me to do a quick test to see.  The test is simple.  Create a small database, backup the database, run checkdb, check to see what the dbi_dbccLastKnownGood value is, restore the previous backup and check the dbi_dbccLastKnownGood value again.

So here is a quick script

USE [master]
GO
 
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TestB')
DROP DATABASE [TestB]
GO
 
USE [master]
GO
CREATE DATABASE [TestB] 
GO
 
DECLARE @BackupPath VARCHAR(256)
	,@BackupName VARCHAR(50)
SET @BackupPath = 'C:\Database\Backup\' --replace with valid file path
SET @BackupName = 'TestB.bak'
 
SET @BackupPath = @BackupPath + @BackupName
BACKUP DATABASE [TestB]
	TO DISK = @BackupPath
	WITH init;
GO
 
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)
 
INSERT INTO #temp
EXECUTE ('DBCC Page ( TestB,1,9,3) WITH TABLERESULTS');
 
/* You will get two results from the following query */
SELECT *
	FROM #temp
	WHERE Field = 'dbi_dbccLastKnownGood';
 
/* TAKE note OF the date returned by the last query */
 
DROP TABLE #temp;
GO
 
DBCC CHECKDB(TestB) WITH no_infomsgs;
GO
 
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)
 
INSERT INTO #temp
EXECUTE ('DBCC Page ( TestB,1,9,3) WITH TABLERESULTS');
 
/* You will get two results from the following query */
SELECT *
	FROM #temp
	WHERE Field = 'dbi_dbccLastKnownGood';
 
/* TAKE note OF the date returned by the last query */
 
DROP TABLE #temp;
GO
 
/* Now Restore the database */
DECLARE @BackupPath VARCHAR(256)
	,@BackupName VARCHAR(50)
SET @BackupPath = 'C:\DATABASE\BACKUP\' --replace with valid file path
SET @BackupName = 'TestB.bak'
 
SET @BackupPath = @BackupPath + @BackupName
/* for this contrived example, i will not take a tail log backup
and just use replace instead */
 
RESTORE DATABASE TestB
	FROM DISK = @BackupPath
	WITH REPLACE; 
GO
 
/* Rerun The Boot Page Check */
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)
 
INSERT INTO #temp
EXECUTE ('DBCC Page ( TestB,1,9,3) WITH TABLERESULTS');
 
/* You will get two results from the following query */
SELECT *
	FROM #temp
	WHERE Field = 'dbi_dbccLastKnownGood';
 
/* TAKE note OF the date returned by the last query */
 
DROP TABLE #temp;
GO

If you run this little test, you should observe that the date value for dbi_dbccLastKnownGood changes with each test.  Now let’s discuss the question and the answer to that original question about whether or not this value gets cleared.

The value does not get cleared.  The value does not get reset.  The cause for the change in the value that you have observed is due simply to the boot page having been restored.  If CheckDB has never been run on the database, you will get the SQL default date of ’1900-01-01 00:00:00.000′.

And then…

Now that the back story is told, that brings us to how to track this – at least one such method.  I had to cover the back story since it is what prompted the writing of a method to gather this information in a quick script so I could use it to monitor.  Yes, it is just another tool to throw into the toolbox.  And to reiterate, it is by no means the only way to track or gather this type of information.  Some are more elaborate than others.  It is up to you to choose.

Keeping in mind that we can get the last time that Checkdb completed without a report of corruption, I delve into this cursor based method to retrieve the dbi_dbccLastKnownGood value for all databases within your SQL Server Instance.

CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [OBJECT] VARCHAR(255),
       Field VARCHAR(255),
       [VALUE] VARCHAR(255)
)
 
CREATE TABLE #DBCCRes (
       Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED, 
       DBName sysname ,
       dbccLastKnownGood DATETIME,
       RowNum	INT
)
 
DECLARE
	@DBName SYSNAME,
	@SQL    VARCHAR(512);
 
DECLARE dbccpage CURSOR
	LOCAL STATIC FORWARD_ONLY READ_ONLY
	FOR SELECT name
		FROM sys.databases
		WHERE name not in ('tempdb');
 
OPEN dbccpage;
FETCH NEXT FROM dbccpage INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'Use [' + @DBName +'];' +CHAR(10)+CHAR(13)
SET @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' +CHAR(10)+CHAR(13)
 
INSERT INTO #temp
	EXECUTE (@SQL);
SET @SQL = ''
 
INSERT INTO #DBCCRes
        ( DBName, dbccLastKnownGood,RowNum )
	SELECT @DBName, VALUE
			, ROW_NUMBER() OVER (PARTITION BY Field ORDER BY VALUE) AS Rownum
		FROM #temp
		WHERE field = 'dbi_dbccLastKnownGood';
 
TRUNCATE TABLE #temp;
 
FETCH NEXT FROM dbccpage INTO @DBName;
END
CLOSE dbccpage;
DEALLOCATE dbccpage;
 
SELECT DBName,dbccLastKnownGood
	FROM #DBCCRes
	WHERE RowNum = 1;
 
DROP TABLE #temp
DROP TABLE #DBCCRes

You can use this or find something else if you desire.  The sole purpose of this script is to provide a means to gather quickly the last date known to report a clean bill of health from Checkdb.  From there, I will leave it to you to add it to another process in order to track over the long term.

You can read the twitter conversation here.  I also have it on good authority that Bob Pusateri (twitter) will be blogging about the same topic.

Maintenance Plan Owner

Comments: 5 Comments
Published on: June 25, 2012

We all inherit things from time to time through our profession.  Sometimes we inherit some good things, sometimes we inherit some things that are not so good.  Other times we inherit some things that are just plan annoying.  Yet other times, we inherit things that may be annoying and we probably just haven’t discovered them yet.

Dizzying, I know.

Inheritance

Have you ever taken over a server that had several maintenance plans on it?  Have you ever really checked who the owner of those plans is?  Or, maybe you had a failing job relating to one of these maintenance plans and you changed the job owner, but did you really fix the root cause?  That could be one of those things that you inherited that could be annoying but you just don’t know it yet.

Step by Step

No this is not New Kids on the Block (I think I just threw up in my mouth thinking that).

Let’s create a generic maintenance plan and see what happens.

The first thing we do is navigate to Maintenance Plans under the Management menu in Management Studio.  Right Click the Maintenance Plan folder and select New Maintenance Plan… from the context menu.  This will prompt us with the following dialog box.

In this box, we can type a name for this Maintenance Plan that is to be created.  I chose MaintPlanOwner, since that is the topic of this article.

 

 

After clicking ok on this dialog box, you will be presented with a blank canvas with which to design your maintenance plan.  I have chose a simple task for the purposes of this article.

I will create a subplan named Statistics and add the Update Statistics task to the canvas.

You can see this illustrated to the left.  I chose to update the statistics on all databases and left all other options as the default option – for simplicity of this article.

At this point, the only thing left to do is to save this Maintenance Plan.  Once the plan is saved, then we can move on to the next step – some fun with TSQL.

 

 

 

 

Fun with TSQL

This is the stage of the article where we get to play with TSQL and investigate at a high level the Maintenance Plan we just created.

Within the msdb database, we have some system tables that store information about SSIS packages, DTS packages, and Maintenance Plans.  We will be investigating from a SQL 2008 and SQL 2005 standpoint (it changed in 2005 and then again in 2008).

In SQL 2005, we can query the sysdtspackages90 and sysdtspackagefolders90 to gain insight into who owns these Maintenance Plans.  In SQL 2008 and up, we can query sysssispackages and sysssispackagefolders to gain the same insight.  These system tables are within the msdb database.

In SQL 2005, we can use the following to find that I am now the owner of that maintenance plan we just created.

USE msdb;
GO
SELECT p.name AS PackageName,p.createdate,pf.foldername AS PackageType,sp.name AS PrincipalName
	FROM msdb.dbo.sysdtspackages90  p
	INNER JOIN dbo.sysdtspackagefolders90 pf
		ON p.folderid = pf.folderid
	INNER JOIN master.sys.server_principals sp
		ON p.ownersid = sp.sid
WHERE pf.foldername = 'Maintenance Plans';

For 2008, a slight modification yields the following query that returns the same results.

USE msdb;
GO
SELECT p.name AS PackageName,p.createdate,pf.foldername AS PackageType,sp.name AS PrincipalName
	FROM msdb.dbo.sysssispackages  p
	INNER JOIN dbo.sysssispackagefolders pf
		ON p.folderid = pf.folderid
	INNER JOIN master.sys.server_principals sp
		ON p.ownersid = sp.sid
WHERE pf.foldername = 'Maintenance Plans';

 

Notice that in both queries, I delve out in the sys.server_principals catalog view.  I did this to retrieve the name of the owner of the package that was found in the sysdtspackages90 and sysssispackages  tables respective to version of SQL Server.  These queries would yield the following result set for that new Maintenance Plan that was just created.

Caveat

Let’s assume that this package is scheduled via a SQL Agent job on a production server.  I then get moved to a different department and no longer have permissions on this particular production server.  The job will start failing due to the principal not having access.  One fix would be to change the owner of the job.

That will work.  However, there is a problem with that fix.  As soon as somebody opens and saves the Maintenance Plan, the owner of the job will revert back to the owner of the Maintenance Plan.  When that happens, then the job will fail again.

A permanent fix is needed.  The permanent fix is to change the owner of the Maintenance Plan.  The following will change the owner to ‘sa’ for both SQL 2005 and SQL 2008 (and up).

SQL 2005

USE msdb;
GO
UPDATE p
SET ownersid = 0x01
FROM msdb.dbo.sysdtspackages90  p
	INNER JOIN dbo.sysdtspackagefolders90 pf
		ON p.folderid = pf.folderid
WHERE pf.foldername = 'Maintenance Plans';

SQL 2008

USE msdb;
GO
UPDATE p
SET ownersid = 0x01
FROM msdb.dbo.sysssispackages  p
	INNER JOIN dbo.sysssispackagefolders pf
		ON p.folderid = pf.folderid
WHERE pf.foldername = 'Maintenance Plans';

Now if you run the code used earlier to investigate, you will find that the owner has indeed changed.  The results of that query should be similar to the following.

There you have it.  No more hair tugging over something as benign as the owner of a Maintenance Plan.  This is one of those things that should be looked at as soon as you inherit a new server.

Seldom Used Indexes

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: 7 Comments
Published on: March 20, 2012

On occasion you may ask yourself if there are any under used indexes in your database.  If not you, then possibly a manager or client.  Usually this comes up when evaluating for missing indexes or better indexes.

SQL Server provides a means to find the information we seek.  We can query the sys.dm_db_index_usage_stats DMV  to garner much of the information we want.  You can read about this DMV here.

The columns we want to take a look at are the seeks, scans and lookups columns.  In conjunction with that, we want to compare those columns to the updates related columns.

Here is an example query.

SELECT TableName = OBJECT_NAME(s.OBJECT_ID),IndexName = i.name, i.index_id
        ,TotalUserWrites = user_updates
        ,TotalUserReads = user_seeks + user_scans + user_lookups 
        ,[DIFFERENCE] = user_updates - (user_seeks + user_scans + user_lookups) 
		,TotalSystemReads = s.system_seeks + s.system_scans + s.system_lookups 
		,TotalSystemWrites = s.system_updates 
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
	INNER JOIN sys.indexes AS i WITH (NOLOCK)
		ON s.[OBJECT_ID] = i.[OBJECT_ID]
		AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
	AND s.database_id = DB_ID()
	AND user_updates > (user_seeks + user_scans + user_lookups)
	And (user_seeks + user_scans + user_lookups + s.system_seeks + s.system_scans + s.system_lookups) = 0
	AND i.index_id > 1
ORDER BY [DIFFERENCE] DESC, TotalUserWrites DESC, TotalUserReads ASC OPTION (RECOMPILE);

In this query, I am looking at two main components for comparison.  I want to determine where there are more updates on an index than reads.  I also want to see only those indexes that have no reads.

Using a query like this can help you to narrow your search for under-performing indexes.  Indexes returned by this query tend to be more costly to maintain than the benefit they may be providing.  Starting with the indexes returned by this query, you can test and confirm the findings.

Agent Jobs Using SSIS

Categories: News, Professional, Scripts, SSC, SSSOLV
Comments: No Comments
Published on: March 15, 2012

This is a short script to help the DBA with documentation purposes.  This would come in handy especially in those cases where you are consulting or you have taken on a new job.

Suppose you want/need to find out what SQL Agent jobs are running SSIS packages.  This script will help to quickly identify those jobs.

SELECT DISTINCT j.name
	FROM msdb.dbo.sysjobs j
		INNER JOIN msdb.dbo.sysjobsteps js
			ON j.job_id = js.job_id
	WHERE js.subsystem = 'SSIS'
		OR js.command LIKE '%DTEXEC.exe%'

Table Size and Missing FK Indexes

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: March 14, 2012

I am bringing an oldie back with another twist.  I recently ran into the need to correlate information between a couple of different queries that I like to use.  The two scripts returned different pieces of data about tables in a database.  This information was table size and missing foreign key indexes.

I needed to combine the two queries due to a desire to create indexes on foreign keys based on table size.  The premise behind this was to get the biggest bang for the buck initially as we work toward optimizing a database.  We happened to know heading into this that some of the larger tables are the most heavily queried tables as well.

So, here is what I did to get that information quickly.

/* Part I */
--Drop Table #indstats
IF exists (SELECT * FROM tempdb.sys.objects WHERE name like '%#indstats%')
BEGIN
	DROP TABLE tempdb.dbo.#indstats
END
 
BEGIN
CREATE TABLE #indstats (
         IndStatsID INT PRIMARY KEY CLUSTERED
        ,database_id BIGINT
        ,index_id BIGINT
        ,IndexSizeMB DECIMAL(16,1)
        ,OBJECT_ID	BIGINT
);
END
 
INSERT INTO #indstats (IndStatsID,database_id,index_id,OBJECT_ID,IndexSizeMB)
        SELECT Row_Number() OVER (ORDER BY OBJECT_ID) AS IndStatsID
				,database_id,index_id,OBJECT_ID
				,CONVERT(DECIMAL(19,2),(SUM(ps.page_count))) * 8 /1024 AS IndexSizeMB
			FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'SAMPLED') ps
			GROUP BY database_id,OBJECT_ID,index_id;
 
/* Part II */
DECLARE @dbsize DECIMAL(19,2)
 
SET NOCOUNT ON
 
/*
**  Summary data.
*/
BEGIN
	SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
		FROM sys.database_files
 
END
 
/* Part III */
BEGIN
	WITH RegData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 1
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	)
	, LOBData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.partition_id = a.container_id
				And a.type = 2
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	)
	, OverFlowData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 3
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
	), IndexSum AS (
		SELECT a.OBJECT_ID
		,AllDataSizeMB = SUM(CASE WHEN a.index_id in (0,1)
							THEN IsNull(a.DataSizeMB,0) +  IsNull(p2.DataSizeMB,0) +  IsNull(p3.DataSizeMB,0)
							ELSE IsNull(p2.DataSizeMB,0) + IsNull(p3.DataSizeMB,0)
							END)
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
		GROUP BY a.OBJECT_ID
	), SummaryInfo AS (
		SELECT
			TableName = MAX(a.TableName)
			,InRowDataSizeMB = SUM(IsNull(a.DataSizeMB,0))
			,LOBDataSizeMB = SUM(IsNull(p2.DataSizeMB,0))
			,OFlowDataSizeMB = SUM(IsNull(p3.DataSizeMB,0))
			,NumRows = MAX(COALESCE(a.NumRows,p2.NumRows,p3.NumRows,0))
			,AllUsedPages = SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0))
			,AllPages = SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
			,FreeDataSpace = CONVERT(DECIMAL(19,2),
				SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
				- SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0)))* 8 / 1024
			,AllDataSizeMB = MAX(ids.AllDataSizeMB)
			,IndexSizeMB = SUM(IsNull(a.IndexSizeMB,0))
				+ SUM(IsNull(p2.IndexSizeMB,0)) + SUM(IsNull(p3.IndexSizeMB,0))
			,UserRequests = AVG(IsNull(a.UserRequests,0)
				+ IsNull(p2.UserRequests,0) + IsNull(p3.UserRequests,0))
			,UserUpdates = AVG(IsNull(a.UserUpdates,0) + IsNull(p2.UserUpdates,0) + IsNull(p3.UserUpdates,0))
			,LastUpdate = MAX(COALESCE(a.LastUpdate,p2.LastUpdate,p3.LastUpdate,null))
			,DatabaseSize = @dbsize
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
			LEFT Outer Join sys.indexes i
				ON i.OBJECT_ID = a.OBJECT_ID
				And i.index_id = a.index_id
			LEFT Outer Join IndexSum ids
				ON i.OBJECT_ID = ids.OBJECT_ID
		GROUP BY a.OBJECT_ID
	), TotalUnused AS (
				SELECT SUM(FreeDataSpace) AS UnusedSpace
					FROM SummaryInfo
		)
	SELECT TableName,ChildTableColumn AS MissingFKIndexColumn,NumRows,InRowDataSizeMB,LOBDataSizeMB,OFlowDataSizeMB
			,AllUsedPages,AllPages
			,FreeDataSpace,AllDataSizeMB,IndexSizeMB
			,TableSizeMB = AllDataSizeMB + IndexSizeMB + FreeDataSpace
			,UserRequests,UserUpdates,LastUpdate
			,PercentofDB = ((IndexSizeMB + AllDataSizeMB) / DatabaseSize) * 100
			,DatabaseSize
			,DataUsedSize = DatabaseSize - TU.UnusedSpace
			,PercentofDataFileUsed = ((IndexSizeMB + AllDataSizeMB) / (DatabaseSize - TU.UnusedSpace)) * 100
		FROM SummaryInfo SI
			CROSS APPLY TotalUnused TU
			Cross Apply (SELECT
								OBJECT_NAME(FK.parent_object_id) AS FKtable
								,OBJECT_NAME(Fk.referenced_object_id) AS ReferencesThisTable
								,OBJECT_NAME(FK.constraint_object_id) AS FKName
								,C.name ChildTableColumn
							FROM sys.foreign_key_columns FK
								INNER JOIN sys.columns C
									ON FK.parent_object_id = C.OBJECT_ID 
									AND FK.parent_column_id = C.column_id
								INNER JOIN sys.objects O
									ON FK.parent_object_id = o.OBJECT_ID
								LEFT OUTER JOIN sys.index_columns ic
									ON FK.parent_object_id = ic.OBJECT_ID
									AND FK.parent_column_id = ic.column_id 
							WHERE ic.OBJECT_ID IS NULL) FK
		WHERE Fk.fktable = SI.TableName
		ORDER BY PercentofDB DESC
END

The change is not overly much.  I added a subquery via a cross apply to get the missing foreign key info.  Then I return the pertinent columns back to the Select query.

«page 1 of 4




Calendar
April 2014
M T W T F S S
« Mar    
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp


Welcome , today is Tuesday, April 15, 2014