How to troubleshoot Writelog wait type?

Few weeks ago when I was teaching the SQL Server PTO Workshop, I didn’t do a deep dive into how to troubleshoot writelog wait type.  Before I wrote something, I decided to look what was out there.  I think Sakthivel does a far more justice to the issue then I would have.  Please have a read, how T-Log works and why you might see writelog wait type.

• Sakthivel Chidambaram; What is WRITELOG waittype and how to troubleshoot and fix this wait in SQL Server? (Link)
• SQLCAT, Lindsey Allan; Diagnosing Transaction Log Performance Issues and Limits of the Log Manager (Link)
• SQLCAT Blog; Deploying SQL Server 2005 with SAN #3 (Link)
• CSS SQL Server Engineer Blog; Discussion About SQL Server I/O (Link)
• MSDN, SQL Server 2005 Technical Articles; SQL Server I/O Basics, Chapter 2 (Link)
• Bob Dorr, Microsoft SQL Server Principal PFE; Microsoft SQL Server Database Engine I/O PowerPoint Presentation (Link)

(copied links from Sakthivel’s Blog).

Replication Configuration Issue

Client Question

Replication setup keeps failing, permissions are correct.  Why does it keep giving me error code 5 (access denied).

Error Description

Command attempted:

\\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc\ReplFolder\WOI_WorkOrde180608a5_10125.pre
(Transaction sequence number: 0x0000005F0001433C00CC00000000, Command ID: 10128)

Error messages:

The process could not read file ‘\\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc\ReplFolder\20111114154553\WOI_WorkOrde180608a5_10125.pre’ due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21022)
Get help: http://help/MSSQL_REPL21022

Access is denied.
(Source: MSSQL_REPL, Error number: MSSQL_REPL5)
Get help: http://help/MSSQL_REPL5

The error message is fairly clear; OS Error 5 is error messages returned from Windows API when it is trying to read from the file.  But why was he getting this error?  All permissions are set; SQL Server (Publisher) is able to write to it, why wouldn’t the SQL Server (Subscriber) be able to read it if it is running under same service account?

Replication Configuration Issue - Setup Screen

If you take note of the image the location the file is written to; its \\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\..; problem with this is the R$ share.  This is an administrative share, you must grant the SQL Server service account local administrative permissions on your Distributor to be able to read the file from there; which is against security best-practices.  Therefore a better way to do is to use UNC paths.

1. Create a new folder on you Distribution Server.
2. Grant your SQL Server Service Account(s), if you are using more than one, permissions to read/write to the folder.
3. Create a HIDDEN share on the folder (any share with $ after words is hidden share; I generally think anything that doesn’t need to be public on network; hide it).
4. Control the permission on the share also; so only SQL Server Service Account can read/write to it.
5. Re-setup your Replication using \\ServerName\ShareName$\.

Client confirmed it worked for them

Last updated date for statistics?

There is many ways to get his information; however in most cases you get information for a single table. If you want to get it for all statistics in a database, how to do it? We can use the STATS_DATE function to get this information. Here is a simple query …

SELECT  OBJECT_NAME(s.OBJECT_ID)         AS TableName
      , s.name                           AS StatisticsName
      , STATS_DATE(s.OBJECT_ID,stats_id) AS LastUpdated
 FROM sys.stats s
 JOIN sys.tables t ON s.OBJECT_ID = t.OBJECT_ID

You can use this query to control what stats to build based on whent hey were last built.  With the change windows being small in some organization, you might not be able to rebuild all stats every week.  Specially for larger tables with large sample size; in which case basing your builds on Last Updated date will be helpful.

Getting List of Fragmented Indexes in a Database

Index fragmentation is naturally happens as an action of insert, deletes and updates.  It is important to manage this fragmentation, if not you can have performance issue with range type queries.

  SELECT   OBJECT_NAME(IPS.OBJECT_ID)       AS TableName
         , CASE WHEN i.name IS NULL THEN
              'HEAP TABLE, NO INDEXES'
           ELSE
              i.name
           END                              AS IndexName
         , index_type_desc                  AS IndexType
         , index_depth
         , avg_fragmentation_in_percent
         , page_count
    FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') IPS
    JOIN sys.indexes I ON IPS.OBJECT_ID = I.OBJECT_ID AND IPS.index_id = I.index_id
ORDER BY avg_fragmentation_in_percent

