• About

sqlerrors

~ From the errors of others, a wise man corrects his own

sqlerrors

Monthly Archives: December 2013

Rebuild or Reorganize: Smart SQL Server Index Maintenance

30 Monday Dec 2013

Posted by Praveen D'sa in Index

≈ Leave a comment

Tags

Alter Index, fragmentation, index fragmentation, index maintenance, Index Rebuild, Index Reorganize, indexes

To keep the indexes in proper shape, a timely defragmentation is necessary. There is no any thumb rule when to defragment the indexes, it depends on the environment. However, this script could be scheduled as per ones need to make sure that the indexes gets defragmented when need.

Firstly do not blindly REBUILD every index which not only increases your I/O’s but also create huge amount of T-log, and if you have enabled mirroring or log shipping then it would take considerable time to ship these logs, defragment your indexes smartly.

In our environment we follow the index maintenance as given below:

If fragmentation is less than 10% or index page count is less than 100 no action will be taken.

If fragmentation is between 10-30% then the index will be reorganized and statistics will be updated. (REORGANISE index will not update STATISITCS automatically)

If fragmentation is greater than 30% index will be rebuilt.

You can also download this script from Microsoft Gallery

Hope this helps.!

Advertisement

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Merry Christmas and Best Wishes for the New Year!!

24 Tuesday Dec 2013

Posted by Praveen D'sa in Uncategorized

≈ Leave a comment

sqlerrorsA big thank you for everyone who supported me and the many visitors
who have come along to read and comment on my Blog in 2013.
I hope you all return here again in 2014.
May you have a wonderful Christmas with your loved ones and enjoy all the blessing the season brings.
Merry Christmas and Best Wishes for the New Year!!

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Log shipping false errors messages – 14420

09 Monday Dec 2013

Posted by Praveen D'sa in Log Shipping, SQL Server

≈ Leave a comment

Tags

@@ServerName, error 14420, log shipping, log shipping status, SERVERPROPERTY, sp_addserver, sp_dropserver

Problem:
We recently migrated our SQL Server 2008 R2 and SAN storage to the new infrastructure, I have log shipping setup on few databases and to retain all the jobs I restored MSDB database on the new server, post migration I synced primary and secondary servers, enabled all log shipping related jobs and the entire process was successful, but few hours later Primary Server started generating alerts, error 14420 “The log shipping primary database [ServerName].[DatabaseName] has backup threshold of 60 minutes and has not performed a backup log operation for 120 minutes.”

Investigation:
I started my investigation with sp_readeerorlog, I found a lot of error messages as below.
sp_readerrorlg

To confirm my log shipping status I checked Log backup , log copy and log restore jobs and all were running successfully, my last log backup was 15 min back and it was successfully restored on secondary server as shown below.

On Primary Server.
log_shipping_monitor

Secondary Server.
log_shipping_history

Now I am pretty sure there is no problem with log shipping, but why server generating alerts, on further investigation using following query on Primary server.

Select * from msdb.dbo.log_shipping_monitor_primary

last_backup_date column was not at all updating with latest dates and it was showing date and time of last backup just before we migrated to new server.
This helped me a lot for my further investigation, and somewhere I read a suggestion just to check my server name.

Select ServerProperty('ServerName'), @@ServerName

Above query returned two different names,ServerProperty(‘ServerName’) returned actual server, whereas @@ServerName returned the name we had assigned to the server during SQL Installation(SQL Server Setup sets the server name to the computer name during installation )

Solution:
When you run following queries all should return you same server name.

Select ServerProperty('ServerName')
Select @@ServerName
Select primary_server from msdb.dbo.log_shipping_monitor_primary

This happens if you install SQL Server and later you change Server name, to solve my problem I have to drop old server name and add it again with new server name as shown in the following queries

EXEC sp_dropserver 'OLD_SERVER_NAME'
EXEC sp_addserver 'NEW_SERVER_NAME', 'local'

You need to restart your SQL Service for changes to take effect, no more error messages now.

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Recent Posts

  • What’s wrong with this query? July 30, 2015

Categories

  • Learning (2)
  • MS SQL Tips (3)
  • Performance Tuning (1)
  • SQL Script (5)
  • SQL Server (14)
    • Index (1)
    • Log Shipping (1)
  • SQL Tips & Tricks (1)
  • Uncategorized (5)

Archives

  • July 2015 (2)
  • February 2015 (1)
  • October 2014 (2)
  • September 2014 (1)
  • May 2014 (1)
  • April 2014 (3)
  • March 2014 (2)
  • February 2014 (2)
  • January 2014 (1)
  • December 2013 (3)
  • November 2013 (9)

sqlerrors

sqlerrors

Disclaimer:

This is my personal blog. The opinions & suggestions expressed here are
my own. For accuracy and official references please refer to MSDN,Microsoft TechNet,
Books Online. I do not endorse any of the tools / applications / books / concepts
mentioned here on my blog. I have simply documented my personal experiences on this
blog.

Create a free website or blog at WordPress.com.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • sqlerrors
    • Already have a WordPress.com account? Log in now.
    • sqlerrors
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

    %d bloggers like this: