Fill in missing date ranges

I have a table AssetValues that contains several rows of data for a
particular date. The table has missing dates and I need to populate these
missing dates one-time to create historical data and then daily to populate
missing data on an ongoing basis. The missing dates are not related to the
particular day of the w.
The logic for inserting new rows is as follows:
If there is a date without data, insert data using the previous date that
has data as long as the previous day is in the same month. So if I was
missing data for 20060430 and there was data for 20060429 then copy the data
from 20060429 changing the date to 20060430. If I was missing 20060430 and
20060429 then use the data from 20060428.
If I was missing 20060501 do not use the data from 20060430. The data needs
to be in the same month. In this case I would need to use the data from
20060502 if this was available or the next day in May when the data is
available. If the data is not available then take no action until it is.
I have a calendar table if needed.
Thanks to anyone who could help.
CREATE TABLE AssetValues
(
AssetValueDate datetime,
Category char(1),
Subcategory int
)
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','A', 1 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','B', 2 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','C', 3 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060502','A', 4 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','B', 5 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','C', 6 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','A', 7 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060501','B', 8 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','C', 9 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','A', 10 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','B', 11 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060428','C', 12 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','A', 13 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','B', 14 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','C', 15 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060427','A', 16 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','B', 17 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','C', 18 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','A', 19 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060426','B', 20 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','C', 21 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','A', 22 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','B', 23 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060425','C', 24 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','A', 25 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','B', 26 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','C', 27 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060424','A', 28 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','B', 29 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','C', 30 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','A', 31 )
INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
('20060421','B', 32 )
DROP TABLE AssetValuesTry,
create view v1
as
select a.dt
from dbo.calendar as a
where not exists (
select * from dbo.AssetValues as b
where b.AssetValueDate = a.dt
)
and a.dt between convert(char(6), (select min(AssetValueDate) from
dbo.AssetValues), 112) + '01' and dateadd(day, -1, dateadd(month, 1,
convert(char(6), (select max(AssetValueDate) from dbo.AssetValues), 112) +
'01'))
go
select *
from v1
go
-- previous available day
insert into dbo.AssetValues
select
v1.dt, t1.Category, t1.Subcategory
from
v1 inner join dbo.AssetValues as t1
on t1.AssetValueDate = (select max(a.AssetValueDate) from dbo.AssetValues
as a where a.AssetValueDate < v1.dt and datediff(month, a.AssetValueDate,
v1.dt) = 0)
go
-- next available day
insert into dbo.AssetValues
select
v1.dt, t1.Category, t1.Subcategory
from
v1 inner join dbo.AssetValues as t1
on t1.AssetValueDate = (select min(a.AssetValueDate) from dbo.AssetValues
as a where a.AssetValueDate > v1.dt and datediff(month, v1.dt,
a.AssetValueDate) = 0)
go
AMB
"Terri" wrote:

