Troubleshooting Site Usage issue in sharepoint 2007

  • Article

 

Troubleshooting Site Usage Report:

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

  1. We first check if there is any patching done to the server. If yes, which patch and does it have any effect on the Site usage report or any known issue with the site usage report.
  2. Check if the Usage Log files are getting generated in the Usage Logs folder.
  3. If the files are not getting generated then it could be a permission issue and we need to add WSS_ADMIN_WPG group with Read and Execute, List Folder contents and Just Read permission. Then add WSS_WPG group with the same permissions.
  4. If the Usage Log files are getting generated then open the Log file and see if the Logs are getting processed or not. If the logs are processed then it will add & symbol in every line that just after the URL.
  5. If the Logs are not getting processed then we can enable Procmon on the Usage Analysis folder to capture all the activities that happens on that folder. This will give us a clear view as what’s going on during the Logs processing time. Based on the result of Procmon we can troubleshoot further. But the common Procmon should show something like this. You should first see the Explorer.exe process creating the GUID folder and the date folder inside the Usage Analysis folder. You should then see W3WP.exe process dumping the IIS Logs into the Usage Logs. Then you should finally see the OWSTIMER.exe process running that will process the IIS Logs and adds & symbol at the end of the URL.
  6. Let’s say the Usage Logs are also getting processed with no problems. Then we need to dig into the database.
  7. Go to SQL Management studio and run the below query on the SSP database.

                Select * from ANLDay with no lock

  1. You will see 3 columns (DayId, FullDate and IsHistorical). Now look into the latest DayId column and FullDate column and see if you can see the Yesterday’s date.
  2. If you do see it then run the below command on the SSP database.

Select * from ANLHistoricalSiteUsage no lock

  1. You will again see 4 columns (DayID, SiteGuid, Hits and UniqueUsers).
  2. Compare the latest DayId with the ANLDay table and they should be in Sync. If these Two tables are not in Sync then try running the command Stsadm – execadmsvcjobs and wait for some time to see if they get in Sync. If not then the only way to resolve this is to Disable and Re-enable the Site Usage from SSP first and then in Central admin.
  3. Let’s suppose the Two tables are in sync then and still Site usage report shows 0 entries then the only way is to REBOOT the Sharepoint Server.

For more information you can check ResourceID in the ANLHit and ANLResource tables in SSP database. Apart from restarting the server, what you can also do is to take a SQL profiler trace and look for the Stored procedure that updates the Database information to the site and dig more into it to see why it’s not doing it.