Clean Install Windows Auth Error

Hi,

I cannot log in to SQL Server 2005 Dev Edition in my local machine using Windows Authentication. The server returned "Login failed..." when connecting with SQL Server Management Studio.

I have not change anything since installation of this server.

This problem happens in RTM and SP1 versions, both running on Windows Vista RTM.

Anyone having this kind of problem too? Any solution? I'm guessing it's Vista-related.

True, this is something of Vista. http://www.microsoft.com/sql/howtobuy/windowsvistasupport.mspx

Sorry for not reading it first.

Clarification on Joins

Dear friends,

Can any one clearly explain about all type of joins or any tutorials for that. please help me b'se i failed it to explain in an interview clearly

samy

Quote:

Originally Posted by samycbe

Dear friends,

Can any one clearly explain about all type of joins or any tutorials for that. please help me b'se i failed it to explain in an interview clearly

samy

These are SQL server specification.

Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Cross joins.
Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.|||Thanks and Its really helpful

Child packages : Failed to acquire connection

Hi!

I am having a problem with a Parent package that invokes Child SSIS packages.

The Child packages have EncryptSensitiveWithPassword as their security setting.

I have placed the PackagePassword for each Child in the Parent package.

Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.

Here are what seem to be the relevant entries from a log file when the failure occurs:

Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.
OnError,XXXX,YYYYY\xxxx,SQLTask1 in ChildPackage1,GUID3,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

OnError,XXXX,YYYYY\xxxx,ChildPackage1,GUID4,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

When I run the Child package by itself, it works without any errors. So the problem seems to be related to the fact that it is being called by the parent, and somehow the "security settings" for the parent are not quite the same as the settings when I run the Child package by itself.

What might I be doing wrong?

I am using File System storage for my packages, on a shared network drive.

Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm. |||

Yes, I have set the password in each Execute Package task -- and the error message is not about an inability to open the package (for which there appear to be a number of error messages available). Instead, it is one that seems to indicate an inability to login to SQL Server with the SQL Server Authentication incorporated in the package.

I have read some web pages (e.g., the link below) that refer to the Integrated Security of a SQL Server connection. I see what appears to be some XML for that in the Child packages (but none in the Parent package) -- and the value that I found was "True". I tried changing it to "SSPI", but then I couldn't even run the child package by itself, nor when called by the Parent.

http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx

|||

Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?

Dan

|||

I'm going to take a look at using the script Jamie Thompon wrote, as found at

http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.

|||

John,

Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.

I am sorry to have dismissed your suggestion so quickly -- but I didn't see it until I was off on vacation and happened to logon and notice your post. So I didn't have SQL Server at my disposal to investigate further. You have my sincere apologies.

Dan

|||No problem - I'm glad you were able to resolve it.

Child packages : Failed to acquire connection

Hi!

I am having a problem with a Parent package that invokes Child SSIS packages.

The Child packages have EncryptSensitiveWithPassword as their security setting.

I have placed the PackagePassword for each Child in the Parent package.

Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.

Here are what seem to be the relevant entries from a log file when the failure occurs:

Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.
OnError,XXXX,YYYYY\xxxx,SQLTask1 in ChildPackage1,GUID3,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

OnError,XXXX,YYYYY\xxxx,ChildPackage1,GUID4,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

When I run the Child package by itself, it works without any errors. So the problem seems to be related to the fact that it is being called by the parent, and somehow the "security settings" for the parent are not quite the same as the settings when I run the Child package by itself.

What might I be doing wrong?

I am using File System storage for my packages, on a shared network drive.

Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm. |||

Yes, I have set the password in each Execute Package task -- and the error message is not about an inability to open the package (for which there appear to be a number of error messages available). Instead, it is one that seems to indicate an inability to login to SQL Server with the SQL Server Authentication incorporated in the package.

I have read some web pages (e.g., the link below) that refer to the Integrated Security of a SQL Server connection. I see what appears to be some XML for that in the Child packages (but none in the Parent package) -- and the value that I found was "True". I tried changing it to "SSPI", but then I couldn't even run the child package by itself, nor when called by the Parent.

http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx

|||

Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?

Dan

|||

I'm going to take a look at using the script Jamie Thompon wrote, as found at

http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.

|||

John,

Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.

I am sorry to have dismissed your suggestion so quickly -- but I didn't see it until I was off on vacation and happened to logon and notice your post. So I didn't have SQL Server at my disposal to investigate further. You have my sincere apologies.

Dan

|||No problem - I'm glad you were able to resolve it.

CheckQueryProcessorAlive: sqlexecdirect failed

