Database in loading state

I have several databases in a loading state, I have tried using the detach
option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
SQLState: 42000)
Error 15010: The database 'name' does not exist. Use sp_helpdb to show
available databases.
When using the sp_helpdb command, of course it only shows the databases that
are not in the loading state.
I need to get the databases that in loading state, detached or removed
completely. My nightly backups are failing since they can not attach to the
databases (loading)
All assistance in this matter is greatly appreciated and thanks in advance
Forgot to advise. Using SQL 2000 with SP 3 on Win2K server
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance
|||Never mind... I found the solution... Forgot that I needed to use the
RECOVERY DATABASE WITH RECOVERY command
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance
sql

Database in loading state

I have several databases in a loading state, I have tried using the detach
option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
SQLState: 42000)
Error 15010: The database 'name' does not exist. Use sp_helpdb to show
available databases.
When using the sp_helpdb command, of course it only shows the databases that
are not in the loading state.
I need to get the databases that in loading state, detached or removed
completely. My nightly backups are failing since they can not attach to the
databases (loading)
All assistance in this matter is greatly appreciated and thanks in advanceForgot to advise. Using SQL 2000 with SP 3 on Win2K server
"scuba79" wrote:
> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance|||Never mind... I found the solution... Forgot that I needed to use the
RECOVERY DATABASE WITH RECOVERY command
"scuba79" wrote:
> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance

Database in loading state

I have several databases in a loading state, I have tried using the detach
option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
SQLState: 42000)
Error 15010: The database 'name' does not exist. Use sp_helpdb to show
available databases.
When using the sp_helpdb command, of course it only shows the databases that
are not in the loading state.
I need to get the databases that in loading state, detached or removed
completely. My nightly backups are failing since they can not attach to the
databases (loading)
All assistance in this matter is greatly appreciated and thanks in advanceForgot to advise. Using SQL 2000 with SP 3 on Win2K server
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases th
at
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to t
he
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance|||Never mind... I found the solution... Forgot that I needed to use the
RECOVERY DATABASE WITH RECOVERY command
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases th
at
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to t
he
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance

Database hung after failure of large update query

I tried to run a large update query last night that did not complete.
But now I cannot access that database. In fact, when I click on Databases
in Enterprise Manager, that hangs and shows an hourglass.
I suspect that it failed due to a disc space. When I look at the process
status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
on that database.
I don't really need to complete the update (partial complete would be fine).
I just need to free up the database so I can work on the database.
Any thoughts?
--
- JesseJesse A wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on
> Databases in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the
> process status, I see a SPID that has a wait type of PAGEIOLatch_EX
> that is 'working' on that database.
> I don't really need to complete the update (partial complete would be
> fine). I just need to free up the database so I can work on the
> database.
> Any thoughts?
There is no such thing as a partial update unless you were using batches
to perform the updates in the first place. What likely happened is that
you ran out of space and SQL Server may be in the process of rolling
back the transaction (which can take just as long as the original update
took).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If u dont want any transaction for that particular SPID then u will kill the
particular process by KILL SPID
hope this will help
--
Herbert
"Jesse A" wrote:
> I tried to run a large update query last night that did not complete.
> But now I cannot access that database. In fact, when I click on Databases
> in Enterprise Manager, that hangs and shows an hourglass.
> I suspect that it failed due to a disc space. When I look at the process
> status, I see a SPID that has a wait type of PAGEIOLatch_EX that is 'working'
> on that database.
> I don't really need to complete the update (partial complete would be fine).
> I just need to free up the database so I can work on the database.
> Any thoughts?
> --
> - Jesse

Database group

In sql server 2005 you have a group of databases called System Databases -
Is it possible to create your own database group?

Best regards,

Jakobsgaard
If you mean in the Object Explorer tree, no not in this version.|||

This seems like a tease. Is this in grouping the works?

I am coming from an Oracle perspective where the world revolves around Schemas and Tablespaces.

The Database group makes sense to me.

Thanks

Database group

In sql server 2005 you have a group of databases called System Databases -
Is it possible to create your own database group?

Best regards,

Jakobsgaard
If you mean in the Object Explorer tree, no not in this version.|||

This seems like a tease. Is this in grouping the works?

I am coming from an Oracle perspective where the world revolves around Schemas and Tablespaces.

The Database group makes sense to me.

Thanks

database files locked out by SQL?

I have a SQL2005 SP2 server hosting several databases for different
applications. Recently i stood up a virtual server with Office
Communications Sever 2007 using my existing physical SQL 2005.
Everytime a reboot is required on the SQL server, the databases for OCS get
locked out. It took me a while to figure out what was going on, at first i
thought they were getting corrupted. After using SysInternals Process
explorer, I was able to determine that it is SQL itself that is locking the
database files and logs out, but apparently it isn't communicating that with
itself because when I try to bring them online, i get an error stating that
the files are in use or the disk is full.
Has anyone seen this before? I'm not an SQL expert by any means, so please
bear with me.
Cheers
Joe> Everytime a reboot is required on the SQL server, the databases for OCS get
> locked out.
I don't understand exactly what is meant here. The database files are indeed
locked by the SQL Server process, and that is expected. The files are not
supposed to be accessed by anything other than SQL Server. What error
messages are getting? What are you trying to accomplish that is prevented by
the files being locked?
Linchi
"Josef Hanning" wrote:
> I have a SQL2005 SP2 server hosting several databases for different
> applications. Recently i stood up a virtual server with Office
> Communications Sever 2007 using my existing physical SQL 2005.
> Everytime a reboot is required on the SQL server, the databases for OCS get
> locked out. It took me a while to figure out what was going on, at first i
> thought they were getting corrupted. After using SysInternals Process
> explorer, I was able to determine that it is SQL itself that is locking the
> database files and logs out, but apparently it isn't communicating that with
> itself because when I try to bring them online, i get an error stating that
> the files are in use or the disk is full.
> Has anyone seen this before? I'm not an SQL expert by any means, so please
> bear with me.
> Cheers
> Joe|||Sorry, guess I should have been more clear with the issue. After the reboot,
the databases will not come online properly. OCS gets application connection
errors, and you can't actually expand the database folder within SQL manager.
If I try and take the databases offline and then online again, I get a file
in use access error. I've managed to get the databases online by stopping
all SQL services and restarting but I shouldn't have to do this after a
reboot.
"Linchi Shea" wrote:
> > Everytime a reboot is required on the SQL server, the databases for OCS get
> > locked out.
> I don't understand exactly what is meant here. The database files are indeed
> locked by the SQL Server process, and that is expected. The files are not
> supposed to be accessed by anything other than SQL Server. What error
> messages are getting? What are you trying to accomplish that is prevented by
> the files being locked?
> Linchi
> "Josef Hanning" wrote:
> > I have a SQL2005 SP2 server hosting several databases for different
> > applications. Recently i stood up a virtual server with Office
> > Communications Sever 2007 using my existing physical SQL 2005.
> >
> > Everytime a reboot is required on the SQL server, the databases for OCS get
> > locked out. It took me a while to figure out what was going on, at first i
> > thought they were getting corrupted. After using SysInternals Process
> > explorer, I was able to determine that it is SQL itself that is locking the
> > database files and logs out, but apparently it isn't communicating that with
> > itself because when I try to bring them online, i get an error stating that
> > the files are in use or the disk is full.
> >
> > Has anyone seen this before? I'm not an SQL expert by any means, so please
> > bear with me.
> >
> > Cheers
> >
> > Joe|||"Josef Hanning" <JosefHanning@.discussions.microsoft.com> wrote in message
news:BEC23679-2F5F-448E-BBC4-5ECA8C8E6C70@.microsoft.com...
> Sorry, guess I should have been more clear with the issue. After the
> reboot,
> the databases will not come online properly. OCS gets application
> connection
> errors, and you can't actually expand the database folder within SQL
> manager.
> If I try and take the databases offline and then online again, I get a
> file
> in use access error. I've managed to get the databases online by stopping
> all SQL services and restarting but I shouldn't have to do this after a
> reboot.
Sounds to me like something is competing for the files - first one wins and
locks the other(s) out. Do you have multiple instances of sql server
installed - each of which is configured to use the same user database(s)?|||Scott,
Thanks for the reply. There looks to be 3 instances of SQL running on the
box, the default, the RTC instance created by the OCS installation, and an
OCS instance installed using SQL setup by a co-worker. The OCS instance has
never been used to my knowledge and I have not done any database
configuration to any of the other instances myself.
From your question I gather that I need to somehow configure my RTC
databases for OCS to be used by only one of these instances of SQL?
thanks!
-Joe
"Scott Morris" wrote:
> "Josef Hanning" <JosefHanning@.discussions.microsoft.com> wrote in message
> news:BEC23679-2F5F-448E-BBC4-5ECA8C8E6C70@.microsoft.com...
> > Sorry, guess I should have been more clear with the issue. After the
> > reboot,
> > the databases will not come online properly. OCS gets application
> > connection
> > errors, and you can't actually expand the database folder within SQL
> > manager.
> > If I try and take the databases offline and then online again, I get a
> > file
> > in use access error. I've managed to get the databases online by stopping
> > all SQL services and restarting but I shouldn't have to do this after a
> > reboot.
> Sounds to me like something is competing for the files - first one wins and
> locks the other(s) out. Do you have multiple instances of sql server
> installed - each of which is configured to use the same user database(s)?
>
>|||"Josef Hanning" <JosefHanning@.discussions.microsoft.com> wrote in message
news:84729A32-1E6D-4CED-9343-A1FE3EC35E02@.microsoft.com...
> Scott,
> Thanks for the reply. There looks to be 3 instances of SQL running on the
> box, the default, the RTC instance created by the OCS installation, and an
> OCS instance installed using SQL setup by a co-worker. The OCS instance
> has
> never been used to my knowledge and I have not done any database
> configuration to any of the other instances myself.
> From your question I gather that I need to somehow configure my RTC
> databases for OCS to be used by only one of these instances of SQL?
What you need to do I cannot answer. I can only say that the set of
phsyical files that comprise a database should only be used within a single
instance. Multiple instances cannot "share" a database. It sounds like
you may need to get additional expertise to help with this situation.

Database files encryption

Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.See if this helps:
http://www.sqlservercentral.com/col...menting_efs.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bryan" <anonymous@.discussions.microsoft.com> wrote in message
news:352501c51f3d$555d2140$a401280a@.phx.gbl...
Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.|||Thanks..........

>--Original Message--
>See if this helps:
>http://www.sqlservercentral.com/col...bkelley/impleme
nting_efs.asp
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Bryan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:352501c51f3d$555d2140$a401280a@.phx.gbl...
>Can anyone direct me to some info about Encrypting SQL
>Server database files (Including system databases) using
>EFS ?. Facts, pros & cons e.t.c.
>Thanks.
>
>.
>sql

Database files encryption

Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.
See if this helps:
http://www.sqlservercentral.com/colu...enting_efs.asp
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bryan" <anonymous@.discussions.microsoft.com> wrote in message
news:352501c51f3d$555d2140$a401280a@.phx.gbl...
Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.
|||Thanks..........

>--Original Message--
>See if this helps:
>http://www.sqlservercentral.com/colu...kelley/impleme
nting_efs.asp
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Bryan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:352501c51f3d$555d2140$a401280a@.phx.gbl...
>Can anyone direct me to some info about Encrypting SQL
>Server database files (Including system databases) using
>EFS ?. Facts, pros & cons e.t.c.
>Thanks.
>
>.
>

Database files encryption

Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.See if this helps:
http://www.sqlservercentral.com/columnists/bkelley/implementing_efs.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bryan" <anonymous@.discussions.microsoft.com> wrote in message
news:352501c51f3d$555d2140$a401280a@.phx.gbl...
Can anyone direct me to some info about Encrypting SQL
Server database files (Including system databases) using
EFS ?. Facts, pros & cons e.t.c.
Thanks.|||Thanks..........
>--Original Message--
>See if this helps:
>http://www.sqlservercentral.com/columnists/bkelley/impleme
nting_efs.asp
>--
>HTH,
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Bryan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:352501c51f3d$555d2140$a401280a@.phx.gbl...
>Can anyone direct me to some info about Encrypting SQL
>Server database files (Including system databases) using
>EFS ?. Facts, pros & cons e.t.c.
>Thanks.
>
>.
>

Database files are "Hidden" after becoming Suspect

We had created some databases on the C Drive. Today the C drive went bad and
all the databases that
were on the C Drive became suspect.
I tried to copy the files onto another drive and I did to the D drive.
However, when I tried to attach the
datafile the data file was not showing up. I checked the properties and the
file's "Read Only" attribute was checked.
I unchecked it. However, the "Hidden" attribute has been greyed out and
looks like this is the reason why
the files are not showing up.
Would someone know how to address this problem ?
Thanks,
rgn
Hi
This is an OS issue, but here goes:
Run
ATTRIB
This returns all the files that are hidden in the current directory.
If some of your files are listed, run
ATTRIB -H <filename.ext>
This will remove the Hidden attribute on the file.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:OUyfFwoEFHA.3244@.TK2MSFTNGP15.phx.gbl...
> We had created some databases on the C Drive. Today the C drive went bad
and
> all the databases that
> were on the C Drive became suspect.
> I tried to copy the files onto another drive and I did to the D drive.
> However, when I tried to attach the
> datafile the data file was not showing up. I checked the properties and
the
> file's "Read Only" attribute was checked.
> I unchecked it. However, the "Hidden" attribute has been greyed out and
> looks like this is the reason why
> the files are not showing up.
> Would someone know how to address this problem ?
> Thanks,
> rgn
>

Database files are "Hidden" after becoming Suspect

We had created some databases on the C Drive. Today the C drive went bad and
all the databases that
were on the C Drive became suspect.
I tried to copy the files onto another drive and I did to the D drive.
However, when I tried to attach the
datafile the data file was not showing up. I checked the properties and the
file's "Read Only" attribute was checked.
I unchecked it. However, the "Hidden" attribute has been greyed out and
looks like this is the reason why
the files are not showing up.
Would someone know how to address this problem ?
Thanks,
rgnHi
This is an OS issue, but here goes:
Run
ATTRIB
This returns all the files that are hidden in the current directory.
If some of your files are listed, run
ATTRIB -H <filename.ext>
This will remove the Hidden attribute on the file.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:OUyfFwoEFHA.3244@.TK2MSFTNGP15.phx.gbl...
> We had created some databases on the C Drive. Today the C drive went bad
and
> all the databases that
> were on the C Drive became suspect.
> I tried to copy the files onto another drive and I did to the D drive.
> However, when I tried to attach the
> datafile the data file was not showing up. I checked the properties and
the
> file's "Read Only" attribute was checked.
> I unchecked it. However, the "Hidden" attribute has been greyed out and
> looks like this is the reason why
> the files are not showing up.
> Would someone know how to address this problem ?
> Thanks,
> rgn
>

Database files are "Hidden" after becoming Suspect

We had created some databases on the C Drive. Today the C drive went bad and
all the databases that
were on the C Drive became suspect.
I tried to copy the files onto another drive and I did to the D drive.
However, when I tried to attach the
datafile the data file was not showing up. I checked the properties and the
file's "Read Only" attribute was checked.
I unchecked it. However, the "Hidden" attribute has been greyed out and
looks like this is the reason why
the files are not showing up.
Would someone know how to address this problem ?
Thanks,
rgnHi
This is an OS issue, but here goes:
Run
ATTRIB
This returns all the files that are hidden in the current directory.
If some of your files are listed, run
ATTRIB -H <filename.ext>
This will remove the Hidden attribute on the file.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:OUyfFwoEFHA.3244@.TK2MSFTNGP15.phx.gbl...
> We had created some databases on the C Drive. Today the C drive went bad
and
> all the databases that
> were on the C Drive became suspect.
> I tried to copy the files onto another drive and I did to the D drive.
> However, when I tried to attach the
> datafile the data file was not showing up. I checked the properties and
the
> file's "Read Only" attribute was checked.
> I unchecked it. However, the "Hidden" attribute has been greyed out and
> looks like this is the reason why
> the files are not showing up.
> Would someone know how to address this problem ?
> Thanks,
> rgn
>

Database Error during backup

We are currently using SQL 2000 SP4. When I try to backup any of our
Databases I receive the error message below, any suggestions that would fix
this problem for me. I would also like to make note that I viewed the Error
Log and could not identify any possible causes to this error, I have also
checked the Event Log and nothing logged there either.
--
Microsoft SQL-DMO (ODBC SQLState: 01000)
--
Error 926: Database 'msdb' cannot be opened. It has been marked SUSPECT by
recovery. See the SQL Server errorlog for more information.
--
OK
--
Regards,
JohnCheck BOL for DBCC CHECKDB
"John" <John@.discussions.microsoft.com> wrote in message
news:10ECD5A7-3C9A-427C-BD9B-F0DAA3F1FBD9@.microsoft.com...
> We are currently using SQL 2000 SP4. When I try to backup any of our
> Databases I receive the error message below, any suggestions that would
> fix
> this problem for me. I would also like to make note that I viewed the
> Error
> Log and could not identify any possible causes to this error, I have also
> checked the Event Log and nothing logged there either.
> --
> Microsoft SQL-DMO (ODBC SQLState: 01000)
> --
> Error 926: Database 'msdb' cannot be opened. It has been marked SUSPECT by
> recovery. See the SQL Server errorlog for more information.
> --
> OK
> --
> Regards,
> John
>|||... and be prepared for the worst: to restore your msdb database from a clean backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:u46ACdaEIHA.2004@.TK2MSFTNGP06.phx.gbl...
> Check BOL for DBCC CHECKDB
> "John" <John@.discussions.microsoft.com> wrote in message
> news:10ECD5A7-3C9A-427C-BD9B-F0DAA3F1FBD9@.microsoft.com...
>> We are currently using SQL 2000 SP4. When I try to backup any of our
>> Databases I receive the error message below, any suggestions that would
>> fix
>> this problem for me. I would also like to make note that I viewed the
>> Error
>> Log and could not identify any possible causes to this error, I have also
>> checked the Event Log and nothing logged there either.
>> --
>> Microsoft SQL-DMO (ODBC SQLState: 01000)
>> --
>> Error 926: Database 'msdb' cannot be opened. It has been marked SUSPECT by
>> recovery. See the SQL Server errorlog for more information.
>> --
>> OK
>> --
>> Regards,
>> John
>|||IIRC on sql2000 you can directly modify system tables. perhaps you can try
updating sysdatabases to clear the suspect flag (there may even be advice on
this in BOL) after you exhaust all other attempts and just before you
restore/rebuild your msdb.
You can also consider calling Microsoft for support.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"John" <John@.discussions.microsoft.com> wrote in message
news:10ECD5A7-3C9A-427C-BD9B-F0DAA3F1FBD9@.microsoft.com...
> We are currently using SQL 2000 SP4. When I try to backup any of our
> Databases I receive the error message below, any suggestions that would
> fix
> this problem for me. I would also like to make note that I viewed the
> Error
> Log and could not identify any possible causes to this error, I have also
> checked the Event Log and nothing logged there either.
> --
> Microsoft SQL-DMO (ODBC SQLState: 01000)
> --
> Error 926: Database 'msdb' cannot be opened. It has been marked SUSPECT by
> recovery. See the SQL Server errorlog for more information.
> --
> OK
> --
> Regards,
> John
>

Database Documentation

Hi guys, my manager asked me that to do documentation; right now we are using MSSQL SERVER 2005 Databases, here you assume I am new to make documents on any database but good idea about databases currently that data warehouse in development environment need to done this documentation before go on test environment . So that way you advise me to prepare a document.

Here is my main concerned how to start and what things should we keep in mind while preparing document (what are measurements).if you any prepared documents could help to start or there tool we should use (power point) them. any help appreciated

Things to include in DB documentation, and some suggested sources:

1.Server where DB is implemented; SQL Server version used, and required (for example SQL 2005 SP1 because of fix to XXX).

2.Database design - in an ideal world, you should have a ERD for a

database design before the database was created. This may have been

done in an external tool like ERWin, Rational Rose, PowerDesigner etc.

etc. You can also use the Database Diagram tool in Enterprise manager

(sql 2000, not sure about SQL 7.0) or SQL Server Management Studio

(SQL2005) to create these diagrams. The physical DB model (or

design, or ERD, - whatever your preferred name), is, imho, the single

most important piece of documentation about a database;

3. Volumetrics - this is a field on it's own, and one that is often

largely ignored (until a problem occurs!). This is about predicting the

sizing of a database, so as to ensure that the production server has

sufficient capacity when initially created, and sufficient space during

the database life. Since space addition can be a difficult/time

consuming activity (for example, ordering disks, taking server off-line

to add them, or getting drives purchased and added to a SAN and

allocated to the LUNs for you server, if you have a SAN), you really

want to know how much space you need, and when you are going to need

more. There are 2 large sections to this: predictions based on

assumptions, and forecasts made on trend analysis.

- predictions are done during design and creation phase. For example,

the business should have some kind of idea about the viability of a

system. In that, they would, hopefully, have some kind of expectations

regarding usage - for example: We expect 2000 orders a day, typically

of an average of 13.5 products. That already tells us we can expect

2000 rows in our order table, on a daily basis, and 27000 rows in the

OrderItem table. Extrapolation that further, based on 21*12 working

days a year, and we have a prediction of 504 000 rows in the order

table at year end, and 6 804 000 rows in the OrderItem table. This kind

of information can be used to generate an estimated size for the

database, so we can ensure that the database will have enough space on

the brand new server the business bought

- forecasts are done on an on-going basis and are a pro-active measure

by the DBAs in production to determine real growth of a database. So,

after the first month, for example, the dba's note that the ORder table

has 60000 entries, and the Orderitem table has 900 000 rows. Based on

that, they can forecast that the year end sizes will now be 720 000

order rows, and 10 800 000 OrderItems. The goods news is that the

business is doing 42% more orders, at that point in time, that

predicted (see the prediction above), and has sold 58% more Items. They

can use the extra money to buy some disk space, because the forecast

says that at 60000 order per month, the DB is going to be the size that

was predicted for year-end, after less than 9 months (9*60000 = 540 000

> 504 000, for example).

I haven't covered a discussion about the increase in volumes on the

