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

avatar image

180 8 9 14

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

4 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

avatar image

4.9k 4 5

(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

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(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

avatar image

10.8k 37 57 51

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

avatar image

12.1k 30 36 42

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jul 10, 2012 at 10:12 AM

Seen: 1228 times

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

Copyright 2018 Redgate Software. Privacy Policy