4 Transaction Log questions

[TZoner]

1) Can one find the location of the 'Transaction Log' at: <Hard
Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?

2) Is it safe to delete it, as SQL will create a new Transaction Log when it
realises none already exist?

3) When trying to do 2) I get error message that file is in use, even though
SQL Server is closed. Any suggestions?

4) Can anyone shed light on the error message 'Write on ... <my path
here>... failed, status = 112. BACKUP LOG is terminating abnormally'? I get
this when trying to manually backup the Transaction Log 'All Tasks - Backup'
Thanks for any replies to my ignorance.

[John Bell]

See inline:
"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

1) Can one find the location of the 'Transaction Log' at: <Hard
Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf? I am not sure what you are asking but sp_helpfile will give the names and
locations of the files associated with a current database.
2) Is it safe to delete it, as SQL will create a new Transaction Log when it realises none already exist? I would not recommend deleting any logfile. If SQLServer is running then you
won't be able to as the file is locked. If you have detatched the database
or SQL Server is not running, you may have problems re-attaching the
database or connecting to it once SQL Server is running.

3) When trying to do 2) I get error message that file is in use, even though SQL Server is closed. Any suggestions? I doubt if you have stopped the service, but in any case you should not be
deleting it.
If all you want to do is move the file then check out
http://support.microsoft.com/default...b;EN-US;224071

If you want to shrink it check out
http://msdn.microsoft.com/library/de...us/architec/8_
ar_da2_1uzr.asp

4) Can anyone shed light on the error message 'Write on ... <my path
here>... failed, status = 112. BACKUP LOG is terminating abnormally'? I get this when trying to manually backup the Transaction Log 'All Tasks - Backup'
I don't know what this error is, at a guess there is a lack of disk space.
Maybe there is more information in the SQL Server log file or in the Event
log. You will also get errors if you backup to a mapped network drive.

John

Thanks for any replies to my ignorance.

[TZoner]

John, thanks for the valuable information.

Where is the 'Transaction Log' located? I can't find the answer sp_helpfile
or after endless searches on google.

Does a Transaction Log exist per database in SQL Server? When backup occurs,
does these Transaction Log shrink?





"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...

See inline:
"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

1) Can one find the location of the 'Transaction Log' at: <Hard
Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf? I am not sure what you are asking but sp_helpfile will give the names and
locations of the files associated with a current database.

2) Is it safe to delete it, as SQL will create a new Transaction Log

when it

realises none already exist? I would not recommend deleting any logfile. If SQLServer is running then

you won't be able to as the file is locked. If you have detatched the database
or SQL Server is not running, you may have problems re-attaching the
database or connecting to it once SQL Server is running.

3) When trying to do 2) I get error message that file is in use, even though

SQL Server is closed. Any suggestions?

I doubt if you have stopped the service, but in any case you should not be
deleting it.
If all you want to do is move the file then check out
http://support.microsoft.com/default...b;EN-US;224071

If you want to shrink it check out

http://msdn.microsoft.com/library/de...us/architec/8_ ar_da2_1uzr.asp

4) Can anyone shed light on the error message 'Write on ... <my path
here>... failed, status = 112. BACKUP LOG is terminating abnormally'? I

get

this when trying to manually backup the Transaction Log 'All Tasks -

Backup'
I don't know what this error is, at a guess there is a lack of disk space.
Maybe there is more information in the SQL Server log file or in the Event
log. You will also get errors if you backup to a mapped network drive.

John

Thanks for any replies to my ignorance.

[John Bell]

Your transction logs are held in the ldf file which sp_help would give e.g
for Northwind you would get something like:

Name FileId filename filegroup Size Maxsize Growth Usage
Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB Unlimited
10% log only

Northwind_log is the internal logical name for the Transaction Log file
located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a log
file.

The log file will not automatically shrink on backup, see the link I posted
in the last reply regarding how to shrink it.

John

"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

John, thanks for the valuable information.

Where is the 'Transaction Log' located? I can't find the answer sp_helpfile or after endless searches on google.

Does a Transaction Log exist per database in SQL Server? When backup occurs, does these Transaction Log shrink?





"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...

See inline:
"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

1) Can one find the location of the 'Transaction Log' at: <Hard
Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?

I am not sure what you are asking but sp_helpfile will give the names and locations of the files associated with a current database.

