This one is simple but interesting... Log shipping which was running for an year, suddenly stopped working. Log shipping monitor says that there are no backups restored for last 14849 minutes... Why? We started figuring this out, the copy and restore...

So after Max. Server Memory calculator, to make your life much more easier, here is the Max Degree of Parallelism or MAXDOP calculator. We are usi ng SQL DMV's to find out the number of NUMA nodes and Powershell query to find out the number of processor...

This calculator is now withdrawn since I'm not able to invest my time to improve it to make it "stay current". Apologize for the inconvenience! Hello SQL Community, I have written a simplified “Max. Server Memory” calculator...

SQL Server team has published the "SQL Server 2012 Upgrade Technical Guide" at http://download.microsoft.com/download/9/5/3/9533501A-6F3E-4D03-A6A3-359AF6A79877/SQL_Server_2012_Upgrade_Technical_Reference_Guide_White_Paper.pdf I encourage you to upgrade...

Today while working on a SQL Server performance case, I was asked this question "What happens to a database when transaction rollback fails?". I thought of sharing the answer to the community so that it can help DBA's to understand how SQL Server handles...

When you try to grant any privilege to SQL Server Service SID using Group Policy Editor in Windows 2008, you might get this error: An extended error has occurred. Failed to save Local Policy Database. This happens only when you try to add virtual...

Worked on a SQL Server startup issue which I would like to share here: Here is how the SQL Server errorlog looked like 2012-04-19 03:43:26.50 Server Microsoft SQL Server 2005 - 9.00.4035.00 (Intel IA-64) Nov 24 2008 12:59:53 Copyright (c) 1988-2005...

There are already few blogs and a KB to talk about this problem so I'm trying to tie them together to make sure you get this quickly When you are restoring a database (in my case a database having 32 files), post 90% complete, all but one threads in...

Sharing one of the interesting scenario I worked today. Somebody messed up the data at publication database (few rows updated with a value which they aren't supposed to) and publication database was restored from a older backup to rollback the change...

How? Here is the answer: Preview of SQL Server ODBC driver for Linux is now available for download: Download Link: The Microsoft SQL Server ODBC Driver for Linux provides native connectivity from Linux to Microsoft SQL Server http://www...

This post is all about "replicate_dll" property in sp_addpublication, so if you are aware of this property then this is the answer for the problem described in the blog title. Here is a little more background about this issue: A column was added...

Just helped a performance problem with the information below and thought of sharing since it might be useful in some situations.. OPTIMIZE FOR UNKNOWN introduced in SQL Server 2008 gives this benefit: ".... This hint directs the query optimizer...

This is a simple information sharing post because there is nothing much to troubleshoot... SQL Server failed to start with following message: Server Failed allocate pages: FAIL_PAGE_ALLOCATION 262 Server Error: 17127, Severity: 16, State: 1. Server...

Today I worked on a interesting performance issue whereby SQL Server 2005 x86 (32-bit) had sever Non-BPool memory pressure causing all sort of errors lilke Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576 Downgrading backup log buffers...

A quick guide on how to troubleshoot Incorrect syntax near - Source: MSSQLServer, Error number: 102)" errors in replication Firstly, check for any known issues like http://support.microsoft.com/kb/935563 by searching with the error message in bing...

If you have researched about replication enhancement, for sure you would have heard about this Parameter which is applicable for both snapshot agent and distribution agent. I'm going to share some useful information regarding this parameter which you...

I want to share this troubleshooting scenario since it may help you: After upgrading SQL Server 2000 SP4 to SQL Server 2008 SP2, SQL Server experienced performance problems and many a times, connection failures SQL Server errorlog shows this message...

Do you know what is VLF? Are you aware that as a SQL Server DBA, you need to take care of the VLF Fragmentation in your SQL Server? Do you know that not-managing VLF's in your SQL Server databases can cause multiple recovery, performance issues...

One of the interesting scenario I worked recently. .NET application which has multiple tabs showing employee details shows poor performance when run for the first time during anytime of the day but when we connect to SQL Server Management Studio and...

Since a long time I wanted to leverage the details from the DMV dm_tran_database_transactions into my regular SPID monitoring script. I just tried it today and looks to be useful. Any feedbacks/bugs/thoughts, let me know. I'm combining sys.dm_os_workers...

I wanted to share this information to all those who are searching for better performer when it comes to distributed query vs openquery for running linked server queries. Distributed Query : Linked server four part queries are also called distributed...

What does WRITELOG waittype mean? WRITELOG waits represent the time waiting on a LOG I/O to complete. Common operations that cause log flushes are checkpoints and transaction commits. Transaction commits can be whenever you run a COMMIT TRAN (incase...

"Fire and Forget" is a scenario in Service Broker where Initiator starts a conversation -> Sends a message -> End Conversation without considering whether Target received and processed the message. In these scenarios, the Service Broker conversations...

This is a get started post for this topic and I will be adding more content as and when it is available Worker threads are the agents of SQL Server which are scheduled in CPU and they carry out the tasks Memory for Worker threads come from Non-Buffer...

Of late, I'm observing that some of the customers are not setting up Max. Server Memory Properly or they never set Max. Server Memory at all in 64-bit SQL Server installations. What will happen if I don't set Max. Server Memory? Working Set trimming...