April 14, 2012 at 3:11 am
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?
2. 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
April 14, 2012 at 3:48 am
1) no
2) the multiple files applies to data files only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 14, 2012 at 4:38 am
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.
April 14, 2012 at 7:35 am
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 14, 2012 at 8:07 am
Jeffrey,
Yes, I am facing contention issues regularly. Hence the decision to divide tempdb. We are also looking at queries to make it better.
April 15, 2012 at 3:01 pm
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?
2. 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" 😉
April 15, 2012 at 10:49 pm
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?
April 16, 2012 at 12:32 am
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" 😉
April 16, 2012 at 3:08 am
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)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2012 at 4:40 am
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 after file addition to tempdb.
Only If I have sufficient free time to bounce sql, then I will do the same.
Perry/Gail, Thanks for the clarification.
April 16, 2012 at 5:00 am
Cool, no problem.
Like I said though, in my experience i'm dealing with an instance of SQL Server where i am not only adding files but also reconfiguring the primary file, very rarely do i just add extra files with the same properties as the primary, as it's incorrect in the first place 😉
A restart is always my preferred option and doesn't take long at all to cycle the SQL Server service.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2012 at 6:37 am
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 mdf files + one log file as given below:
One MDF file
+
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, SIZE = 200, FILENAME = 'D:\\...\tempdb2.mdf');
GO
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, SIZE = 200, FILENAME = 'D:\\...\tempdb8.mdf');
GO
.
.
.
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev8, SIZE = 200, FILENAME = 'D:\\...\tempdb8.mdf');
GO
+
one LDF file
Do you see any issues with the above steps?
April 16, 2012 at 7:10 am
9 files? That's an odd number.
You have 9, 18, 27 or 36 (or some other multiple of 9) processor cores?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2012 at 7:17 am
balasach82 (4/16/2012)
tempdb data is currently at default 8mb, which will be increased to 1gb..........................ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, SIZE = 200, FILENAME = 'D:\\...\tempdb2.mdf');
GO
.................
......................one LDF file
Do you see any issues with the above steps?
Apart from the fact it looks like your files will have different sizes and so destroy the proportionate fill, no :Whistling:
Set all files to the same initial size and if allowing autogrowth set the same max size and growth rates 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2012 at 7:51 am
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 new files would be set to 1000 (mb), so all the 8 mdf files (one old (but size increased) + 7 new files) would be 1GB.
Yes, we would be giving unlimited growth with the equal increment for all the files.
adding further, query for increasing the existing data file
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, SIZE = 1000MB, FILENAME = '\\...\tempdb.mdf');
GO
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply