How to stop the log shipping alert which sending the error message 14421?

How to stop the log shipping alert which sending the below error message?

Error Message:

Over the previous interval, there were 5 occurrences of the following event.

Logfile: Application
Event Type: Error
Event ID: 14421
Computer: XXXXXXXX
Source: MSSQL$SQL2008
Text:
The log shipping secondary database Servername \SQL2008.Databasename has restore threshold of 45 minutes and is out of sync.
No restore was performed for 9426 minutes. Restored latency is 8 minutes. Check agent log and logshipping monitor information.

Cause:

Database which involved in source as well in destination server for log shipping setup is not available. Because database dropped by somebody.

Resolution:

The database name entry is found in the below logshipping tables that are referencing the alert jobs LSAlert_ XXXXXXXX \SQL2008 and LSAlert_YYYYYYY\SQL2008 in both primary and secondary server.

Action Taken:

 By removing the record entry in the below log shipping tables corresponding to the log shipping database, Alerts stating above error message 14421 won’t come anymore.

Select * from msdb.dbo.log_shipping_monitor_primary
select * from msdb.dbo.log_shipping_monitor_secondary

Delete from msdb.dbo.log_shipping_monitor_primary where primary_database='logshippingdatabasename'

Delete from msdb.dbo.log_shipping_monitor_secondary where secondary_database='logshippingdatabasename'

How to do any activity like select, delete or truncate for all tables in a databases?

Use databasename Go sp_tables

Filter the above query output by TABLE_TYPE AS TABLE.

Then copy all table_name column values from the results in SSMS and paste it in the excel sheet columns.

Then join table_name with below statements in another excel column.

Lets take how to truncate all tables in a database.

="TRUNCATE TABLE ” &A1 &" "

Employeesdetails TRUNCATE TABLE  Employeesdetails

Any other method to do the above activity.

How to check existing job is scheduled or not in sql server?

How to check existing job is scheduled or not in sql server?

By running below stored procedure in sql server management studio,we can able to find job schedule details.

Exec msdb..sp_help_jobschedule @job_name='DBA Maintenance plan job’

If it shows row means, job scheduled else not.

Steps for connection related issues in sql server

Please make use of below steps for connection related issues in sql server

Step1:

First check below ping information related to the specific server.

C:\Documents and Settings\username>ping servername

Pinging servername.xx.yyy.com [12.345.67.890] with 32 bytes of data:

Reply from 12.345.67.890: bytes=32 time=96ms TTL=122

Reply from 12.345.67.890: bytes=32 time=95ms TTL=122

Reply from 12.345.67.890: bytes=32 time=95ms TTL=122

Reply from 12.345.67.890: bytes=32 time=96ms TTL=122

Ping statistics for 12.345.67.890:

    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

Approximate round trip times in milli-seconds:

    Minimum = 95ms, Maximum = 96ms, Average = 95ms

--------------------------------------------------------------------------------

C:\Documents and Settings\ username >ping -a 12.345.67.890

Pinging servername [12.345.67.890] with 32 bytes of data:

Reply from 12.345.67.890: bytes=32 time=97ms TTL=122

Reply from 12.345.67.890: bytes=32 time=95ms TTL=122

Reply from 12.345.67.890: bytes=32 time=95ms TTL=122

Reply from 12.345.67.890: bytes=32 time=95ms TTL=122

Ping statistics for 12.345.67.890:

    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

Approximate round trip times in milli-seconds:

    Minimum = 95ms, Maximum = 97ms, Average = 95ms

---------------------------------------------------------------------------------

If I ping server name, it show server name with fully qualified name. If I reverse ping with ip address, it show only server name not with fully qualified name.

We couldn't able to find fully qualified name along with server name.

 Step2:

Resolution:

Ask DNS support team to make changes in DNS entry to resolve above reverse ping issue.

After that change, we can able to connect SQL server with no issues.

Duplicate records finding in all tables in a database.

 Duplicate records finding in all tables in a database.

--To find all userdefined tables in the database.

Use databasename

GO

select * from sysobjects where xtype='u'

--then copy the tables in excelsheet column and calculate using below formula in --another excel column

="select id from "&A1&" group by id where having count(*)>1 "

--Id is the primary key column name,A1 is the table cell name.

Finally execute the all tables select query collections from the excel sheet to the database.

Select id from employee group by id  having count(*)>1

Select id from employee1 s group by id  having count(*)>1

Select id from employee2 group by id  having count(*)>1

Select id from employee3 group by id  having count(*)>1

--if any records come,it seems to be presence of duplicate records…

Is any other method to find?

Give Access to check Activity monitor and to run SQL Profiler for non sysadmin login.

Scenario:

Need to get permission on the server that will enable to see the activity monitor and to work with the SQL Server Profiler on sql server.

Those permission are needed for testing the work done on the Test DB

--------------------------------------------------------------------------------------------

How to give access to check Activity monitor?

USE master;

GRANT VIEW SERVER STATE TO [windowslogin]

How to give access to run Sql Profiler on the server to one windows login i.e. non sysadmin?

USE master;

GRANT ALTER TRACE TO [windowslogin]

Or

Security tab --> Logins --> right click name to modify select properties --> Securables tab --> Click add --> Select the server you want to add the permissions to. The permissions should appear in the effective permissions list

You need to check the Grant column for the required permissions.

How to grant permission to view database objects definition?

Scenario:

Customer asked DBA to grant permission to view recently created stored procedure script in SQL.

How will we do?

Solution:

For example the stored procedures sp1, sp2 and sp3 are created recently. User asking view access only for stored procedure objects .Not executes permission.

How will you do?

By using this query, we can do.

USE databasename

GO

GRANT VIEW DEFINITION ON sp1 TO sqlloginname;

GRANT VIEW DEFINITION ON sp1 TO sqlloginname;

GRANT VIEW DEFINITION ON sp3 TO sqlloginname;

View definition role allows user to view the corresponding objects

Any other methods?