Increase Performance with two connections

HI all,
I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
In DB2 I use a view which uses some joins against DB1 like:
select * from MyTable left outer join DB1.dbo.users on ...
This works, but I get a time problem: the view is very slow.
The reason seems to be the connection to DB1.
In SQL Management Studio I can reproduce the situation: when I´m in DB2 and
try to "USE DB1" it takes something like >=2 seconds on my local machine.
After making the "USE DB1" the view works great - very fast (SQL Server
seemed to cache the connection).
But the connection caching works only for some minutes - when "falling back"
the view is slow again.
So, I´m looking for a solution for my problem (I can´t change the
database-concept, some data from DB1 is needed also in DB2). Maybe open the
connection DB1 for ... some hours? Or somebody has better ideas?
Thanks,
TonyI assume you have two databases in the same SQL Server instance (which is what it sounds like from
your description). Check if the database DB1 has the database property autoclose turned on. If so,
turn it off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
> HI all,
> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
> In DB2 I use a view which uses some joins against DB1 like:
> select * from MyTable left outer join DB1.dbo.users on ...
> This works, but I get a time problem: the view is very slow.
> The reason seems to be the connection to DB1.
> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
> takes something like >=2 seconds on my local machine.
> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
> connection).
> But the connection caching works only for some minutes - when "falling back" the view is slow
> again.
> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data from
> DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody has
> better ideas?
> Thanks,
> Tony
>|||HI Tibor,
thanks for your reply.
Yes, Autoclose=ON is standard.
Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume
it will work).
Does Autoclose=OFF have any other effects...?
Help of SQL2005 says:
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature.
The AutoClose property exposes server behavior for databases not accessed by
a user.
Thanks, Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>I assume you have two databases in the same SQL Server instance (which is
>what it sounds like from your description). Check if the database DB1 has
>the database property autoclose turned on. If so, turn it off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>|||> Yes, Autoclose=ON is standard.
Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this turned
on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes your problem.
> Does Autoclose=OFF have any other effects...?
I hope you don't have other programs that tries to do anything with the database files while no user
is connected? If you do, then they will be affected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
> HI Tibor,
> thanks for your reply.
> Yes, Autoclose=ON is standard.
> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
> Does Autoclose=OFF have any other effects...?
> Help of SQL2005 says:
> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature
> in new development work, and plan to modify applications that currently use this feature.
> The AutoClose property exposes server behavior for databases not accessed by a user.
> Thanks, Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like from
>>your description). Check if the database DB1 has the database property autoclose turned on. If so,
>>turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1" it
>> takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>|||HI Tibor,
yes, with Autoclose=OFF the App works perfect!
But I only don´t really understand
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
which all use database DB1 - and some other database-stored procs/views.
Will they be affected?
Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any
side-effects)?
Thanks again,
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
> Standard? In your shop or in SQL Server? I believe that some edition of
> SQL Server has this turned on by default, which was a bad (IMO) choice.
> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
> I hope you don't have other programs that tries to do anything with the
> database files while no user is connected? If you do, then they will be
> affected.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server.
>> Avoid using this feature in new development work, and plan to modify
>> applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not accessed
>> by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is
>>what it sounds like from your description). Check if the database DB1 has
>>the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2
>> and try to "USE DB1" it takes something like >=2 seconds on my local
>> machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server
>> seemed to cache the connection).
>> But the connection caching works only for some minutes - when "falling
>> back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe open
>> the connection DB1 for ... some hours? Or somebody has better ideas?
>> Thanks,
>> Tony
>>
>|||The application uses the database *through* SQL Server. I.e., they don't access the database files
directly.
I recommend that you have autoclose set to off.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Toni Pohl" <atwork43@.hotmail.com> wrote in message
news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
> HI Tibor,
> yes, with Autoclose=OFF the App works perfect!
> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0) which all use database
> DB1 - and some other database-stored procs/views. Will they be affected?
> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about any side-effects)?
> Thanks again,
> Tony
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
> news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of SQL Server has this
>> turned on by default, which was a bad (IMO) choice. Anyhow, turn this off and see if it fixes
>> your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the database files while no
>> user is connected? If you do, then they will be affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL Server. Avoid using this
>> feature in new development work, and plan to modify applications that currently use this
>> feature.
>> The AutoClose property exposes server behavior for databases not accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb im Newsbeitrag
>> news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which is what it sounds like
>>from your description). Check if the database DB1 has the database property autoclose turned on.
>>If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in DB2 and try to "USE DB1"
>> it takes something like >=2 seconds on my local machine.
>> After making the "USE DB1" the view works great - very fast (SQL Server seemed to cache the
>> connection).
>> But the connection caching works only for some minutes - when "falling back" the view is slow
>> again.
>> So, I´m looking for a solution for my problem (I can´t change the database-concept, some data
>> from DB1 is needed also in DB2). Maybe open the connection DB1 for ... some hours? Or somebody
>> has better ideas?
>> Thanks,
>> Tony
>>
>>
>|||Hi Tibor,
yes, I also did a little reasearch on AutoClose - schould always be off on
server and productive environments!
Thanks again for your hint! This was my solution!
Tony
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:ePINj6U6HHA.5164@.TK2MSFTNGP05.phx.gbl...
> The application uses the database *through* SQL Server. I.e., they don't
> access the database files directly.
>
> I recommend that you have autoclose set to off.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
> news:44904194-2251-46E3-995E-9616DD6FD563@.microsoft.com...
>> HI Tibor,
>> yes, with Autoclose=OFF the App works perfect!
>> But I only don´t really understand
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> Well, in my case there are only _some_ asp.net webapps (.net 1.1 and 2.0)
>> which all use database DB1 - and some other database-stored procs/views.
>> Will they be affected?
>> Or is it ok to simply use Autoclose=OFF (and I don´t have to care about
>> any side-effects)?
>> Thanks again,
>> Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:%23XzAX3T6HHA.5096@.TK2MSFTNGP04.phx.gbl...
>> Yes, Autoclose=ON is standard.
>> Standard? In your shop or in SQL Server? I believe that some edition of
>> SQL Server has this turned on by default, which was a bad (IMO) choice.
>> Anyhow, turn this off and see if it fixes your problem.
>> Does Autoclose=OFF have any other effects...?
>> I hope you don't have other programs that tries to do anything with the
>> database files while no user is connected? If you do, then they will be
>> affected.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:E59C38A0-EECA-4B32-92B3-9B12FFFE6FFD@.microsoft.com...
>> HI Tibor,
>> thanks for your reply.
>> Yes, Autoclose=ON is standard.
>> Sounds like a workaround (I´ll try tomorrow and post the result, I´ll
>> assume it will work).
>> Does Autoclose=OFF have any other effects...?
>> Help of SQL2005 says:
>> This feature will be removed in a future version of Microsoft SQL
>> Server. Avoid using this feature in new development work, and plan to
>> modify applications that currently use this feature.
>> The AutoClose property exposes server behavior for databases not
>> accessed by a user.
>> Thanks, Tony
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schrieb im Newsbeitrag news:eGO8g%23O6HHA.3740@.TK2MSFTNGP02.phx.gbl...
>>I assume you have two databases in the same SQL Server instance (which
>>is what it sounds like from your description). Check if the database
>>DB1 has the database property autoclose turned on. If so, turn it off.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Toni Pohl" <atwork43@.hotmail.com> wrote in message
>> news:7C81F6B8-1F6E-4DC0-85F0-004E17590108@.microsoft.com...
>> HI all,
>> I´ve two databases: DB1 and DB2 and an ASP.NET application using the
>> data.
>> In DB2 I use a view which uses some joins against DB1 like:
>> select * from MyTable left outer join DB1.dbo.users on ...
>> This works, but I get a time problem: the view is very slow.
>> The reason seems to be the connection to DB1.
>> In SQL Management Studio I can reproduce the situation: when I´m in
>> DB2 and try to "USE DB1" it takes something like >=2 seconds on my
>> local machine.
>> After making the "USE DB1" the view works great - very fast (SQL
>> Server seemed to cache the connection).
>> But the connection caching works only for some minutes - when
>> "falling back" the view is slow again.
>> So, I´m looking for a solution for my problem (I can´t change the
>> database-concept, some data from DB1 is needed also in DB2). Maybe
>> open the connection DB1 for ... some hours? Or somebody has better
>> ideas?
>> Thanks,
>> Tony
>>
>>
>

inconsistent database naming scheme?

In my physical database directory, I found the databases were named in several different ways:
1. db1.mdf
db1.ldf
2. db2_Data.MDF
db2_Log.LDF
3. db3.mdf
db3_Log.LDF
4. msdbdata.mdf
msdblog.ldf
5. model.mdf
modellog.ldf
How could this happen? I read the online books and just found that .mdf and .ldf are generally standard suffix for database names. What about other parts of a name? I do not remember how the names of these databases originally looked like. We're exper
iencing database corruptions these days. Those database names just started looking suspicious to me.
I would greatly appreciate any insights or pointers.
Bing
The names of the files has absolutely nothing to do with corruption in the
database. Usually corruption is the result of faulty hardware these days.
The only standard per say for data files is the Primary data file has an
extension of .mdf and any secondary ones have an extension of .ndf and of
coarse Log files are .ldf.
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing
|||> How could this happen?
Physical database file names can be any legal file name supported by your
file system:
CREATE DATABASE MyDatabase
ON(NAME='MyDatabase', FILENAME='C:\ICanNameThisAnything.abcde')
LOG ON(NAME='MyDatabase_Log', FILENAME='C:\ICanNameThisAnythingToo.defgh')
Andrew noted the suggested suffixes and it's a good practice to specify the
database name as the first part of the file name. The remainder of the file
name is up to you. Personally, I add a number to make the file name unique
and also include the filegroup name for files in user-defined filegroups.
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing
|||Thanks for the information.
Bing
"Dan Guzman" wrote:

> Physical database file names can be any legal file name supported by your
> file system:
> CREATE DATABASE MyDatabase
> ON(NAME='MyDatabase', FILENAME='C:\ICanNameThisAnything.abcde')
> LOG ON(NAME='MyDatabase_Log', FILENAME='C:\ICanNameThisAnythingToo.defgh')
> Andrew noted the suggested suffixes and it's a good practice to specify the
> database name as the first part of the file name. The remainder of the file
> name is up to you. Personally, I add a number to make the file name unique
> and also include the filegroup name for files in user-defined filegroups.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> several different ways:
> and .ldf are generally standard suffix for database names. What about other
> parts of a name? I do not remember how the names of these databases
> originally looked like. We're experiencing database corruptions these days.
> Those database names just started looking suspicious to me.
>
>
|||Additionally, if you are getting corruptions..
Ensure you are running your dbccs and watching them closely. corruption is
generally a preview for an upcoming ( but now transient) hardware error. Get
your hardware guys involved and check the disks, controllers, etc.
You may wish to keep your database backups a little longer until you have
found the problem and corrected it...You do not want to get into a
situation where all of your database backups are copies of a corrupted
database
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing
|||Good points., Wayne. Just like you said, a disk drive just went belly up not long after I noticed a lot errors I've never seen in the SQL server logs.
Thanks,
Bing
"Wayne Snyder" wrote:

> Additionally, if you are getting corruptions..
> Ensure you are running your dbccs and watching them closely. corruption is
> generally a preview for an upcoming ( but now transient) hardware error. Get
> your hardware guys involved and check the disks, controllers, etc.
> You may wish to keep your database backups a little longer until you have
> found the problem and corrected it...You do not want to get into a
> situation where all of your database backups are copies of a corrupted
> database
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> several different ways:
> and .ldf are generally standard suffix for database names. What about other
> parts of a name? I do not remember how the names of these databases
> originally looked like. We're experiencing database corruptions these days.
> Those database names just started looking suspicious to me.
>
>

inconsistent database naming scheme?

In my physical database directory, I found the databases were named in sever
al different ways:
1. db1.mdf
db1.ldf
2. db2_Data.MDF
db2_Log.LDF
3. db3.mdf
db3_Log.LDF
4. msdbdata.mdf
msdblog.ldf
5. model.mdf
modellog.ldf
How could this happen? I read the online books and just found that .mdf and
.ldf are generally standard suffix for database names. What about other pa
rts of a name? I do not remember how the names of these databases originall
y looked like. We're exper
iencing database corruptions these days. Those database names just started
looking suspicious to me.
I would greatly appreciate any insights or pointers.
BingThe names of the files has absolutely nothing to do with corruption in the
database. Usually corruption is the result of faulty hardware these days.
The only standard per say for data files is the Primary data file has an
extension of .mdf and any secondary ones have an extension of .ndf and of
coarse Log files are .ldf.
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing|||> How could this happen?
Physical database file names can be any legal file name supported by your
file system:
CREATE DATABASE MyDatabase
ON(NAME='MyDatabase', FILENAME='C:\ICanNameThisAnything.abcde')
LOG ON(NAME='MyDatabase_Log', FILENAME='C:\ICanNameThisAnythingToo.defgh')
Andrew noted the suggested suffixes and it's a good practice to specify the
database name as the first part of the file name. The remainder of the file
name is up to you. Personally, I add a number to make the file name unique
and also include the filegroup name for files in user-defined filegroups.
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing|||Thanks for the information.
Bing
"Dan Guzman" wrote:

> Physical database file names can be any legal file name supported by your
> file system:
> CREATE DATABASE MyDatabase
> ON(NAME='MyDatabase', FILENAME='C:\ICanNameThisAnything.abcde')
> LOG ON(NAME='MyDatabase_Log', FILENAME='C:\ICanNameThisAnythingToo.defgh')
> Andrew noted the suggested suffixes and it's a good practice to specify th
e
> database name as the first part of the file name. The remainder of the fi
le
> name is up to you. Personally, I add a number to make the file name uniqu
e
> and also include the filegroup name for files in user-defined filegroups.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> several different ways:
> and .ldf are generally standard suffix for database names. What about oth
er
> parts of a name? I do not remember how the names of these databases
> originally looked like. We're experiencing database corruptions these day
s.
> Those database names just started looking suspicious to me.
>
>|||Additionally, if you are getting corruptions..
Ensure you are running your dbccs and watching them closely. corruption is
generally a preview for an upcoming ( but now transient) hardware error. Get
your hardware guys involved and check the disks, controllers, etc.
You may wish to keep your database backups a little longer until you have
found the problem and corrected it...You do not want to get into a
situation where all of your database backups are copies of a corrupted
database
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing|||Good points., Wayne. Just like you said, a disk drive just went belly up no
t long after I noticed a lot errors I've never seen in the SQL server logs.
Thanks,
Bing
"Wayne Snyder" wrote:

> Additionally, if you are getting corruptions..
> Ensure you are running your dbccs and watching them closely. corruption is
> generally a preview for an upcoming ( but now transient) hardware error. G
et
> your hardware guys involved and check the disks, controllers, etc.
> You may wish to keep your database backups a little longer until you have
> found the problem and corrected it...You do not want to get into a
> situation where all of your database backups are copies of a corrupted
> database
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> several different ways:
> and .ldf are generally standard suffix for database names. What about oth
er
> parts of a name? I do not remember how the names of these databases
> originally looked like. We're experiencing database corruptions these day
s.
> Those database names just started looking suspicious to me.
>
>

inconsistent database naming scheme?

