Mar
20
Written by:
Cindy Gross
Thursday, March 20, 2008 8:31:38 AM
Some SQL Server troubleshooting and investigation requires checking operating system configuration, security settings, and currently running processes. These tools can help you in those sorts of issues. They are free downloads available from TechNet.
You can download individual utilities or the entire Sysinternals troubleshooting suite. You can click through on each item on this page for a full description of its usage and what operating systems it runs on. Some only run on x86, some also run on x64.
http://technet.microsoft.com/en-us/sysinternals/0e18b180-9b7a-4c49-8120-c47c5a693683.aspx
Some of the utilities that might be useful for your SQL Servers:
• ProcMon – Combines FileMon and RegMon plus adds some new features. It shows all activity in the file system, registry keys, and processes/threads. Example or usage: 918695 You may experience a failure when you install SQL Server 2005 Service Pack 1 on an instance of SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;918695
• Contig – if you don’t have time to analyze and defrag the entire drive, you can use this tool to analyze and defrag individual directories or files. This can be useful if you’ve been letting autogrow and autoshrink control the size of your database. In that case you might have hundreds or thousands of fragments slowing down your performance. It also lets you determine ahead of time how fragmented your most important files are and plan downtime for defragmenting them.
315512 INF: Considerations for Autogrow and Autoshrink configuration in SQL Server http://support.microsoft.com/default.aspx?scid=kb;EN-US;315512
• AccessEnum/AccessChk – find out which users have access to which files. You may find out that more people than necessary have access to backups, SQL data and log files, etc. You can use the information to remove unneeded permissions and reduce the change an important file will be deleted, overwritten, or tampered with. It can also help with some “access is denied” messages such as when a linked server tries to use a driver in a folder with locked down permissions.
• Handle (can also use Process Explorer) – Sometimes you’ll see a message that a database file cannot be opened because it’s already in use. Handle can track down what application (often a virus scanner or backup software) has the file open.
• PendMoves – Could be useful in setup troubleshooting. It shows what files will be replaced at reboot per HKLM\System\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations.
• ProcFeatures/PSInfo – Quick look at basic CPU properties and basic system info. Use for documenting server configuration.
• PSExec – Execute processes remotely on a server. Could be useful in automating activity.
• PSFile – Lets you view and close files opened remotely.
• PSList – Lists basic info on running processes. Could help in performance or setup cases where you need to know what else is executing on the box.
• PSSuspend – Lets you suspend a process without killing it. This could help in ruling out processes in performance troubleshooting.
You can learn more about the tools here (only the “tour” video is free):
http://solsem.com/videolibrary.html
Discuss the tools here:
http://forum.sysinternals.com/default.asp
http://blogs.technet.com/sysinternals/?wt.svl=whatsnew
Copyright ©2008 Cindy Gross
Tags: