blogs

26 Nov

gunjan.gupta@theperfectfuture.com
anjali@readmindinfo.com

Kerebros
===============
http://blogs.msdn.com/b/saponsqlserver/archive/2013/05/03/faqs-around-kerberos-and-sql-server.aspx

Greatest articles
http://blogs.msdn.com/b/saponsqlserver/archive/2011/07/22/using-mounted-volumes-in-a-cluster.aspx

aul White said:

Something that can work well in many environments, assuming a primarily OLTP-type workload:

1. Server-wide setting to MAXDOP = 1

2. Cost threshold set to zero

3. Use the MAXDOP = N query hint on code that benefits from parallelism (even if it’s just MAXDOP 2 to allow bitmap operators).

I also would dearly love to see an OPTION (USE_PARALLEL_PLAN) hint in SQL Server, but I can quite see why the SQL Server team would be reluctant to do this – sigh.
January 26, 2010 6:02 AM

Jonathan Kehayias said:

Paul,

Your comment is exactly what Bob Ward said not to do in his Wait Stats Session at PASS. In addition ‘cost threshold for parallelism’ is ignored as is the MAXDOP hint when you set the server wide setting for ‘max degree of parallelism’ to 1, so your #2 and #3 are overridden all the time by #1.

http://msdn.microsoft.com/en-us/library/ms188603.aspx
January 26, 2010 2:33 PM

Paul White said:

Jonathan,

That is absolutely *not* correct. A query hint MAXDOP overrides the server-wide setting, and the cost threshold for parallelism is still respected when overriding MAXDOP in this way.

Consider the following very simple query using data from the 2008 SR4 AdventureWorksDW sample database:

SELECT COUNT_BIG(*) FROM dbo.FactInternetSales;

On a server configured with Server MAXDOP = 1, cost threshold = 0, you get a simple serial plan as expected.

Adding OPTION (MAXDOP 2) to the query produces a parallel plan with an estimated plan cost of 0.17

Change the cost threshold for parallelism to 1 and we return to a serial plan.

Separately, Bob’s point was to not set server-wide MAXDOP to 1 in an attempt to fix CXPACKET waits. I could not agree more. My point was, and continues to be, that OLTP environments can benefit from server MAXDOP = 1, with the DBA selectively overriding MAXDOP as shown. That was why I heavily qualified my previous comment with the statement concerning OLTP workloads.

Paul

http://www.tataindicom.com/myaccount.aspx
Punit@123
Nickname: manish

http://blogs.msdn.com/b/saponsqlserver/archive/2013/05/03/faqs-around-kerberos-and-sql-server.aspx

http://social.msdn.microsoft.com/profile/timchapman/
rating 4

http://dougbert.com/blog/post/ssis-and-clustering-what-you-should-do-instead.aspx
rating 4
http://blogs.technet.com/b/dataplatforminsider/archive/2011/08/04/columnstore-indexes-a-new-feature-in-sql-server-known-as-project-apollo.aspx

http://www.kendalvandyke.com/search/label/Replication
http://msdn.microsoft.com/en-us/library/dd263442%28v=sql.100%29.aspx
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9349cd3b-6b66-4ba3-a3ac-0532e6d2bea9/replication-latency-9891-for-the-log-reader-agent-with-agentid-too-high-threshold-for-the
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/288758d9-fb5f-44ff-a6f4-42a7fe87a84a/delivery-latency-90712-for-the-distribution-agent-with-agentid-id-too-high-threshold-for

http://www.sqlnotes.info/2013/05/14/protect-yourself-from-using-dbcc-writepage/

http://www.sqlnotes.info/2012/05/07/collation-of-temp-tables/

http://blogs.msdn.com/b/psssql/archive/2010/12/07/alwayson-hadron-learning-series-what-is-hadron.aspx

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/10/sql-server-v-next-denali-changes-to-resource-governor.aspx
https:\\cs-lon.emimusic.com

9213562429