2) Is it safe to delete it, as SQL will create a new Transaction Log when

it

realises none already exist?

I would not recommend deleting any logfile. If SQLServer is running then

you

won't be able to as the file is locked. If you have detatched the

database or SQL Server is not running, you may have problems re-attaching the
database or connecting to it once SQL Server is running.

3) When trying to do 2) I get error message that file is in use, even

though

SQL Server is closed. Any suggestions?

I doubt if you have stopped the service, but in any case you should not be deleting it.
If all you want to do is move the file then check out
http://support.microsoft.com/default...b;EN-US;224071

If you want to shrink it check out

http://msdn.microsoft.com/library/de...us/architec/8_

ar_da2_1uzr.asp

4) Can anyone shed light on the error message 'Write on ... <my path
here>... failed, status = 112. BACKUP LOG is terminating abnormally'?

I get

this when trying to manually backup the Transaction Log 'All Tasks -

Backup'
I don't know what this error is, at a guess there is a lack of disk space. Maybe there is more information in the SQL Server log file or in the Event log. You will also get errors if you backup to a mapped network drive.

John

Thanks for any replies to my ignorance.

[TZoner]

John you've nearly solved my dilemma!!

As per your excellent like I was able to work out that running the 2
following lines will reduce the Transaction File back to it's size!!!
Hooray!!

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

However, because MyDb has it's Transaction File full, hence I can't execute
the above two lines. How do I run these commands when MyDb won't let me do
anything to it? To test that the two commands worked I created a dummy MyDb,
grew its Transaction File then executed the two lines and it worked 100%.

Thank you so much for your suggestions!





"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...

Your transction logs are held in the ldf file which sp_help would give e.g
for Northwind you would get something like:

Name FileId filename filegroup Size Maxsize Growth Usage
Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB Unlimited
10% log only

Northwind_log is the internal logical name for the Transaction Log file
located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a log file.

The log file will not automatically shrink on backup, see the link I posted in the last reply regarding how to shrink it.

John

"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

John, thanks for the valuable information.

Where is the 'Transaction Log' located? I can't find the answer sp_helpfile

or after endless searches on google.

Does a Transaction Log exist per database in SQL Server? When backup

occurs,

does these Transaction Log shrink?





"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...

See inline:
"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...
> 1) Can one find the location of the 'Transaction Log' at: <Hard
> Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
I am not sure what you are asking but sp_helpfile will give the names and locations of the files associated with a current database.
>
> 2) Is it safe to delete it, as SQL will create a new Transaction Log

when

it
> realises none already exist?
I would not recommend deleting any logfile. If SQLServer is running then

you

won't be able to as the file is locked. If you have detatched the database or SQL Server is not running, you may have problems re-attaching the
database or connecting to it once SQL Server is running.

>
> 3) When trying to do 2) I get error message that file is in use,

even though
> SQL Server is closed. Any suggestions?
I doubt if you have stopped the service, but in any case you should not be deleting it.
If all you want to do is move the file then check out
http://support.microsoft.com/default...b;EN-US;224071

If you want to shrink it check out

http://msdn.microsoft.com/library/de...us/architec/8_ ar_da2_1uzr.asp

>
> 4) Can anyone shed light on the error message 'Write on ... <my path
> here>... failed, status = 112. BACKUP LOG is terminating

abnormally'? I get
> this when trying to manually backup the Transaction Log 'All Tasks -
Backup'
I don't know what this error is, at a guess there is a lack of disk space. Maybe there is more information in the SQL Server log file or in the Event log. You will also get errors if you backup to a mapped network drive.

John
>
>
> Thanks for any replies to my ignorance.
>
>
>

[John Bell]

Hi

I am not sure what you mean by not work. As in the link previously posted,
if you last virtual log is in use the file will not shrink
"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

John you've nearly solved my dilemma!!

As per your excellent like I was able to work out that running the 2
following lines will reduce the Transaction File back to it's size!!!
Hooray!!

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

However, because MyDb has it's Transaction File full, hence I can't execute the above two lines. How do I run these commands when MyDb won't let me do
anything to it? To test that the two commands worked I created a dummy MyDb, grew its Transaction File then executed the two lines and it worked 100%.

Thank you so much for your suggestions!





"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...

Your transction logs are held in the ldf file which sp_help would give e.g

for Northwind you would get something like:

Name FileId filename filegroup Size Maxsize Growth Usage Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB Unlimited 10% log only

Northwind_log is the internal logical name for the Transaction Log file
located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a

log

file.

The log file will not automatically shrink on backup, see the link I

posted

in the last reply regarding how to shrink it.

John

"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

John, thanks for the valuable information.

Where is the 'Transaction Log' located? I can't find the answer

sp_helpfile

or after endless searches on google.

Does a Transaction Log exist per database in SQL Server? When backup

occurs,

does these Transaction Log shrink?





"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...
> See inline:
> "TZoner" <tz****@hotmail.com> wrote in message
> news:3e***********************@news.optusnet.com.a u...
> > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> I am not sure what you are asking but sp_helpfile will give the names

and

> locations of the files associated with a current database.
> >
> > 2) Is it safe to delete it, as SQL will create a new Transaction

Log when
> it
> > realises none already exist?
> I would not recommend deleting any logfile. If SQLServer is running

then you
> won't be able to as the file is locked. If you have detatched the

database

> or SQL Server is not running, you may have problems re-attaching the
> database or connecting to it once SQL Server is running.
>
> >
> > 3) When trying to do 2) I get error message that file is in use, even > though
> > SQL Server is closed. Any suggestions?
> I doubt if you have stopped the service, but in any case you should not

be

> deleting it.
> If all you want to do is move the file then check out
> http://support.microsoft.com/default...b;EN-US;224071
>
> If you want to shrink it check out
>

http://msdn.microsoft.com/library/de...us/architec/8_

> ar_da2_1uzr.asp
>
> >
> > 4) Can anyone shed light on the error message 'Write on ... <my path > > here>... failed, status = 112. BACKUP LOG is terminating

abnormally'?

I

> get
> > this when trying to manually backup the Transaction Log 'All Tasks - > Backup'
> I don't know what this error is, at a guess there is a lack of disk

space.

> Maybe there is more information in the SQL Server log file or in the

Event

> log. You will also get errors if you backup to a mapped network drive. >
> John
> >
> >
> > Thanks for any replies to my ignorance.
> >
> >
> >
>
>

[John Bell]

Hi

I am not sure what you mean by the transaction log being full. It would
normally wrap around or expand if you have the disk space.

What does DBCC SQLPERF(LOGSPACE) say?

Have you looked at the output from DBCC OPENTRAN ( 'database_name' )?

You may want to look at:
http://www.support.microsoft.com/?id=256650
http://support.microsoft.com/default...b;EN-US;110139
http://support.microsoft.com/default...kb;EN-US;62866

John

"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

John you've nearly solved my dilemma!!

As per your excellent like I was able to work out that running the 2
following lines will reduce the Transaction File back to it's size!!!
Hooray!!

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

However, because MyDb has it's Transaction File full, hence I can't execute the above two lines. How do I run these commands when MyDb won't let me do
anything to it? To test that the two commands worked I created a dummy MyDb, grew its Transaction File then executed the two lines and it worked 100%.

Thank you so much for your suggestions!





"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...

Your transction logs are held in the ldf file which sp_help would give e.g

for Northwind you would get something like:

Name FileId filename filegroup Size Maxsize Growth Usage Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB Unlimited 10% log only

Northwind_log is the internal logical name for the Transaction Log file
located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a

log

file.

The log file will not automatically shrink on backup, see the link I

posted

in the last reply regarding how to shrink it.

John

"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

John, thanks for the valuable information.

Where is the 'Transaction Log' located? I can't find the answer

sp_helpfile

or after endless searches on google.

Does a Transaction Log exist per database in SQL Server? When backup

occurs,

does these Transaction Log shrink?





"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...
> See inline:
> "TZoner" <tz****@hotmail.com> wrote in message
> news:3e***********************@news.optusnet.com.a u...
> > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> I am not sure what you are asking but sp_helpfile will give the names

and

> locations of the files associated with a current database.
> >
> > 2) Is it safe to delete it, as SQL will create a new Transaction

Log when
> it
> > realises none already exist?
> I would not recommend deleting any logfile. If SQLServer is running

then you
> won't be able to as the file is locked. If you have detatched the

database

> or SQL Server is not running, you may have problems re-attaching the
> database or connecting to it once SQL Server is running.
>
> >
> > 3) When trying to do 2) I get error message that file is in use, even > though
> > SQL Server is closed. Any suggestions?
> I doubt if you have stopped the service, but in any case you should not

