"TZoner" <tzoner@hotmail.com> wrote in message
news:3ef952a0$0$31277$afc38c87@news.optusnet.com.au...
> 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 -

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

"TZoner" <tzoner@hotmail.com> wrote in message
news:3ef9813b$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> news:3ef95a3b$0$18495$ed9e5944@reading.news.pipex.net...
> > See inline:
> > "TZoner" <tzoner@hotmail.com> wrote in message
> > news:3ef952a0$0$31277$afc38c87@news.optusnet.com.au...
> > > 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.aspx?scid=kb;EN-US;224071
> >
> > If you want to shrink it check out
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-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" <tzoner@hotmail.com> wrote in message
news:3ef9a575$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> news:3ef98e4f$0$10627$ed9e5944@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" <tzoner@hotmail.com> wrote in message
> > news:3ef9813b$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> > > news:3ef95a3b$0$18495$ed9e5944@reading.news.pipex.net...
> > > > See inline:
> > > > "TZoner" <tzoner@hotmail.com> wrote in message
> > > > news:3ef952a0$0$31277$afc38c87@news.optusnet.com.au...
> > > > > 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.aspx?scid=kb;EN-US;224071
> > > >
> > > > If you want to shrink it check out
> > > >
> > >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-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" <tzoner@hotmail.com> wrote in message
news:3ef9a575$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> news:3ef98e4f$0$10627$ed9e5944@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" <tzoner@hotmail.com> wrote in message
> > news:3ef9813b$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> > > news:3ef95a3b$0$18495$ed9e5944@reading.news.pipex.net...
> > > > See inline:
> > > > "TZoner" <tzoner@hotmail.com> wrote in message
> > > > news:3ef952a0$0$31277$afc38c87@news.optusnet.com.au...
> > > > > 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.aspx?scid=kb;EN-US;224071
> > > >
> > > > If you want to shrink it check out
> > > >
> > >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-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" <jbellnewsposts@hotmail.com> wrote in message
news:3ef9abc1$0$10629$ed9e5944@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" <tzoner@hotmail.com> wrote in message
> news:3ef9a575$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> > news:3ef98e4f$0$10627$ed9e5944@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" <tzoner@hotmail.com> wrote in message
> > > news:3ef9813b$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> > > > news:3ef95a3b$0$18495$ed9e5944@reading.news.pipex.net...
> > > > > See inline:
> > > > > "TZoner" <tzoner@hotmail.com> wrote in message
> > > > > news:3ef952a0$0$31277$afc38c87@news.optusnet.com.au...
> > > > > > 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.aspx?scid=kb;EN-US;224071
> > > > >
> > > > > If you want to shrink it check out
> > > > >
> > > >
> > >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-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" <tzoner@hotmail.com> wrote in message
news:3ef9aec4$0$31274$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> news:3ef9abc1$0$10629$ed9e5944@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" <tzoner@hotmail.com> wrote in message
> > news:3ef9a575$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> > > news:3ef98e4f$0$10627$ed9e5944@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" <tzoner@hotmail.com> wrote in message
> > > > news:3ef9813b$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> > > > > news:3ef95a3b$0$18495$ed9e5944@reading.news.pipex.net...
> > > > > > See inline:
> > > > > > "TZoner" <tzoner@hotmail.com> wrote in message
> > > > > > news:3ef952a0$0$31277$afc38c87@news.optusnet.com.au...
> > > > > > > 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.aspx?scid=kb;EN-US;224071
> > > > > >
> > > > > > If you want to shrink it check out
> > > > > >
> > > > >
> > > >
> > >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-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" <jbellnewsposts@hotmail.com> wrote in message
news:3ef95a3b$0$18495$ed9e5944@reading.news.pipex.net...
> See inline:
> "TZoner" <tzoner@hotmail.com> wrote in message
> news:3ef952a0$0$31277$afc38c87@news.optusnet.com.au...
> > 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.aspx?scid=kb;EN-US;224071
>
> If you want to shrink it check out
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-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" <jbellnewsposts@hotmail.com> wrote in message
news:3ef98e4f$0$10627$ed9e5944@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" <tzoner@hotmail.com> wrote in message
> news:3ef9813b$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> > news:3ef95a3b$0$18495$ed9e5944@reading.news.pipex.net...
> > > See inline:
> > > "TZoner" <tzoner@hotmail.com> wrote in message
> > > news:3ef952a0$0$31277$afc38c87@news.optusnet.com.au...
> > > > 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.aspx?scid=kb;EN-US;224071
> > >
> > > If you want to shrink it check out
> > >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-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" <jbellnewsposts@hotmail.com> wrote in message
news:3ef9abc1$0$10629$ed9e5944@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" <tzoner@hotmail.com> wrote in message
> news:3ef9a575$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> > news:3ef98e4f$0$10627$ed9e5944@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" <tzoner@hotmail.com> wrote in message
> > > news:3ef9813b$0$31280$afc38c87@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" <jbellnewsposts@hotmail.com> wrote in message
> > > > news:3ef95a3b$0$18495$ed9e5944@reading.news.pipex.net...
> > > > > See inline:
> > > > > "TZoner" <tzoner@hotmail.com> wrote in message
> > > > > news:3ef952a0$0$31277$afc38c87@news.optusnet.com.au...
> > > > > > 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.aspx?scid=kb;EN-US;224071
> > > > >
> > > > > If you want to shrink it check out
> > > > >
> > > >
> > >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-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.
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>