Transactionlog location issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LordFox - 04 Mar 2008 11:03 GMT

Hi,

By default, the transactionlogs are created in the same location as
the databases. We've gone through the works of moving the already
exisitng transactionlogs to a seperate disk and have changed the
default location of the transactionlogfiles.

When I create a new databases from the SSMC, the transactionlog is
created in the new location, so the setting seems to work fine.

Yesterday, someone installed an application (Virtual Machine Manager)
and created a database on the SQL server. The transactionlog was,
however, created in the same location as the database, completely
ignoring the default location.

How is this possible? What can I do from preventing this from
happening?

Kind regards,

LF

Reply to this Message

Uri Dimant - 04 Mar 2008 11:58 GMT

LordFox
Is it possible that an application runs script where LOG files goes to the
'old' location?

> Hi,
>
[quoted text clipped - 17 lines]
>
> LF

Reply to this Message

LordFox - 04 Mar 2008 12:09 GMT

Uri,

I doubt there would be a script that does this (at least in our
environment), as all other logfiles have been moved to the new
location and are staying right where they ought to be.

Kind regards,

Rick

> LordFox
> Is it possible that an application runs script where LOG files goes to the
[quoted text clipped - 23 lines]
>
> - Show quoted text -

Reply to this Message

Uri Dimant - 04 Mar 2008 12:19 GMT

Well, I can overwrite the default by running script CREATE DATABASE.....

Uri,

I doubt there would be a script that does this (at least in our
environment), as all other logfiles have been moved to the new
location and are staying right where they ought to be.

Kind regards,

Rick

On Mar 4, 12:58 pm, "Uri Dimant" <u...@iscar.co.il> wrote:

> LordFox
> Is it possible that an application runs script where LOG files goes to the
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Reply to this Message

LordFox - 04 Mar 2008 12:59 GMT

Uri,

Thanks, this reply may prove to be very valuable. I will investigate
this.

Any other comments/solutions are welcome.

Thanks so far,

Rick

> Well, I can overwrite the default by running script CREATE DATABASE.....
>
[quoted text clipped - 43 lines]
>
> - Show quoted text -

Reply to this Message

Dan Guzman - 04 Mar 2008 13:43 GMT

The bottom line is that SQL Server will create database files in the default
location as specified under SSMS Server properties-->Database settings
unless the CREATE DATABASE statement specifies a different location. If
"CREATE DATABASE MyDatabase" creates the database files in the desired
location, then the only way to create files elsewhere is with an explicit ON
clause.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Uri,

Thanks, this reply may prove to be very valuable. I will investigate
this.

Any other comments/solutions are welcome.

Thanks so far,

Rick

On Mar 4, 1:19 pm, "Uri Dimant" <u...@iscar.co.il> wrote:

> Well, I can overwrite the default by running script CREATE DATABASE.....
>
[quoted text clipped - 45 lines]
>
> - Show quoted text -

Reply to this Message

LordFox - 04 Mar 2008 14:16 GMT

Dan,

Creating a database from the SSMC (rightclick -> New database) will
create the logfile at the desired location.

Running "CREATE DATABASE testdb2" (indeed, with no further options)
from the SQL Query window in the SSMC, will create the logfile at the
same location as the databasefile (the incorrect location).

So I am not using any explicit ON clause, but the transactionlog is
created in the wrong location.

Is this expected behaviour?

Kind regards,

Rick

On Mar 4, 2:43 pm, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net>
wrote:

> The bottom line is that SQL Server will create database files in the default
> location as specified under SSMS Server properties-->Database settings
[quoted text clipped - 73 lines]
>
> - Show quoted text -

Reply to this Message

Tibor Karaszi - 04 Mar 2008 16:22 GMT

Yes, that is expected behavior. Best practice is to specify attributes for the database files
instead of relying on various default settings.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> The bottom line is that SQL Server will create database files in the default
> location as specified under SSMS Server properties-->Database settings
[quoted text clipped - 78 lines]
>
> - Show quoted text -

Reply to this Message

LordFox - 04 Mar 2008 21:06 GMT

