Move Log Files From C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG To E:\MSSQL Logs

How do I move the default log files placed in: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG to a different location? I want to place them in a partition I have setup which stores log files? Reason being is that this log directory is getting quite large, 600MB and I want to conserve space on my C:\ partition.

Thanks.

You can move the files using sp_detach_db and sp_attach_db. You can find the steps explained in the following article:

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

http://support.microsoft.com/kb/224071

-Sue

|||

Sue:

Acutally I dont want to move any database files, what I want to move is simply the log files, just errors, activity and such. The files reside in the /log directory not the /data directory.

Thanks for the help.

|||

For the SQL error log, open up SQL Server Configuration Manager. Select the SQL Services node, double click on SQL Server in the right pane and go to the advanced tab. View the startup parameters. The path following -e is for the error log. See the books online topic: Using the SQL Server Service Startup Options.

You can use Configuration Manager to change the path for SQL Agent as well. Select the service node, double click on SQL Server agent and go to the advanced tab. Change the path for the dump directory.

In terms of the default trace, that will default to your installation directory. You can create your own trace if you want another location.

-Sue

|||

Thanks Sue that did the trick.

Changed dump directory to: E:\MSSQL and startup parameters e to E:\MSSQL\ERRORLOG and restarted the service.

move database from 1 instance to another