be

> deleting it.
> If all you want to do is move the file then check out
> http://support.microsoft.com/default...b;EN-US;224071
>
> If you want to shrink it check out
>

http://msdn.microsoft.com/library/de...us/architec/8_

> ar_da2_1uzr.asp
>
> >
> > 4) Can anyone shed light on the error message 'Write on ... <my path > > here>... failed, status = 112. BACKUP LOG is terminating

abnormally'?

I

> get
> > this when trying to manually backup the Transaction Log 'All Tasks - > Backup'
> I don't know what this error is, at a guess there is a lack of disk

space.

> Maybe there is more information in the SQL Server log file or in the

Event

> log. You will also get errors if you backup to a mapped network drive. >
> John
> >
> >
> > Thanks for any replies to my ignorance.
> >
> >
> >
>
>

[John Bell]

Ooops!!!
"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...

Hi

I am not sure what you mean by not work. As in the link previously posted,
if you last virtual log is in use the file will not shrink
"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

John you've nearly solved my dilemma!!

As per your excellent like I was able to work out that running the 2
following lines will reduce the Transaction File back to it's size!!!
Hooray!!

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

However, because MyDb has it's Transaction File full, hence I can't

execute

the above two lines. How do I run these commands when MyDb won't let me do

anything to it? To test that the two commands worked I created a dummy

MyDb,

grew its Transaction File then executed the two lines and it worked 100%.
Thank you so much for your suggestions!





"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...

Your transction logs are held in the ldf file which sp_help would give e.g for Northwind you would get something like:

Name FileId filename filegroup Size Maxsize Growth Usage Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB Unlimited 10% log only

Northwind_log is the internal logical name for the Transaction Log file located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a log

file.

The log file will not automatically shrink on backup, see the link I

posted

in the last reply regarding how to shrink it.

John

"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...
> John, thanks for the valuable information.
>
> Where is the 'Transaction Log' located? I can't find the answer
sp_helpfile
> or after endless searches on google.
>
> Does a Transaction Log exist per database in SQL Server? When backup
occurs,
> does these Transaction Log shrink?
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> "John Bell" <jb************@hotmail.com> wrote in message
> news:3e***********************@reading.news.pipex. net...
> > See inline:
> > "TZoner" <tz****@hotmail.com> wrote in message
> > news:3e***********************@news.optusnet.com.a u...
> > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > Disk>\Program Files\Microsoft SQL

Server\MSSQL\Data\MyDb_Log.ldf? > > I am not sure what you are asking but sp_helpfile will give the names and
> > locations of the files associated with a current database.
> > >
> > > 2) Is it safe to delete it, as SQL will create a new Transaction Log > when
> > it
> > > realises none already exist?
> > I would not recommend deleting any logfile. If SQLServer is running then

> you
> > won't be able to as the file is locked. If you have detatched the
database
> > or SQL Server is not running, you may have problems re-attaching

the > > database or connecting to it once SQL Server is running.
> >
> > >
> > > 3) When trying to do 2) I get error message that file is in use,

even

> > though
> > > SQL Server is closed. Any suggestions?
> > I doubt if you have stopped the service, but in any case you should

not

be
> > deleting it.
> > If all you want to do is move the file then check out
> > http://support.microsoft.com/default...b;EN-US;224071
> >
> > If you want to shrink it check out
> >
>

http://msdn.microsoft.com/library/de...us/architec/8_ > > ar_da2_1uzr.asp
> >
> > >
> > > 4) Can anyone shed light on the error message 'Write on ... <my

path > > > here>... failed, status = 112. BACKUP LOG is terminating

abnormally'?

I
> > get
> > > this when trying to manually backup the Transaction Log 'All Tasks - > > Backup'
> > I don't know what this error is, at a guess there is a lack of disk space.
> > Maybe there is more information in the SQL Server log file or in the Event
> > log. You will also get errors if you backup to a mapped network drive. > >
> > John
> > >
> > >
> > > Thanks for any replies to my ignorance.
> > >
> > >
> > >
> >
> >
>
>

[TZoner]

John

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

