Find the Number of Rows in Database Tables

Had a need to track empty tables in a DB to see if converted systems were correctly hooked up and wanted to add filters.Sorry I didn't keep the link to the original website.

SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sdmvPTNS.row_count) AS [RowCount]
FROM sys.objects AS sOBJ
INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
ON sOBJ.object_id = sdmvPTNS.object_id
WHERE 
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND sdmvPTNS.index_id < 2
--Find all by schema
-- AND SCHEMA_NAME(sOBJ.schema_id) = ''
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
--Only find empty tables
--HAVING SUM(sdmvPTNS.row_count) = 0
ORDER BY [TableName]
, [RowCount];
GO

Add Log Percent Used Alert to all non System DBs

in my never ending quest to automate more monitoring I have added this to my pre-production deployment package. It assumes a Operator group of the name DBgroup has been created to transmit the alerts.
Also Database Mail must be set up on the agent.

USE [msdb]
GO

DECLARE @RemoveExisting as bit = 1; --Set to 1 to purge any existing

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('model','master', 'msdb','tempdb')

DECLARE @DBname nvarchar(max) = '';
DECLARE @AlertName nvarchar(max) = '';
DECLARE @AlertText nvarchar(max) = '';

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @AlertName = @DBname  + N'_LogPercentUsed';
SET @AlertText = N'Databases|Percent Log Used|' + @DBname + '|>|90';

IF EXISTS(select id from sysalerts where name = @AlertName) AND  @RemoveExisting = 1
EXEC msdb.dbo.sp_delete_alert @AlertName;

IF NOT EXISTS(select id from sysalerts where name = @AlertName)
BEGIN
EXEC msdb.dbo.sp_add_alert @name= @AlertName ,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=1715,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition= @AlertText
, @job_id=N'00000000-0000-0000-0000-000000000000';

EXEC msdb.dbo.sp_add_notification @alert_name= @AlertName, @operator_name=N'DBGroup', @notification_method = 1;

END

  FETCH NEXT FROM db_cursor INTO @DBname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Fail a jobs is any pre-requisite jobs have failed

USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
Used to detemine if any jobs in a category have failed
All jobs must be assigned to the same category

Parameters:
@JobCategoryName - The category name set up in hte local instance
@HoursToCheck - the number of hours back in history you want the call to look. DEFAULT = 24
@IsSuccessful - Whether or not any jobs meet the criteria
To fine all failed jobs put 0

RETURNS
0 = No Jobs in the tested status
1 = Jobs exist in the tested status

Example of use to fail a job when any jobs in the category have a failure
IF (SELECT [dbo].[CheckJobCategoryStatus] ('SAP_to_staging', 24, 0)) = 1
  BEGIN
    RAISERROR('SAP_to_staging pre-requisite has failed. Re-run all failed prerequisites', 16, 1)
  END
*/

CREATE FUNCTION [dbo].[CheckJobCategoryStatus]
(@JobCategoryName nvarchar(50)
, @HoursToCheck int = 24
, @IsSuccessful bit = 0
)
RETURNS bit
AS 
BEGIN
--DECLARE @JobCatagory nvarchar(50) = 'SAP_to_staging';
--DECLARE @HoursToCheck int = 24;
--DECLARE @IsSuccessful bit = 1;

DECLARE @IsCategoryStatusTrue bit = 0;

IF EXISTS(SELECT
SJ.NAME AS [Job Name]
,RUN_STATUS AS [Run Status]
,MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
FROM dbo.SYSJOBS SJ
LEFT OUTER JOIN dbo.SYSJOBHISTORY JH
ON SJ.job_id = JH.job_id
WHERE JH.step_id = 0
AND jh.run_status = @IsSuccessful
and category_id IN (select category_id
from dbo.syscategories
where name = @JobCategoryName)
and DATEDIFF(HH , DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME), getdate()) <= @HoursToCheck
GROUP BY SJ.name, JH.run_status)
SET  @IsCategoryStatusTrue = 1;
ELSE
SET  @IsCategoryStatusTrue = 0;
RETURN @IsCategoryStatusTrue;
END


GO

Monitor Job Satus through script

Had a need to verify prerequisite site had run prior to downstream asynchronous jobs so I found and modified a couple queries.

USE [msdb]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
Used to detemine if any jobs in a category have failed and list them
All jobs must be assigned to the same category

Parameters:
@JobCategoryName - The category name set up in hte local instance
@HoursToCheck - the number of hours back in history you want the call to look. DEFAULT = 24
@IsSuccessful - Whether or not any jobs meet the criteria
To fine all failed jobs put 0

RETURNS
Table with 
Job Name
Success or Failure status
Date Time last run

Example of a table of all failed jobs in a category
SELECT * FROM [dbo].[JobCategoryStatusList] ('SAP_to_staging', 24, 1)
*/


CREATE FUNCTION [dbo].[JobCategoryStatusList]
(@JobCategory nvarchar(50)
, @HoursToCheck int = 24
, @IsSuccessful bit = 0
)
RETURNS @tAllJobsInStatus TABLE
(
JobName nvarchar(128)
, RunStatus bit
, LastTimeJobRan datetime
)
AS
BEGIN
--DECLARE @JobCatagory nvarchar(50) = 'SAP_to_staging';
--DECLARE @HoursToCheck int = 24;
--DECLARE @IsSuccessful bit = 1;
INSERT INTO @tAllJobsInStatus
SELECT
SJ.NAME AS [Job Name]
,RUN_STATUS AS [Run Status]
,MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
FROM dbo.SYSJOBS SJ
LEFT OUTER JOIN dbo.SYSJOBHISTORY JH
ON SJ.job_id = JH.job_id
WHERE JH.step_id = 0
AND jh.run_status = @IsSuccessful
and category_id IN (select category_id
from dbo.syscategories
where name = @JobCategory)
and DATEDIFF(HH , DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME), getdate()) <= @HoursToCheck
GROUP BY SJ.name, JH.run_status
RETURN
END;

Yet a more improve bulk DB set up file

This on attempt to do more and protect the system DBs

This script will:
1. Change the DB owner
2. Set all to simple mode. Cannot figure out how to stop the tempdb message
3. Set all DBs to 2012 mode
4. Add the domain users as owners. Please use this as a prototype for other test and dev users
5. Shrink all logs to 500 MB to ensure there is plenty of space

Left to do standardize file growths, block create user when the user exists

USE Master
GO


DECLARE @prefix nvarchar(120) = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN  ';
DECLARE @postfix nvarchar(50) = ' END';
DECLARE @cmd nvarchar(512);

SET @cmd =  @prefix + 'PRINT ''?''; USE ? exec sp_changedbowner sa ' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd
PRINT 'Successfully set DB owner to sa';


SET @cmd =  @prefix + 'ALTER DATABASE ? SET RECOVERY SIMPLE;' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Set all DBs to SIMPLE';

SET @cmd =  @prefix + 'ALTER DATABASE ? SET COMPATIBILITY_LEVEL = 110;'+ @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Upgrade all DBs to 2012';

SET @cmd =  @prefix + 'Use ?; CREATE USER [[DOMAIN]\[USER]] WITH DEFAULT_SCHEMA = dbo;' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Add group [DOMAIN]\[USER]';

SET @cmd =  @prefix + 'Use ?; ALTER ROLE db_owner ADD MEMBER [[DOMAIN]\[USER]];' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Add db_owner [DOMAIN]\[USER]';

SET @cmd =  @prefix + 'Use ?; DBCC SHRINKFILE (N''?_log'' , 500);' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Shrink all logs to 500 MB';

SQL Connection Error: "The target principal name is incorrect. Cannot generate SSPI context"

Welcome to the rabbit hole.

This can be caused when there are more than one entry for a SQL Server entry in Kerberos. Sometimes it is caused when a SQL Server is installed under one domain user and is then is switch to another.

Technet article: How to troubleshoot the "Cannot generate SSPI context" error message
https://support.microsoft.com/en-us/kb/811889?wa=wsignin1.0 

Really good description but no examples:
How Windows Server 2012 Eases the Pain of Kerberos Constrained Delegation, Part 2

Basically you delete the existing entries and make new ones. You have to be an AD admin to make the deletions.

Commands of use:

List Command 

setspn -L [Machine name if default instance]

C:\windows\system32>setspn -L wkonedev01

Registered ServicePrincipalNames for CN=WKONEDEV01,OU=Member Servers,DC=******,

DC=com:

        MSSQLSvc/WkOneDev01.******.com:1433

        MSSQLSvc/WkOneDev01.******.com

        WSMAN/wkonedev01.******.com

        TERMSRV/wkonedev01.******.com

        RestrictedKrbHost/wkonedev01.******.com

        HOST/wkonedev01.******.com

        WSMAN/WKONEDEV01

        TERMSRV/WKONEDEV01

        RestrictedKrbHost/WKONEDEV01

        HOST/WKONEDEV01

Delete Command

setspn -D MSSQLsvc/[Machine Name].[Domain].com:1433 [Domain]\[Domain User Name]

 C:\windows\system32>setspn -D MSSQLsvc/wkonedev01.******.com:1433 ******\wkone

dev01server

Unregistering ServicePrincipalNames for CN=wkonedev01Server,OU=Service Accounts,

DC=*******,DC=com

        MSSQLsvc/wkonedev01.*******.com:1433

Updated object

Safe Add Command

setspn -S MSSQLsvc/[Machine Name].[Domain].com:1433 [Domain]\[Domain User Name]

C:\windows\system32>setspn -A MSSQLsvc/wkonedev01.*******.com:1433 ********\wkone

dev01server

Registering ServicePrincipalNames for CN=wkonedev01Server,OU=Service Accounts,DC

=********,DC=com

        MSSQLsvc/wkonedev01.********.com:1433

Updated object

After the commands make sure AD is given time to update the DNS then run

C:>ipconfig /flushdns

C:>ipconfig /renew

Adding Bulk Rights SQL 2005

Was trying to do some quick administration and realized the SQL Server 2005 had a different method than 2012. Do not put any [] brackets in the user name. However, make sure you put brackets around the DB name especially with Sharepoint to handle the dashed in the DB name.


DECLARE @cmd nvarchar(255);

--first create user
SET @cmd = 'IF ''[?]'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] CREATE USER [domain\user] FOR LOGIN [domain\user]''  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd

--second assign role
SET @cmd = 'IF ''[?]'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] exec sp_addrolemember ''db_owner'', ''Domain\User''  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd