Thanks for the reply.

That alert has been set for, say 100 mb of data file. During the time of creation of the alert itself, the size is over 100 mb....

Thanks very much Gail (and sorry for testing your/Perry patience).

"tempdb is cleared and reset to the specified sizes in the system catalogs"

this makes it clear to my soft brain.

Gail,

So, now my understanding it that, during reboot, tempdb does make use of model to an extent, but it does not get the size details. Those are got from...

Thanks Gail for the immediate clarification. I too was wondering that it does not make sense to modify model (which eventually would have an effect on the new user databases).

A...

Perry/Gail,

Had this strange doubt. After dividing and increasing the Data/log file size, If the SQL is rebooted later as part of regular maintenance, then tempdb would be created by making...

😀 Perry,

you are right. its 1024Mb, not 1000mb

Sorry, there were mistakes in my last post.

Gail,

Its 8 datafiles only... one mdf + 7new mdf files + one ldf

==> 1GB + 1GB * 7 + Log

Perry,

Size for all the...

tempdb data is currently at default 8mb, which will be increased to 1gb

log at 1mb which will be increased to 500mb

Finally it would be One MDF file + 8 new...

We are not thinking of moving the files to another drive; Its an addition of n number of new files to tempdb. So, I am not going to restart SQL...

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

Jeffrey,

Yes, I am facing contention issues regularly. Hence the decision to divide tempdb. We are also looking at queries to make it better.

Gail, your answers are very crisp and to the point. Thanks.

Now, I am clear that,

a. all connections to the DB must be closed (killed as the case maybe) and then...

I will look in BOL for the point2 (what would happen to the queries running at the time of point of change).

Can you please clarify on,

a. whether would it...

Thanks again Gail.

About point2, I was wondering what would happen to the live/running transactions when I change from RC to RCSI. Since, only committed data is read, should I not...

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