re-initialize replication not working

for someone re-initialization is very simple, just go to publisher and right click and select re-initiallize and it will ask for do you what to generate snapshot now, if you say “yes” it will create a metadata of the articles accotiated with that publisher at snapshot specified location(if its at distributor -would be good). and snapshot agent runs and done. starts appling that snapshot at subsriber which inturns delete the tables and re-create.

but what if you run it several time but nothing happens and you dont know whats going on, as it dont provide you the error any where and also it will not show where is it going and what it is doing. for you its just like nothing happening.

so what will you do, as we are re-initializing the subsriber so we are safe to delete the subsriber and add the new one which inturns does the same of initialization. so I did so, guess what…. it started working and surprisingly I get error at the replication stating

cannot drop the table ‘xxx’ as its it is being referenced by object ‘yyy’

so now what to do, if you see the object ‘yyy’ its a view with schema binding option enable, due to which table cannot be drop.

this could be also because of function is using that articles(objects). so if  you see re-initialize is not working first check this dependent /reference objects with respect to replicated articles.

This may save some time for some one…

 

 

Posted in Add/remove, High Avaliability, Replication, Troubleshooting | Leave a comment

Sql Cluster Resource not comming online

when you check on the sql server services and sql agent services its getting started on windows cluster but on cluser its going down again. on error log you may get the cluster service

Login failed for user ‘cluster service account’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

first thing you might think would be check the account password and see if its working– yeah its working now what next.

read the error log carefully and try to understand what is happening. here you could see sql server is started but when it tryed to attach – make available to cluster with cluster service acocunt its getting failed. means the given service account might not have previlage to sql server.

so what we did, we made cluster/sql fool and made a work aournd as failed over the cluster and as soon as sql server started, we open the sql server and checked is the given service account has access to it, and we were right, that account were not existed on to sql server, we have added that accound and granted sysadmin role and you know what…. it WORKS !!!

the possiblility of this issue could be:

“The cluster service account has been changed and that has not been updated at sql level”

Thanks to my frined Nitin here who helped me to resolve this issue.

Posted in Add/remove, Bug, Disaster Recovery, Error Log, High Avaliability | Tagged , , , , | Leave a comment

TempDB Contention 2.1.%

Problem:

When you are performing many DML operations and makes tempdb contesnion on it, You may get wait_resource of  2.1.103

check perfomance counter:

  • Temp Tables Creation Rate
  • Temp Tables For Destruction

When you are performing many DML operations and makes tempdb contesnion on it, You may get wait_resource of  2.1.1 and 2.1.3

Sys,Dm_os_waiting_task – resource like (2:1:%) where % 1:PFS and 3 GAMs contention issue and 103 is DDL contension on Create/Drop temp table on sysmultiobjrefs objects.

Solution:

for 2.1.1 and 2.1.3:

>> check tempdb is on seperate LUN (drives)

>>Enable the trace flag -T1118

>>check the Sort and DML queries on temp objects and try to tune it.

for 2.1.103:

>> enable trace flag -T1118

>> try not us create temp objects.

>> try to include indexes on the temp table.

>> apply hotfix

Must read :

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

http://www.mattwrock.com/post/2011/09/10/Latch-waits-on-21103-You-are-probably-creating-too-many-temp-tables-in-Sql-Server.aspx

http://michaeljswart.com/2013/01/pagelatch-contention-on-21103/

 

 

 

Posted in DB Design, DMV, Internal, Lock/Blocking, Performance Tuning, TempDB, Troubleshooting | Leave a comment

Cluster failover fails

you may get an error while trying to failover to another node cluster.

 

to resolve this issue create an alias on the node with the cluster name and it should work.

will blog more on this later.

 

Posted in High Avaliability, Troubleshooting | Leave a comment

LogReader agent failed

You may get an error on log reader agent failed with following error:

 

Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.

 

reason could be due to some activity the owner of the publisher database is gone (UNKNOWN) can cause this issue.

 

change the owner of the job usinga

EXEC sp_changedbowner ‘sa’

and it should work.

ref:

http://stackoverflow.com/questions/13823354/sql-server-principal-dbo-does-not-exist

 

Posted in Replication, Transaction Log, Troubleshooting, What I learned today | Leave a comment

Sql Server 2014…….What!!!

Yes, its true.

Microsoft vice president has announce  that new version of sql server 2014, will release on 2014…

do you think it is possible, as  not most of us not familiar with 2012 only….

any way…as MS already clear  that they will release new version after every 2 years… this announcement is part of it.

you must see this diagram shows features and sql version implement.

[Must Read ]Whitepaper by expert – Kalen Delney on SQL Server 2014 In-Memory OLTP (“Hekaton”) : http://sqlblog.com/blogs/kalen_delaney/archive/2013/06/05/hekaton-whitepaper.aspx 

below are some good  links related to SQL server 2014.

http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2013/06/11/first-look-microsoft-sql-server-2014.aspx

http://sqlmag.com/blog/teched-2013-eron-kelly-talks-about-sql-server-2014