Hi,
I have the following error in my sql error log.
2004-05-05 09:44:34.23 spid3 SQL Server is terminating
due to 'stop' request from Service Control Manager.
I have the following error in my cluster log just before
my entry into the sql error log. Could you let me know
what could be the cause? The server load was not high and
the system was not running any dbcc at that time.
000006b4.000008bc::2004/05/05-09:43:16.050 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.050 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = HYT00;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Timeout expired
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] OnlineThread: QP is not online.
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] ODBC sqldriverconnect failed
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] checkODBCConnectError: sqlstate =
08001; native error = b; message = [Microsoft][ODBC SQL
Server Driver][DBNETLIB]General network error. Check your
network documentation.
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] ODBC sqldriverconnect failed
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] checkODBCConnectError: sqlstate =
01000; native error = 274c; message = [Microsoft][ODBC SQL
Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake
()).
Operating System and Version: Windows 2000 Server SP4
Database: MSSQL2000 SP3
HARDWARE CONFIGURATION INFORMATION
Machine Type: HP Netserver LT6000R
Amount of RAM: 4 GB
Type of Network Card: HP NetServer 10/100TX PCI LAN
Adapter (Slot)
Disk Type & Size: 4 x 36GB
Controller(s): HP NetRAID-2M Controller
Integrated HP NetRaid
Tape Unit, make/model:
CD-ROM make/model: TEAC CD-224E
regards,
bharathThe cluster service was unable to connect to SQL Server to verify that it
was running. There are numerous reasons why this could happen. I then
shutdown SQL Server and, I assume, failed it over to the other node or
restarted on the same node.
Rand
This posting is provided "as is" with no warranties and confers no rights.

CheckQueryProcessorAlive: sqlexecdirect failed

Hi,
I have the following error in my sql error log.
2004-05-05 09:44:34.23 spid3 SQL Server is terminating
due to 'stop' request from Service Control Manager.
I have the following error in my cluster log just before
my entry into the sql error log. Could you let me know
what could be the cause? The server load was not high and
the system was not running any dbcc at that time.
000006b4.000008bc::2004/05/05-09:43:16.050 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.050 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = HYT00;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Timeout expired
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] OnlineThread: QP is not online.
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] ODBC sqldriverconnect failed
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = b; message = [Microsoft][ODBC SQL
Server Driver][DBNETLIB]General network error. Check your
network documentation.
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] ODBC sqldriverconnect failed
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] checkODBCConnectError: sqlstate = 01000; native error = 274c; message = [Microsoft][ODBC SQL
Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake
()).
Operating System and Version: Windows 2000 Server SP4
Database: MSSQL2000 SP3
HARDWARE CONFIGURATION INFORMATION
Machine Type: HP Netserver LT6000R
Amount of RAM: 4 GB
Type of Network Card: HP NetServer 10/100TX PCI LAN
Adapter (Slot)
Disk Type & Size: 4 x 36GB
Controller(s): HP NetRAID-2M Controller
Integrated HP NetRaid
Tape Unit, make/model:
CD-ROM make/model: TEAC CD-224E
regards,
bharathThe cluster service was unable to connect to SQL Server to verify that it
was running. There are numerous reasons why this could happen. I then
shutdown SQL Server and, I assume, failed it over to the other node or
restarted on the same node.
Rand
This posting is provided "as is" with no warranties and confers no rights.

CheckQueryProcessorAlive: sqlexecdirect failed

Hi,
I have the following error in my sql error log.
2004-05-05 09:44:34.23 spid3 SQL Server is terminating
due to 'stop' request from Service Control Manager.
I have the following error in my cluster log just before
my entry into the sql error log. Could you let me know
what could be the cause? The server load was not high and
the system was not running any dbcc at that time.
000006b4.000008bc::2004/05/05-09:43:16.050 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.050 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = HYT00;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Timeout expired
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] OnlineThread: QP is not online.
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] CheckQueryProcessorAlive:
sqlexecdirect failed
000006b4.000008bc::2004/05/05-09:43:16.066 SQL Server <SQL
Server>: [sqsrvres] printODBCError: sqlstate = 08S01;
native error = 0; message = [Microsoft][ODBC SQL Server
Driver]Communication link failure
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] ODBC sqldriverconnect failed
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] checkODBCConnectError: sqlstate =
08001; native error = b; message = [Microsoft][ODBC SQL
Server Driver][DBNETLIB]General network error. Check your
network documentation.
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] ODBC sqldriverconnect failed
000006b4.000008bc::2004/05/05-09:43:37.300 SQL Server <SQL
Server>: [sqsrvres] checkODBCConnectError: sqlstate =
01000; native error = 274c; message = [Microsoft][ODBC SQL
Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake
()).
Operating System and Version: Windows 2000 Server SP4
Database: MSSQL2000 SP3
HARDWARE CONFIGURATION INFORMATION
Machine Type: HP Netserver LT6000R
Amount of RAM: 4 GB
Type of Network Card: HP NetServer 10/100TX PCI LAN
Adapter (Slot)
Disk Type & Size: 4 x 36GB
Controller(s): HP NetRAID-2M Controller
Integrated HP NetRaid
Tape Unit, make/model:
CD-ROM make/model: TEAC CD-224E
regards,
bharath
The cluster service was unable to connect to SQL Server to verify that it
was running. There are numerous reasons why this could happen. I then
shutdown SQL Server and, I assume, failed it over to the other node or
restarted on the same node.
Rand
This posting is provided "as is" with no warranties and confers no rights.

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.