Wednesday, March 6, 2013

Database cannot be opened due to inaccessible files or insufficient memory or disk space

Msg 945, Level 14, State 2, Line 1
Database 'db' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.


One of my friends called me that their application is giving error message of disk space , and i asked him to increase the space of drive. later he responded me that he shrinked the files and space has been raised to 70GB, but the error is still there.
I asked him to access the Database through management studio
When he tried to access the database, through a query or by trying to look at the database properties in Management Studio, he got the following message:

Database 'db' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. 


Here is Solution/Fix/workaround of this problem.

1. check the DB status, most of the time , it will return 1

use master
select databaseproperty('dbname','isShutdown')

2. Change the database to offline to clear the db status

use master
alter database
dbname set offline

3. Now change the database to online, at this step log file and data files will be verified by sql server


use master
alter database dbname set online


This solution solved the problem.

Tuesday, September 18, 2012

SQL SERVER – SSMS Automatically Generates TOP (100) PERCENT in Query Designer

SQL SERVER – SSMS Automatically Generates TOP (100) PERCENT in Query Designer:
Earlier this week, I was surfing various SQL forums to see what kind of help developer need in the SQL Server world. One of the question indeed caught my attention. I am here regenerating complete question as well scenario to illustrate the point in a precise manner. Additionally, I have added added second part of the question to give completeness.

Question:

I am trying to create a view in Query Designer (not in the New Query Window). Every time I am trying to create a view it always adds  TOP (100) PERCENT automatically on the T-SQL script. No matter what I do, it always automatically adds the TOP (100) PERCENT to the script. I have attempted to copy paste from notepad, build a query and a few other things – there is no success. I am really not sure what I am doing wrong with Query Designer.
Here is my query script: (I use AdventureWorks as a sample database)
SELECT Person.Address.AddressID

FROM Person.Address INNER JOIN

Person.AddressType ON Person.Address.AddressID = Person.AddressType.AddressTypeID

ORDER BY Person.Address.AddressID
This script automatically replaces by following query:
SELECT TOP (100) PERCENT Person.Address.AddressID

FROM Person.Address INNER JOIN

Person.AddressType ON Person.Address.AddressID = Person.AddressType.AddressTypeID

ORDER BY Person.Address.AddressID
However, when I try to do the same from New Query Window it works totally fine. However, when I attempt to create a view of the same query it gives following error.
Msg 1033, Level 15, State 1, Procedure myView, Line 6

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
It is pretty clear to me now that the script which I have written seems to need TOP (100) PERCENT, so Query . Why do I need it? Is there any work around to this issue.
I particularly find this question pretty interesting as it really touches the fundamentals of the T-SQL query writing. Please note that the query which is automatically changed is not in New Query Editor but opened from SSMS using following way.
Database >> Views >> Right Click >> New View (see the image below)

Answer:

The answer to the above question can be very long but I will keep it simple and to the point. There are three things to discuss in above script 1) Reason for Error 2) Reason for Auto generates TOP (100) PERCENT and 3) Potential solutions to the above error. Let us quickly see them in detail.

1) Reason for Error

The reason for error is already given in the error. ORDER BY is invalid in the views and a few other objects. One has to use TOP or other keywords along with it. The way semantics of the query works where optimizer only follows(honors) the ORDER BY in the same scope or the same SELECT/UPDATE/DELETE statement. There is a possibility that one can order after the scope of the view again the efforts spend to order view will be wasted. The final resultset of the query always follows the final ORDER BY or outer query’s order and due to the same reason optimizer follows the final order of the query and not of the views (as view will be used in another query for further processing e.g. in SELECT statement). Due to same reason ORDER BY is now allowed in the view. For further accuracy and clear guidance I suggest you read this blog post by Query Optimizer Team. They have explained it very clear manner the same subject.

2) Reason for Auto Generated TOP (100) PERCENT

One of the most popular workaround to above error is to use TOP (100) PERCENT in the view. Now TOP (100) PERCENT allows user to use ORDER BY in the query and allows user to overcome above error which we discussed. This gives the impression to the user that they have resolved the error and successfully able to use ORDER BY in the View. Well, this is incorrect as well. The way this works is when TOP (100) PERCENT is used the result is not guaranteed as well it is ignored in our the query where the view is used. Here is the blog post on this subject: Interesting Observation – TOP 100 PERCENT and ORDER BY. Now when you create a new view in the SSMS and build a query with ORDER BY to avoid the error automatically it adds the TOP 100 PERCENT. Here is the connect item for the same issue. I am sure there will be more connect items as well but I could not find them.

3) Potential Solutions

