Hi Lads,

Is there any problem in deleting a transaction log file for tempdb database?
I have triend fisrt in my own machine and it works fine, but prior to doing
so in UAT or production I would like to know if there is any potential data
loss and if I should do it whenever the box is in maintenance mode (hardly
ever, haha).

Just to make things clear, there are to tlog files in the tempdb, and as
there is no gain having them I will like to get rid of one of them. About the
data files I dont care as they seem properly set.

Thanks in advance!

TempDb 'should' manage the TLog file just fine. But if, when the server is
down, the tempdb database and/or TLog file is removed, the server will just
create a new one when it starts up.

There will no data loss. TempDb only stores data for a user session, and
when those sessions are finished, the data is discarded.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


"Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
news:914CD8CC-3910-4E8A-AEC2-64CE35333837@microsoft.com...
> Hi Lads,
>
> Is there any problem in deleting a transaction log file for tempdb
> database?
> I have triend fisrt in my own machine and it works fine, but prior to
> doing
> so in UAT or production I would like to know if there is any potential
> data
> loss and if I should do it whenever the box is in maintenance mode (hardly
> ever, haha).
>
> Just to make things clear, there are to tlog files in the tempdb, and as
> there is no gain having them I will like to get rid of one of them. About
> the
> data files I dont care as they seem properly set.
>
> Thanks in advance!

Thanks Arnie for your quick response, however I dont think I got it clear jet.

The situatios is as follows. We have a 4 CPU machine, previous DBA detected
tempdb contemption and created 4 tempdb data files (fine by me) along with 4
tempdb log files. As there is no gain in having 4 tempdb tlog files, I would
like to get rid of three of them. The system hardly ever gets rebooted, so I
was wandering if there was any potiential data loss if I just went online and
deleted those files while the system is running.

Thanks,

Rufiano



"Arnie Rowland" wrote:

> TempDb 'should' manage the TLog file just fine. But if, when the server is
> down, the tempdb database and/or TLog file is removed, the server will just
> create a new one when it starts up.
>
> There will no data loss. TempDb only stores data for a user session, and
> when those sessions are finished, the data is discarded.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
> news:914CD8CC-3910-4E8A-AEC2-64CE35333837@microsoft.com...
> > Hi Lads,
> >
> > Is there any problem in deleting a transaction log file for tempdb
> > database?
> > I have triend fisrt in my own machine and it works fine, but prior to
> > doing
> > so in UAT or production I would like to know if there is any potential
> > data
> > loss and if I should do it whenever the box is in maintenance mode (hardly
> > ever, haha).
> >
> > Just to make things clear, there are to tlog files in the tempdb, and as
> > there is no gain having them I will like to get rid of one of them. About
> > the
> > data files I dont care as they seem properly set.
> >
> > Thanks in advance!
>
>
>

You can get rid of the files via ALTER DATABASE. You won't be able to
delete them with SQL Server up until you do that.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
news:C06080A0-51AD-416B-BA95-24EF2198073A@microsoft.com...
Thanks Arnie for your quick response, however I dont think I got it clear
jet.

The situatios is as follows. We have a 4 CPU machine, previous DBA detected
tempdb contemption and created 4 tempdb data files (fine by me) along with 4
tempdb log files. As there is no gain in having 4 tempdb tlog files, I would
like to get rid of three of them. The system hardly ever gets rebooted, so I
was wandering if there was any potiential data loss if I just went online
and
deleted those files while the system is running.

Thanks,

Rufiano



"Arnie Rowland" wrote:

> TempDb 'should' manage the TLog file just fine. But if, when the server is
> down, the tempdb database and/or TLog file is removed, the server will
> just
> create a new one when it starts up.
>
> There will no data loss. TempDb only stores data for a user session, and
> when those sessions are finished, the data is discarded.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
> news:914CD8CC-3910-4E8A-AEC2-64CE35333837@microsoft.com...
> > Hi Lads,
> >
> > Is there any problem in deleting a transaction log file for tempdb
> > database?
> > I have triend fisrt in my own machine and it works fine, but prior to
> > doing
> > so in UAT or production I would like to know if there is any potential
> > data
> > loss and if I should do it whenever the box is in maintenance mode
> > (hardly
> > ever, haha).
> >
> > Just to make things clear, there are to tlog files in the tempdb, and as
> > there is no gain having them I will like to get rid of one of them.
> > About
> > the
> > data files I dont care as they seem properly set.
> >
> > Thanks in advance!
>
>
>

Hi Tom,

dont know if it is because I am using sql 2005, but deleting an extra tlog
file is pretty strighforwards. The question for me is not "if i can", as I am
indeeed capable of doing that, but insted "if I should" delete a tempdb tlog
while the system is online.

Thanks

"Tom Moreau" wrote:

> You can get rid of the files via ALTER DATABASE. You won't be able to
> delete them with SQL Server up until you do that.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
> news:C06080A0-51AD-416B-BA95-24EF2198073A@microsoft.com...
> Thanks Arnie for your quick response, however I dont think I got it clear
> jet.
>
> The situatios is as follows. We have a 4 CPU machine, previous DBA detected
> tempdb contemption and created 4 tempdb data files (fine by me) along with 4
> tempdb log files. As there is no gain in having 4 tempdb tlog files, I would
> like to get rid of three of them. The system hardly ever gets rebooted, so I
> was wandering if there was any potiential data loss if I just went online
> and
> deleted those files while the system is running.
>
> Thanks,
>
> Rufiano
>
>
>
> "Arnie Rowland" wrote:
>
> > TempDb 'should' manage the TLog file just fine. But if, when the server is
> > down, the tempdb database and/or TLog file is removed, the server will
> > just
> > create a new one when it starts up.
> >
> > There will no data loss. TempDb only stores data for a user session, and
> > when those sessions are finished, the data is discarded.
> >
> > --
> > Arnie Rowland, Ph.D.
> > Westwood Consulting, Inc
> >
> > Most good judgment comes from experience.
> > Most experience comes from bad judgment.
> > - Anonymous
> >
> >
> > "Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
> > news:914CD8CC-3910-4E8A-AEC2-64CE35333837@microsoft.com...
> > > Hi Lads,
> > >
> > > Is there any problem in deleting a transaction log file for tempdb
> > > database?
> > > I have triend fisrt in my own machine and it works fine, but prior to
> > > doing
> > > so in UAT or production I would like to know if there is any potential
> > > data
> > > loss and if I should do it whenever the box is in maintenance mode
> > > (hardly
> > > ever, haha).
> > >
> > > Just to make things clear, there are to tlog files in the tempdb, and as
> > > there is no gain having them I will like to get rid of one of them.
> > > About
> > > the
> > > data files I dont care as they seem properly set.
> > >
> > > Thanks in advance!
> >
> >
> >
>
>

Yep, no problem. You'll have to do a DBCC SHRINKFILE with EMPTYFILE first.
Just make sure you have enough room in the remaining file(s).

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
news:3D98562F-D865-44F6-8F6A-4EE30D1F7EA9@microsoft.com...
Hi Tom,

dont know if it is because I am using sql 2005, but deleting an extra tlog
file is pretty strighforwards. The question for me is not "if i can", as I
am
indeeed capable of doing that, but insted "if I should" delete a tempdb tlog
while the system is online.

Thanks

"Tom Moreau" wrote:

> You can get rid of the files via ALTER DATABASE. You won't be able to
> delete them with SQL Server up until you do that.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
> news:C06080A0-51AD-416B-BA95-24EF2198073A@microsoft.com...
> Thanks Arnie for your quick response, however I dont think I got it clear
> jet.
>
> The situatios is as follows. We have a 4 CPU machine, previous DBA
> detected
> tempdb contemption and created 4 tempdb data files (fine by me) along with
> 4
> tempdb log files. As there is no gain in having 4 tempdb tlog files, I
> would
> like to get rid of three of them. The system hardly ever gets rebooted, so
> I
> was wandering if there was any potiential data loss if I just went online
> and
> deleted those files while the system is running.
>
> Thanks,
>
> Rufiano
>
>
>
> "Arnie Rowland" wrote:
>
> > TempDb 'should' manage the TLog file just fine. But if, when the server
> > is
> > down, the tempdb database and/or TLog file is removed, the server will
> > just
> > create a new one when it starts up.
> >
> > There will no data loss. TempDb only stores data for a user session, and
> > when those sessions are finished, the data is discarded.
> >
> > --
> > Arnie Rowland, Ph.D.
> > Westwood Consulting, Inc
> >
> > Most good judgment comes from experience.
> > Most experience comes from bad judgment.
> > - Anonymous
> >
> >
> > "Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
> > news:914CD8CC-3910-4E8A-AEC2-64CE35333837@microsoft.com...
> > > Hi Lads,
> > >
> > > Is there any problem in deleting a transaction log file for tempdb
> > > database?
> > > I have triend fisrt in my own machine and it works fine, but prior to
> > > doing
> > > so in UAT or production I would like to know if there is any potential
> > > data
> > > loss and if I should do it whenever the box is in maintenance mode
> > > (hardly
> > > ever, haha).
> > >
> > > Just to make things clear, there are to tlog files in the tempdb, and
> > > as
> > > there is no gain having them I will like to get rid of one of them.
> > > About
> > > the
> > > data files I dont care as they seem properly set.
> > >
> > > Thanks in advance!
> >
> >
> >
>
>

