Error 5120 is a sharing violation of the files we are trying to open.

Possible reasons:

• Incorrectly configured path of MDF and LDF files
• Either of MDF or LDF files are missing
• Because the file is already attached to one of the existing databases on the server

Possible Solutions:

• When attaching the MDF file, remove the log file (assuming you have taken full back of the DB before detach and attach) by clicking on remove using UI
  or
  use SQL script

[cc lang=”TSQL”]

— Attaching MDF File without ldf file
CREATE DATABASE PubsDB ON
(FILENAME = N’D:\MSSQL\DATA\PubsDB.mdf’)
FOR ATTACH_REBUILD_LOG
GO

— if one file missing
CREATE DATABASE PubsDB ON
( FILENAME = N’D:\MSSQL\DATA\PubsDB.mdf’)
FOR ATTACH
GO

— recreate all log files
EXECUTE sp_attach_single_file_db @dbname = ‘PubsDB’,
@physname = N’D:\MSSQL\DATA\PubsDB.mdf’
GO

[/cc]

It is a clear indication that either MDF or LDF files is missing.

Try below options:

• give permissions to the account running SQL Server service to the location where MDF & LDF files are located

• open SSMS with admin privileges

• modify the MDF and LDF files properties as below

right-click on the file in Windows Explorer, select Properties
select the Security tab
Click Advanced
Click Change Permission
Uncheck “Include inheritable permissions…”, a window will open
Click Remove (removes all permissions), the window will close
Click Add
Enter your login name and click OK, the permission window will open
Check Full Control – Allow
Click OK, OK, OK, OK

Here are some important perfomon counters that can be captured to track the performance of SQL Server.

Average Disk sec/read – measures how much time it takes to for an IO request to be sent from the application to the storage system

Average Disk sec/read – measures how much time it takes to for an IO request to be sent from the storage system to the application

Disk read/sec and disk write/sec – track the physical data being transferred

Disk Queue length let’s us know how busy the system

% Processor time – shows the processor load on the server.

System: Processor Queue Length – determines how many tasks are waiting on the CPU.

Memory : Available Mbytes – tells the memory currently in use

How does SQL Server read and write ?

• normally Reads in 64K data extents
• normally Writes in 8K data pages

Simple answer is No.

in fact when you try to issue a DML statement it complains:

Invalid use side-effecting operator Insert within a function

This is one of the limitation of SQL server function as defined in books online.
Functions are used to fetch data and stored procedures are ideally suited for all DML operations.

The error is normally generated when migrating the applications or code segments that used to run prior to SQL Server 2012 to later versions of SQL Server 2012 (Because it was deprecated in SQL Server 2012).

• When can you get it ?
  When the SQL script was created in earlier versions before SQL Server 2012 ()i.e compatibilty mode < 110). So installation of older databases like pubs, northwind fail.

• When trying to create a sharepoint 2003 site (WSS 2.0) collection as it relies on this system stored procedure using SQL Server 2012

• when configuring reporting services for sharepoint 2010 (non SP1) using SQL Server 2012 (SQL 2012 only works with SP 2010 SP1 or higher) – so slipstream SP1 for Sharepoint 2010 installation

• When configuring ACM (audit and control management) server for Sharepoint

• When creating WebMarshal database

Solution:
replace the occurrence of sp_dboption with ALTER DATABASE statement manually.

Please see attached sp_dboption SQL file when needed.

version 1

version 2