Adventures in Installing the SQL Server AdventureWorks Sample Database

I was thinking of writing an article about Microsoft SQL Server T-SQL Basics; however, in order to do the examples and demos I wanted to use a database that’s readily available on the Internet, so I decided to use the common AdventureWorks sample databaseavailable on Codeplex,  After downloading the database, it should have been an easy install. I’ve installed it hundreds of times with no issues, but this time it didn’t work!  Here’s my journey to figuring out a solution, hoping it will help some of you caught in similar circumstances.

After I agreed to the EULA, the installation abruptly stopped and I didn’t pay attention to error message. Maybe I should have, but then again if I did I wouldn’t have written this post. Anyhow, without trying to figure it out I decided to create the database manually by using the SQLCMD utility.

The AdventureWorks2008R2_SR1.exe file is self-extracting compressed file. I extracted the full contents of this file and got the following:

I didn’t know the AdventureWorks database gets created from scratch every time. I remember in the SQL 2005 days it was just MDF/LDF files. If you go into any of these folders you’ll see a myriad of files, including CSV, PNG, Visio Diagrams, etc..

The instawdb.sql file caught my eye. If you open it talks about using SQLCMD to create the database. So without any further delay I opened the command prompt and executed the following command from the directory where I had all these files extracted:.

sqlcmd -iinstawdb.sql -v SqlSamplesSourceDataPath="C:\Work\Tools\Sample Databases\AdventureWorks2008R2_SR1\" SqlSamplesDatabasePath="C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"

It didn’t work. An error came back stating that Filestream was disabled. Okay, no problem, open up SQL Server Configuration Manager and it should be easy fix.  But then I got another error:

I have run into this error (i.e. 0×80041010) before when I had multiple versions of SQL Installed. However, this was not the case here.  Searching on Internet, it quickly became apparent it is fairly common issue and I ran into two useful articles: one by Eric Charran and another on Microsoft Connect.

I tried the fix outlined in Eric’s article but I couldn’t find the MOF file he referenced. Digging into his post and few follow up links from there, I discovered that since I am running Windows 7 Pro x64-bit, my file is in the C:\Program Files (x86)\Microsoft SQL Server\100\Shared\ directory. So I tried executing the command Eric outlined:

mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

It failed again, giving me WMI error 0×80041003, which simply means “Current user does not have permissions to perform the action.” Seeing this, I decided to run same command using “Run As Administrator” on the command prompt and presto, it worked with no issues.

Now I retried SQL Server Configuration Manager and it worked! So thanks, Eric .  I made my change for the Filestream, came back to the command prompt for the Sample Database, executed the command and everything worked like a charm.

So it got me wondering why my setup failed to begin with. I ran the setup again to try and read the error message (FINALLY); but it worked this time so I didn’t see the error. It seems the Sample Database Installer is trying to read what services are installed on your computer and server so it knows where to install the sample databases when you select these. Duh! Oh well, it was still nice tracking it down and getting it working so I can actually work on my original article.

One lesson (re)Learned: Read error messages more carefully!

Also avalaible on OpsVault.com and MSDN Blog.

Whats the plan?

I don’t have one to be exact.  But I am at Microsoft GeekREADY! conference, learning lots of interesting stuff.  Some stuff about social networking and over all ePersona. So so I have three blogs now; going to try to do stuff that will help it organize what I am doing.

SQL Canada @ WordPress - Non-Microsoft, just what ever I feel like talking about, but it will be SQL.

SQL Canada @ MSDN (Link) – Experiences as SQL Server PFE (hopefully fun & interesting articles).

OpsVault (Link) – PFE Exclusive Articles, deep technical articles on various topics.  I am hoping to get deeper then my first attempt (yeeeh! I hope).

Any how…

I am thinking the posts will work this way (probably will change as my brain sorts out stuff)… Article on OpsVault will filter down to (MSDN & WordPress), Article on MSDN will filter down t0 (WordPress), WordPress, is its own man.. thing… it. Yaa thats it!

Okay 12:40AM, I need to clean up all the posts make sure links and images are good that have been ported over from my old blog home.  I might remove some of the older posts, I am unsure.  Anyhow for now bed calls, still got GeekREADY! tomorrow.

No wait, it’s now almost 1AM.  Just had another idea; WordPress might also include posts from my MSDN Forms and MSDN newsgroups issues I assist with .

Now its time to go sleep, if my brain stops turning.