Cluster Upgrade (SQL Server 2008 R2–> SQL Server “Denali”)
http://blogs.msdn.com/b/petersad/archive/2011/05/10/cluster-upgrade-sql-server-2008-r2-gt-sql-server-denali.aspx
http://www.sqlservercentral.com/blogs/james-sql-footprint/2013/07/02/trace-flag-for-backup-and-restore-operation/
http://blogs.msdn.com/b/jay_akhawri/archive/2009/02/16/resolving-operating-system-error-code-5-with-bulk-insert-a-different-perspective.aspx
http://blogs.msdn.com/b/petersad/archive/2011/07/13/product-update-in-sql-server-code-named-denali.aspx
http://blogs.msdn.com/b/petersad/archive/2011/07/28/product-update-frequently-asked-questions.aspx
http://blogs.msdn.com/b/petersad/archive/2011/01/24/how-to-perform-an-sql-server-edition-upgrade.aspx
http://rapidlibrary.com/files/a-guide-to-sql-server-2000-transactional-and-snapshot-replication-pdf-pdf_51231286.html
http://dangerousdba.blogspot.co.uk/2011/10/bit-reversion.html
http://sqlmag.com/database-performance-tuning/solving-merge-replication-performance-problems
http://sqlmag.com/blog/preemptive-rowguidcols-merge-replicated-databases
http://blogs.msdn.com/b/mangeshd/archive/2009/05/20/replication-snapshot-folder-cleanup.aspx
http://www.dbtalk.net/microsoft-public-sqlserver-replication/snapshot-cleanup-sql-server-2000-a-290003.html
http://blogs.msdn.com/b/mangeshd/archive/2009/10/02/sp-mergemetadataretentioncleanup-not-cleaning-merge-metadata-during-regular-sync-causing-stale-data-in-metadata-tables.aspx
http://blogs.msdn.com/b/mangeshd/archive/2008/05/30/how-to-use-database-snapshot-sync-mode-while-creating-a-new-tran-snapshot-publication-using-sp-addpublication.aspx
http://www.sqlha.com/2009/03/19/installing-a-sql-server-2005-failover-clustering-instance-on-a-windows-server-2008-failover-cluster-part-one-cluster-exe/

SSIS clustering
SELECT * FROM ::FN_VIRTUALSERVERNODES()

SE Master

SELECT * FROM sys.dm_io_cluster_shared_drives

Meanwhile, the following system function has the same results in SQL Server 2000
http://www.sqlskills.com/blogs/paul/sql-server-2008-configuring-peer-to-peer-replication/
http://www.databasejournal.com/features/mssql/article.php/3820361/Peer-to-Peer-Replication-in-SQL-Server-2008-150-Add-a-node-and-resolve-conflict.htm

0120-4526700

Replication Merge
http://searchsqlserver.techtarget.com/tip/Ten-merge-replication-tips-and-tricks

using-service-broker-instead-of-replication
http://itknowledgeexchange.techtarget.com/sql-server/using-service-broker-instead-of-replication/

https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/

BW3WK-M3JW8-7TT33-27M62-WMY7Y

new enetered key WQ27D-PY77P-R9CQK-MCPPB- QGJYQ

135475

http://blogs.msdn.com/b/psssql/archive/2013/01/30/appdomain-unloading-messages-flooding-the-sql-server-error-log.aspx
http://blogs.msdn.com/b/sqlclr/archive/2005/10/03/476661.aspx
http://blogs.msdn.com/b/sqlclr/archive/2006/02/22/536507.aspx
http://blogs.msdn.com/b/sqlclr/archive/2006/03/24/560154.aspx

0120-4526700

Replication Merge
http://searchsqlserver.techtarget.com/tip/Ten-merge-replication-tips-and-tricks

using-service-broker-instead-of-replication
http://itknowledgeexchange.techtarget.com/sql-server/using-service-broker-instead-of-replication/

CSS
=========================
http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

Gud topics or blogs
http://blogs.msdn.com/b/cindygross/archive/2010/06/07/sql-server-lifecycle-and-the-compatibility-level-settings.aspx
http://blogs.technet.com/b/dataplatforminsider/archive/2011/08/04/columnstore-indexes-a-new-feature-in-sql-server-known-as-project-apollo.aspx
http://blogs.technet.com/b/dataplatforminsider/archive/2012/12/19/disk-and-file-layout-for-sql-server.aspx
http://nancyhidywilson.wordpress.com/2012/06/26/discontinued-features-in-sql-server-2012-part-2/
http://blogs.msdn.com/b/cindygross/archive/2010/06/13/dbcc-checkdb-database-integrity.aspx
http://blogs.msdn.com/b/cindygross/archive/2009/07/07/x64-windows-upgrade-from-32bit-sql-server-to-64bit-sql-server.aspx
http://blogs.msdn.com/b/cindygross/archive/2010/10/18/do-i-need-dtc-for-my-sql-server.aspx
http://blogs.msdn.com/b/cindygross/archive/2010/03/17/what-do-those-io-requests-taking-longer-than-15-seconds-messages-on-my-sql-box-mean.aspx
http://blogs.msdn.com/b/cindygross/archive/2011/07/05/using-mount-points-with-sql-server.aspx
http://blogs.msdn.com/b/cindygross/archive/2009/09/02/sql-server-security-granularity.aspx
http://www.mssqltips.com/sqlservertip/1436/upgrading-sql-server-databases-and-changing-compatibility-levels/
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/12/21/tempdb-basics.aspx
http://sqlbie.wordpress.com/2010/08/04/four-phase-sql-server-database-recovery%C2%A0process/
http://colleenmorrow.com/2011/12/13/t-sql-tuesday-025-tips-and-tricks/
http://colleenmorrow.com/2011/02/09/plan-guides-and-parameterization/

http://blogs.msdn.com/b/suhde/archive/2011/07/31/internals-of-database-snapshot.aspx
http://msdn.microsoft.com/en-IN/library/ms179314.aspx
http://synsol.eu/blog/2012/08/
Deadlocks and trace flag 1204
http://colleenmorrow.com/2011/03/01/deadlocks-and-trace-flag-1204/
http://www.google.co.in/url?sa=t&rct=j&q=&esrc=s&source=web&cd=4&sqi=2&ved=0CD8QFjAD&url=http%3A%2F%2Fblogs.msdn.com%2Fb%2Fpsssql%2Farchive%2F2010%2F11%2F19%2Fhow-it-works-io-affinity-mask-should-i-use-it.aspx&ei=0fsxUfvTOsjXrQfopYHABA&usg=AFQjCNHDz1V53kw_LA5C8q6-HYAK5GlzNA&bvm=bv.43148975,d.bmk

http://scottless.com/blog/archive/2012/01.aspx

http://sqlblog.com/blogs/sqlos_team/archive/2011/01/26/what-causes-multi-page-allocations.aspx
trace flags
==========
http://blogs.msdn.com/b/sqlcat/archive/2007/06/20/distributed-partitioned-views-federated-databases-lessons-learned.aspx
http://blogs.msdn.com/b/timchapman/archive/2012/08/27/the-drawback-of-using-filtered-indexes.aspx

http://blogs.msdn.com/b/sqlcat/archive/2011/08/31/unexpected-query-plan-for-computed-column.aspx
http://blogs.msdn.com/b/sqlprogrammability/archive/2009/06.aspx
http://www.sqlskills.com/blogs/kimberly/stored-procedures-recompilation-and-netrocks/

http://social.technet.microsoft.com/wiki/contents/articles/3540.sql-server-column

store-index-faq.aspx#Batch_mode_processing

http://blogs.msdn.com/b/sqlcat/archive/2005/09/05/461199.aspx
http://blogs.msdn.com/b/sqlcat/archive/2005/12/12/502735.aspx
http://blogs.msdn.com/b/sqlcat/archive/2005/10/11/479887.aspx

http://blogs.msdn.com/b/sqlcat/archive/2005/10/27/how-can-sql-server-2005-online-

http://blogs.msdn.com/b/slavao/archive/2005/02/01/364523.aspx

piecemeal-restore-improve-availability.aspx
http://blogs.msdn.com/b/sqlcat/archive/2006/03/31/566046.aspx
http://blogs.msdn.com/b/sqlcat/archive/2005/11/21/495440.aspx
http://blogs.msdn.com/b/sqlcat/archive/2006/03/01/541550.aspx
http://blogs.msdn.com/b/sqlcat/archive/2005/11/30/498415.aspx
http://blogs.msdn.com/b/sqlcat/archive/2007/02.aspx
http://blogs.msdn.com/b/sqlcat/archive/2007/05.aspx

http://www.patrickkeisler.com/search/label/DBCC
http://www.patrickkeisler.com/2013/01/sql-server-has-encountered-occurences.html
http://www.patrickkeisler.com/search/label/Table%20Partitioning

http://sqlblog.com/blogs/kalen_delaney/archive/2009/08/16/altering-a-partition-function.aspx

http://www.sqlsoldier.com/wp/sqlserver/day28of31daysofdisasterrecoveryrecoveringsqlifthetempdbdrivedies
http://www.sqlsoldier.com/wp/sqlserver/day24of31daysofdisasterrecoveryhandlingcorruptioninaclusteredindex
http://www.sqlmag.com/article/high-availability/log%20shipping%20techniques-129747
http://www.sqlsoldier.com/wp/sqlserver/whatdoesredonemean
http://www.sqlsoldier.com/wp/sqlserver/doesamirroringfailovercleartheprocedurecache

http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/which-replication-databases-can-i-mirror-137122
http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-monitor-checkpoints-137148
http://sqlcat.com/sqlcat/b/technicalnotes/archive/2010/02/10/mirroring-a-large-number-of-databases-in-a-single-sql-server-instance.aspx
http://msmvps.com/blogs/eladio_rincon/archive/2008/12/07/using-xevents-extended-events-in-sql-server-2008-to-detect-which-queries-are-causing-page-splits.aspx

http://blogs.msdn.com/b/sqlblog/archive/2011/08/24/setting-up-database-mirroring-with-certificates.aspx

http://blogs.msdn.com/b/sqljourney/archive/2012/07/04/the-most-interesting-issue-in-db-mirroring-you-will-ever-see.aspx

http://blogs.msdn.com/b/grahamk/archive/2008/11/12/database-mirroring-monitor-can-cause-large-tempdb-growth-in-sql-server-2005-changed-behavior-in-sql-server-2008.aspx

http://msdn.microsoft.com/en-us/library/ms366279.aspx (Must)

http://www.sqlskills.com/blogs/erin/
http://blogs.msdn.com/b/saponsqlserver/archive/2012/04/24/sql-server-2012-alwayson-part-7-details-behind-an-alwayson-availability-group.aspx

