Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Posts Tagged ‘Troubleshooting’

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.

image

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

image

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

image

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.

image

(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 AlwaysOnPaperback, Kindle
  • Posted in Troubleshooting | Tagged: , , , , , , , , | 2 Comments »

    Tips and Tricks : BACKUP LOG … TRUNCATE_ONLY

    Posted by blakhani on July 29, 2014


    Below are the most common search terms when someone is sees a big file with extension LDF and identifies that it’s used by SQL Server.

    • LDF file is huge
    • LDF Files Too Large
    • Shrink huge LDF File SQL
    • How to Shrink LDF File

    There are many article on the internet which can explain what these files are and how to reduce the size. Here is an official KB http://support.microsoft.com/kb/317375 If the size growth is due to the fact that database is in full recovery model and no log backup taken from long time then here are few common tasks are:

    • Take a transaction log backup and then shrink the LDF file.
    • Change recovery model to simple and then shrink the file.

    One situation which I faced recently that we were even unable to change the recovery model of the database because the drive the completely full. There was no space so taking log backup was not an option. In earlier version of SQL there was a option called TRUNCATE_ONLY which is removed in later versions of SQL but in SQL 2008 onwards, we would get below error message.

    Msg 155, Level 15, State 1, Line 2
    ‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

    Before attempting backup to NUL device (explained below) there are other thing you might want to try.

    • Add a new LDF file to database and take regular log backup and then shrink the log.
      • Above option would be a safe option as you still have log backups.
    • Change recovery model to simple and make it full again.
      • This will cause flush of inactive portion of log and you should be able to shrink the log.
      • Make sure you take a full backup immediate and this is your baseline.

    In certain situations the only option would be to take backup on NUL device (Yes that single L in NUL). What is NUL device? Try creating a folder with name NUL or rename a folder to NUL, you would get below error message.

    image

    [Window Title]
    Rename

    [Content]
    The specified device name is invalid.

    [OK]

    As per WikiPedia http://en.wikipedia.org/wiki/NUL:  The null device is a device file that discards all data written to it but reports that the write operation succeeded. … NUL: or NUL on DOS…
    The very important point here is “discards all data written” which means if we take backup using below command it would be reported as success but nothing is backed up.

    BACKUP DATABASE [Sales] TO  DISK = N'NUL' 
    


    Here is what we would see in ERRORLOG

    2014-07-29 07:41:07.67 Backup      Database backed up. Database: Sales, creation date(time): 2014/06/03(11:08:59), pages dumped: 315, first LSN: 48:360:37, last LSN: 48:392:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘NUL’}). This is an informational message only. No user action is required.

    2014-07-29 07:41:07.69 Backup      BACKUP DATABASE successfully processed 306 pages in 0.077 seconds (31.047 MB/sec).

    If we use DISK = NULL then it’s NOT The same.

    2014-07-29 07:45:50.10 Backup      Database backed up. Database: Sales, creation date(time): 2014/06/03(11:08:59), pages dumped: 316, first LSN: 49:16:37, last LSN: 49:48:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\NULL’}). This is an informational message only. No user action is required.

    2014-07-29 07:45:50.20 Backup      BACKUP DATABASE successfully processed 306 pages in 0.289 seconds (8.272 MB/sec).

    IMPORTANT: Taking backup like this would break the log chain which means next log backup would not be restorable. Here is what I mean.

    create database TestMe
    go
    alter database TestMe set recovery full
    go
    /*
    Command(s) completed successfully.
    */
    use TestMe
    go
    backup database TestMe to disk = 'F1.bak'
    go
    /*
    Processed 296 pages for database 'TestMe', file 'TestMe' on file 1.
    Processed 3 pages for database 'TestMe', file 'TestMe_log' on file 1.
    BACKUP DATABASE successfully processed 299 pages in 0.363 seconds (6.435 MB/sec).
    */
    use TestMe
    go
    Create Table TestTable1 (i int)
    go
    backup log TestMe to disk = 'T1.trn'
    go
    /*
    Processed 6 pages for database 'TestMe', file 'TestMe_log' on file 1.
    BACKUP LOG successfully processed 6 pages in 0.205 seconds (0.209 MB/sec).
    */
    use TestMe
    go
    Create Table TestTable2 (i int)
    go
    backup log TestMe to disk = 'NUL'
    go
    /*
    Processed 1 pages for database 'TestMe', file 'TestMe_log' on file 1.
    BACKUP LOG successfully processed 1 pages in 0.001 seconds (7.812 MB/sec).
    */
    use TestMe
    go
    Create Table TestTable3 (i int)
    go
    backup log TestMe to disk = 'T2.trn'
    go
    /*
    Processed 1 pages for database 'TestMe', file 'TestMe_log' on file 1.
    BACKUP LOG successfully processed 1 pages in 0.133 seconds (0.058 MB/sec).
    */
    
    use master 
    go
    drop database TestMe
    go
    
    
    

    Here is the attempt to restore the backups.

    USE [master]
    go
    RESTORE DATABASE [TestMe] FROM  DISK = N'F1.bak' WITH NORECOVERY,  NOUNLOAD,  STATS = 5
    GO
    RESTORE LOG [TestMe] FROM  DISK = N'T1.trn' WITH NORECOVERY,  NOUNLOAD,  STATS = 5
    GO
    RESTORE LOG [TestMe] FROM  DISK = N'T2.trn' WITH NORECOVERY,  NOUNLOAD,  STATS = 5
    GO
    

    and the error while restoring T2 is below

    Msg 4305, Level 16, State 1, Line 6

    The log in this backup set begins at LSN 48000000022400001, which is too recent to apply to the database. An earlier log backup that includes LSN 48000000020800001 can be restored.

    Msg 3013, Level 16, State 1, Line 6

    RESTORE LOG is terminating abnormally.


    Hope this gives you some idea about usage of backup to NUL device and danger as well.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Posted in Tips and Tricks | Tagged: , , | 4 Comments »

    Solution: The file "MDF" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

    Posted by blakhani on May 22, 2014


    Recently I was trying to create a database on my SQL Instance using below command

    CREATE DATABASE [MyDatabase]
    ON   
    ( NAME = N'MyDatabase', FILENAME = N'E:\BlogContent\DatabaseFiles\MyDatabase.mdf')
    LOG ON 
    ( NAME = N'MyDatabase_log', FILENAME = N'E:\BlogContent\DatabaseFiles\MyDatabase_log.ldf')
    GO

    But encountered below error

    Msg 5118, Level 16, State 1, Line 1
    The file "E:\BlogContent\DatabaseFiles\MyDatabase.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
    Msg 1802, Level 16, State 4, Line 1
    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    Error giving hint about some kind of compression. So if we look at the folder which is specified E:\BlogContent\DatabaseFiles it was in blue color.

    image

    Blue color actually indicates that folder is compressed. Right Click on folder, go to “Properties” and click on “Advanced…”

    image

    The checkbox “Compress contents to save disk space” is checked which means compression is enabled. Once we uncheck the box, we should be able to create database.

    Here is official stand about compression http://support.microsoft.com/kb/231347/en-us (SQL Server databases are not supported on compressed volumes). There is a great blog by Ken Henderson on this topic detailing the ramifications and I would encourage you to read it http://blogs.msdn.com/khen1234/archive/2005/04/25/411852.aspx 

    If we try to restore a database and keep files on compressed folder, same error is raised (in SQL 2014 Management Studio)

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    Restore of database 'MyDatabase' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
    ------------------------------
    ADDITIONAL INFORMATION:
    System.Data.SqlClient.SqlError: The file "E:\BlogContent\DatabaseFiles\MyDatabase.mdf" is compressed but does not reside in a read-only database or filegroup. 
    The file must be decompressed. (Microsoft.SqlServer.SmoExtended)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------

    Hope this helps!

     

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Error | Tagged: , , | 18 Comments »