ohhh, thats great Tom, thanks, I had the impression i was missing something
and I didnt recall that, I am sure that will work great.

Thanks again

"Tom Moreau" wrote:

> Yep, no problem. You'll have to do a DBCC SHRINKFILE with EMPTYFILE first.
> Just make sure you have enough room in the remaining file(s).
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
> news:3D98562F-D865-44F6-8F6A-4EE30D1F7EA9@microsoft.com...
> Hi Tom,
>
> dont know if it is because I am using sql 2005, but deleting an extra tlog
> file is pretty strighforwards. The question for me is not "if i can", as I
> am
> indeeed capable of doing that, but insted "if I should" delete a tempdb tlog
> while the system is online.
>
> Thanks
>
> "Tom Moreau" wrote:
>
> > You can get rid of the files via ALTER DATABASE. You won't be able to
> > delete them with SQL Server up until you do that.
> >
> > --
> > Tom
> >
> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > ..
> > "Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
> > news:C06080A0-51AD-416B-BA95-24EF2198073A@microsoft.com...
> > Thanks Arnie for your quick response, however I dont think I got it clear
> > jet.
> >
> > The situatios is as follows. We have a 4 CPU machine, previous DBA
> > detected
> > tempdb contemption and created 4 tempdb data files (fine by me) along with
> > 4
> > tempdb log files. As there is no gain in having 4 tempdb tlog files, I
> > would
> > like to get rid of three of them. The system hardly ever gets rebooted, so
> > I
> > was wandering if there was any potiential data loss if I just went online
> > and
> > deleted those files while the system is running.
> >
> > Thanks,
> >
> > Rufiano
> >
> >
> >
> > "Arnie Rowland" wrote:
> >
> > > TempDb 'should' manage the TLog file just fine. But if, when the server
> > > is
> > > down, the tempdb database and/or TLog file is removed, the server will
> > > just
> > > create a new one when it starts up.
> > >
> > > There will no data loss. TempDb only stores data for a user session, and
> > > when those sessions are finished, the data is discarded.
> > >
> > > --
> > > Arnie Rowland, Ph.D.
> > > Westwood Consulting, Inc
> > >
> > > Most good judgment comes from experience.
> > > Most experience comes from bad judgment.
> > > - Anonymous
> > >
> > >
> > > "Rufiano" <Rufiano@discussions.microsoft.com> wrote in message
> > > news:914CD8CC-3910-4E8A-AEC2-64CE35333837@microsoft.com...
> > > > Hi Lads,
> > > >
> > > > Is there any problem in deleting a transaction log file for tempdb
> > > > database?
> > > > I have triend fisrt in my own machine and it works fine, but prior to
> > > > doing
> > > > so in UAT or production I would like to know if there is any potential
> > > > data
> > > > loss and if I should do it whenever the box is in maintenance mode
> > > > (hardly
> > > > ever, haha).
> > > >
> > > > Just to make things clear, there are to tlog files in the tempdb, and
> > > > as
> > > > there is no gain having them I will like to get rid of one of them.
> > > > About
> > > > the
> > > > data files I dont care as they seem properly set.
> > > >
> > > > Thanks in advance!
> > >
> > >
> > >
> >
> >
>
>