http://sqlblog.com/blogs/kevin_kline/archive/2012/11/14/quick-tip-speed-a-slow-restore-from-the-transaction-log.aspx
http://sqlblog.com/blogs/davide_mauri/archive/2013/03/25/sql-server-2008r2-2012-standard-backup-compression.aspx
http://sqlblog.com/blogs/kevin_kline/archive/2013/03/28/squishy-limits-in-sql-server-express-edition.aspx

http://www.sqlpassion.at/blog/PermaLink,guid,03a7af66-b480-44c1-af26-262994351e2e.aspx
http://sqlblog.com/blogs/kevin_kline/archive/2012/10/08/the-zombie-perfmon-counter-that-never-dies-quick-tip.aspx

How to enable trace flags on MS cluster? in registry, in one node or every node in cluster
http://sqlcat.com/sqlcat/b/top10lists/archive/2008/11/20/six-failover-clustering-benefits-realized-from-migrating-to-sql-server-2008.aspx
http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/03/07/how-many-files-should-a-database-have-part-1-olap-workloads.aspx
http://blogs.technet.com/b/sqlpfeil/archive/2012/07.aspx
http://blogs.technet.com/b/sqlpfeil/archive/2012/05/21/network-bind-order-warning-during-sql-server-installation-at-failover-cluster.aspx
http://blogs.msdn.com/b/ialonso/archive/2012/01/13/what-s-in-enterprise-only-honor-lazy-schema-validation-option-in-a-linked-server.aspx
http://blogs.msdn.com/b/sqlcat/archive/2011/03/08/linked-servers-to-sql-azure.aspx

http://colleenmorrow.com/2012/06/07/page_verify-checksum-vs-torn-page-detection/
http://sqlblog.com/blogs/paul_white/archive/2013/02/01/a-creative-use-of-ignore-dup-key.aspx
http://sqlblog.com/blogs/paul_white/archive/2013/02/06/incorrect-results-with-indexed-views.aspx
http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx
http://sqlblog.com/blogs/paul_white/archive/2012/09/12/why-doesn-t-partition-elimination-work.aspx

Add data file to logshipping
http://ask.sqlservercentral.com/questions/4065/adding-a-datafile-to-a-log-shipping-primary.html
http://sql-channel.com/tag/add-user-logshipping/
http://hassanszone.wordpress.com/2009/03/23/stored-procedure-to-refresh-logshipping-monitor-in-sqlserver2005/
Adding a log shipping monitor
http://www.sqlservercentral.com/articles/Log+Shipping/77295/
http://dbaspot.com/ms-sqlserver/434263-switch-between-restore-modes-transaction-log-shipping.html
http://blogs.technet.com/b/josebda/archive/2009/04/02/sql-server-2008-log-shipping.aspx

You can get the response time and the service time of a query by looking at sys.dm_exec_sessions,
columns total_elapsed_time and cpu_time
http://erinstellato.com/2010/08/readonly-filegroups-splitssec-error/

Integration Services Roles (SSIS Service)
http://msdn.microsoft.com/en-us/library/ms141053.aspx

Migrating SQL Reporting Services to a new server by moving the Reporting Services databases
http://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases/

Understanding SQL server memory grant
=========================================
http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx
http://troubleshootingsql.com/2012/09/12/why-did-the-restore-fail-on-the-log-shipped-secondary-database/

2 time
cinarest cold

combiflam
ciprobid 250
citrazin

13,915.90

Request No: 261610246

CLR
===========
http://blogs.msdn.com/b/johnhicks/archive/2009/06/08/four-myths-about-sql-server-s-clr-engine.aspx

http://thomaslarock.com/2012/01/stop-guessing-about-cpu-pressure/
http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/
http://colleenmorrow.com/2012/04/17/tds-endpoints-sql-servers-listener/
http://blogs.msdn.com/b/sqlcat/archive/2010/03/04/enabling-partition-level-locking-in-sql-server-2008.aspx
http://www.sqlskills.com/blogs/paul/search-engine-qa-4-using-emergency-mode-to-access-a-recovery-pending-or-suspect-database/
http://www.sqlskills.com/blogs/paul/search-engine-qa-2-moving-a-database-while-database-mirroring-is-running/
http://blogs.msdn.com/b/sqlcat/archive/2008/11/06/best-practices-for-integrated-full-text-search-ifts-in-sql-2008.aspx
http://blogs.msdn.com/b/sqlcat/archive/2005/10/27/how-can-sql-server-2005-online-piecemeal-restore-improve-availability.aspx
http://blogs.msdn.com/b/sqlcat/archive/2007/05/07/sql-server-2005-transactional-replication-benefit-of-using-subscriptionstreams-for-low-bandwidth-high-latency-environments.aspx
http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/09/22/resolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx
http://blogs.msdn.com/b/queryoptteam/archive/2006/07/21/674350.aspx
http://www.sqlskills.com/blogs/kimberly/clearing-the-cache-are-there-other-options/
http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx
http://sqlcat.com/sqlcat/b/msdnmirror/archive/2010/03/16/how-to-enable-rcsi-for-a-database-with-database-mirroring.aspx
http://blogs.msdn.com/b/psssql/archive/2009/11/13/how-it-works-how-many-databases-can-be-backed-up-simultaneously.aspx
http://blogs.msdn.com/b/queryoptteam/archive/2006/08/29/730521.aspx
http://www.sqlskills.com/blogs/kimberly/changing-database-collation-and-dealing-with-tempdb-objects/
http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server-2008-r2/moving-lob-data-142636
http://blogs.msdn.com/b/sqldatabasetalk/archive/2013/03/03/troubleshooting-blocking-in-sql-database-the-who.aspx
http://sqlblog.com/blogs/argenis_fernandez/archive/2012/12/30/the-myth-around-32-bit-sql-server-instances-on-64-bit-operating-systems-and-awe.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163832
http://www.mssqltips.com/sqlservertip/2562/sql-server-log-shipping-to-a-different-domain-or-workgroup/
http://www.sqlskills.com/blogs/kimberly/