> I have a table AssetValues that contains several rows of data for a
> particular date. The table has missing dates and I need to populate these
> missing dates one-time to create historical data and then daily to populat
e
> missing data on an ongoing basis. The missing dates are not related to the
> particular day of the w.
> The logic for inserting new rows is as follows:
> If there is a date without data, insert data using the previous date that
> has data as long as the previous day is in the same month. So if I was
> missing data for 20060430 and there was data for 20060429 then copy the da
ta
> from 20060429 changing the date to 20060430. If I was missing 20060430 and
> 20060429 then use the data from 20060428.
> If I was missing 20060501 do not use the data from 20060430. The data need
s
> to be in the same month. In this case I would need to use the data from
> 20060502 if this was available or the next day in May when the data is
> available. If the data is not available then take no action until it is.
> I have a calendar table if needed.
> Thanks to anyone who could help.
> CREATE TABLE AssetValues
> (
> AssetValueDate datetime,
> Category char(1),
> Subcategory int
> )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 1 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','B', 2 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','C', 3 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 4 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 5 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','C', 6 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','A', 7 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 8 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 9 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','A', 10 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','B', 11 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 12 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 13 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','B', 14 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','C', 15 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 16 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 17 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','C', 18 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','A', 19 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 20 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 21 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','A', 22 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','B', 23 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 24 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 25 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','B', 26 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','C', 27 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 28 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 29 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','C', 30 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','A', 31 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 32 )
>
> DROP TABLE AssetValues
>
>|||This duplicates all rows from the previous day. Is that what you want?
CREATE TABLE Dates (DateValue DATETIME PRIMARY KEY)
DECLARE @.Date DATETIME
, @.MaxDate DATETIME
SELECT @.Date = MIN(AssetValueDate)
, @.MaxDate = MAX(AssetValueDate)
FROM AssetValues
WHILE @.Date <= @.MaxDate BEGIN
INSERT Dates
SELECT @.Date
SET @.Date = @.Date + 1
END
GO
SELECT DateValue
, AssetValueDate
, Category
, Subcategory
FROM AssetValues av
, Dates
WHERE YEAR(DateValue) = YEAR(AssetValueDate)
AND
MONTH(DateValue) = MONTH(AssetValueDate)
AND
EXISTS
(
SELECT *
FROM AssetValues av1
WHERE AssetValueDate BETWEEN av.AssetValueDate AND DateValue
HAVING COUNT(DISTINCT AssetValueDate) = 1
)
ORDER BY DateValue, AssetValueDate
--Alan
Terri wrote:
> I have a table AssetValues that contains several rows of data for a
> particular date. The table has missing dates and I need to populate these
> missing dates one-time to create historical data and then daily to populat
e
> missing data on an ongoing basis. The missing dates are not related to the
> particular day of the w.
> The logic for inserting new rows is as follows:
> If there is a date without data, insert data using the previous date that
> has data as long as the previous day is in the same month. So if I was
> missing data for 20060430 and there was data for 20060429 then copy the da
ta
> from 20060429 changing the date to 20060430. If I was missing 20060430 and
> 20060429 then use the data from 20060428.
> If I was missing 20060501 do not use the data from 20060430. The data need
s
> to be in the same month. In this case I would need to use the data from
> 20060502 if this was available or the next day in May when the data is
> available. If the data is not available then take no action until it is.
> I have a calendar table if needed.
> Thanks to anyone who could help.
> CREATE TABLE AssetValues
> (
> AssetValueDate datetime,
> Category char(1),
> Subcategory int
> )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 1 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','B', 2 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','C', 3 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060502','A', 4 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 5 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','C', 6 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','A', 7 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060501','B', 8 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 9 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','A', 10 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','B', 11 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060428','C', 12 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 13 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','B', 14 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','C', 15 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060427','A', 16 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 17 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','C', 18 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','A', 19 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060426','B', 20 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 21 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','A', 22 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','B', 23 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060425','C', 24 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 25 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','B', 26 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','C', 27 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060424','A', 28 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 29 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','C', 30 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','A', 31 )
> INSERT INTO AssetValues (AssetValueDate,Category,Subcategory) VALUES
> ('20060421','B', 32 )
>
> DROP TABLE AssetValues|||Thanks that worked. The only thing I still need to change is that the script
populated all the days in May from May 2nd data which was the most recent
data I had. I think I can add a date filter that will prevent population of
future dates.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:87565D10-F136-4D26-988B-5208CBEED90A@.microsoft.com...
> Try,
> create view v1
> as
> select a.dt
> from dbo.calendar as a
> where not exists (
> select * from dbo.AssetValues as b
> where b.AssetValueDate = a.dt
> )
> and a.dt between convert(char(6), (select min(AssetValueDate) from
> dbo.AssetValues), 112) + '01' and dateadd(day, -1, dateadd(month, 1,
> convert(char(6), (select max(AssetValueDate) from dbo.AssetValues), 112) +
> '01'))
> go
> select *
> from v1
> go
> -- previous available day
> insert into dbo.AssetValues
> select
> v1.dt, t1.Category, t1.Subcategory
> from
> v1 inner join dbo.AssetValues as t1
> on t1.AssetValueDate = (select max(a.AssetValueDate) from dbo.AssetValues
> as a where a.AssetValueDate < v1.dt and datediff(month, a.AssetValueDate,
> v1.dt) = 0)
> go
> -- next available day
> insert into dbo.AssetValues
> select
> v1.dt, t1.Category, t1.Subcategory
> from
> v1 inner join dbo.AssetValues as t1
> on t1.AssetValueDate = (select min(a.AssetValueDate) from dbo.AssetValues
> as a where a.AssetValueDate > v1.dt and datediff(month, v1.dt,
> a.AssetValueDate) = 0)
> go
>
> AMB
>|||No, but thanks. Sometimes I need to populate a date with data from a more
recent date when that data becomes available.
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1146684129.666739.113960@.j73g2000cwa.googlegroups.com...
> This duplicates all rows from the previous day. Is that what you want?
> CREATE TABLE Dates (DateValue DATETIME PRIMARY KEY)
> DECLARE @.Date DATETIME
> , @.MaxDate DATETIME
> SELECT @.Date = MIN(AssetValueDate)
> , @.MaxDate = MAX(AssetValueDate)
> FROM AssetValues
> WHILE @.Date <= @.MaxDate BEGIN
> INSERT Dates
> SELECT @.Date
> SET @.Date = @.Date + 1
> END
> GO
> SELECT DateValue
> , AssetValueDate
> , Category
> , Subcategory
> FROM AssetValues av
> , Dates
> WHERE YEAR(DateValue) = YEAR(AssetValueDate)
> AND
> MONTH(DateValue) = MONTH(AssetValueDate)
> AND
> EXISTS
> (
> SELECT *
> FROM AssetValues av1
> WHERE AssetValueDate BETWEEN av.AssetValueDate AND DateValue
> HAVING COUNT(DISTINCT AssetValueDate) = 1
> )
> ORDER BY DateValue, AssetValueDate
> --Alan
> Terri wrote:
these
populate
the
that
data
and
needs
>

Filegroups and RAID 1+0

Here's a question I hadn't encountered before: Does SQL Server populate and
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#
http://msdn.microsoft.com/library/en...es_02_2ak3.asp
David Portas
SQL Server MVP
|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegr oups.com...
> http://msdn.microsoft.com/library/en...es_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>

Filegroups and RAID 1+0

Here's a question I hadn't encountered before: Does SQL Server populate and
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#http://msdn.microsoft.com/library/e...des_02_2ak3.asp
--
David Portas
SQL Server MVP
--|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegroups.com...
> http://msdn.microsoft.com/library/e...des_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>

Filegroups and RAID 1+0

Here's a question I hadn't encountered before: Does SQL Server populate and
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2ak3.asp
--
David Portas
SQL Server MVP
--|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegroups.com...
> http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>

File in use error on connecting to database.

* Running SQL 2005 on XP Pro *

After running a ETL package to populate a sample DW which completed successfully, I attempted to open the SQL Server Management Studio. After login, an error was reported :

TITLE: Microsoft SQL Server Management Studio


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


ADDITIONAL INFORMATION:

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476

In the server error log I see... (note, the problem persists after reboot)

2006-01-13 11:24:07.25 spid12s The Service Broker protocol transport is disabled or not configured.
2006-01-13 11:24:07.25 spid12s The Database Mirroring protocol transport is disabled or not configured.
2006-01-13 11:24:07.77 Logon Error: 17187, Severity: 16, State: 1.
2006-01-13 11:24:07.77 Logon SQL Server is not ready to accept new client connections; the connection has been closed. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: <local machine>]
2006-01-13 11:24:07.82 Logon Error: 17187, Severity: 16, State: 1.
2006-01-13 11:24:07.82 Logon SQL Server is not ready to accept new client connections; the connection has been closed. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: <local machine>]
2006-01-13 11:24:08.24 spid12s Service Broker manager has started.
2006-01-13 11:24:14.97 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2006-01-13 11:24:14.97 Server Dedicated admin connection support was established for listening locally on port 1434.
2006-01-13 11:24:14.97 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-01-13 11:24:17.28 Logon Error: 18456, Severity: 14, State: 16.
2006-01-13 11:24:17.28 Logon Login failed for user 'FUSION-NET\scottt'. [CLIENT: <local machine>]
2006-01-13 11:24:18.91 spid15s Starting up database 'ReportServer'.
2006-01-13 11:24:18.91 spid14s Starting up database 'msdb'.
2006-01-13 11:24:18.91 spid17s Starting up database 'Subscriptions'.
2006-01-13 11:24:18.91 spid16s Starting up database 'ReportServerTempDB'.
2006-01-13 11:24:19.23 spid14s Error: 17207, Severity: 16, State: 1.
2006-01-13 11:24:19.23 spid14s FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf'. Diagnose and correct the operating system error, and retry the operation.
2006-01-13 11:24:19.23 spid14s Error: 17204, Severity: 16, State: 1.
2006-01-13 11:24:19.23 spid14s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2006-01-13 11:24:19.25 spid14s Error: 5120, Severity: 16, State: 101.
2006-01-13 11:24:19.25 spid14s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2006-01-13 11:24:19.37 spid16s Error: 17207, Severity: 16, State: 1.
2006-01-13 11:24:19.37 spid16s FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf'. Diagnose and correct the operating system error, and retry the operation.
2006-01-13 11:24:19.37 spid17s Error: 17207, Severity: 16, State: 1.
2006-01-13 11:24:19.37 spid17s FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Subscriptions.mdf'. Diagnose and correct the operating system error, and retry the operation.
2006-01-13 11:24:19.37 spid17s Error: 17204, Severity: 16, State: 1.
2006-01-13 11:24:19.37 spid17s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Subscriptions.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2006-01-13 11:24:19.37 spid17s Error: 5120, Severity: 16, State: 101.
2006-01-13 11:24:19.37 spid17s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Subscriptions.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2006-01-13 11:24:19.38 spid16s Error: 17204, Severity: 16, State: 1.
2006-01-13 11:24:19.38 spid16s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2006-01-13 11:24:19.38 spid16s Error: 5120, Severity: 16, State: 101.
2006-01-13 11:24:19.38 spid16s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2006-01-13 11:24:19.91 spid14s Starting up database 'AdventureWorksDW'.
2006-01-13 11:24:19.91 spid17s Starting up database 'AdventureWorks'.
2006-01-13 11:24:19.91 spid16s Starting up database 'DnBSampleDW'.
2006-01-13 11:24:28.07 spid5s Error: 8355, Severity: 16, State: 1.
2006-01-13 11:24:28.07 spid5s Service Broker is disabled in MSDB or MSDB failed to start. Server level event notifications can not be delivered. Event notifications with FAN_IN in other databases could be affected as well.
2006-01-13 11:24:28.11 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-01-13 11:24:34.23 Logon Error: 18456, Severity: 14, State: 16.
2006-01-13 11:24:34.23 Logon Login failed for user 'FUSION-NET\scottt'. [CLIENT: <local machine>]
2006-01-13 11:24:34.90 Logon Error: 18456, Severity: 14, State: 16.
2006-01-13 11:24:34.90 Logon Login failed for user 'FUSION-NET\scottt'. [CLIENT: <local machine>]
2006-01-13 11:26:50.52 spid53 Using 'xpstar90.dll' version '2005.90.1399' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.

Just a note here... I let the computer sit after a reboot while at lunch, I opened the Studio and tried the connection and it came up just fine...

|||I am getting a similar problem with my instance running on Windows 2003 Server. I have a database that is used by a .Net application. When somebody is using the .Net application I am unable to open the database in Management Studio. The same goes the other way, when I have the database open in Management Studio the application is unable to access the database. Why can I not have the database open and still have the application access the database?|||Turn your anti-virus scanner off or at least exclude the directory containing your SQL Server files from the scanning.|||

I have a similar problem here but it occurs only when I attempt to use my other application's web service which needs to be connected to my db. The db's file is there.

System.Web.Services.Protocols.SoapException: Server was unable to process request. > System.Data.SqlClient.SqlException: Unable to open the physical file "c:\inetpub\wwwroot\WebSite1\App_Data\ASPNETDB.MDF". Operating system error 32: "32(error not found)".
An attempt to attach an auto-named database for file c:\inetpub\wwwroot\WebSite1\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ViewPropertyTableAdapters.AgentInfoTableAdapter.GetAgentInfo(String AgentUserName) in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\website1\353383d4\c7efb847\App_Code.ekppx9ry.1.vb:line 3229
at getDetails.getAgentInfo(String userName) in c:\inetpub\wwwroot\WebSite1\App_Code\getDetails.vb:line 11
at getAgentContact.getContact(String userName) in c:\inetpub\wwwroot\WebSite1\App_Code\getAgentContact.vb:line 17

