Open failed. Could not open file mdf for file number 2. OS error 5(Access denied)

Today my one client told me that their database could not access in yesterday, we try to use the database and got message error 

the database could not access due to disk space or insufficient memory

First I checked the disk space and found that there is ample disk space.

Then I checked SQL Server error log, It shows the below error:

FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file DATABASE_log.ldf ‘. Diagnose and correct the operating system error, and retry the operation.

Generally an OS error 5 means there is no permission to modify a particular file. Than I suspect that the account that is running SQL Server Service does not have permissions to modify the LDF and MDF files.

So, then I checked the write permission to the file by right click on the files (both mdf and LDF) and select properties and than from security TAB checked the write permission to these files. I set the write permission. 

Now problem solved.

Swap data between columns

Sometime we may mistakenly insert data into a wrong column if we have two identical columns with same data type. To correct this we have to delete the data from the wrong column and insert that in the proper column. Here we had to do lot of work. Swapping can be done :
                              
1.with the help of a temporary variable .
   or
2.directly.

Suppose we have a table named Customer with 3 columns: CustomerID, FName, LName.
       
              CustomerID: int 
              FName: varchar(35)
              LName: varchar(35)

and we have inserted some rows to the table.

Insert into Customer values(1,'Ladha','Arun')
Insert into Customer values(2,'Daga','Ashit')
Insert into Customer values(3,'Shah','Nishi')
Insert into Customer values(4,'Singh','Ayan')

Here I had inserted FName data in LName and LName data into FName.
Now, I want to swap them.

1. Swap with the help of temporary variable:
-------------------------------------------------
DECLARE @temp AS varchar(10)

UPDATE Customer SET @temp=LName, LName=FName, FName=@temp

  The resulting  table can be verified.

2.directly:
------------
UPDATE Customer SET LName = FName, FName = LName

This query can also swap the column data.

database last accessed date time

Sometime you may required to know the last accessed date and time of database..
Below query returns the lass accessed date and time of SQL Database.


SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT 
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY MAX(LastAccessDate)

The biggest problem of above query is that all information will be lost when you restart SQL Server. So, this is not applicable for servers which frequently restarted. 

Other problem is related to Full-Text-Catalogs. If you have enabled Full-Text-Index on your tables, you might see a record saying that database has been accessed without any user operations. This is because of Full-Text-Indexes.

Verifying Foreign Keys in MS SQL Server

Today I was checking one Demo database and surprised to see that foreign key columns contained some values which were not stored in the referenced tables. The foreign key constraints were there, but they had been deactivated with the WITH NOCHECK clause. 

The below script verify the foreign key constraint:

DECLARE @TableName sysname, @ForeignKey sysname, @String NVARCHAR(1000)

DECLARE cur CURSOR FOR

SELECT t.name, fk.name

FROM sys.foreign_keys fk

INNER JOIN sys.objects t ON t.object_id = fk.parent_object_id

ORDER BY t.name, fk.name

OPEN cur 

FETCH cur INTO @TableName, @ForeignKey

WHILE @@FETCH_STATUS = 0 BEGIN

SET @String = 'ALTER TABLE ' + @TableName + 

' WITH CHECK CHECK CONSTRAINT ' + @ForeignKey

PRINT @String

BEGIN TRY

EXEC (@String)

END TRY

BEGIN CATCH

PRINT 'Conflict in ' + @TableName + N'.' + @ForeignKey

DBCC CHECKCONSTRAINTS (@TableName)

END CATCH

FETCH cur INTO @TableName, @ForeignKey

END

CLOSE cur

DEALLOCATE cur

SELECT t.name, fk.name, fk.is_disabled, fk.is_not_trusted

FROM sys.foreign_keys fk

INNER JOIN sys.objects t ON t.object_id = fk.parent_object_id

WHERE fk.is_not_trusted = 1

ORDER BY t.name, fk.name

The script tries to enable each foreign key constraint it finds in the database using the WITH CHECK CHECK CONSTRAINT clause. 

If the check fails, it calls DBCC CHECKCONSTRAINTS which returns the set

of recordings violating the foreign key constraint. 

The result of the script execution shows you which tables and records need to be cleaned up. 

Run the script repeatedly after data cleanup until a single empty result set is returned.

The database cannot be opened because it is version 706. This server supports version 661 and earlier

Today I was trying to Attach a database in SQL server 2008 R2.

I got below error while attaching database:

This is because the data file I was trying to attach is from SQL server 2012. I was trying to attach this in SQL Server 2008. The only solution to this problem is to generate script from SQL server 2012 and than run that script in SQL server 2008.