In my physical database directory, I found the databases were named in several different ways:
1. db1.mdf
db1.ldf
2. db2_Data.MDF
db2_Log.LDF
3. db3.mdf
db3_Log.LDF
4. msdbdata.mdf
msdblog.ldf
5. model.mdf
modellog.ldf
How could this happen? I read the online books and just found that .mdf and .ldf are generally standard suffix for database names. What about other parts of a name? I do not remember how the names of these databases originally looked like. We're experiencing database corruptions these days. Those database names just started looking suspicious to me.
I would greatly appreciate any insights or pointers.
BingThe names of the files has absolutely nothing to do with corruption in the
database. Usually corruption is the result of faulty hardware these days.
The only standard per say for data files is the Primary data file has an
extension of .mdf and any secondary ones have an extension of .ndf and of
coarse Log files are .ldf.
--
Andrew J. Kelly SQL MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing|||> How could this happen?
Physical database file names can be any legal file name supported by your
file system:
CREATE DATABASE MyDatabase
ON(NAME='MyDatabase', FILENAME='C:\ICanNameThisAnything.abcde')
LOG ON(NAME='MyDatabase_Log', FILENAME='C:\ICanNameThisAnythingToo.defgh')
Andrew noted the suggested suffixes and it's a good practice to specify the
database name as the first part of the file name. The remainder of the file
name is up to you. Personally, I add a number to make the file name unique
and also include the filegroup name for files in user-defined filegroups.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing|||Thanks for the information.
Bing
"Dan Guzman" wrote:
> > How could this happen?
> Physical database file names can be any legal file name supported by your
> file system:
> CREATE DATABASE MyDatabase
> ON(NAME='MyDatabase', FILENAME='C:\ICanNameThisAnything.abcde')
> LOG ON(NAME='MyDatabase_Log', FILENAME='C:\ICanNameThisAnythingToo.defgh')
> Andrew noted the suggested suffixes and it's a good practice to specify the
> database name as the first part of the file name. The remainder of the file
> name is up to you. Personally, I add a number to make the file name unique
> and also include the filegroup name for files in user-defined filegroups.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> > In my physical database directory, I found the databases were named in
> several different ways:
> >
> > 1. db1.mdf
> > db1.ldf
> >
> > 2. db2_Data.MDF
> > db2_Log.LDF
> >
> > 3. db3.mdf
> > db3_Log.LDF
> >
> > 4. msdbdata.mdf
> > msdblog.ldf
> >
> > 5. model.mdf
> > modellog.ldf
> >
> > How could this happen? I read the online books and just found that .mdf
> and .ldf are generally standard suffix for database names. What about other
> parts of a name? I do not remember how the names of these databases
> originally looked like. We're experiencing database corruptions these days.
> Those database names just started looking suspicious to me.
> >
> > I would greatly appreciate any insights or pointers.
> >
> >
> > Bing
>
>|||Additionally, if you are getting corruptions..
Ensure you are running your dbccs and watching them closely. corruption is
generally a preview for an upcoming ( but now transient) hardware error. Get
your hardware guys involved and check the disks, controllers, etc.
You may wish to keep your database backups a little longer until you have
found the problem and corrected it...You do not want to get into a
situation where all of your database backups are copies of a corrupted
database
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bing" <bing@.discussions.microsoft.com> wrote in message
news:17493040-675C-432A-8D95-F845EDB7A4C1@.microsoft.com...
> In my physical database directory, I found the databases were named in
several different ways:
> 1. db1.mdf
> db1.ldf
> 2. db2_Data.MDF
> db2_Log.LDF
> 3. db3.mdf
> db3_Log.LDF
> 4. msdbdata.mdf
> msdblog.ldf
> 5. model.mdf
> modellog.ldf
> How could this happen? I read the online books and just found that .mdf
and .ldf are generally standard suffix for database names. What about other
parts of a name? I do not remember how the names of these databases
originally looked like. We're experiencing database corruptions these days.
Those database names just started looking suspicious to me.
> I would greatly appreciate any insights or pointers.
>
> Bing

Inactive Transaction/Log will not truncate after replication disabled