underlying server resources (for example, are the batch jobs able to

finish processing within the agreed SLA's, now that we are doing 2857

orders per day? Do we have periods where this causes excessive load on

the server? These things are NOT (imho) part of a DB document, but they

do flow out of the forecasts that are being done. A final comment on

forecasts above is that, when they are done regularly, you can also do

trend analysis - this is use for technical people - for example, orders

are growing by 10% each month, so in fact that 9 month prediction is

too far away - it needs to be earlier

(60000+66000+72600+79860+87846+96630.6+106293.66=569230 after 7 months).

4. Access - who needs access, to what. For example, Users required, and

which stored procedures they need to execute. Hopefully, there is

little of no dorect table access, but that can also be listed. This can

be done in a matrix in excel:

User -> UserA UserB

Object
TableA - S

TableB - -

SP_1 - Ex

SP_2 - Ex

SP_3 Ex ExGr

SP_4 Ex -

And a key that defines the meanings:

S = Select (D=Delete,U=Update,I=Insert)

Ex = Execute

ExGr = Execute with Grant

etc. etc. These are examples - create as seems relevant.

5. Maintenance. what is the DRP strategy - do you have off-site

backups, how often, are they tested? Do you do regular maintenance (db

indexdefrag's etc) and if so, are they maintenance plans, or specific

jobs?

depending on the target audience for the document (for example someone

using it to rebuild servers in a disaster), you could consider adding

reference to the Source Control system, so that if the database needs

to be rebuilt, they know where to get relevant code.

hmm - well that was off the top of my head. Hopefully it gives you a

good palce to start. I'd be interested to see the additional items

other people suggest.|||

You might want to check out SqlSpec. It will generate documentation for any SQL 2000 or 2005 database. It's very reasonably priced at $50, a fraction of other data dictionary software out there.

see www.elsasoft.org for more info.

|||Interesting. It does seem quite useful and covers much of what I listed|||

You might be interested in checking out dbdesc as well. This tool documents SQL Server databases and you can fully customize its reports as it uses XSL templates to generate the final files. It has built-in templates to generate Word 2003, RTF, HTML, XML and PDF reports.

It was reviewed by Mike Gunderloy (Larkware News) here.

|||

Hi, Geth. Have you gotten everything you need on this subject? If so, you can close the thread, if not, let us know and we'll try to give you some more help.

Buck Woody

|||ERm. I'm happy, but I wasn't the thread starter. Can I cl,ose someone else's thread?|||

You're right! My bad. I'm not sure if you can mark it "happy" or not. I'll ask the right person this time...

|||

Hello - did you get everything you needed? I have some more info on building your own documentor here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=108&rl=1

If you've found these answers to be helpful, you can mark this thread as "answered". Thanks!

|||IAM Happy we can close this thread

Database Documentation

Hi guys, my manager asked me that to do documentation; right now we are using MSSQL SERVER 2005 Databases, here you assume I am new to make documents on any database but good idea about databases currently that data warehouse in development environment need to done this documentation before go on test environment . So that way you advise me to prepare a document.

Here is my main concerned how to start and what things should we keep in mind while preparing document (what are measurements).if you any prepared documents could help to start or there tool we should use (power point) them. any help appreciated

Things to include in DB documentation, and some suggested sources:

1.Server where DB is implemented; SQL Server version used, and required (for example SQL 2005 SP1 because of fix to XXX).

2.Database design - in an ideal world, you should have a ERD for a

database design before the database was created. This may have been

done in an external tool like ERWin, Rational Rose, PowerDesigner etc.

etc. You can also use the Database Diagram tool in Enterprise manager

(sql 2000, not sure about SQL 7.0) or SQL Server Management Studio

(SQL2005) to create these diagrams. The physical DB model (or

design, or ERD, - whatever your preferred name), is, imho, the single

most important piece of documentation about a database;

3. Volumetrics - this is a field on it's own, and one that is often

largely ignored (until a problem occurs!). This is about predicting the

sizing of a database, so as to ensure that the production server has

sufficient capacity when initially created, and sufficient space during

the database life. Since space addition can be a difficult/time

consuming activity (for example, ordering disks, taking server off-line

to add them, or getting drives purchased and added to a SAN and

allocated to the LUNs for you server, if you have a SAN), you really

want to know how much space you need, and when you are going to need

more. There are 2 large sections to this: predictions based on

assumptions, and forecasts made on trend analysis.

- predictions are done during design and creation phase. For example,

the business should have some kind of idea about the viability of a

system. In that, they would, hopefully, have some kind of expectations

regarding usage - for example: We expect 2000 orders a day, typically

of an average of 13.5 products. That already tells us we can expect

2000 rows in our order table, on a daily basis, and 27000 rows in the

OrderItem table. Extrapolation that further, based on 21*12 working

days a year, and we have a prediction of 504 000 rows in the order

table at year end, and 6 804 000 rows in the OrderItem table. This kind

of information can be used to generate an estimated size for the

database, so we can ensure that the database will have enough space on

the brand new server the business bought

- forecasts are done on an on-going basis and are a pro-active measure

by the DBAs in production to determine real growth of a database. So,

after the first month, for example, the dba's note that the ORder table

has 60000 entries, and the Orderitem table has 900 000 rows. Based on

that, they can forecast that the year end sizes will now be 720 000

order rows, and 10 800 000 OrderItems. The goods news is that the

business is doing 42% more orders, at that point in time, that

predicted (see the prediction above), and has sold 58% more Items. They

can use the extra money to buy some disk space, because the forecast

says that at 60000 order per month, the DB is going to be the size that

was predicted for year-end, after less than 9 months (9*60000 = 540 000

> 504 000, for example).

I haven't covered a discussion about the increase in volumes on the

underlying server resources (for example, are the batch jobs able to

finish processing within the agreed SLA's, now that we are doing 2857

orders per day? Do we have periods where this causes excessive load on

the server? These things are NOT (imho) part of a DB document, but they

do flow out of the forecasts that are being done. A final comment on

forecasts above is that, when they are done regularly, you can also do

trend analysis - this is use for technical people - for example, orders

are growing by 10% each month, so in fact that 9 month prediction is

too far away - it needs to be earlier

(60000+66000+72600+79860+87846+96630.6+106293.66=569230 after 7 months).

4. Access - who needs access, to what. For example, Users required, and

which stored procedures they need to execute. Hopefully, there is

little of no dorect table access, but that can also be listed. This can

be done in a matrix in excel:

User -> UserA UserB

Object
TableA - S

TableB - -

SP_1 - Ex

SP_2 - Ex

SP_3 Ex ExGr

SP_4 Ex -

And a key that defines the meanings:

S = Select (D=Delete,U=Update,I=Insert)

Ex = Execute

ExGr = Execute with Grant

etc. etc. These are examples - create as seems relevant.

5. Maintenance. what is the DRP strategy - do you have off-site

backups, how often, are they tested? Do you do regular maintenance (db

indexdefrag's etc) and if so, are they maintenance plans, or specific

jobs?

depending on the target audience for the document (for example someone

using it to rebuild servers in a disaster), you could consider adding

reference to the Source Control system, so that if the database needs

to be rebuilt, they know where to get relevant code.

hmm - well that was off the top of my head. Hopefully it gives you a

good palce to start. I'd be interested to see the additional items

other people suggest.|||

You might want to check out SqlSpec. It will generate documentation for any SQL 2000 or 2005 database. It's very reasonably priced at $50, a fraction of other data dictionary software out there.

see www.elsasoft.org for more info.

|||Interesting. It does seem quite useful and covers much of what I listed|||

You might be interested in checking out dbdesc as well. This tool documents SQL Server databases and you can fully customize its reports as it uses XSL templates to generate the final files. It has built-in templates to generate Word 2003, RTF, HTML, XML and PDF reports.

It was reviewed by Mike Gunderloy (Larkware News) here.

|||

Hi, Geth. Have you gotten everything you need on this subject? If so, you can close the thread, if not, let us know and we'll try to give you some more help.

Buck Woody

|||ERm. I'm happy, but I wasn't the thread starter. Can I cl,ose someone else's thread?|||

You're right! My bad. I'm not sure if you can mark it "happy" or not. I'll ask the right person this time...

|||

Hello - did you get everything you needed? I have some more info on building your own documentor here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=108&rl=1

If you've found these answers to be helpful, you can mark this thread as "answered". Thanks!

|||IAM Happy we can close this thread

Database distribution

Hi,
I am going to distribute a webb site and need to distribute the databases used by this webbsite. A newbie as I am to databases I ask if there is any tool to extract the databases from my machine, store it on a CD and then install it on another machine.
Thing is... I want to distribute the databases, but not the data currently stored in it.

Gudday!1. No such a tool as I know.
2. Backup your database and restore it with different name.
3. DROP TABLEs unusable to user (all user tables), analytic tables ...
4. In this database, use DELETE/TRUNCATE TABLE statements (See BOL) to clear your tables. If you use some tables for configuration, you cannot delete them !!!
5. Select distribution type BACKUP/Script. Script (made for example by EM) is most version compatible, but it takes some time to create it, if you distibute also data.
6. BACKUP installation files for testing UPGRADE.
7. Installation of database on user side can be done by runing script by isql.exe by installation program ( most use InstallShield )
8. Installation is a nightmare of each advanced developer:mad:|||Is it possible to use method of detaching the database, copy the files, distribute files and finally attach the database on remote site?

// Dobaz|||I have a similar situation in my company.I had gone for creating the scripts of the database ,since i also needed the data in the database, the size of the database file was too big .
if u script the database u have total control over it .|||You can use a outer program to restore the database during the installitaion or the program's first running.|||Thanks for all replies and help!

I have tried to:

1. Detached the databases that needs to be distributed from the SQL Server on my original machine.

2. Copied the .mdf and .ldf files from the MSSQL data folder onto a second machine.

3. Attached the distributed databases to SQL Server on the second machine.

It all seems to work ok between my own machines... Maybe this is a good way to go?

As I mentioned eariler I want to distribute the databases to keep table structure intact and only some example data stored in it (i.e. database files will be pretty small).

database disappearence

All of a sudden all the databases under the SQL Server Enterprise Manager have disappeared. Any idea why and how to get them back? I 'm on SQL 2000 with SP3Damn Miracle thing again....

Close and restart EM...What happens?|||That sure is a miracle brett :)|||I though miracles were supposed to be good things? Sounds more like a voodoo curse to me.

Try waving a dead chicken over it.

Database disappeared from sysdatabases after running out of file space

Guys and gals, I'm in all sorts of trouble here. I've been nursing a server
with very little free space for my sql databases while I get a new one
built, and it's been pretty safe, until a power cut the other day caused
some sort of huge write to the log file and it ran out of space. I mean,
really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
30gig. So, I needed to backup the transaction log, reorganise and shrink
the database. Couldn't do that, no elbow room, so I thought I'd move the
files to a USB hdd I have on there, slow but ok temporarily, and do the
reorg there. Unfortunately, detatching the database has, somehow caused the
sysdatabases table to lose any knowledge of it. I've got the files in their
new location and would dearly love to be able to reattach them somehow to
shrink them down but I'm at a loss now.
I'd really appreciate a pointer here, I've run out of ideas.
Cheers
Chris Weston"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> Guys and gals, I'm in all sorts of trouble here. I've been nursing a
server
> with very little free space for my sql databases while I get a new one
> built, and it's been pretty safe, until a power cut the other day caused
> some sort of huge write to the log file and it ran out of space. I mean,
> really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
> 30gig. So, I needed to backup the transaction log, reorganise and shrink
> the database. Couldn't do that, no elbow room, so I thought I'd move the
> files to a USB hdd I have on there, slow but ok temporarily, and do the
> reorg there. Unfortunately, detatching the database has, somehow caused
the
> sysdatabases table to lose any knowledge of it. I've got the files in
their
> new location and would dearly love to be able to reattach them somehow to
> shrink them down but I'm at a loss now.
> I'd really appreciate a pointer here, I've run out of ideas.
Just to follow up here, I've tried attaching the database from the context
menu on my databases folder, but it complains about the log file (all 22g of
it) being faulty. Can't I attach the mdf and let the log file be recreated?
I'm no sql expert but the mdf is the important part, surely?
Thanks,
--
Chris Weston|||sp_attach_single_file_db [ @.dbname = ] 'dbname'
, [ @.physname = ] 'physical_name'
It will generate a new log file.
good luck.
d.
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:tIYSf.2054$H%3.875@.newsfe5-gui.ntli.net...
> "Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
> news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> > Guys and gals, I'm in all sorts of trouble here. I've been nursing a
> server
> > with very little free space for my sql databases while I get a new one
> > built, and it's been pretty safe, until a power cut the other day caused
> > some sort of huge write to the log file and it ran out of space. I
mean,
> > really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
> > 30gig. So, I needed to backup the transaction log, reorganise and
shrink
> > the database. Couldn't do that, no elbow room, so I thought I'd move
the
> > files to a USB hdd I have on there, slow but ok temporarily, and do the
> > reorg there. Unfortunately, detatching the database has, somehow caused
> the
> > sysdatabases table to lose any knowledge of it. I've got the files in
> their
> > new location and would dearly love to be able to reattach them somehow
to
> > shrink them down but I'm at a loss now.
> >
> > I'd really appreciate a pointer here, I've run out of ideas.
> Just to follow up here, I've tried attaching the database from the context
> menu on my databases folder, but it complains about the log file (all 22g
of
> it) being faulty. Can't I attach the mdf and let the log file be
recreated?
> I'm no sql expert but the mdf is the important part, surely?
> Thanks,
> --
> Chris Weston
>|||"d" <d@.d.com> wrote in message news:ruZSf.1311$I7.1016@.trnddc03...
> sp_attach_single_file_db [ @.dbname = ] 'dbname'
> , [ @.physname = ] 'physical_name'
> It will generate a new log file.
>
> good luck.
>
I appreciate the sentiment :) However, I'm not sure where to run this
command? In a query window in Enterprise Manager?
Many thanks,
Chris Weston|||Hi Chris
This is expected behavior. When you detach a database, it is completely
removed from the system. It is different from DROP in that the files still
exist on disk.
To get the db back, you have to attach it.
If you are attaching through Enterprise Manager, and SQL Server complains
about the log, you can try changing the name of the log file on disk so EM
can't find it, and see if a new log will be rebuilt.
Or, as d suggested, you can run the procedure from sp_attach_single_file_db
from a query window in Query Analyzer.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> Guys and gals, I'm in all sorts of trouble here. I've been nursing a
> server
> with very little free space for my sql databases while I get a new one
> built, and it's been pretty safe, until a power cut the other day caused
> some sort of huge write to the log file and it ran out of space. I mean,
> really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
> 30gig. So, I needed to backup the transaction log, reorganise and shrink
> the database. Couldn't do that, no elbow room, so I thought I'd move the
> files to a USB hdd I have on there, slow but ok temporarily, and do the
> reorg there. Unfortunately, detatching the database has, somehow caused
> the
> sysdatabases table to lose any knowledge of it. I've got the files in
> their
> new location and would dearly love to be able to reattach them somehow to
> shrink them down but I'm at a loss now.
> I'd really appreciate a pointer here, I've run out of ideas.
> Cheers
> Chris Weston
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uKXsmotSGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hi Chris
> This is expected behavior. When you detach a database, it is completely
> removed from the system. It is different from DROP in that the files still
> exist on disk.
> To get the db back, you have to attach it.
> If you are attaching through Enterprise Manager, and SQL Server complains
> about the log, you can try changing the name of the log file on disk so EM
> can't find it, and see if a new log will be rebuilt.
> Or, as d suggested, you can run the procedure from
sp_attach_single_file_db
> from a query window in Query Analyzer.
Still, no joy - I have tried both these methods but still get an error
saying that it can't find the .ldf file. How can I get past this?
Thanks,
Chris Weston|||Hi
The methods described by Kalen should work if you followed them exactly.
Can you post the exact sp_attach_single_file_db command that you have used?
Make sure that you have move or renamed the ldf file and there is enough
space to create a new one.
Make sure that the mdf file does exist in the directory you have specified.
Make sure that directory where it wants to create the ldf file exists and
that the permissions allow you to create a new file.
John
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:dz8Tf.2219$g76.437@.newsfe2-gui.ntli.net...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uKXsmotSGHA.5500@.TK2MSFTNGP12.phx.gbl...
>> Hi Chris
>> This is expected behavior. When you detach a database, it is completely
>> removed from the system. It is different from DROP in that the files
>> still
>> exist on disk.
>> To get the db back, you have to attach it.
>> If you are attaching through Enterprise Manager, and SQL Server complains
>> about the log, you can try changing the name of the log file on disk so
>> EM
>> can't find it, and see if a new log will be rebuilt.
>> Or, as d suggested, you can run the procedure from
> xslmailinglist@.btopenworld.com>> from a query window in Query Analyzer.
> Still, no joy - I have tried both these methods but still get an error
> saying that it can't find the .ldf file. How can I get past this?
> Thanks,
> Chris Weston
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e%23SFB3zSGHA.4600@.TK2MSFTNGP11.phx.gbl...
> Hi
> The methods described by Kalen should work if you followed them exactly.
> Can you post the exact sp_attach_single_file_db command that you have
used?
> Make sure that you have move or renamed the ldf file and there is enough
> space to create a new one.
> Make sure that the mdf file does exist in the directory you have
specified.
> Make sure that directory where it wants to create the ldf file exists and
> that the permissions allow you to create a new file.
It exists, it's my default sql data directory and I'm logged in as
administrator. The mdf exists. I'm using
EXEC sp_attach_single_file_db @.dbname = 'SysCompanyA',
@.physname = N'h:\systemp\SyscompA.mdf'
but I get an error saying that 'the physical file name 'E:\program
files\...\data\syscompanyA_log.ldf may be incorrect'
So is it saying it can't create or that it can't find the ldf file?
--
Chris Weston|||HI Chris
Was E:\ the original drive? It looks like when SQL Server creates a new log
file, it wants to create it on the drive where it originally existed.
I have use sp_attach_db to move a db, or to rebuild a log, but never at the
same time.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:o0hTf.2330$g76.772@.newsfe2-gui.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e%23SFB3zSGHA.4600@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> The methods described by Kalen should work if you followed them exactly.
>> Can you post the exact sp_attach_single_file_db command that you have
> used?
>> Make sure that you have move or renamed the ldf file and there is enough
>> space to create a new one.
>> Make sure that the mdf file does exist in the directory you have
> specified.
>> Make sure that directory where it wants to create the ldf file exists and
>> that the permissions allow you to create a new file.
> It exists, it's my default sql data directory and I'm logged in as
> administrator. The mdf exists. I'm using
> EXEC sp_attach_single_file_db @.dbname = 'SysCompanyA',
> @.physname = N'h:\systemp\SyscompA.mdf'
> but I get an error saying that 'the physical file name 'E:\program
> files\...\data\syscompanyA_log.ldf may be incorrect'
> So is it saying it can't create or that it can't find the ldf file?
> --
> Chris Weston
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUXQlB9SGHA.5172@.TK2MSFTNGP12.phx.gbl...
> HI Chris
> Was E:\ the original drive? It looks like when SQL Server creates a new
log
> file, it wants to create it on the drive where it originally existed.
> I have use sp_attach_db to move a db, or to rebuild a log, but never at
the
> same time.
Kalen,
E:\ was the original drive, and it still exists. I've no problem with it
creating the log there if need be.
--
Chris Weston|||Chris Weston wrote:
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OUXQlB9SGHA.5172@.TK2MSFTNGP12.phx.gbl...
>> HI Chris
>> Was E:\ the original drive? It looks like when SQL Server creates a new
> log
>> file, it wants to create it on the drive where it originally existed.
>> I have use sp_attach_db to move a db, or to rebuild a log, but never at
> the
>> same time.
> Kalen,
> E:\ was the original drive, and it still exists. I've no problem with it
> creating the log there if need be.
>
You have to make sure that the full path/folder exists before you run
the command. SQL server can't create the folder so if it's not present
you'll get an error message like the one you see.
Regards
Steen|||So make sure there is no file on E with the name and path the log should
have. Make sure the directory exists on E and that the Windows account that
SQL Server runs under has access to write to the directory.
Once the attach succeeds, it should have a very small log file. You can then
detach it, move the new small log file to your new location, and then attach
again, specifying explicitly the log file location.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:7msTf.11031$5B4.10504@.newsfe6-gui.ntli.net...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OUXQlB9SGHA.5172@.TK2MSFTNGP12.phx.gbl...
>> HI Chris
>> Was E:\ the original drive? It looks like when SQL Server creates a new
> log
>> file, it wants to create it on the drive where it originally existed.
>> I have use sp_attach_db to move a db, or to rebuild a log, but never at
> the
>> same time.
> Kalen,
> E:\ was the original drive, and it still exists. I've no problem with it
> creating the log there if need be.
> --
> Chris Weston
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uUKGRCETGHA.4740@.TK2MSFTNGP14.phx.gbl...
> So make sure there is no file on E with the name and path the log should
> have. Make sure the directory exists on E and that the Windows account
that
> SQL Server runs under has access to write to the directory.
> Once the attach succeeds, it should have a very small log file. You can
then
> detach it, move the new small log file to your new location, and then
attach
> again, specifying explicitly the log file location.
Folks, thanks very much for your efforts. I couldn't attach this db, it
just wasn't having it. I ended up restoring from a backup and we'll just
have to do the work to bring it up to date. I did eventually attach the db
using the procedure detailed here
http://www.spaceprogram.com/knowledge/2002/06/recovering-from-deleted-log-file-on_12.html
although I had to repair it using allow_data_loss, and I can use it to help
me get the restored data back to usability.
So, thanks for your thoughts on this one, but may I ask one question? Why
the consistent top-posting in this ng?
Thanks
--
Chris Weston

Database disappeared from sysdatabases after running out of file space

Guys and gals, I'm in all sorts of trouble here. I've been nursing a server
with very little free space for my sql databases while I get a new one
built, and it's been pretty safe, until a power cut the other day caused
some sort of huge write to the log file and it ran out of space. I mean,
really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
30gig. So, I needed to backup the transaction log, reorganise and shrink
the database. Couldn't do that, no elbow room, so I thought I'd move the
files to a USB hdd I have on there, slow but ok temporarily, and do the
reorg there. Unfortunately, detatching the database has, somehow caused the
sysdatabases table to lose any knowledge of it. I've got the files in their
new location and would dearly love to be able to reattach them somehow to
shrink them down but I'm at a loss now.
I'd really appreciate a pointer here, I've run out of ideas.
Cheers
Chris Weston
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> Guys and gals, I'm in all sorts of trouble here. I've been nursing a
server
> with very little free space for my sql databases while I get a new one
> built, and it's been pretty safe, until a power cut the other day caused
> some sort of huge write to the log file and it ran out of space. I mean,
> really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
> 30gig. So, I needed to backup the transaction log, reorganise and shrink
> the database. Couldn't do that, no elbow room, so I thought I'd move the
> files to a USB hdd I have on there, slow but ok temporarily, and do the
> reorg there. Unfortunately, detatching the database has, somehow caused
the
> sysdatabases table to lose any knowledge of it. I've got the files in
their
> new location and would dearly love to be able to reattach them somehow to
> shrink them down but I'm at a loss now.
> I'd really appreciate a pointer here, I've run out of ideas.
Just to follow up here, I've tried attaching the database from the context
menu on my databases folder, but it complains about the log file (all 22g of
it) being faulty. Can't I attach the mdf and let the log file be recreated?
I'm no sql expert but the mdf is the important part, surely?
Thanks,
Chris Weston
|||sp_attach_single_file_db [ @.dbname = ] 'dbname'
, [ @.physname = ] 'physical_name'
It will generate a new log file.
good luck.
d.
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:tIYSf.2054$H%3.875@.newsfe5-gui.ntli.net...[vbcol=seagreen]
> "Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
> news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> server
mean,[vbcol=seagreen]
shrink[vbcol=seagreen]
the[vbcol=seagreen]
> the
> their
to
> Just to follow up here, I've tried attaching the database from the context
> menu on my databases folder, but it complains about the log file (all 22g
of
> it) being faulty. Can't I attach the mdf and let the log file be
recreated?
> I'm no sql expert but the mdf is the important part, surely?
> Thanks,
> --
> Chris Weston
>
|||"d" <d@.d.com> wrote in message news:ruZSf.1311$I7.1016@.trnddc03...
> sp_attach_single_file_db [ @.dbname = ] 'dbname'
> , [ @.physname = ] 'physical_name'
> It will generate a new log file.
>
> good luck.
>
I appreciate the sentiment However, I'm not sure where to run this
command? In a query window in Enterprise Manager?
Many thanks,
Chris Weston
|||Hi Chris
This is expected behavior. When you detach a database, it is completely
removed from the system. It is different from DROP in that the files still
exist on disk.
To get the db back, you have to attach it.
If you are attaching through Enterprise Manager, and SQL Server complains
about the log, you can try changing the name of the log file on disk so EM
can't find it, and see if a new log will be rebuilt.
Or, as d suggested, you can run the procedure from sp_attach_single_file_db
from a query window in Query Analyzer.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:w9YSf.2115$g76.696@.newsfe2-gui.ntli.net...
> Guys and gals, I'm in all sorts of trouble here. I've been nursing a
> server
> with very little free space for my sql databases while I get a new one
> built, and it's been pretty safe, until a power cut the other day caused
> some sort of huge write to the log file and it ran out of space. I mean,
> really ran out. The mdf and logs are at about 29.5 gigs and the hdd is
> 30gig. So, I needed to backup the transaction log, reorganise and shrink
> the database. Couldn't do that, no elbow room, so I thought I'd move the
> files to a USB hdd I have on there, slow but ok temporarily, and do the
> reorg there. Unfortunately, detatching the database has, somehow caused
> the
> sysdatabases table to lose any knowledge of it. I've got the files in
> their
> new location and would dearly love to be able to reattach them somehow to
> shrink them down but I'm at a loss now.
> I'd really appreciate a pointer here, I've run out of ideas.
> Cheers
> Chris Weston
>
>
|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uKXsmotSGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hi Chris
> This is expected behavior. When you detach a database, it is completely
> removed from the system. It is different from DROP in that the files still
> exist on disk.
> To get the db back, you have to attach it.
> If you are attaching through Enterprise Manager, and SQL Server complains
> about the log, you can try changing the name of the log file on disk so EM
> can't find it, and see if a new log will be rebuilt.
> Or, as d suggested, you can run the procedure from
sp_attach_single_file_db
> from a query window in Query Analyzer.
Still, no joy - I have tried both these methods but still get an error
saying that it can't find the .ldf file. How can I get past this?
Thanks,
Chris Weston
|||Hi
The methods described by Kalen should work if you followed them exactly.
Can you post the exact sp_attach_single_file_db command that you have used?
Make sure that you have move or renamed the ldf file and there is enough
space to create a new one.
Make sure that the mdf file does exist in the directory you have specified.
Make sure that directory where it wants to create the ldf file exists and
that the permissions allow you to create a new file.
John
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:dz8Tf.2219$g76.437@.newsfe2-gui.ntli.net...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uKXsmotSGHA.5500@.TK2MSFTNGP12.phx.gbl...
> xslmailinglist@.btopenworld.com>> from a query window in Query Analyzer.
> Still, no joy - I have tried both these methods but still get an error
> saying that it can't find the .ldf file. How can I get past this?
> Thanks,
> Chris Weston
>
|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e%23SFB3zSGHA.4600@.TK2MSFTNGP11.phx.gbl...
> Hi
> The methods described by Kalen should work if you followed them exactly.
> Can you post the exact sp_attach_single_file_db command that you have
used?
> Make sure that you have move or renamed the ldf file and there is enough
> space to create a new one.
> Make sure that the mdf file does exist in the directory you have
specified.
> Make sure that directory where it wants to create the ldf file exists and
> that the permissions allow you to create a new file.
It exists, it's my default sql data directory and I'm logged in as
administrator. The mdf exists. I'm using
EXEC sp_attach_single_file_db @.dbname = 'SysCompanyA',
@.physname = N'h:\systemp\SyscompA.mdf'
but I get an error saying that 'the physical file name 'E:\program
files\...\data\syscompanyA_log.ldf may be incorrect'
So is it saying it can't create or that it can't find the ldf file?
Chris Weston
|||HI Chris
Was E:\ the original drive? It looks like when SQL Server creates a new log
file, it wants to create it on the drive where it originally existed.
I have use sp_attach_db to move a db, or to rebuild a log, but never at the
same time.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris Weston" <chrisweston[losethislot]@.ntlworld.com> wrote in message
news:o0hTf.2330$g76.772@.newsfe2-gui.ntli.net...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e%23SFB3zSGHA.4600@.TK2MSFTNGP11.phx.gbl...
> used?
> specified.
> It exists, it's my default sql data directory and I'm logged in as
> administrator. The mdf exists. I'm using
> EXEC sp_attach_single_file_db @.dbname = 'SysCompanyA',
> @.physname = N'h:\systemp\SyscompA.mdf'
> but I get an error saying that 'the physical file name 'E:\program
> files\...\data\syscompanyA_log.ldf may be incorrect'
> So is it saying it can't create or that it can't find the ldf file?
> --
> Chris Weston
>
>
|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OUXQlB9SGHA.5172@.TK2MSFTNGP12.phx.gbl...
> HI Chris
> Was E:\ the original drive? It looks like when SQL Server creates a new
log
> file, it wants to create it on the drive where it originally existed.
> I have use sp_attach_db to move a db, or to rebuild a log, but never at
the
> same time.
Kalen,
E:\ was the original drive, and it still exists. I've no problem with it
creating the log there if need be.
Chris Weston