If a Transaction File has grown from originally 1Mb to 100Mb, executing the
above 2 lines in a stored procedure will shrink that Transaction File to as
close to 1Mb as possible. This is exactly what I urgently need to do.
However, if the Transaction File is full, hence I'm prevented from running
the stored procedure, how do I execute these 2 lines of code against MyDb?
I've tried using the Query Analyser. Line 1 executes correctly. However line
2 causes the error "Server: Msg 8985, Level 16, State 1, Line 1. Could not
locate file 'MyDb_Log' in sysfiles". I've manually verified that MyDb_Log
does indeed exist in the sysfiles table of MyDb.

Any suggestions?



"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...

Hi

I am not sure what you mean by not work. As in the link previously posted,
if you last virtual log is in use the file will not shrink
"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

John you've nearly solved my dilemma!!

As per your excellent like I was able to work out that running the 2
following lines will reduce the Transaction File back to it's size!!!
Hooray!!

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

However, because MyDb has it's Transaction File full, hence I can't execute

the above two lines. How do I run these commands when MyDb won't let me do

anything to it? To test that the two commands worked I created a dummy

MyDb,

grew its Transaction File then executed the two lines and it worked 100%.
Thank you so much for your suggestions!





"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...

Your transction logs are held in the ldf file which sp_help would give e.g for Northwind you would get something like:

Name FileId filename filegroup Size Maxsize Growth Usage Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB Unlimited 10% log only

Northwind_log is the internal logical name for the Transaction Log file located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a log

file.

The log file will not automatically shrink on backup, see the link I

posted

in the last reply regarding how to shrink it.

John

"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...
> John, thanks for the valuable information.
>
> Where is the 'Transaction Log' located? I can't find the answer
sp_helpfile
> or after endless searches on google.
>
> Does a Transaction Log exist per database in SQL Server? When backup
occurs,
> does these Transaction Log shrink?
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> "John Bell" <jb************@hotmail.com> wrote in message
> news:3e***********************@reading.news.pipex. net...
> > See inline:
> > "TZoner" <tz****@hotmail.com> wrote in message
> > news:3e***********************@news.optusnet.com.a u...
> > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > Disk>\Program Files\Microsoft SQL

Server\MSSQL\Data\MyDb_Log.ldf? > > I am not sure what you are asking but sp_helpfile will give the names and
> > locations of the files associated with a current database.
> > >
> > > 2) Is it safe to delete it, as SQL will create a new Transaction Log

> when
> > it
> > > realises none already exist?
> > I would not recommend deleting any logfile. If SQLServer is

running then

> you
> > won't be able to as the file is locked. If you have detatched the
database
> > or SQL Server is not running, you may have problems re-attaching

the > > database or connecting to it once SQL Server is running.
> >
> > >
> > > 3) When trying to do 2) I get error message that file is in use,

even

> > though
> > > SQL Server is closed. Any suggestions?
> > I doubt if you have stopped the service, but in any case you should not

be
> > deleting it.
> > If all you want to do is move the file then check out
> > http://support.microsoft.com/default...b;EN-US;224071
> >
> > If you want to shrink it check out
> >
>

http://msdn.microsoft.com/library/de...us/architec/8_ > > ar_da2_1uzr.asp
> >
> > >
> > > 4) Can anyone shed light on the error message 'Write on ... <my

path > > > here>... failed, status = 112. BACKUP LOG is terminating

abnormally'?

I
> > get
> > > this when trying to manually backup the Transaction Log 'All Tasks - > > Backup'
> > I don't know what this error is, at a guess there is a lack of disk space.
> > Maybe there is more information in the SQL Server log file or in the Event
> > log. You will also get errors if you backup to a mapped network drive. > >
> > John
> > >
> > >
> > > Thanks for any replies to my ignorance.
> > >
> > >
> > >
> >
> >
>
>

[John Bell]

Hi

Try using the file id instead of the logical file name.

sp_helpfile will give your bother the logical name and the file id.

John

"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

John

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

If a Transaction File has grown from originally 1Mb to 100Mb, executing the above 2 lines in a stored procedure will shrink that Transaction File to as close to 1Mb as possible. This is exactly what I urgently need to do.
However, if the Transaction File is full, hence I'm prevented from running
the stored procedure, how do I execute these 2 lines of code against MyDb?
I've tried using the Query Analyser. Line 1 executes correctly. However line 2 causes the error "Server: Msg 8985, Level 16, State 1, Line 1. Could not
locate file 'MyDb_Log' in sysfiles". I've manually verified that MyDb_Log
does indeed exist in the sysfiles table of MyDb.