Hi, all
We have a clustered SQL Server 2000 (SP3a) in Window 2003 server
environment. I set up a transactional replication from 'DB1' at node A (
serves as 'publisher' ) to 'DB2' (as 'distributor' and 'subscriber') at node
B. Database option for DB1 in Node A is 'full recovery' model with
scheduled transaction backup run.
I set up the push replication at 'DB1' with published article 'T' and not
yet to let the snapshot agent start to do the reinitiating, I decided to
disable/remove all replications setup via Enterprise Manager. The Enterprise
Manager did remove everything from what I can see. But from that on, I
noticed that the Transaction Log in the 'DB1' keep growing even after log
file backup, the inactive transactions are queued for future snapshot usage,
I guess. I followed some articles found to run DTS to transfer some data
over to other database or reset up the replication and run the 'EXEC
sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time = 0,
@.reset = 1'. They all did truncate the log after the action.
However, problem is that I do not want the replication any more on the
DB1/Node1. It is kind of a vicious cycle in the set up now. If I removed the
replication/publisher setup, I can not run that 'sp_repldone' to clear the
log. But once I put back the replication/publisher for that cleanup action
and remove the replication after that, it seems to put a mark in the
database and the transaction log starts to queue and grow in a
no-replication setup. For comparison, I also did some test in a stand-alone,
non-clustered SQL server box and with publisher/distributor/subscriber all
in the same box but with two SQL Server instances to mimic the operation,
the transaction log seems to work fine and not to keep growing after
replication setup was removed.
Does anyone have the same experience or any insight to this issue ? please
help.
Thanks in advance for any of your input.
Eugene
I am a little confused by this statement "I decided to disable/remove all
replications setup via Enterprise Manager" Do you mean you disabled
replication or your dropped all subscriptions? If you dropped anonymous pull
subscriptions, it is possible that the log reader will keep reading the tlog
on DB1, and writing to the distribution database on DB2.
It seems however, from what you describe that you have disabled replication
on DB1, and still your tlog continues to grow. If this is the case, can you
run dbcc opentran in the database you are publishing? Also run sp_repltran.
sp_repltran should return nothing.
I would try to disable the publisher (db1) from being able to use the
distribution database on db2, and then enable db1 as a distributor, create a
local publication and a local subscription, and then disable publishing.
This should clear the condition.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on SQL FTS/Indexing Services
http://www.indexserverfaq.com
"Eugene Hwang" <hwang_eugene@.hotmail.com> wrote in message
news:uxkXd9KCFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Hi, all
> We have a clustered SQL Server 2000 (SP3a) in Window 2003 server
> environment. I set up a transactional replication from 'DB1' at node A (
> serves as 'publisher' ) to 'DB2' (as 'distributor' and 'subscriber') at
node
> B. Database option for DB1 in Node A is 'full recovery' model with
> scheduled transaction backup run.
> I set up the push replication at 'DB1' with published article 'T' and not
> yet to let the snapshot agent start to do the reinitiating, I decided to
> disable/remove all replications setup via Enterprise Manager. The
Enterprise
> Manager did remove everything from what I can see. But from that on, I
> noticed that the Transaction Log in the 'DB1' keep growing even after log
> file backup, the inactive transactions are queued for future snapshot
usage,
> I guess. I followed some articles found to run DTS to transfer some data
> over to other database or reset up the replication and run the 'EXEC
> sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0,
> @.reset = 1'. They all did truncate the log after the action.
> However, problem is that I do not want the replication any more on the
> DB1/Node1. It is kind of a vicious cycle in the set up now. If I removed
the
> replication/publisher setup, I can not run that 'sp_repldone' to clear the
> log. But once I put back the replication/publisher for that cleanup action
> and remove the replication after that, it seems to put a mark in the
> database and the transaction log starts to queue and grow in a
> no-replication setup. For comparison, I also did some test in a
stand-alone,
> non-clustered SQL server box and with publisher/distributor/subscriber all
> in the same box but with two SQL Server instances to mimic the operation,
> the transaction log seems to work fine and not to keep growing after
> replication setup was removed.
> Does anyone have the same experience or any insight to this issue ?
please
> help.
> Thanks in advance for any of your input.
> Eugene
>
>
>
|||Hilary,
I did what you said to re-establish the replication setup again but have
everything ( publisher/distributor/subscriber) LOCALLY in one node instead
of across two nodes that I did before and then disable publishing. It works
perfectly to clean up all marks, the inactive transaction log is cleared up
after the backup. Everything is working now.
Thanks a lot for your prompt suggestion and want to say thanks again on your
attention on almost every post in the group.
Eugene
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e2PBQROCFHA.3840@.tk2msftngp13.phx.gbl...
> I am a little confused by this statement "I decided to disable/remove all
> replications setup via Enterprise Manager" Do you mean you disabled
> replication or your dropped all subscriptions? If you dropped anonymous
pull
> subscriptions, it is possible that the log reader will keep reading the
tlog
> on DB1, and writing to the distribution database on DB2.
> It seems however, from what you describe that you have disabled
replication
> on DB1, and still your tlog continues to grow. If this is the case, can
you
> run dbcc opentran in the database you are publishing? Also run
sp_repltran.
> sp_repltran should return nothing.
> I would try to disable the publisher (db1) from being able to use the
> distribution database on db2, and then enable db1 as a distributor, create
a[vbcol=seagreen]
> local publication and a local subscription, and then disable publishing.
> This should clear the condition.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on SQL FTS/Indexing Services
> http://www.indexserverfaq.com
> "Eugene Hwang" <hwang_eugene@.hotmail.com> wrote in message
> news:uxkXd9KCFHA.2288@.TK2MSFTNGP14.phx.gbl...
> node
not[vbcol=seagreen]
> Enterprise
log[vbcol=seagreen]
> usage,
=[vbcol=seagreen]
> 0,
> the
the[vbcol=seagreen]
action[vbcol=seagreen]
> stand-alone,
all[vbcol=seagreen]
operation,
> please
>