Transaction replication performance
============================
http://blogs.msdn.com/b/sqlcat/archive/2007/05/07/sql-server-2005-transactional-replication-benefit-of-using-subscriptionstreams-for-low-bandwidth-high-latency-environments.aspx
How to Massively Optimise Transactional Replication
——————————————————————————————
http://replicationanswers.com/TransactionalOptimisation.asp
Can I write to a replicated database?
=============================
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=160019

http://www.sqlmag.com/article/sql-server/Troubleshooting-Transactional-Replication
Understanding How Transaction are Applied in Transactional Replication in SQL Server 2005
========================================================================
http://replicationanswers.com/TransactionsInTRSQL2005.asp

Indexs not replicated
================
http://www.informit.com/articles/article.aspx?p=601350&seqNum=2
Restore of Replicated Database fails with “Cannot drop the table because it is being used for replication
===================================================================================================================
http://blogs.msdn.com/b/repltalk/archive/2011/01/02/restore-of-replicated-database-fails-with-cannot-drop-the-table-lt-tablename-gt-because-it-is-being-used-for-replication.aspx
http://replicationanswers.com/AlterSchema2005.asp

Indexes
========
http://blogs.msdn.com/b/repltalk/archive/2012/04/03/replicating-non-clustered-indexes-improves-subscriber-query-performance.aspx
Calculating Replication Schema Options
==========================================
http://www.englishtosql.com/english-to-sql-blog/2010/9/9/calculating-replication-schema-options.html
Indexes
==============
http://blogs.msdn.com/b/repltalk/archive/2012/04/03/replicating-non-clustered-indexes-improves-subscriber-query-performance.aspx
Published Database Transaction Log continues to Grow!(sync with backup)
=======================================================================
http://blogs.msdn.com/b/repltalk/archive/2010/10/24/published-database-transaction-log-continues-to-grow.aspx
Troubleshooting LogReader Timeout executing sp_replcmd
==========================================================
http://blogs.msdn.com/b/repltalk/archive/2010/10/25/troubleshooting-logreader-timeout-executing-sp-replcmds.aspx
Lessons learned updating 100 millions rows
http://blogs.msdn.com/b/repltalk/archive/2010/03/07/tips-to-improve-performance-when-applying-snapshot-in-transactional-replication.aspx
Add article
http://ansqldba.blogspot.in/
tracing delay
http://blogs.msdn.com/b/repltalk/archive/2010/03/11/divide-and-conquer-transactional-replication-using-tracer-tokens.aspx
===========

9350523619 shabir

http://blogs.msdn.com/b/bartd/archive/2010/05/26/finding-procedure-cache-bloat.aspx
Commithreshold is honored at a transaction boundary. Let’s take the
following example

Commitbatchsize 10
Commitbatchthreshold 20

Transaction1 has 19 commands
Transaction 2 has 11 commands
Transaction 3 has 5 commands

With the above setting, the distribution agent reader thread will set the
commit flag for the command block after we read all the commands in
transaction 2. This is because we check the commitbatch size and
commitbatchthreshold after a transaction boundary is reached. We dont issue
a commit after we read the first command (20th command in that batch) in
transaction 2. So this commit batch on the subscriber will have committed 2
transactions with 30 commands even though the threshold is 20.

So the batch will be committed either when we hit the commitbatchsize OR
when a transaction crosses the command threshold before the commitbatchsize
is hit.

With regard to the threshold adding one more command that seems to be a bug.

http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/22/3-0-changes-in-caching-behavior-between-sql-server-2000-sql-server-2005-rtm-and-sql-server-2005-sp2.aspx

Download videos
====================
http://blogs.msdn.com/b/sqlserverfaq/archive/2011/12/12/procedure-cache.aspx
http://blogs.msdn.com/b/sqlserverfaq/archive/2011/12/12/procedure-cache-part-ii.aspx

Ghost records,recover sa password, forgot password of ssis package,login to sql server when sa lost, transactional replication lessons
==================================================================
http://sqlblog.com/blogs/argenis_fernandez/archive/2011/05/31/transactional-replication-and-wan-links.aspx