If you are reading this post from the beginning in that case, it is clear by now that ORDER BY should not be used in the View as it does not serve any purpose unless there is a specific need of it. If you are going to use TOP 100 PERCENT with ORDER BY there is absolutely no need of using ORDER BY rather avoid using it all together. Here is another blog post of mine which describes the same subject ORDER BY Does Not Work – Limitation of the Views Part 1. It is valid to use ORDER BY in a view if there is a clear business need of using TOP with any other percentage lower than 100 (for example TOP 10 PERCENT or TOP 50 PERCENT etc). In most of the cases ORDER BY is not needed in the view and it should be used in the most outer query for present result in desired order. User can remove TOP 100 PERCENT and ORDER BY from the view before using the view in any query or procedure. In the most outer query there should be ORDER BY as per the business need.
I think this sums up the concept in a few words. This is a very long topic and not easy to illustrate in one single blog post. I welcome your comments and suggestions.
Reference: Pinal Dave (http://blog.SQLAuthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, SQL View, T SQL, Technology



Thursday, May 3, 2012

Lengh of LOB data to be replicated exceeds configured maximum 65536

Whenever you try to replicate data from a database that saves the images(filestream) in the database, and you have included those images in your replication, then you might get this error

Length of LOB data (583669) to be replicated exceeds configured maximum 65536

 Solution:

There are two solutions for this problem 
  • using TSQL
    • sp_configure 'max text repl size', '2147483647'
      GO
      RECONFIGURE 
  • using SQL Server Manager Studio
    • In Object Explorer, Right click on your server name and select properties 
    • Click the Advanced node
    • Under Miscellaneous option, change Max Text Replication Size to the desired value.




Tuesday, April 3, 2012

Attached failed, Unable to open physical file, operating system error

Some time when you try to attach database files (.mdf, . ldf) to sql server , you might get the error
"Unable to open physical file, operating system error"


The reason behind this is that the account from which you are accessing the file , does not have rights for this specific folder or file.

Solution for this is that , you have to give the rights. In my case i have given the rights to the local administrator





After giving the rights , try to attach the file again.

I have attached the database successfully.





Tuesday, September 27, 2011

A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column

I was trying to add a filestream column in one of my existing tables, initially i thought it would be very easy for me, but when i got the error "A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column."  i started to look here and there. Surfed on the net but didn't find a solution.
Below is the solution which i tried.

Workaround to produce the problem again

CREATE TABLE Employee (
    [emp_id] [int] IDENTITY(1,1) NOT NULL,
    [emp_name] [nvarchar](150) NULL,
    [emp_FHname] [nvarchar](150) NULL,
    [emp_age] [int] NULL,
    [emp_sex] [bit] NULL,
 CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED
(
    [emp_id] ASC
)
) ON [PRIMARY]

GO

emp_id is defined as IDENTITY and Primary key

now i wants to add a filestream enabled column named "emp_pic" to store employee pictures, and the table doesn't have any ROWGUIDCOL so i added a empid

ALTER TABLE Employee ADD
    empid UNIQUEIDENTIFIER DEFAULT (newid()) ROWGUIDCOL NOT NULL


After this i executed this statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM
 

it will give you the error  Msg 5505, Level 16, State 1, Line 1
A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

To solve this problem i just changed my primary key column to empid that i added later as ROWGUIDCOL
After changing the primary key, execute the statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM

It will be executed successfully








Monday, April 11, 2011

An easy way to get the Definition of an Object

I was using the sp_helptext very frequently to get the definition of an object.

Here is another easy way to get the definition of an object

SELECT OBJECT_DEFINITION(object_id) FROM sys.objects WHERE type='V'

You can use the, ALTER in the result set of the above query by using the REPLACE.

In the where condition you can specify the Procedure(P) or Functions(FN) as well.

Monday, April 4, 2011

Difference between NEWSEQUENTIALID() and NEWID()

In this article, we will be looking at the Difference between NEWSEQUENTIALID() and NEWID() and How NEWSEQUENTIALID() is generated.

NEWSEQUENTIALID() generates the unique identifier values sequentially.
Whereas NEWID() generates the unique identifier values randomly.

NEWSEQUENTIALID() function creates the GUIDs greater than the previously generated GUIDs, since the last restart of the system, because after restarting the system the next NEWSEQUENTIALID can be started from the lower range.
Both NEWID() and NEWSEQUENTIALID() are globally unique. Starting of NEWSEQUENTIALID after the restart of the computer does not affect its globally uniqueness.

NEWSEQUENTIALID() uses the default constraint, and it can't be used in SELECT or SET queries like NEWID().
The following query SELECT NEWSEQUENTIALID() will generate error

The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

This will also generate the similar error

declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique

Msg 302, Level 16, State 0, Line 3
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

while if you use
SELECT NEWID()
OR
declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique

No error will be produced and guid will be returned.

NEWID() is not good for performance because it is generated randomly so that it increases the page splits in the indexes.
NEWSEQUENTIALID() can be used to generate GUIDs to reduce page contention at the leaf level of indexes.

Each GUID generated by using NEWSEQUENTIALID() is unique on that computer.
GUIDs generated by using NEWSEQUENTIALID() are unique across multiple computers only if the source computer has a network card.
NEWSEQUENTIALID() returns the value that includes the MAC of the NIC of the system. If the system has the network card, then it is guaranteed that the GUID generated using NEWSEQUENTIALID() will globally unique across the servers.
If the NEWSEQUENTIALID() used on a computer that doesn't have NIC, the values generated using NEWSEQUENTIALID() are not guaranteed to be unique across the servers, it will be unique only for that system.
Internally NEWSEQUENTIALID() usses a Windows API called UuidCreateSequential(), which generates the value based on the NIC's MAC address and an internal hardware timestamp.

Here you can see the Newsequentialid (Histrory/Benefits and Implementation)

Share This