Temdb & Transaction Log Backups
-
Hi
we recently had a problem where the tempdblogs could not grow because of lack
of free space on the disk.What also happened was the transaction log backuo
also failed and on clearing the tempdb database after restarting the server
the t-logs backup worked fine.
Is there a relation between the transaction log backups and the tempdb.
Does SQL 2000 uses tempdb for creating t-log backups.
thanks
-
There is not a straight relation between ldf files and tempdb; for keeping your log files under control you need at least to keep your user database in full recovery mode and take transaction log backups; the interval of backing your transaction log backup depends on the amount of data pushed into your database daily - this behaviour is specific to ETL application (batch inserts/updates), however you did not specify the type of your transactions. About your tempdb, this grows because of the type of queries you're running: if having temporary tables, aggregate functions etc (see BOL) your tempdb will grow large. For this case I would create as many tempdb files as the number of CPUs available on the machine. If you've got only one CPU then create a second Tempdb (ndf) file on other physical disk - just in case when running out of space; just shrink it when goes over.
Remember to always whatch your free disk size; MS recommens at least 10%.
Good luck
-
That's fascinating, this concept of having as many CPU's as TempDB datafiles. I had always just contented myself with a single TempDb.mdf at 100Mb, a TempDb.ldf at 40Mb, and both spliced across multiple disks, or at least separate from the data.
Have you found performance benefits from TempDb.ndf's??
-
Heh... TempDb.mdf of 100Mb??? We start ours at 12 gig on boot up with growth settings of 500Mb. And, yes, we've found a huge performance benefit in having it that large and split across several (8, I think which is same number as CPU's) disks.
One fault I've seen some folks do is to change the recovery mode of TempDB... is needs to be set to SIMPLE (the default). Never set it to anything else...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Please check this http://support.microsoft.com/default.aspx/kb/834846
And yes, the performance improvement is visible.
-
not got an hour and a half for that presentation at the moment, is there a performance improvement spreading the tempdb data files across multiple files on the same drive?
---------------------------------------------------------------------
-
I suspect only nominal. Probably not enough of a saving to warrant even the few seconds spent clicking or typing the files into existence.
Jeff - 12 GIGABYTES?? GEEEEEEEYAAAZUS man, what on earth are you administering, Google itself?!???!?!?
Jaybee.
-
Heh
No... it is a tera-byte database with a lot going on, though. Web users, billing, invoicing, new customers, Dunning runs, 4 million + new CDR's per day, etc, etc. Administering Google wouldn't be as much "fun". 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Dunning runs and CDR's???
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply