The alert for 'time behind' has been raised. The current value of 'xxx' surpasses the threshold 'xxx'.

The alert for 'time behind' has been raised. The current value of 'xxx' surpasses the threshold 'xxx'.


Usually the above error will report only when there is unsent log has stuck up in the principal server.

This can be cause when there is heavy load in the principal server or if there is any network slowness between principal and mirror server and couple of other things.

I encountered the above error on the principal server and error number is 32040 is detected in the event viewer,

First if you want to identify which database is not in sync, to do that you need to launch database mirroring monitor wizard and add all the databases and verify the database which has unsent log

Second Launch mirroring monitor and add the problematic database and verify the sync status with the mirror server.

If you dont find any unsent log and unsent log is showing zero (0) kb, then I used Pause and Restart mirroring to fix the issue.

Alter Database KalyanDB Set Partner Suspend  -- It pauses the mirroring on Kalyandb
Alter Database KalyanDB Set Partner Resume  -- It resumes the mirroring on Kalyandb

If you want to do it from SSMS, you can do that by using the database properties window.

Insert values into Identity column in sql server

How to insert values into identity column in sql server

If you create any table with identity column, SQLServer automatically assigns value to that column when a new row is inserted, If you want to explicitly insert a new record then we can insert by using
the below option

SET Identity_Insert table_name ON

Below is the example to demonstrate

-- Create Names table.
CREATE TABLE dbo.Names( Slno INT IDENTITY NOT NULL PRIMARY KEY, Name VARCHAR(40) NOT NULL )
GO
-- Inserting values into products table.
INSERT INTO dbo.Names(Name) VALUES ('Kalyan')
INSERT INTO dbo.Names(Name) VALUES ('Hari')
INSERT INTO dbo.Names(Name) VALUES ('Ravi')
INSERT INTO dbo.Names(Name) VALUES ('Vishnu')
INSERT INTO dbo.Names(Name) VALUES ('Satya')
GO

select * from Names  -- List all names with their slno's

delete from Names where Name='Hari'  -- Here we are removing record of slno =2

-- Now if you insert a new record the record number will be 6, Let see
Insert into Names values ('Sandeep')
select * from Names  -- Verify the slno for newly inserted record, It will be 6

-- If you want to fill the gap record then we will use the below commands, We need to switch on Identity_Insert = ON on table name

SET IDENTITY_INSERT dbo.Names ON

Insert into Names (slno, name )values (2, 'Hari')

SET IDENTITY_INSERT dbo.Names OFF


If you directly tried without IDENTITY_INSERT ON then you will encounter the below error message

Cannot insert explicit value for identity column in table 'Names' when IDENTITY_INSERT is set to OFF.
 

File and Filegroup fill pattern

File and Filegroup fill pattern -- SQL server uses proportional fill algorithm to keep the free space within a filegroup evenly distributed across all the files in the filegroup which are located at different drives.

It uses the round-robin mechanism to for filling in files, for example first it fills file1 and file2 and so on then it come back to file1 again, Along with that it uses proportional fill algorithm which means if file1 has occupied with 1 GB space and file2 is filled with 500 Mb space while filling the files it allocates 1 extent to file1 and 2 extents to file2.

If a database has two files in F: drive which is almost full, due to various limitations we may not increase the storage for F:\drive 

The solution for this is we will add a new data file in G: drive, but due to proportionate fill algorithm the newly added ndf file will get more allocations when compared to other two files hence this file becomes IO hot spot.

We can rebuild the index to prevent a write hot spot on new file after adding a new file in the file group. This index step will proportional fill all files in the filegroup, if the file is added to new file group then we need to move the table to that file group, use create index with drop_existing=on by specifying filegroup name.

Note : This wont work if the table has LOB data.