|
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
(comments are locked)
|
|
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)
(comments are locked)
|
|
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!
(comments are locked)
|
|
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. 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 '12 at 04:40 PM
TimothyAWiseman
There is always an exception to the rule...
Jul 10 '12 at 08:34 PM
Blackhawk-17
(comments are locked)
|
|
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.
(comments are locked)
|