SQL2K Side-By-Side with SQL2K5
Changing Service Account & Service Account Password

====================================================

http://blogs.msdn.com/b/support_sql_france/archive/2012/01/24/10253472.aspx

http://blogs.msdn.com/b/dtjones/archive/2010/12/15/changing-service-account-amp-service-account-password.aspx

http://sqlblog.com/blogs/linchi_shea/archive/2007/02/26/maintain-database-partial-availability.aspx

http://erinstellato.com/2012/01/statistics-recompilations/
Gogula: try the MCTS for sql server
there are 3 differnet ones in the SQL Server 2008 track
70-433 is easy
me: can i complete for sql 2008 now?
only 1 is sufficient
Sent at 5:12 AM on Friday
Gogula: 1 is sufficient for MCTS
then you can do 70-451 to get the MCITP
if you want to do the easiest – those are the two
Sent at 5:20 AM on Friday
https://editor.cutepdf.com/edit.asp

http://www.freebookspot.es/Comments.aspx?Element_ID=255652
–Professional Microsoft SQL Server 2012 Administration free ebook

2 time
cinarest cold

combiflam
ciprobid 250
citrazin

http://www.sqldbadiaries.com/2010/09/13/get-table-name-from-data-file-offset-address/

The best of sqlactions.com Site
============================

Simple backup Strategy for Distribution database
http://sqlactions.com/2012/05/17/simple-backup-strategy-for-distribution-database/comment-page-1/#comment-283

DBCC MEMORYSTATUS : How is Stolen Potential calculated
http://sqlactions.com/2013/02/16/dbcc-memorystatus-how-is-stolen-potential-calculated/
http://sqlactions.com/2013/01/15/sql-litespeed-backup-hung-with-wait-type-preemptive_os_reportevent/
http://sqlactions.com/2012/08/05/how-to-create-custom-schedule-for-sql-server-agent-job/

=>How to create custom schedule for SQL Server Agent Job
http://sqlactions.com/2012/08/05/how-to-create-custom-schedule-for-sql-server-agent-job/

Halloween Protection
=========================================================

Why does SQL Server not compress data on LOB pages?
http://sqlblog.com/blogs/hugo_kornelis/archive/2013/01/31/why-does-sql-server-not-compress-data-on-lob-pages.aspx
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/07/21/Snapshot-and-integrity-part-1.aspx
https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
http://blogs.msdn.com/b/karthick_pk/archive/2010/11/26/the-database-model-is-marked-restoring-and-is-in-a-state-that-does-not-allow-recovery-to-be-run.aspx

score250
vlonp217m

http://www.sqlskills.com/BLOGS/PAUL/post/Do-transactions-rollback-when-DBCC-CHECKDB-runs.aspx

https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
http://blogs.msdn.com/b/karthick_pk/archive/2010/11/26/the-database-model-is-marked-restoring-and-is-in-a-state-that-does-not-allow-recovery-to-be-run.aspx

http://www.kendalvandyke.com/2010/02/tale-of-two-execution-plans-solved.html

The best of sqlactions.com Site
============================
Simple backup Strategy for Distribution database
http://sqlactions.com/2012/05/17/simple-backup-strategy-for-distribution-database/comment-page-1/#comment-283

DBCC MEMORYSTATUS : How is Stolen Potential calculated
http://sqlactions.com/2013/02/16/dbcc-memorystatus-how-is-stolen-potential-calculated/
http://sqlactions.com/2013/01/15/sql-litespeed-backup-hung-with-wait-type-preemptive_os_reportevent/
http://sqlactions.com/2012/08/05/how-to-create-custom-schedule-for-sql-server-agent-job/


SELECT
(CASE
WHEN mdh.runstatus = ‘1’ THEN ‘Start – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘2’ THEN ‘Succeed – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘3’ THEN ‘InProgress – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘4’ THEN ‘Idle – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘5’ THEN ‘Retry – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘6’ THEN ‘Fail – ‘+cast(mdh.runstatus as varchar)
ELSE CAST(mdh.runstatus AS VARCHAR)
END) [Run Status],
mda.subscriber_db [Subscriber DB],
mda.publication [PUB Name],
right(left(mda.name,LEN(mda.name)-(len(mda.id)+1)), LEN(left(mda.name,LEN(mda.name)-(len(mda.id)+1)))-(10+len(mda.publisher_db)+(case when mda.publisher_db=’ALL’ then 1 else LEN(mda.publication)+2 end))) [SUBSCRIBER],
CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized],
und.UndelivCmdsInDistDB [UndistCom],
mdh.comments [Comments],
‘select * from distribution.dbo.msrepl_errors (nolock) where id = ‘ + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
mdh.xact_seqno [SEQ_NO],
(CASE
WHEN mda.subscription_type = ‘0’ THEN ‘Push’
WHEN mda.subscription_type = ‘1’ THEN ‘Pull’
WHEN mda.subscription_type = ‘2’ THEN ‘Anonymous’
ELSE CAST(mda.subscription_type AS VARCHAR)
END) [SUB Type],

mda.publisher_db+’ – ‘+CAST(mda.publisher_database_id as varchar) [Publisher DB],
mda.name [Pub – DB – Publication – SUB – AgentID]
FROM distribution.dbo.MSdistribution_agents mda
LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
JOIN
(SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
FROM distribution.dbo.MSrepl_commands t (NOLOCK)
JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
JOIN
(SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq
FROM distribution.dbo.MSdistribution_history hist (NOLOCK)
JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
FROM distribution.dbo.MSdistribution_history (NOLOCK)
GROUP BY agent_id) AS h
ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
GROUP BY hist.agent_id, h.maxseq
) AS MaxAgentValue
ON MaxAgentValue.agent_id = s.agent_id
GROUP BY s.agent_id, MaxAgentValue.[time]
) und
ON mda.id = und.agent_id AND und.[time] = mdh.[time]
where mda.subscriber_db’virtual’ — created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change.
–and mdh.runstatus=’6′ –Fail
–and mdh.runstatus’2′ –Succeed
order by mdh.[time]

Index Fragmentation
=====================
http://www.sqlskills.com/blogs/paul/post/Inside-sysdm_db_index_physical_stats.aspx

Great SQL Server Debates: Buffer Cache Hit Ratio
=========================================
http://www.simple-talk.com/content/article.aspx?article=1426

http://synsol.eu/blog
=========================
http://synsol.eu/blog/2011/03/
http://synsol.eu/blog/2012/09/
http://synsol.eu/blog/2012/08/

http://www.sqlperformance.com/2012/12/system-configuration/sql-2012-slipstream

Contained database
===================
http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/04/contained-database-authentication-monitoring-and-controlling-contained-users.aspx
http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/08/contained-database-authentication-in-depth.aspx

Advertisements

SQL Server Maintenance Plans

9 Jan

SQL Server Maintenance Plans
The results generated by maintenance tasks in a maintenance plan can be written as a report to a text file, or written to the maintenance plan tables, sysmaintplan_log and sysmaintplan_logdetail, in msdb. To view the results in the log file viewer, right-click Maintenance Plans, and then click View History.

Note that maintenance plans only run against databases set to compatibility level 80 or higher. The maintenance plan designer in SQL Server Management Studio does not display databases set to compatibility level 70 or lower.

Important security considerations:  You must be a member of the sysadmin role to create and manage maintenance plans, and to view them in Object Explorer. The SQL Server Agent node in Object Explorer is only displayed for members of the sysadmin fixed server role, SQLAgentReaderRole, SQLAgentUserRole, or SQLAgentOperatorRole fixed database roles. Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles

Remember that statistics are not the same in SQL Server 2005 as they are in SQL Server 2000 and that they must be upgraded using the sp_updatestats stored procedure after you migrate from version 2000 to 20005.  Otherwise, the old statistics are unusable by the query engine.  Once you’ve run sp_updatestats, the engine will maintain them automatically.

important links sql Server

12 Nov

Evaluation edition to paid edition upgrade and trace flag sp_procoption for DBCC

==============================================================

http://www.mssqlgirl.com/category/dba

Analysis services administration notes
=========================================
http://blogs.msdn.com/b/karang/archive/2010/08/11/how-to-deal-with-corruption-in-analysis-services.aspx
http://blogs.msdn.com/b/karang/archive/2009/11/01/tampering-master-vmp-file-may-result-in-losing-all-analysis-services-databases.aspx
http://sqlserverpedia.com/wiki/MSAS_-_Using_Profiler_for_Performance_Monitoring
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/07024324-8328-4a92-83fc-32dbabd44aa7
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/3ff5cd79-5563-43f7-b815-a4a905829753
http://sqlcat.com/sqlcat/b/technicalnotes/archive/2010/02/08/microsoft-sql-server-2008-analysis-services-consolidation-best-practices.aspx

Stats
======
http://blogs.msdn.com/b/saponsqlserver/archive/2007/05/22/sap-application-leveraging-sql-server-automatic-update-statistics.aspx

Parallelism
===========
http://sqlblog.com/blogs/adam_machanic/archive/2010/05/28/sql-university-parallelism-week-part-3-settings-and-options.aspx

SSRS
======
http://blogs.msdn.com/b/jgalla/archive/2008/06/30/all-those-temporary-files-rstempfiles.aspx

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/06/26/report-server-catalog-best-practices.aspx

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx

http://www.mssqltips.com/sqlservertip/2778/how-to-add-reporting-services-to-an-existing-sql-server-clustered-instance/

SSIS over cluster

================

http://msdn.microsoft.com/en-in/library/ms345193%28v=sql.105%29.aspx

Concurrency Series: Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?
==================================================================================================
http://sqlcat.com/sqlcat/b/msdnmirror/archive/2011/02/20/concurrency-series-why-do-i-get-blocking-when-i-use-read-uncommitted-isolation-level-or-use-nolock-hint.aspx

Restore over network
=====================
http://ms-abhay.blogspot.in/2008/02/restoring-database-on-network-using-unc.html

