September 23, 2008 at 7:23 am
Hi All,
I have a SQL 2000 Production server whose tempdb resides on a drive (approx space54 GB).I created 3 seperate ndf files to support my primary mdf file.
I used the following query command to create the secondary files
Alter database tempdb add file (name =tempdb1, filename = 'T:\sqldata\tempdb1.ndf',size=8MB,filegrowth=10%)
go
Alter database tempdb add file (name =tempdb2, filename = 'T:\sqldata\tempdb2.ndf',size=8MB,filegrowth=10%)
go
Alter database tempdb add file (name =tempdb3, filename = 'T:\sqldata\tempdb3.ndf',size=8MB,filegrowth=10%)
go
however after creating them and restarting the tempdb and when i execute sp_helpdb i can find that the filegrowth of 10% is not getting reflected.
The primary (mdf ) data file alone rasies to 53 GB of the 54 GB drive whereas the created ndf files just have aroungd Kb's of data.
Please help me to fix this issue or offer suggestions as am tired of restarting my SQL agent which is also not wise for a production machine.
Is this a SQL 2000 bug or something ?
Regards,
Eben
September 23, 2008 at 8:53 am
filegrowth takes effect when the database runs out of space, not when the server is started.
September 24, 2008 at 1:36 am
Ok....solution to my current problem please,..:blink:
September 24, 2008 at 7:15 am
The solution is simple. Uncheck Auto growth on the mdf. If not, specify the limit to say 20 Gb on MDF
The logic behind this is SQL server by default increases the space on other files if the existing file cannot grow. If all the files are enabled with auto growth then the on which has more space limit grows.
Hope this helps...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 25, 2008 at 3:04 am
To add to The_SQL_DBA... If you have multiple file in a filegroup that are all the same size and all allow autogrow, then growth is likely to be concentrated into a single file.
SQL Server uses a proportional fill technique, where the file with the most free space is preferred for meeting space requests. In your tempdb situation, one of the files will need to be grown before the others. Once this file is larger than the others and the statement causing the growth has finished, that file will have more free space than the other files. It will then be the preferred file for the next use of tempdb, and is therefore more likely to get grown again than any other file.
If all the files in tempdb are set to a fixed size and not allowed to grow, then they will all get used equally. You need to work out what is your normal peak tempdb use, add maybe 20%, and divide that space between the tempdb files.
However, this is not the end of the story. What happens if you have sized your tempdb files too small but there is still space on the disk? The answer is you get a message that tempdb is full and the query that wanted the extra space gets terminated.
The best way to manage a filegroup containing multiple files is:
1) Set up all the files with equal and adequate size, and with NO growth allowance.
2) Create an extra file with a size of 1 MB and a large growth allowance (specified in MB or GB).
This will result in all your space neds being met from the big fixed size files until they are all filled up. SQL Server will not allocate anything to the 1MB file while space exists elsewhere because it only has 1MB free space and other files will have more space. But when the rest of the files are full, SQL is forced to use your 1MB file and it will then grow as large as needed.
If you do set things up in this way, you need to monitor how full your main files are getting, and increase their size if more than 70% of available space is used. When you increase your main file size, they must all be increased to the same size or the proportional fill process will be out of balance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 25, 2008 at 4:44 am
Thanks a lot....
Can i also know how to find which database or session or SP or SQL leaves temporary table without properly removing itself from tempdb.
I can find that in my tempdb datbase there are plenty of temporary tables which have the same name.
Based on your suggestion i can inform the developers to make necessary changes in their scripts.
September 25, 2008 at 4:55 am
Temporary tables should be deleted automatically by SQL Server when the thing that created them ends.
If a #temp table is created in a stored procedure, it will be deleted as soon as the SP ends.
If a #temp table is created in a Management Studio query session, it will be deleted when that session is disconnected.
However, good coding standards say that if you create an object you should also explicitly delete it.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 25, 2008 at 6:10 am
What is the point of having three additional ndf files on the same drive for a tempdb database? All the IO is going thru the same channel to the same same disk.
Rather than use percent, why not use fixed increments of 16M (or some other suitable number).
September 25, 2008 at 6:18 am
If all 3 files are on the same physical disk, there is no point in doing this. At best performance will be the sme as with 1 file but you could flood your disk controller with IO requests and end up with worse performance. (I wonder if this would still be true if the 'disk' was solid-state...)
If the 3 files are all on the same LUN in a SAN, and the LUN is spread over multiple physical disks, then you should see a performance improvement.
You need to be aware of how yor disk subsystem is implemented before deciding if multiple files on a mount point sounds a good or bad idea. You then need to test to see if your assumptions were right.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply