December 2025
M	T	W	T	F	S	S
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

Archive for the ‘Troubleshooting’ Category

Tips and Tricks : OS error: 32(The process cannot access the file because it is being used by another process.).

Posted by blakhani on August 7, 2014

While playing with tempDB database on my machine, I have made some mistake and then was not able to start one SQL Instance. As usual, started troubleshooting and used sysinternals tool to find the cause of the problem.

First, I looked into ERRORLOG and found below messages. I have highlighted some text for clarity.

2014-08-07 05:53:44.13 spid11s     Clearing tempdb database.
2014-08-07 05:53:44.40 spid11s     Error: 5123, Severity: 16, State: 1.
2014-08-07 05:53:44.40 spid11s     CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file ‘E:\TempDB\tempdb.mdf’.

2014-08-07 05:53:45.42 spid11s     Error: 17204, Severity: 16, State: 1.
2014-08-07 05:53:45.42 spid11s     FCB::Open failed: Could not open file E:\TempDB\tempdb.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2014-08-07 05:53:45.43 spid11s     Error: 5120, Severity: 16, State: 101.
2014-08-07 05:53:45.43 spid11s     Unable to open the physical file "E:\TempDB\tempdb.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2014-08-07 05:53:45.46 spid11s     Error: 1802, Severity: 16, State: 4.
2014-08-07 05:53:45.46 spid11s     CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2014-08-07 05:53:45.46 spid11s     Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2014-08-07 05:53:45.46 spid11s     SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
2014-08-07 05:53:49.68 Logon       Error: 17188, Severity: 16, State: 1.
2014-08-07 05:53:49.68 Logon       SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [CLIENT: <local machine>]

Due to OS Error 32, SQL was not able to use files which are needed by TempDB database and unable to start.

Next task for us would be to find out which is that “another process”. If it’s an open handle by a use mode process we would be able to find out using Process Explorer. Once you download and run it, we can see something like below.

Then press Ctrl+F or use Menu option “Find” > “Find Handle or DLL” as shown below

In the find window provide file name with complete path and search. I was able to get below

Now, since I know that “another process” I can take the action which is suitable. This happened to me because my two instances are pointing to same location for tempdb database files. I have rectified them on non-starting instance by below steps.

1. Started SQL Server using “Net Start MSSQL$SQL2014 /mSQLCMD /f /T3608”
2. Connected to SQL via SQLCMD –S(local)\SQL2014
3. Executed below T-SQL

USE master; 
GO 
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\tempdb.mdf'); 
GO 
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\tempdb.ldf'); 
GO

4. Stopped SQL via “net stop MSSQL$SQL2014”

5. Started SQL normally.

(Click on Image to enlarge)

Hope this would help you in troubleshooting OS Error 32 for other application as well.

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in Troubleshooting | Tagged: Could not create tempdb, Error: 17204, Error: 1802, Error: 5120, Error: 5123, SQL, sql server, The process cannot access the file because it is being used by another process, Troubleshooting | 2 Comments »

Troubleshooting : Slow Delete Database from Management Studio

Posted by blakhani on July 10, 2014

Not very long ago, I had a database on my SQL 2012 Instance which was getting log shipped happily at frequency of 1 minute. Long long ago I have done such configuration for a demo purpose and forgot. Today I had “Memory Recall” when space was getting filled up with log backups. Since the demo was complete, I decided to drop the database. So I broke log shipping and tried dropping the database. What you do as a DBA to drop a database? Right Click > Delete .. huh?

