Apr 16, 2014 Can somebody give me a SP I can run weekly to get rid of this thx The log file for database 'tempdb' is full. Back up the transaction log. Can somebody give me a SP I can run weekly to get rid of this thx The log file for database 'tempdb' is full. Back up the transaction log.
Create only ONE transaction log file. Wpc Files Pazzles Software. Even though you can create multiple transaction log files, you only need one.
SQL Server DOES not 'stripe' across multiple transaction log files. Instead, SQL Server uses the transaction log files sequentially. While this might sound bad - it's not. If you want to get better performance out of the transaction log, place it on faster disks and/or a more performant disk (RAID) configuration. You could place the transaction log on a RAID 0 array but you'll likely want fault tolerance as well as performance. Isolation, as per number 1 is a good start but if you also need increased capacity then using hardware based RAID to combine disks for both better performance and redundancy is your next choice.
Read more: Anup Forum Support If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
Balasach82 (4/14/2012) Thanks Gail for the immediate response. Now, I am clear that after dividing the files of tempdb, 1.
SQL restart not required 2. Divide the data (mdf) files, like, temp1.mdf, temp2.mdf I am also referring to the link provided by you. Thanks a lot. Are you actually seeing contention issues with tempdb?
Or, do you anticipate possibly having these issues in the near future? If not, there really isn't any reason to create multiple files. Jeffrey Williams Problems are opportunities brilliantly disguised as insurmountable obstacles. Balasach82 (4/14/2012) I do have SQL 2005. Plan is to divide tempdb into multiple files. I had some doubts, 1.
After dividing the tempdb into 'n' number of files, do we need to restart SQL? Can I create the additional files as MDF or LDF say for eg: temp1.mdf, temp2.mdf, tempdev.log or temp1.mdf, tempdev1.ldf, tempdev2.ldf I always restart the SQL server service when adding extra files on TempDB to kick the proportionate fill algorithm across the filegroup. ----------------------------------------------------------------------------------------------------------- 'Ya can't make an omelette without breaking just a few eggs';-). Balasach82 (4/15/2012) Perry/Gail, I had settled for Gail's view that SQL restart is not required after tempdb division. Perry recommends a restart for the algorithm (for which the tempdb is being divided) to work.
Now I am confused as to whether an restart is required or not. Could you please clarify my doubt. Is there any MS link/post for the same which I can refer to?
If all you're doing is adding extra files and not touching the primary file then sure it may be fine but even then I still prefer to restart the SQL server service to force the proportionate fill. 99% of the time though, your instance would have been running for a while and you will be adding files and re sizing the primary file, again I always restart the service to clean things up.
----------------------------------------------------------------------------------------------------------- 'Ya can't make an omelette without breaking just a few eggs';-). A restart is not required.
The additional files will be created when you run the alter database and SQL will start using them. If you want to restart, you can. If you're also changing the location of the files or reducing the size of the current file, then a restart is required. Gail Shaw Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci): Discussions on DB performance with occasional diversions into recoverability We walk in the dark places no others will enter We stand on the bridge and no one may pass.
You can't post new topics. You can't post topic replies. You can't post new polls. You can't post replies to polls. You can't edit your own topics. You can't delete your own topics. You can't edit other topics.
You can't delete other topics. You can't edit your own posts. You can't edit other posts. You can't delete your own posts. You can't delete other posts. You can't post events.
You can't edit your own events. You can't edit other events. You can't delete your own events. You can't delete other events. You can't send private messages.
You can't send emails. You can read topics. You can't vote in polls. You can't upload attachments. You can download attachments. You can't post HTML code.
You can't edit HTML code. You can't post IFCode. You can't post JavaScript.