Great :-(

Any time a software developer does not specify these attributes the
logfiles will be created in the wrong location. Unfortunately,
Microsoft does not (always) use these attributes either.

I'm not amused, but thanks for clearing that up for me. I guess
internal procedures will need to be updated to reflect this
situation..

Kind regards,

Rick

On Mar 4, 5:22 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.com> wrote:

> Yes, that is expected behavior. Best practice is to specify attributes for the database files
> instead of relying on various default settings.
[quoted text clipped - 107 lines]
>
> - Show quoted text -

Reply to this Message

Dan Guzman - 05 Mar 2008 03:48 GMT

> Is this expected behaviour?

I think both the SSMS GUI and T-SQL create database methods should default
to the same folder locations. However, the SQL Engine may need to be
restarted before to the default folder locations are recognized. This is in
contrast with SSMS, which will pick up the change immediately.

I agree with Tibor that the best approach is to explicitly specify the
desired paths when creating databases.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> The bottom line is that SQL Server will create database files in the
> default
[quoted text clipped - 81 lines]
>
> - Show quoted text -

Reply to this Message

LordFox - 05 Mar 2008 08:40 GMT

Dan,

Thanks. In my opinion, too, it should, but it doesn't. The SQL Engine
for the instance was stopped and started. I have, however, found the
fix for this.

As we were moving the transactionlogs to the new location, we decided
to keep the logs for the 6 system databases in the old location, as
these databases need a different approach and we wanted to automate as
much of the process as possible.

Confronted with this issue yesterday, I decided to dig deeper into SQL
db and logfiles and this led me to the cause of the issue - and the
solution.

When SQL creates a new database, it makes a copy of the model
database, including everything in it. Here is where I said to myself:
'Hey, wait a minute! If it is a *copy*, maybe the logfile is created
at the same location as the modellog.ldf file. And that would mean
that if I move that particular logfile, it might well be that a new
database, created with the 'CREATE DATABASE' T-SQL command will have
its logfile put in the new location as well.'

I just tested it, and indeed, that's the way it works.

Problem solved, thanks for everyone's help.

Rick

On Mar 5, 4:48 am, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net>
wrote:

> > Is this expected behaviour?
>
[quoted text clipped - 120 lines]
>
> - Show quoted text -

Reply to this Message

LordFox - 05 Mar 2008 09:23 GMT

[Yet another update, especially for the people who might read this
thread sometime in the future.]

I may have been wrong. It might just be that the setting applies to T-
SQL creation as well after a restart of the Engine.

As I was just updating the documentation on this, it occurred to me
that the indeed I did restart the SQL Engine (I checked that from the
eventlog), but in the documentation, the actual change of the location
in SSMC was done *after* that.

I set the location to a new location again, then restarted the engine,
and then did a 'CREATE DATABASE' - and the logfile was created in the
right location.

Just goes to show that you need to think and rethink every step you
make and when to make it - and not to jump to conclusions too
soon :-).

Cheers,

Rick

> Dan,
>
[quoted text clipped - 157 lines]
>
> - Show quoted text -

Reply to this Message

Dan Guzman - 05 Mar 2008 12:26 GMT

> I may have been wrong. It might just be that the setting applies to T-
> SQL creation as well after a restart of the Engine.

I'm glad you got this sorted out. The default database file folders that
you specify under database settings are actually registry settings that are
honored by both the SMO and T-SQL applications.

A common misconception is that the model database file locations is used as
a default. The model is used as a template for file sizes and schema but
not file locations.

Signature

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Dan,
>
[quoted text clipped - 168 lines]
>
> - Show quoted text -

Reply to this Message

Tibor Karaszi - 05 Mar 2008 16:16 GMT

> I'm glad you got this sorted out. The default database file folders that you specify under
> database settings are actually registry settings that are honored by both the SMO and T-SQL
> applications.

Sorry for the misinformation I posted earlier in this thread. I believe that some earlier version of
SQL server did indeed have two sets of registry values (one for engine default and another for GUI
default). So I didn't know that MS coalesced the two into one...

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>> I may have been wrong. It might just be that the setting applies to T-
>> SQL creation as well after a restart of the Engine.
[quoted text clipped - 190 lines]
>>
>> - Show quoted text -

Reply to this Message