Any suggestions?



"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...

Hi

I am not sure what you mean by not work. As in the link previously posted,

if you last virtual log is in use the file will not shrink
"TZoner" <tz****@hotmail.com> wrote in message
news:3e***********************@news.optusnet.com.a u...

John you've nearly solved my dilemma!!

As per your excellent like I was able to work out that running the 2
following lines will reduce the Transaction File back to it's size!!!
Hooray!!

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

However, because MyDb has it's Transaction File full, hence I can't execute

the above two lines. How do I run these commands when MyDb won't let me do anything to it? To test that the two commands worked I created a dummy MyDb,

grew its Transaction File then executed the two lines and it worked 100%.
Thank you so much for your suggestions!





"John Bell" <jb************@hotmail.com> wrote in message
news:3e***********************@reading.news.pipex. net...
> Your transction logs are held in the ldf file which sp_help would

give

e.g

> for Northwind you would get something like:
>
> Name FileId filename filegroup Size Maxsize Growth

Usage

> Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008

KB > Unlimited 10% data only
> Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB

Unlimited

> 10% log only
>
> Northwind_log is the internal logical name for the Transaction Log

file > located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a

log
> file.
>
> The log file will not automatically shrink on backup, see the link I
posted
> in the last reply regarding how to shrink it.
>
> John
>
> "TZoner" <tz****@hotmail.com> wrote in message
> news:3e***********************@news.optusnet.com.a u...
> > John, thanks for the valuable information.
> >
> > Where is the 'Transaction Log' located? I can't find the answer
> sp_helpfile
> > or after endless searches on google.
> >
> > Does a Transaction Log exist per database in SQL Server? When

backup > occurs,
> > does these Transaction Log shrink?
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "John Bell" <jb************@hotmail.com> wrote in message
> > news:3e***********************@reading.news.pipex. net...
> > > See inline:
> > > "TZoner" <tz****@hotmail.com> wrote in message
> > > news:3e***********************@news.optusnet.com.a u...
> > > > 1) Can one find the location of the 'Transaction Log' at: <Hard > > > > Disk>\Program Files\Microsoft SQL

Server\MSSQL\Data\MyDb_Log.ldf? > > > I am not sure what you are asking but sp_helpfile will give the

names

> and
> > > locations of the files associated with a current database.
> > > >
> > > > 2) Is it safe to delete it, as SQL will create a new Transaction

Log

> > when
> > > it
> > > > realises none already exist?
> > > I would not recommend deleting any logfile. If SQLServer is running then
> > you
> > > won't be able to as the file is locked. If you have detatched

the > database
> > > or SQL Server is not running, you may have problems re-attaching

the > > > database or connecting to it once SQL Server is running.
> > >
> > > >
> > > > 3) When trying to do 2) I get error message that file is in use, even
> > > though
> > > > SQL Server is closed. Any suggestions?
> > > I doubt if you have stopped the service, but in any case you should not
> be
> > > deleting it.
> > > If all you want to do is move the file then check out
> > > http://support.microsoft.com/default...b;EN-US;224071
> > >
> > > If you want to shrink it check out
> > >
> >
>

http://msdn.microsoft.com/library/de...us/architec/8_

> > > ar_da2_1uzr.asp
> > >
> > > >
> > > > 4) Can anyone shed light on the error message 'Write on ...

<my path

> > > > here>... failed, status = 112. BACKUP LOG is terminating
abnormally'?
> I
> > > get
> > > > this when trying to manually backup the Transaction Log 'All

Tasks -

> > > Backup'
> > > I don't know what this error is, at a guess there is a lack of

disk > space.
> > > Maybe there is more information in the SQL Server log file or in the > Event
> > > log. You will also get errors if you backup to a mapped network

drive.

> > >
> > > John
> > > >
> > > >
> > > > Thanks for any replies to my ignorance.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[Scorpion]

Hi there, I've also got the error "Server: Msg 8985, Level 16, State 1, Line 1. Could not locate file 'MyDb_Log' in sysfiles" when trying to shrink one of my databases's log files. I think I know the cause of the problem. When you de-attach a database, rename the database file and then re-attach the database then this error message will apear. The solution, delete that current database and create a new one but NEVER EVER rename the file name of the database