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.

Introduction to Powershell

What is Powershell?

Powershell is a command line interface like DOS (Disk Operating System). It is a powerful scripting language which is mainly used to automate tasks.

This language is based on .NET Framework. Powershell provides full access to COM and WMI enabling administrators to perform administrative tasks.

In Powershell commands are known as command-lets, (Cmdlets), These cmdlets are .NET classes that appears as commands in powershell.

Powershell cmdlets have verb-noun syntax (get-alias, get-childitem, get-help, get-host, set-variable, set-date etc)

Few cmdlets

get-help
get-member
get-childitem
push-location
pop-location
set-location
 

How to add user and grant access to secondary database server on log shipping?

How to add user and grant access to secondary database server on log shipping?

If database is configured in logshipping the secondary server is in STANBY recovery mode. Because of this secondary server database was in read-only mode where you cannot add user to secondary database.

Workaround

1. Create Login in the primary server
USE [master]
GO
CREATE LOGIN [Kalyan_RO] WITH PASSWORD=N'kalyan_ro123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [KalyanDB]
GO
CREATE USER [Kalyan_RO] FOR LOGIN [Kalyan_RO]
GO
USE [KalyanDB]
GO
EXEC sp_addrolemember N'db_datareader', N'Kalyan_RO'
GO
With the above step you created SQL login in Primary server and provided read access on KalyanDB.

2. Create the login with same name in Secondary Server  (Here you need to remember one thing, even though you a login is created with same name in secondary server you wont get same SID, becoz SIDs will be different in primary and secondary servers)

For that reason you need to get the SID from primary server and create the same login in secondary server with same SID.

Before creating login in secondary server, use the below query to get the SID of the login
select name, sid from master..syslogins where name='Kalyan_RO'
Now use the below syntax to create the login in secondary server with same SID

CREATE LOGIN [Kalyan_RO] WITH PASSWORD= N'kalyan_ro123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, SID=0x8389C417AA312E499C03DC4AE914899E

Using the above script login will be created and user rights will be mapped to it after log backup was restored on secondary server. User level mappings will come thru log backup as that is database level.

Powershell : Profile Examples

Powershell Profile -- If we place any .ps1 powershell file in this profile location that file will run everytime when powershell startup.

To know the profile location

PS> $profile  # It displays full path of the powershell profile. It shows Microsoft.Powershell_profile.ps1 file along with path that means that file doesn't have to exist, If you create profile the system will create fil in that path

C:\Users\kalyan\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1

# We can check whether the profile is already exists or not using test-path, If the file exists it returns true otherwise false

PS > test-path $Profile
False

### Using the below syntax we can creat powershell profile ###
Type the below command and hit enter key to create profile

PS > New-Item -Type file -Path $profile -Force
    Directory: C:\Users\kalyan\Documents\WindowsPowerShell

Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---          4/9/2014   3:46 PM          0 Microsoft.PowerShell_profile.ps1

# Now test-path returns true

PS > test-path $Profile
True

You can browse the profile file using the above path and include the below string and save the file, Now open the powershell It displays good day to you everytime when you open powershell
echo "good day"