File in use error on connecting to database.

* Running SQL 2005 on XP Pro *

After running a ETL package to populate a sample DW which completed successfully, I attempted to open the SQL Server Management Studio. After login, an error was reported :

TITLE: Microsoft SQL Server Management Studio


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


ADDITIONAL INFORMATION:

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476

In the server error log I see... (note, the problem persists after reboot)

2006-01-13 11:24:07.25 spid12s The Service Broker protocol transport is disabled or not configured.
2006-01-13 11:24:07.25 spid12s The Database Mirroring protocol transport is disabled or not configured.
2006-01-13 11:24:07.77 Logon Error: 17187, Severity: 16, State: 1.
2006-01-13 11:24:07.77 Logon SQL Server is not ready to accept new client connections; the connection has been closed. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: <local machine>]
2006-01-13 11:24:07.82 Logon Error: 17187, Severity: 16, State: 1.
2006-01-13 11:24:07.82 Logon SQL Server is not ready to accept new client connections; the connection has been closed. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: <local machine>]
2006-01-13 11:24:08.24 spid12s Service Broker manager has started.
2006-01-13 11:24:14.97 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2006-01-13 11:24:14.97 Server Dedicated admin connection support was established for listening locally on port 1434.
2006-01-13 11:24:14.97 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-01-13 11:24:17.28 Logon Error: 18456, Severity: 14, State: 16.
2006-01-13 11:24:17.28 Logon Login failed for user 'FUSION-NET\scottt'. [CLIENT: <local machine>]
2006-01-13 11:24:18.91 spid15s Starting up database 'ReportServer'.
2006-01-13 11:24:18.91 spid14s Starting up database 'msdb'.
2006-01-13 11:24:18.91 spid17s Starting up database 'Subscriptions'.
2006-01-13 11:24:18.91 spid16s Starting up database 'ReportServerTempDB'.
2006-01-13 11:24:19.23 spid14s Error: 17207, Severity: 16, State: 1.
2006-01-13 11:24:19.23 spid14s FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf'. Diagnose and correct the operating system error, and retry the operation.
2006-01-13 11:24:19.23 spid14s Error: 17204, Severity: 16, State: 1.
2006-01-13 11:24:19.23 spid14s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2006-01-13 11:24:19.25 spid14s Error: 5120, Severity: 16, State: 101.
2006-01-13 11:24:19.25 spid14s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2006-01-13 11:24:19.37 spid16s Error: 17207, Severity: 16, State: 1.
2006-01-13 11:24:19.37 spid16s FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf'. Diagnose and correct the operating system error, and retry the operation.
2006-01-13 11:24:19.37 spid17s Error: 17207, Severity: 16, State: 1.
2006-01-13 11:24:19.37 spid17s FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Subscriptions.mdf'. Diagnose and correct the operating system error, and retry the operation.
2006-01-13 11:24:19.37 spid17s Error: 17204, Severity: 16, State: 1.
2006-01-13 11:24:19.37 spid17s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Subscriptions.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2006-01-13 11:24:19.37 spid17s Error: 5120, Severity: 16, State: 101.
2006-01-13 11:24:19.37 spid17s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Subscriptions.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2006-01-13 11:24:19.38 spid16s Error: 17204, Severity: 16, State: 1.
2006-01-13 11:24:19.38 spid16s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2006-01-13 11:24:19.38 spid16s Error: 5120, Severity: 16, State: 101.
2006-01-13 11:24:19.38 spid16s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2006-01-13 11:24:19.91 spid14s Starting up database 'AdventureWorksDW'.
2006-01-13 11:24:19.91 spid17s Starting up database 'AdventureWorks'.
2006-01-13 11:24:19.91 spid16s Starting up database 'DnBSampleDW'.
2006-01-13 11:24:28.07 spid5s Error: 8355, Severity: 16, State: 1.
2006-01-13 11:24:28.07 spid5s Service Broker is disabled in MSDB or MSDB failed to start. Server level event notifications can not be delivered. Event notifications with FAN_IN in other databases could be affected as well.
2006-01-13 11:24:28.11 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-01-13 11:24:34.23 Logon Error: 18456, Severity: 14, State: 16.
2006-01-13 11:24:34.23 Logon Login failed for user 'FUSION-NET\scottt'. [CLIENT: <local machine>]
2006-01-13 11:24:34.90 Logon Error: 18456, Severity: 14, State: 16.
2006-01-13 11:24:34.90 Logon Login failed for user 'FUSION-NET\scottt'. [CLIENT: <local machine>]
2006-01-13 11:26:50.52 spid53 Using 'xpstar90.dll' version '2005.90.1399' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.