When I clicked on “OK” button it was taking a long time. GUI to seem like its hanging. As usual, troubleshooting started! Ran my standard troubleshooting query to find out what is going on.

 SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id 'Blk by'
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'Wait Sec'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'Elaps Sec'
    ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
            (
                CASE r.statement_end_offset
                    WHEN - 1
                        THEN Datalength(st.TEXT)
                    ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
    ,r.command
    ,s.login_name
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.last_request_end_time
    ,s.login_time
    ,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
    ,r.STATUS
    ,r.blocking_session_id
    ,s.session_id

Here was the result (I have removed few columns to avoid clutter)

session_id

status

cpu_time

logical_reads

writes

Elaps Sec

statement_text

command_text

runnable

247734

42732577

29196

545.223

DELETE msdb.dbo.backupmediaset

FROM msdb.dbo.backupmediaset bms

WHERE bms.media_set_id IN (SELECT media_set_id

FROM @media_set_id)

AND ((SELECT COUNT(*)

FROM msdb.dbo.backupset

WHERE media_set_

[msdb].[dbo].[sp_delete_database_backuphistory]

Why would delete database do that? Well, it’s done by a small little checkbox which we never noticed.

That little checkbox executed this command (along with drop database). If we use “Script” button, this is the outcome

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorks2014'
GO
USE [master]
GO
DROP DATABASE [AdventureWorks2014]
GO

Now we know why it’s taking time but can this be made faster? Well, I check msdb database and there are few indexes which have been added in SQL Server 2014 which would help in this situation. Here is the quick comparison.

Select @@version
go
SELECT 
     TableName = t.name,
     IndexName = ind.name,
     ColumnName = col.name
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND t.is_ms_shipped = 1
     AND t.name in ( 'backupfile', 'backupfilegroup', 'backupmediafamily', 'backupmediaset', 'backupset', 'restorefile', 'restorefilegroup', 
     'restorehistory' ) 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id

If you are facing the same problem which I described on SQL 2008 or SQL 2012, you may want to try creating new indexes as advised on other blogs (search for “msdb performance tuning” in bing/google) but my only suggestion is that it might be unsupported.

If you clean msdb backup history regularly, you might not face the issue though. There is maintenance plan to do that. Try it out!

Hope this helps.

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in Troubleshooting | Tagged: delete database, drop database, performance, sql server, ssms | 4 Comments »

Help : How to find cause of “Login failed for user” error

Posted by blakhani on July 8, 2014

“Login failed for user” is one of the most common error which everyone might have seen at least once. In this blog, I am going to share few possible causes of the error and their solution. First, we need to understand that due to security reasons, SQL Server doesn’t send more information about this error message to client. Here is what client would see in all situations (I have done it from SSMS)

TITLE: Connect to Server
——————————
Cannot connect to .\SQL2014.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘sa’. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
——————————
BUTTONS:
OK
——————————

If we click on more details, we would see this

——————————
Server Name: .\SQL2014
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

This error doesn’t tell “exact” reason about login failed for user. SQL Server deliberately hides the nature of the authentication error and gives State 1.

The very first thing which I always ask is look at ERRORLOG and find error at exact same time. By default, auditing of failed logins is enabled. In this case, the true state of the 18456 error is reported in the SQL Server Errorlog file. You can verify them by Right Click on Server node > Properties > Security and check

Here is what we would see in ERRORLOG is failed login auditing (option 1 and 3 in above image) is enabled.

2014-07-08 04:37:07.910 Logon Error: 18456, Severity: 14, State: 8.
2014-07-08 04:37:07.910 Logon Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

It is important to note that in earlier version of SQL Server (before SQL 2008), the error message was something like below.

2014-07-08 04:37:07.910 Logon Error: 18456, Severity: 14, State: 8.
2014-07-08 04:37:07.910 Logon Login failed for user ‘sa’. [CLIENT: <local machine>]

Notice that reason was not shown in earlier version of SQL in ERRORLOG. In those days, we used to keep a track of all states and their meanings but thankfully, I don’t need it any more. Here is the quick cheat sheet which I have saved. You can easily reproduce them. I have given meaningful login name.

State 5: Incorrect login name provided.

2014-07-08 05:35:48.850 Logon Error: 18456, Severity: 14, State: 5.
2014-07-08 05:35:48.850 Logon Login failed for user ‘InvalidLogin’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

State 7: Account disabled AND incorrect password.

2014-07-08 05:41:30.390 Logon Error: 18456, Severity: 14, State: 7.
2014-07-08 05:41:30.390 Logon Login failed for user ‘DisabledLogin’. Reason: An error occurred while evaluating the password. [CLIENT: <local machine>]

Notice that state 7 would come if account is disabled and incorrect password is supplied. If correct password is supplied for disabled login then client would get below error (18470)

2014-07-08 05:41:22.950 Logon Error: 18470, Severity: 14, State: 1.
2014-07-08 05:41:22.950 Logon Login failed for user ‘DisabledLogin’. Reason: The account is disabled. [CLIENT: <local machine>]

State 58: SQL account used to attempt to login on “Windows Only” authentication mode SQL instance.

2014-07-08 06:21:36.780 Logon Error: 18456, Severity: 14, State: 58.
2014-07-08 06:21:36.780 Logon Login failed for user ‘sa’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

Above can be fixed by this blog by Pinal (b|t|f)

State 11: SQL login account getting deny permission via some group membership.

2014-07-08 06:21:36.780 Logon Error: 18456, Severity: 14, State: 11.
2014-07-08 06:21:36.780 Logon Login failed for user ‘SQL_Login’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

State 12: Windows login account getting deny permission via some group membership or UAC.

2014-07-08 06:21:34.840 Logon Error: 18456, Severity: 14, State: 12.
2014-07-08 06:21:34.840 Logon Login failed for user ‘domain\user’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

To solve state 11 and 12, it is important to find how we are getting deny permission for that account. In most of the cases, it might be due to UAC and this should work but it’s only for local connections.

GRANT CONNECT SQL TO [DOMAIN\User]

In few cases, we can run below query to find if any group has deny permission.

SELECT prin.[name]
    ,prin.type_desc
FROM sys.server_principals prin
INNER JOIN sys.server_permissions PERM ON prin.principal_id = PERM.grantee_principal_id
WHERE PERM.state_desc = 'DENY'

If you find any other state, post in comment, I would enhance the blog further and add their cause.

Hope this helped!

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in Troubleshooting | Tagged: Error: 18456, login failed for user, Severity: 14, SQL Error 18456, State: 5, State: 58, State: 7, State: 8 | 10 Comments »

« Previous Entries

Next Entries »

Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Blog Stats

Select GETDATE()

Subscribe

Follow Blog via Email

Date < getdate()

Select location from world

Meta

Archive for the ‘Troubleshooting’ Category

Tips and Tricks : OS error: 32(The process cannot access the file because it is being used by another process.).

Troubleshooting : Slow Delete Database from Management Studio