473,587 Members | 2,263 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

4 Transaction Log questions

1) Can one find the location of the 'Transaction Log' at: <Hard
Disk>\Program Files\Microsoft SQL Server\MSSQL\Da ta\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.

Jul 20 '05 #1
10 13329
See inline:
"TZoner" <tz****@hotmail .com> wrote in message
news:3e******** *************** @news.optusnet. com.au...
1) Can one find the location of the 'Transaction Log' at: <Hard
Disk>\Program Files\Microsoft SQL Server\MSSQL\Da ta\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.

Jul 20 '05 #2
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.p ipex.net...
See inline:
"TZoner" <tz****@hotmail .com> wrote in message
news:3e******** *************** @news.optusnet. com.au...
1) Can one find the location of the 'Transaction Log' at: <Hard
Disk>\Program Files\Microsoft SQL Server\MSSQL\Da ta\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.


Jul 20 '05 #3
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\n orthwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\n orthwnd.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\n orthwnd.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.au...
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.p ipex.net...
See inline:
"TZoner" <tz****@hotmail .com> wrote in message
news:3e******** *************** @news.optusnet. com.au...
1) Can one find the location of the 'Transaction Log' at: <Hard
Disk>\Program Files\Microsoft SQL Server\MSSQL\Da ta\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.



Jul 20 '05 #4
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.p ipex.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\n orthwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\n orthwnd.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\n orthwnd.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.au...
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.p ipex.net...
See inline:
"TZoner" <tz****@hotmail .com> wrote in message
news:3e******** *************** @news.optusnet. com.au...
> 1) Can one find the location of the 'Transaction Log' at: <Hard
> Disk>\Program Files\Microsoft SQL Server\MSSQL\Da ta\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.
>
>
>



Jul 20 '05 #5
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.au...
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.p ipex.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\n orthwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\n orthwnd.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\n orthwnd.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.au...
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.p ipex.net...
> See inline:
> "TZoner" <tz****@hotmail .com> wrote in message
> news:3e******** *************** @news.optusnet. com.au...
> > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > Disk>\Program Files\Microsoft SQL Server\MSSQL\Da ta\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.
> >
> >
> >
>
>



Jul 20 '05 #6
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(LOGSPAC E) 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.au...
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.p ipex.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\n orthwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\n orthwnd.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\n orthwnd.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.au...
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.p ipex.net...
> See inline:
> "TZoner" <tz****@hotmail .com> wrote in message
> news:3e******** *************** @news.optusnet. com.au...
> > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > Disk>\Program Files\Microsoft SQL Server\MSSQL\Da ta\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.
> >
> >
> >
>
>



Jul 20 '05 #7
Ooops!!!
"John Bell" <jb************ @hotmail.com> wrote in message
news:3e******** *************** @reading.news.p ipex.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.au...
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.p ipex.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\n orthwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\n orthwnd.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\n orthwnd.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.au...
> 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.p ipex.net...
> > See inline:
> > "TZoner" <tz****@hotmail .com> wrote in message
> > news:3e******** *************** @news.optusnet. com.au...
> > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > Disk>\Program Files\Microsoft SQL
Server\MSSQL\Da ta\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.
> > >
> > >
> > >
> >
> >
>
>



Jul 20 '05 #8
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.p ipex.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.au...
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.p ipex.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\n orthwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\n orthwnd.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\n orthwnd.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.au...
> 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.p ipex.net...
> > See inline:
> > "TZoner" <tz****@hotmail .com> wrote in message
> > news:3e******** *************** @news.optusnet. com.au...
> > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > Disk>\Program Files\Microsoft SQL
Server\MSSQL\Da ta\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.
> > >
> > >
> > >
> >
> >
>
>



Jul 20 '05 #9
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.au...
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.p ipex.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.au...
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.p ipex.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\n orthwnd.mdf PRIMARY 3008
KB > Unlimited 10% data only
> Northwind_log 2 E:\MSSQL\DATA\n orthwnd.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\n orthwnd.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.au...
> > 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.p ipex.net...
> > > See inline:
> > > "TZoner" <tz****@hotmail .com> wrote in message
> > > news:3e******** *************** @news.optusnet. com.au...
> > > > 1) Can one find the location of the 'Transaction Log' at: <Hard > > > > Disk>\Program Files\Microsoft SQL
Server\MSSQL\Da ta\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.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
27492
by: Ollie Riches | last post by:
"Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction." I am recieving the above error when attempt to access a remote sql server database 2000 (sp3a) from ADO.Net. The database is on a windows 2003 machine and the ado.net code (web services) are on another winsdows 2003 machine in the...
2
3884
by: John Lee | last post by:
Hi, I have few questions related to .NET 2.0 TransactionScope class behavior: 1. Check Transaction.Current.TransactionInformation.DistributedIdentifier to identify if distributed transaction is used - is it accurate way? 2. I have the following code blocks - In code block 1, the first check the DistributedIdentifier is ALL 0s so it
3
1973
by: Eitan | last post by:
Hello, I have run a long transaction on the DB (sql server) For some long transaction I have got the following message : What can I do in order to run it properly ? Error Type:
4
11275
by: Nick Barr | last post by:
Hi, I am trying to gather stats about how many times a resource in our web app is viewed, i.e. just a COUNT. There are potentially millions of resources within the system. I thought of two methods: 1. An extra column in the resource table which contains a count. a. Each time a resource is viewed an UPDATE statement is run.
2
8116
by: kanda | last post by:
Hello. I am developing the application (VBA&ODBC, to be exact) which periodically calls the stored procedures in the IBM DB2. A few of the procedures require executing with isolation level RR ( ANSI "SERIALIZABLE" ), not the default; default is CS (ANSI "Read Committed")). The procedure language is SQL. According to the documentation, I...
2
2224
by: Ryan | last post by:
I've re-written a stored procedure and when I post the following code into the existing SP in EM, is saves OK. However, when I re-edit the SP, the last line 'Commit Transaction' has been removed. I cannot save the remainder of the SP as it throws error 208 (Invalid Object name #Max) about two of the temp tables I use when I post the entire...
3
2769
by: Ecohouse | last post by:
I was discussing a project today and a question came up about using a transaction table. I need to recreate an input form with many different types of questions. The following is one type of question: 3) How would you characterize the racial/ethnic composition of the individuals served by your program? What is the percent for each? ...
16
3813
by: DataPro | last post by:
New to Sql Server, running SQL Server 2000. Our transaction log file backups occasionally fail as the size of the transaction log gets really huge. We'd like to schedule additional transaction log backups. Does that require an exclusive on the database or can the db be used during a transaction log backup? Also, does switching to a bulk...
3
7075
by: Kurt | last post by:
Hello I have questions about how works transaction log et the database tempdb in SQL Server and I hop you could help me - Is it possible to reduce the size of the transaction log fil during an execution ? Indeed, I have a script inserting a very large quantity of data (many Go) and during that process my transaction log file use all the...
0
7915
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7843
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8205
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8220
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6619
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5392
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3872
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2347
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1452
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.