Just a note here... I let the computer sit after a reboot while at lunch, I opened the Studio and tried the connection and it came up just fine...

|||I am getting a similar problem with my instance running on Windows 2003 Server. I have a database that is used by a .Net application. When somebody is using the .Net application I am unable to open the database in Management Studio. The same goes the other way, when I have the database open in Management Studio the application is unable to access the database. Why can I not have the database open and still have the application access the database?|||Turn your anti-virus scanner off or at least exclude the directory containing your SQL Server files from the scanning.|||

I have a similar problem here but it occurs only when I attempt to use my other application's web service which needs to be connected to my db. The db's file is there.

System.Web.Services.Protocols.SoapException: Server was unable to process request. > System.Data.SqlClient.SqlException: Unable to open the physical file "c:\inetpub\wwwroot\WebSite1\App_Data\ASPNETDB.MDF". Operating system error 32: "32(error not found)".
An attempt to attach an auto-named database for file c:\inetpub\wwwroot\WebSite1\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ViewPropertyTableAdapters.AgentInfoTableAdapter.GetAgentInfo(String AgentUserName) in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\website1\353383d4\c7efb847\App_Code.ekppx9ry.1.vb:line 3229
at getDetails.getAgentInfo(String userName) in c:\inetpub\wwwroot\WebSite1\App_Code\getDetails.vb:line 11
at getAgentContact.getContact(String userName) in c:\inetpub\wwwroot\WebSite1\App_Code\getAgentContact.vb:line 17

File in use error on connecting to database.

* Running SQL 2005 on XP Pro *

After running a ETL package to populate a sample DW which completed successfully, I attempted to open the SQL Server Management Studio. After login, an error was reported :

TITLE: Microsoft SQL Server Management Studio


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


ADDITIONAL INFORMATION:

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476

In the server error log I see... (note, the problem persists after reboot)

2006-01-13 11:24:07.25 spid12s The Service Broker protocol transport is disabled or not configured.
2006-01-13 11:24:07.25 spid12s The Database Mirroring protocol transport is disabled or not configured.
2006-01-13 11:24:07.77 Logon Error: 17187, Severity: 16, State: 1.
2006-01-13 11:24:07.77 Logon SQL Server is not ready to accept new client connections; the connection has been closed. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: <local machine>]
2006-01-13 11:24:07.82 Logon Error: 17187, Severity: 16, State: 1.
2006-01-13 11:24:07.82 Logon SQL Server is not ready to accept new client connections; the connection has been closed. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: <local machine>]
2006-01-13 11:24:08.24 spid12s Service Broker manager has started.
2006-01-13 11:24:14.97 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2006-01-13 11:24:14.97 Server Dedicated admin connection support was established for listening locally on port 1434.
2006-01-13 11:24:14.97 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-01-13 11:24:17.28 Logon Error: 18456, Severity: 14, State: 16.
2006-01-13 11:24:17.28 Logon Login failed for user 'FUSION-NET\scottt'. [CLIENT: <local machine>]
2006-01-13 11:24:18.91 spid15s Starting up database 'ReportServer'.
2006-01-13 11:24:18.91 spid14s Starting up database 'msdb'.
2006-01-13 11:24:18.91 spid17s Starting up database 'Subscriptions'.
2006-01-13 11:24:18.91 spid16s Starting up database 'ReportServerTempDB'.
2006-01-13 11:24:19.23 spid14s Error: 17207, Severity: 16, State: 1.
2006-01-13 11:24:19.23 spid14s FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf'. Diagnose and correct the operating system error, and retry the operation.
2006-01-13 11:24:19.23 spid14s Error: 17204, Severity: 16, State: 1.
2006-01-13 11:24:19.23 spid14s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2006-01-13 11:24:19.25 spid14s Error: 5120, Severity: 16, State: 101.
2006-01-13 11:24:19.25 spid14s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2006-01-13 11:24:19.37 spid16s Error: 17207, Severity: 16, State: 1.
2006-01-13 11:24:19.37 spid16s FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf'. Diagnose and correct the operating system error, and retry the operation.
2006-01-13 11:24:19.37 spid17s Error: 17207, Severity: 16, State: 1.
2006-01-13 11:24:19.37 spid17s FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Subscriptions.mdf'. Diagnose and correct the operating system error, and retry the operation.
2006-01-13 11:24:19.37 spid17s Error: 17204, Severity: 16, State: 1.
2006-01-13 11:24:19.37 spid17s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Subscriptions.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2006-01-13 11:24:19.37 spid17s Error: 5120, Severity: 16, State: 101.
2006-01-13 11:24:19.37 spid17s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Subscriptions.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2006-01-13 11:24:19.38 spid16s Error: 17204, Severity: 16, State: 1.
2006-01-13 11:24:19.38 spid16s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2006-01-13 11:24:19.38 spid16s Error: 5120, Severity: 16, State: 101.
2006-01-13 11:24:19.38 spid16s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2006-01-13 11:24:19.91 spid14s Starting up database 'AdventureWorksDW'.
2006-01-13 11:24:19.91 spid17s Starting up database 'AdventureWorks'.
2006-01-13 11:24:19.91 spid16s Starting up database 'DnBSampleDW'.
2006-01-13 11:24:28.07 spid5s Error: 8355, Severity: 16, State: 1.
2006-01-13 11:24:28.07 spid5s Service Broker is disabled in MSDB or MSDB failed to start. Server level event notifications can not be delivered. Event notifications with FAN_IN in other databases could be affected as well.
2006-01-13 11:24:28.11 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-01-13 11:24:34.23 Logon Error: 18456, Severity: 14, State: 16.
2006-01-13 11:24:34.23 Logon Login failed for user 'FUSION-NET\scottt'. [CLIENT: <local machine>]
2006-01-13 11:24:34.90 Logon Error: 18456, Severity: 14, State: 16.
2006-01-13 11:24:34.90 Logon Login failed for user 'FUSION-NET\scottt'. [CLIENT: <local machine>]
2006-01-13 11:26:50.52 spid53 Using 'xpstar90.dll' version '2005.90.1399' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.

Just a note here... I let the computer sit after a reboot while at lunch, I opened the Studio and tried the connection and it came up just fine...

|||I am getting a similar problem with my instance running on Windows 2003 Server. I have a database that is used by a .Net application. When somebody is using the .Net application I am unable to open the database in Management Studio. The same goes the other way, when I have the database open in Management Studio the application is unable to access the database. Why can I not have the database open and still have the application access the database?|||Turn your anti-virus scanner off or at least exclude the directory containing your SQL Server files from the scanning.|||

I have a similar problem here but it occurs only when I attempt to use my other application's web service which needs to be connected to my db. The db's file is there.

System.Web.Services.Protocols.SoapException: Server was unable to process request. > System.Data.SqlClient.SqlException: Unable to open the physical file "c:\inetpub\wwwroot\WebSite1\App_Data\ASPNETDB.MDF". Operating system error 32: "32(error not found)".
An attempt to attach an auto-named database for file c:\inetpub\wwwroot\WebSite1\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ViewPropertyTableAdapters.AgentInfoTableAdapter.GetAgentInfo(String AgentUserName) in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\website1\353383d4\c7efb847\App_Code.ekppx9ry.1.vb:line 3229
at getDetails.getAgentInfo(String userName) in c:\inetpub\wwwroot\WebSite1\App_Code\getDetails.vb:line 11
at getAgentContact.getContact(String userName) in c:\inetpub\wwwroot\WebSite1\App_Code\getAgentContact.vb:line 17