http://sqlmag.com/sql-server-2014/new-features-sql-server-2014

Posted in Sql server 2014, Whats New | Tagged , , | Leave a comment

Sql server 2012 links

Feature of sqlserver 2012

http://arifudin.com/sql_server_2012_features

good alternative to dbcc ind in sqlserver 2012

SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS

http://www.jasonstrate.com/2013/04/a-replacement-for-dbcc-ind-in-sql-server-2012/

Posted in Denali, DMV, What I learned today, Whats New, Whitepapers | Tagged , , , | Leave a comment

Good Bye 2012 and welcome 2013

Hey Everyone….. from quite some time I have not written Blogs due to some personal and transiting from my old company … yeah If you read my last year good bye Blog I have indicated about my interest for changing the company took a year to do so…:)… yeah Now from this year I have joined a new company….  now having more than 12 Years working… and more than a decade with Sql Server… still Learning…. learning never ends:) loving it.

okay remembering what I have achieved year 2012 and how much achieved.

>> As committed that I will learn Denali and continue writing a Blog a day – have done a Denali Blog a day series on May 2012 , if you want  consolidated pdf  for that please comment or send an email to me.

continue learning Denali more stuff its getting interesting.

>> learning other stuff as well.

Hope I will continue to learn more about Sql server.

Thanks again all expert who share their valuable information on internet.

Thanks for Reading.

Posted in Computers and Internet, General, Personal | Tagged | Leave a comment

Denali –Memory : Memory Error Recovery in SQL Server 2012

 

Continue on What’s new in Denali and Memory enhancement, come across another great article by SQLCAT and Glenn Berry a new feature of Sql server 2012 in association with windows server 2012 called Memory Error Recovery in SQL Server 2012, Wanted to blog about it earlier but forget due to some other person stuff.

 

If you install sql server 2012 with windows server 2012 Enterprise edition will be a great package and have all great features for Denali includes Memory Error Recovery. Generally memory pages wont corrupt that easy but if you have very critical enterprise system and you don’t want to take chance. Could be due to some hardware failure or any other reason data on the memory could be corrupt. At this time for such cases you do not have a solution.

How it works:

If your data on memory BPool is corrupt or lost but that has been committed into disk with this option you could able to fetch the data from disk into memory and correct the memory data cache.

Pre-Requisites:

  • Windows server 2012 -Enterprise
  • Sql Server 2012 -Enterprise
  • ECC_RAM
  • Data should be committed to disk.
  • Hardware/Software should be compatible.

 

Reference :

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2012/06/05/memory-error-recovery-in-sql-server-2012.aspx

http://www.sqlskills.com/blogs/glenn/post/Memory-Error-Recovery-in-SQL-Server-2012.aspx

http://h20324.www2.hp.com/SDP/Content/ContentDetails.aspx?PortalID=1&ID=3397&booth=63&tag=687&goback=%2Egmp_1434987%2Egde_1434987_member_142079817

 

 

 

 

 

 

 

Posted in Denali, Memory, Whats New | Tagged , , | Leave a comment

DBCC Cleaning CACHE – Memory

DBCC Cleaning CACHE – Memory

I wanted to write this blog for quite some time, here you go, as you know at sql everything is on memory, memory information retrieve quickly compare to Disk. So if things are not in memory processor has to fetch that information from disk into memory, as disk is quite slower than memory its time consuming. Ok I am not going in deep on this.

So, if we want to free up the CACHE due to some reason following are the DBCC commands…
(Never run this command on production(run with your own performance risk)

This DBCC commands are just to let everyone know what they are briefly.

  1. DBCC DROPCLEANBUFFERS Removes all clean buffers from the buffer pool … run checkpoint and run this command. clean memory (as good as sql restart).
  2. DBCC FREESYSTEMCACHE
    –Clears all the entries from system cache(
    Releases all unused cache entries from all caches).

    http://ss64.com/sql/dbcc_free.html

3.  DBCC FREEPROCCACHE – Clear Procedure cache, so that next time SP runs with re-compile.

4. DBCC FREESESSIONCACHEClear distributed queries, queries which uses OLEDB –can be connect to other RDBMS as well – Flush the distributed query connection cache.
http://msdn.microsoft.com/en-us/library/ms188721.aspx

http://msdn.microsoft.com/en-us/library/ms187781.aspx

5. DBCC FLUSHPROCINDB(<db_id>); –procedure cache for particular database similar to DBCC FREEPROCCACHE.
if Auto_Close option is set, cache of that database will free automatically.

http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/57/clearing-sql-server-data-plan-cache

http://sqlblog.com/blogs/kalen_delaney/archive/2008/10/31/clearing-a-single-plan-from-cache-in-sql-server-2008.aspx

*Never run this command on production(run with your own performance risk)

List of DBCC Commands:

http://www.extremeexperts.com/sql/articles/ListDBCCCommands.aspx

http://sqlcodebank.blogspot.com/2011/10/useful-sql-server-dbcc-commands.html

Posted in Memory, Performance Tuning, Troubleshooting | Tagged , , , , | 1 Comment