- IIS (1)
- Open Source (1)
- Performance (4)
- Personal (3)
- Powershell (1)
- SQL (1)
- SQL Server (21)
- T-SQL (15)
- Uncategorized (6)
- Utilities (5)
- Windows OS (14)
- 14. April 2011: Deduplicating files with LogParser and SQL Server
- 25. February 2011: The final voyage of the USNS H. H. Hess
- 16. February 2011: Free SQL Server training videos
- 23. August 2010: Alert for long-running SQL datbase backups
- 7. April 2010: Learning SMO & Powershell
- 25. February 2010: SQL Generators for moving database files
- 28. January 2010: Index to Filegroup mapping
- 20. January 2010: PowerShell Script to Clean Up Old Files Based on Age
- 7. January 2010: Quick & Dirty way to identify orphan files
- 29. July 2009: Trigger Mass Enable / Disable
- April 2011
- February 2011
- August 2010
- April 2010
- February 2010
- January 2010
- July 2009
- June 2009
- February 2009
- January 2009
- December 2008
- November 2008
- October 2008
- August 2008
- June 2008
- May 2008
- April 2008
- March 2008
- February 2008
- January 2008
- December 2007
- October 2007
- September 2007
- May 2007
- April 2007
- February 2007
Troubleshooting SQL Server with Filemon
Today I came across an interesting problem while changing the SQL server service to run on a non-administrative domain account. Previously the service was configured to log in as localsystem, which has full rights locally, but no domain rights. With this change, the service would start and then terminate. Here is a SQL error log snippet:
2007-02-13 15:05:01.29 spid5 Clearing tempdb database.
2007-02-13 15:05:01.58 spid5 Encountered an unexpected error while checking the sector size for file ‘k:\mssql\MSSQL\data\tempdb.mdf’. Check the SQL Server error log for more information.
2007-02-13 15:05:01.62 spid11 Starting up database ‘Student’.
2007-02-13 15:05:01.72 spid10 Starting up database ‘Diners’.
2007-02-13 15:05:01.80 spid9 Starting up database ‘distribution’.
2007-02-13 15:05:02.10 spid8 Starting up database ‘Inventory’.
2007-02-13 15:05:02.72 spid11 Starting up database ‘NorthwindReportData’.
2007-02-13 15:05:02.80 spid10 Starting up database ‘TSQLDB’.
2007-02-13 15:05:02.83 spid9 Starting up database ‘SSEMDB’.
2007-02-13 15:05:03.57 spid5 CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
2007-02-13 15:05:03.57 spid5 WARNING: problem activating all tempdb files. See previous errors. Restart server with -f to correct the situation.
Making the service account a member of the administrators group, fixed the problem, but of course this is not a good idea. I decided to use the SysInternals File Monitor, filemon.exe, to see if I could find the cause of the problem. What I found was that mssql.exe was trying to access the root of the volume where the SQL data files exist and was getting an access denied error. The fix was to grant the service account list access to the root of that volume. With that change, the sqlserver.exe service came up and stayed up. I also found a Technet article that points to the same solution for a similar problem: PRB: Error 5177 May Be Raised When Creating Databases
By the way, SysInternals was recently acquired by Microsoft and Mark Russinovich is now a Microsoft Employee. It appears that the great SysInternals utilities continue to be maintained.