x

tempdb log files

Hi All, Suppose i have two log files in tempdb. 1st file has filled completely and second log file in use. so if i delete second log file then what will happen about the data in second log file.where it will go.

means my first log file completely full i.e. 0% space was available . i created second log file in another disk. then second file start using. after some time first file got free space then i delete second file so i want to know about that data was in second file.

Thanks Sumit
more ▼

asked Jul 10, 2012 at 10:12 AM in Default

SumitRana gravatar image

SumitRana
180 6 8 11

show all comments (comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
Just to add, if you attempt to delete that file you might well end up with corruption of TempDB requiring you to have to perform a restart, so I wouldn't recommend it (likewise attempting to shrink TempDB or the log file could potentially lead to tempdb corruption)
more ▼

answered Jul 10, 2012 at 03:46 PM

SirSQL gravatar image

SirSQL
4.8k 1 3

(comments are locked)
10|1200 characters needed characters left

I can't imagine SQL Server would let you delete a tempdb log file in use.

If you managed it, I can't imagine what state your database would be in!
more ▼

answered Jul 10, 2012 at 10:22 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left
Having multiple logs are not recommended practice, because writes to the log files are sequential and SQL Server will not use proportional fill method while writing into log files as it does for data files. SQL Server will continue to use the first log fill until it get full and move to the second one. I don't see any performance improvement by having multiple log files, instead you will end up in maintenance difficulty.
more ▼

answered Jul 10, 2012 at 11:41 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

While I do agree fully, I have run into situations where it made sense to have a second log file. (They involved servers that had internal storage instead of being attached to a SAN and had been dramatically under-provisioned for the use they grew into over the years.)
Jul 10, 2012 at 04:40 PM TimothyAWiseman
There is always an exception to the rule...
Jul 10, 2012 at 08:34 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

IF you can delete the 2nd file any data in it will be lost.

I'm rather surprised that you managed to fill the transaction log of temp dB. It must be on a very full disk.

As SQL Server always rebuilds temp dB on restart it most likely won't be a big issue. I imagine though that any in-flight transactions will probably fail upon deletion.

Alter temp dB and ensure that it's transaction log is on a disk sized for its usage and follow @Cyborg 's advice and go with a single log file.
more ▼

answered Jul 10, 2012 at 02:07 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834

asked: Jul 10, 2012 at 10:12 AM

Seen: 972 times

Last Updated: Jul 11, 2012 at 04:50 AM