Hi,
One of my colleague has a SQL server 2k5 on C:\Program files\...
i have basically installed all instances and program files of SQL server
2k5 on D: drive.
I've recreate his instance on D: drive and he gave me all files MDF,
LDF... of his instance (quick and dirty backup :-( )
when i stop the instance process and copy all his files on my D: drive
into the right instance folder, my instance process does not restart and
i got the following error into log events :
The description for Event ID ( 17204 ) in Source ( MSSQL$MAGICEYE )
cannot be found. The local computer may not have the necessary registry
information or message DLL files to display messages from a remote
computer. You may be able to use the /AUXSOURCE= flag to retrieve this
description; see Help and Support for details. The following information
is part of the event: FCB::Open failed, C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf, 1, 3(The system
cannot find the path specified.).
so this is clear that the instance path is stored into database files.
how can i do to make this instance files (dirty back) work on my D:
drive instance ?
thanks a lot,
A.Did you restore his master, model, msdb perhaps? If so, do not do this.
Just restore his user databses. There will be issues with logins, however.
You will need to recreate them and sync the login/database user information
if you care about that.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Alain R." <noemail@.nospam.com> wrote in message
news:%23RlvthdNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi,
> One of my colleague has a SQL server 2k5 on C:\Program files\...
> i have basically installed all instances and program files of SQL server
> 2k5 on D: drive.
> I've recreate his instance on D: drive and he gave me all files MDF,
> LDF... of his instance (quick and dirty backup :-( )
> when i stop the instance process and copy all his files on my D: drive
> into the right instance folder, my instance process does not restart and i
> got the following error into log events :
> The description for Event ID ( 17204 ) in Source ( MSSQL$MAGICEYE ) cannot
> be found. The local computer may not have the necessary registry
> information or message DLL files to display messages from a remote
> computer. You may be able to use the /AUXSOURCE= flag to retrieve this
> description; see Help and Support for details. The following information
> is part of the event: FCB::Open failed, C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf, 1, 3(The system cannot
> find the path specified.).
> so this is clear that the instance path is stored into database files.
> how can i do to make this instance files (dirty back) work on my D: drive
> instance ?
> thanks a lot,
> A.|||I already only restored his DB file, but after my instance service was
not able to restart :-(
so any other suggestion ?
TheSQLGuru wrote:
> Did you restore his master, model, msdb perhaps? If so, do not do this.
> Just restore his user databses. There will be issues with logins, however.
> You will need to recreate them and sync the login/database user information
> if you care about that.
>|||The files for master db is pointed to in the registry. Resource db need to be in the same folder.
All the other databases are pointed to from master. Problem is that if you fix registry path for
master and make sure resource db is in same folder, then SQL Server will get a bit further, but when
it tires to open model, it won't find the files and won't start (can't live without tempdb and can't
create tempdb without model). There's a section in BOL 2005 on how to move system databases and also
KB 224071 can be useful.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alain R." <noemail@.nospam.com> wrote in message news:uGUuU%23dNIHA.1188@.TK2MSFTNGP04.phx.gbl...
>I already only restored his DB file, but after my instance service was not able to restart :-(
> so any other suggestion ?
> TheSQLGuru wrote:
>> Did you restore his master, model, msdb perhaps? If so, do not do this. Just restore his user
>> databses. There will be issues with logins, however. You will need to recreate them and sync the
>> login/database user information if you care about that.

move database from 1 instance to another

Hi,
One of my colleague has a SQL server 2k5 on C:\Program files\...
i have basically installed all instances and program files of SQL server
2k5 on D: drive.
I've recreate his instance on D: drive and he gave me all files MDF,
LDF... of his instance (quick and dirty backup :-( )
when i stop the instance process and copy all his files on my D: drive
into the right instance folder, my instance process does not restart and
i got the following error into log events :
The description for Event ID ( 17204 ) in Source ( MSSQL$MAGICEYE )
cannot be found. The local computer may not have the necessary registry
information or message DLL files to display messages from a remote
computer. You may be able to use the /AUXSOURCE= flag to retrieve this
description; see Help and Support for details. The following information
is part of the event: FCB::Open failed, C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf, 1, 3(The system
cannot find the path specified.).
so this is clear that the instance path is stored into database files.
how can i do to make this instance files (dirty back) work on my D:
drive instance ?
thanks a lot,
A.Did you restore his master, model, msdb perhaps? If so, do not do this.
Just restore his user databses. There will be issues with logins, however.
You will need to recreate them and sync the login/database user information
if you care about that.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Alain R." <noemail@.nospam.com> wrote in message
news:%23RlvthdNIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Hi,
> One of my colleague has a SQL server 2k5 on C:\Program files\...
> i have basically installed all instances and program files of SQL server
> 2k5 on D: drive.
> I've recreate his instance on D: drive and he gave me all files MDF,
> LDF... of his instance (quick and dirty backup :-( )
> when i stop the instance process and copy all his files on my D: drive
> into the right instance folder, my instance process does not restart and i
> got the following error into log events :
> The description for Event ID ( 17204 ) in Source ( MSSQL$MAGICEYE ) cannot
> be found. The local computer may not have the necessary registry
> information or message DLL files to display messages from a remote
> computer. You may be able to use the /AUXSOURCE= flag to retrieve this
> description; see Help and Support for details. The following information
> is part of the event: FCB::Open failed, C:\Program Files\Microsoft SQL
> Server\MSSQL.2\MSSQL\DATA\mssqlsystemresource.mdf, 1, 3(The system cannot
> find the path specified.).
> so this is clear that the instance path is stored into database files.
> how can i do to make this instance files (dirty back) work on my D: drive
> instance ?
> thanks a lot,
> A.|||I already only restored his DB file, but after my instance service was
not able to restart :-(
so any other suggestion ?
TheSQLGuru wrote:
> Did you restore his master, model, msdb perhaps? If so, do not do this.
> Just restore his user databses. There will be issues with logins, however
.
> You will need to recreate them and sync the login/database user informatio
n
> if you care about that.
>|||The files for master db is pointed to in the registry. Resource db need to b
e in the same folder.
All the other databases are pointed to from master. Problem is that if you f
ix registry path for
master and make sure resource db is in same folder, then SQL Server will get
a bit further, but when
it tires to open model, it won't find the files and won't start (can't live
without tempdb and can't
create tempdb without model). There's a section in BOL 2005 on how to move s
ystem databases and also
KB 224071 can be useful.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alain R." <noemail@.nospam.com> wrote in message news:uGUuU%23dNIHA.1188@.TK2MSFTNGP04.phx.gb
l...[vbcol=seagreen]
>I already only restored his DB file, but after my instance service was not
able to restart :-(
> so any other suggestion ?
> TheSQLGuru wrote: