Add the script below to the file:
In 2008R2
...\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Pages\Report.aspx
In 2012
...\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportManager\Pages\Report.aspx
Wednesday, April 13, 2016
Tuesday, March 22, 2016
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
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
Monday, February 8, 2016
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
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
Thursday, December 17, 2015
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
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;
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;
Wednesday, July 22, 2015
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
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';
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';
Friday, July 17, 2015
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:
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
Subscribe to:
Posts (Atom)