We need to include the NORESEED option when running DBCC CHECKIDENT to find the current identity value

Without NORESEED option if we run DBCC CHECKIDENT, then it checks the current identity value and if the identity and current value don’t match, it reseeds the identity.

USE AdventureWorks2012;

GO

DBCC CHECKIDENT (‘Person.AddressType’);

GO

If your intent is to find the current identity value, then we will have to include NORESEED option.

USE AdventureWorks2012;

GO

DBCC CHECKIDENT (‘Person.AddressType’, NORESEED);

GO

SQL Server job step failed with SQLSTATE 01000] (Message 7989) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Checking identity information: current identity value ‘-1’.

Today suddenly SQL server job step failed with message:

[SQLSTATE 01000] (Message 7989)  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

[SQLSTATE 01000] (Message 2528)  Checking identity information: current identity value ‘-1’.

Same step was running with success till yesterday with the above message as info type message.

In SQL Server How to create job and stored procedure to check and notify any long running jobs every 5 hours

You want the server to notify you that there is a job which is running for more than 5 hours.

Create a SQL server recurring job to run every 5 hours and run the following stored procedure:

create PROCEDURE [dbo].[NotifyLongRunningJobs]

AS
BEGIN
SET NOCOUNT ON;
Declare @jobcount int = 0;
Declare @message nvarchar (max);
Declare @joblist nvarchar (max);
SELECT @jobcount = count(distinct (j.name ))
FROM msdb.dbo.sysjobactivity a INNER JOIN msdb.dbo.sysjobs j
ON a.job_id =j.job_id
WHERE CONVERT(DATE,a.start_execution_date )=CONVERT(DATE,GETDATE ())
AND DATEDIFF(HH,start_execution_date,GETDATE())> 5
AND a.stop_execution_date IS NULL

select @jobcount

if @jobcount>0
Begin
–Create Email text
select @joblist = STUFF(
(

SELECT ‘ , ‘ + CONVERT(NVARCHAR(1000), j.name)
FROM msdb.dbo.sysjobactivity a INNER JOIN msdb.dbo.sysjobs j
ON a.job_id =j.job_id
WHERE CONVERT(DATE,a.start_execution_date )=CONVERT(DATE,GETDATE ())
AND DATEDIFF(HH,start_execution_date,GETDATE())> 5
AND a.stop_execution_date IS NULL

FOR xml path(”)

)
, 1
, 1
, ”)
select @message = N'<FONT face=”Calibri” size=”3″><H3>Long Running Job List</H3>’ +
‘<BR>’ +
@joblist +
‘<P></FONT>’;
–Send Email

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘EMAIL PROFILE’,
@recipients = ‘abc@xyz.com’,
@subject = ‘Long Running Job List’,
@body = @message,
@body_format = ‘HTML’ ;

End
END

How to grant access to couple of tables in sql server database

The best way is to create role and grant the needed access to the needed tables using that role:

CREATE ROLE CoupleOfTablesAccessRole
GRANT SELECT ON [dbo].[Table1] TO CoupleOfTablesAccessRole;

GRANT SELECT,INSERT,UPDATE,DELETE ON [dbo].[Table2] TO CoupleOfTablesAccessRole;

USE [Database_Name]
GO
CREATE USER [ABC\xyz] FOR LOGIN [ABC\xyz] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [Database_Name]
GO
EXEC sp_addrolemember ‘CoupleOfTablesAccessRole’, N’ABC\xyz’

Various time and date datatypes in SQL server overview

Reference: Robert Hartmann  on SQLSERVERCENTRAL.COM 

Various time and date datatypes in SQL server:

Type Format Range Accuracy Storage in bytes User defined precision
time hh:mm:ss [.nnnnnnn] 00:00:00.0000000
through
23:59:59.9999999
100 nanoseconds 3 to 5 Yes
date YYYY-MM-DD 0001-01-01 through
9999-12-31
1 day 3 No
smalldatetime YYYY-MM-DD
hh:mm:ss
1900-01-01 through
2079-06-06
1 minute 4 No
datetime YYYY-MM-DD
hh:mm:ss [.nnn]
1753-01-01 through
9999-12-31
0.00333 seconds 8 No
datetime2 YYYY-MM-DD
hh:mm:ss [.nnnnnnn]
0001-01-01
00:00:00.0000000
through 9999-12-31
23:59:59.9999999
100 nanoseconds 6 to 8 Yes
datetimeoffset YYYY-MM-DD 0001-01-01
00:00:00.0000000
through 9999-12-31
23:59:59.9999999
(int UTC)
100 nanoseconds 8 to 10
(2 for
zone)
Yes