–It is possible to delete the data/log files in SQL Express Edition even if the Instance is UP
http://ms-abhay.blogspot.in/2008/02/it-is-possible-to-delete-datalog-files.html

http://ms-abhay.blogspot.in/2010/11/error-1706-installation-package-for.html

Storage Best practices
=====================
http://sqlcat.com/sqlcat/b/top10lists/archive/2007/11/21/storage-top-10-best-practices.aspx

OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2
============================================================================
http://blogs.msdn.com/b/sqlperf/archive/2007/03/23/object-name-enhancement-and-object-schema-name-addition-in-sql-server-2005-sp2.aspx

Storage Modes in Analysis Services
===================================
http://blogs.msdn.com/b/sqlperf/archive/2007/02/12/storage-modes-in-analysis-services.aspx

Lock Escalation in SQL2005
============================
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx

Bulk Import Optimizations (Minimal Logging)
===========================================
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/02/05/bulk-logging-optimizations-minimal-logging.aspx

sunil agarwal
==============
http://social.msdn.microsoft.com/Profile/sunil%20agarwal/activity
http://blogs.msdn.com/b/chrissk/archive/2010/01/28/troubleshooting-transactional-replication-part-1.aspx

Memory pressure, Lock pages in memory
===================================================
http://www.sqlpassion.at/blog/CategoryView,category,SQLServer.aspx
http://blogs.msdn.com/b/karang/archive/2012/07/30/title_2d00_all_2d00_possible_2d00_scenarios_2d00_of_2d00_changing_2d00_ssas_2d00_data_2d00_directories.aspx
http://tenbulls.co.uk/2012/04/17/whos-active-on-sql-2000/
https://cmg.vlabcenter.com/prerequisites.aspx?moduleid=80373e91-6a9e-48d0-a8d6-4990c535cdc4

================
SAP on sql server
================
http://blogs.msdn.com/b/saponsqlserver/archive/2012/08/20/how-update-statistics-really-works-in-sap-bw.aspx
http://www.sqlpassion.at/blog/CategoryView,category,SQLServer.aspx
http://msdn.microsoft.com/en-us/library/ms173730%28v=sql.90%29.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/21/downgrading-a-database-you-can-t-get-there-from-here.aspx
http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

Quick Fix Engineering, CTPs, RTM,HotFixes,Service Packs,CUs Sql Server
=======================================================
http://weblogs.sqlteam.com/joew/archive/2008/05/07/60591.aspx

Behavior of SQL Server default instance on a NON-Default port
==========================================================
http://blogs.msdn.com/b/karthick_pk/archive/2009/01/15/sql-server-default-instance-behavior-on-a-non-default-port.aspx

What types of objects are useful in SQL CLR?
==============================================
http://sqlblog.com/blogs/greg_low/archive/2011/02/25/what-types-of-objects-are-useful-in-sql-clr.aspx
http://blogs.msdn.com/b/sanchan/archive/tags/32_2d00_bit+vs+64_2d00_bit+world/
http://www.englishtosql.com/english-to-sql-blog/2010/9/13/t-sql-tuesday-10-applying-indexes-to-replicated-tables.html

trans Replication sync with backup
========================================
http://www.sqlservercentral.com/blogs/aschenbrenner/2012/08/05/initialize-a-transactional-replication-from-a-database-backup/

sys.dm_db_index_physical stats
===============================
http://www.sqlskills.com/blogs/paul/post/Inside-sysdm_db_index_physical_stats.aspx

DBCC reindex and alter index
==============================
http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/06/23/rebuild-index-with-alter-index-rebuild-or-dbcc-dbreindex/

Using Bulk Logged recovery model for Bulk Operations will reduce the size of Transaction log backups – Myths and Truths
=======================================================================================================================

Checking for CPU pressure via sys.dm_os_schedulers
====================================================
http://blogs.msdn.com/b/joesack/archive/2009/04/08/checking-for-cpu-pressure-via-sys-dm-os-schedulers.aspx

http://blogs.msdn.com/b/sqlserverfaq/archive/2011/01/07/using-bulk-logged-recovery-model-for-bulk-operations-will-reduce-the-size-of-transaction-log-backups-myths-and-truths.aspx?PageIndex=2#comments

http://blogs.technet.com/b/dataplatforminsider/archive/2011/08/04/columnstore-indexes-a-new-feature-in-sql-server-known-as-project-apollo.aspx

http://www.sql-server-performance.com/2013/ssas-storage-modes/#comment-3710

http://rapidlibrary.com/files/a-guide-to-sql-server-2000-transactional-and-snapshot-replication-pdf-pdf_51231286.html

www.docseek.netHow-to & Guides

http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/05/04/initializing-a-transactional-replication-subscriber-from-an-array-based-snapshot.aspx

http://blogs.technet.com/b/warreny/

http://www.youtube.com/watch?v=hMQDKa3LMz8

http://channel9.msdn.com/posts/SQL11UPD06-REC-05