Have you ever got this TempDB error ?

My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
By default, the TempDB locates at C drive and I increased its size by adding
2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
However, I don't know why the TempDB always cannot recognize these 2 D-drive
files when I come to office every morning.
The 2 D-drive files still exist in D drive but from the SQL Enterprise
Manager tells me the TempDB only has C-drive files.
Why, why the D-drive file pointers always become invvalid ?
Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly.
Refer ALTER DATABASE in books online to add a new file to D Drive. (You can
also use Enterprise manager)
Thanks
Hari
MCDBA
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>
|||What does "cannot recognize" mean? Do you get an error message (as your
subject implies)? If so, what is it?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>
|||It means in SQL Enterprise Manager, it only says the TempDB has 2 C-drive
files but not have the 2 D-drive files I added last night.
However, the 2 D-drive files still exist in the D drive when I used File
Explorer to search them.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:#uPqAqMREHA.2972@.TK2MSFTNGP09.phx.gbl...
> What does "cannot recognize" mean? Do you get an error message (as your
> subject implies)? If so, what is it?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c97hfp$rtr2@.imsp212.netvigator.com...
> adding
> D-drive
>
|||Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly. Please recreate the files using using ALTER DATABASE command.
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezZkyaMREHA.3944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you execute the below statement in query analyzer:-
> sp_helpdb tempdb
> If it is not showing D drive file, then you have not added the new files
> properly.
> Refer ALTER DATABASE in books online to add a new file to D Drive. (You
can
> also use Enterprise manager)
> Thanks
> Hari
> MCDBA
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c97hfp$rtr2@.imsp212.netvigator.com...
> adding
> D-drive
>

Have you ever got this TempDB error ?

My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
By default, the TempDB locates at C drive and I increased its size by adding
2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
However, I don't know why the TempDB always cannot recognize these 2 D-drive
files when I come to office every morning.
The 2 D-drive files still exist in D drive but from the SQL Enterprise
Manager tells me the TempDB only has C-drive files.
Why, why the D-drive file pointers always become invvalid ?Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly.
Refer ALTER DATABASE in books online to add a new file to D Drive. (You can
also use Enterprise manager)
Thanks
Hari
MCDBA
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>|||What does "cannot recognize" mean? Do you get an error message (as your
subject implies)? If so, what is it?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>|||It means in SQL Enterprise Manager, it only says the TempDB has 2 C-drive
files but not have the 2 D-drive files I added last night.
However, the 2 D-drive files still exist in the D drive when I used File
Explorer to search them.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:#uPqAqMREHA.2972@.TK2MSFTNGP09.phx.gbl...
> What does "cannot recognize" mean? Do you get an error message (as your
> subject implies)? If so, what is it?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c97hfp$rtr2@.imsp212.netvigator.com...
> > My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> > By default, the TempDB locates at C drive and I increased its size by
> adding
> > 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> >
> > However, I don't know why the TempDB always cannot recognize these 2
> D-drive
> > files when I come to office every morning.
> > The 2 D-drive files still exist in D drive but from the SQL Enterprise
> > Manager tells me the TempDB only has C-drive files.
> >
> > Why, why the D-drive file pointers always become invvalid ?
> >
> >
> >
>|||Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly. Please recreate the files using using ALTER DATABASE command.
Thanks
Hari
MCDBA
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezZkyaMREHA.3944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Can you execute the below statement in query analyzer:-
> sp_helpdb tempdb
> If it is not showing D drive file, then you have not added the new files
> properly.
> Refer ALTER DATABASE in books online to add a new file to D Drive. (You
can
> also use Enterprise manager)
> Thanks
> Hari
> MCDBA
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c97hfp$rtr2@.imsp212.netvigator.com...
> > My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> > By default, the TempDB locates at C drive and I increased its size by
> adding
> > 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> >
> > However, I don't know why the TempDB always cannot recognize these 2
> D-drive
> > files when I come to office every morning.
> > The 2 D-drive files still exist in D drive but from the SQL Enterprise
> > Manager tells me the TempDB only has C-drive files.
> >
> > Why, why the D-drive file pointers always become invvalid ?
> >
> >
> >
>

Have you ever got this TempDB error ?

My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
By default, the TempDB locates at C drive and I increased its size by adding
2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
However, I don't know why the TempDB always cannot recognize these 2 D-drive
files when I come to office every morning.
The 2 D-drive files still exist in D drive but from the SQL Enterprise
Manager tells me the TempDB only has C-drive files.
Why, why the D-drive file pointers always become invvalid ?Hi,
Can you execute the below statement in query analyzer:-
sp_helpdb tempdb
If it is not showing D drive file, then you have not added the new files
properly.
Refer ALTER DATABASE in books online to add a new file to D Drive. (You can
also use Enterprise manager)
Thanks
Hari
MCDBA
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>|||What does "cannot recognize" mean? Do you get an error message (as your
subject implies)? If so, what is it?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c97hfp$rtr2@.imsp212.netvigator.com...
> My SQL Server 7.0 (with Service Pack 3) server has 3 drives : C, D, E.
> By default, the TempDB locates at C drive and I increased its size by
adding
> 2 more files (1 for TempDB database file and 1 for Temp log) at D drive.
> However, I don't know why the TempDB always cannot recognize these 2
D-drive
> files when I come to office every morning.
> The 2 D-drive files still exist in D drive but from the SQL Enterprise
> Manager tells me the TempDB only has C-drive files.
> Why, why the D-drive file pointers always become invvalid ?
>
>

Has anyone noticed a size difference between 2.0 and 3.0?

I had an application that I had written in C# under the .NET Compact Framework that imported CSV files and wrote them to a newly created SDF. I usually ran the application under Emulation. The resulting SDF was usually about 1.6 Megabytes.

Now, under VS 2005 and SQL Mobile (AKA 3.0), the same application, compiled under the .NET Compact Framework 2.0, using the same CSV files generates an SDF of over 13 Megabytes.

Obviously, this was a bit unexpected and is having a rather negative impact on the HP iPaq rx1950 that the database was supposed to be running on.

The database is encrypted during the create call on both .NET CF's and SQL CE versions. I was finally able to run compact via the SQL CE tool under emulation connecting to the SDF on the desktop (running compact on the device or under emulation resulted in both running out of memory); however, it caused no change whatsoever in file size.

Has anyone seen something similar? Am I missing a new call or, since my code has not changed between versions, could I possibly be making a deprecated call?

Thanks,
Brian

Brian,

I ran this same test - generating a SQL CE/SQL mobile database using a simple CSV loader I wrote and I noticed that if the database contains an NTEXT field, the SQL Mobile database ended up much larger on device (by a factor of 2-3x) than the corresponding SQL CE database.

I sent the sample code to recreate this to the SQL Mobile team and will followup with them on this issue and post the results here.

If I removed the NTEXT fields from my tables (used nvarchar instead), the resulting SQL Mobile db was equitable in size.

-Darren

|||

Darren,

Thanks! That did it. I converted the one ntext column to an nvarchar and we're back to our original size. Since I had more than 4000 rows, I am guessing that SQL CE 3.0 was reserving a ton of additional space, just in case.

Regards,
Brian

|||

Hi,

SQL Mobile 3.0 reserves a data page for Long Value data when data length is more than 256 bytes. NTEXT and IMAGE are long value data types. So, if you have a table with NTEXT/IMAGE column, then SQL Mobile 3.0 creates a data page for each row where the data size is more than 256 bytes. And data page size is typically 4K . If you want to update the NTEXT/IMAGE column, the operation will be very fast and it is by design. Also, whenever there is a data length exceeding 4K (not really 4K but 4K minus some control data size), another data page is allocated. Even if your data value is just 4.1K, 8K is what reserved by SQL Mobile 3.0. Best practice here would be to align your data sizes on 4K boundary.

Note: Page size may not be 4k always. It varies!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Has anyone noticed a size difference between 2.0 and 3.0?

I had an application that I had written in C# under the .NET Compact Framework that imported CSV files and wrote them to a newly created SDF. I usually ran the application under Emulation. The resulting SDF was usually about 1.6 Megabytes.

Now, under VS 2005 and SQL Mobile (AKA 3.0), the same application, compiled under the .NET Compact Framework 2.0, using the same CSV files generates an SDF of over 13 Megabytes.

Obviously, this was a bit unexpected and is having a rather negative impact on the HP iPaq rx1950 that the database was supposed to be running on.

The database is encrypted during the create call on both .NET CF's and SQL CE versions. I was finally able to run compact via the SQL CE tool under emulation connecting to the SDF on the desktop (running compact on the device or under emulation resulted in both running out of memory); however, it caused no change whatsoever in file size.

Has anyone seen something similar? Am I missing a new call or, since my code has not changed between versions, could I possibly be making a deprecated call?

Thanks,
Brian

Brian,

I ran this same test - generating a SQL CE/SQL mobile database using a simple CSV loader I wrote and I noticed that if the database contains an NTEXT field, the SQL Mobile database ended up much larger on device (by a factor of 2-3x) than the corresponding SQL CE database.

I sent the sample code to recreate this to the SQL Mobile team and will followup with them on this issue and post the results here.

If I removed the NTEXT fields from my tables (used nvarchar instead), the resulting SQL Mobile db was equitable in size.

-Darren

|||

Darren,

Thanks! That did it. I converted the one ntext column to an nvarchar and we're back to our original size. Since I had more than 4000 rows, I am guessing that SQL CE 3.0 was reserving a ton of additional space, just in case.

Regards,
Brian

|||

Hi,

SQL Mobile 3.0 reserves a data page for Long Value data when data length is more than 256 bytes. NTEXT and IMAGE are long value data types. So, if you have a table with NTEXT/IMAGE column, then SQL Mobile 3.0 creates a data page for each row where the data size is more than 256 bytes. And data page size is typically 4K . If you want to update the NTEXT/IMAGE column, the operation will be very fast and it is by design. Also, whenever there is a data length exceeding 4K (not really 4K but 4K minus some control data size), another data page is allocated. Even if your data value is just 4.1K, 8K is what reserved by SQL Mobile 3.0. Best practice here would be to align your data sizes on 4K boundary.

Note: Page size may not be 4k always. It varies!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Hardware sizing documentation?

I am running an SAP system with approximately 250 users (150-170
concurrent). The database is currently around 200Gb in size, running on SQL
2000 standard edition and Windows 2000 standard server. The server is a quad
500Mhz Xeon with 4Gb of ram, 1.7Gb assigned to SQL, SAP and OS having whats
left. What I am looking for is a rough guide to how much memory the server
should have in relation to the database size. Anybody know of any good
guides to sizing the server hardware? (I realise that to use over 2Gb I need
different versions of SQL and OS)
thanks
GavGav
http://www.sql-server-performance.com/awe_memory.asp
[url]http://www.sql-server-performance.com/performance_monitor_counters_memory.asp[/url
]
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cl80ka$m3s$1@.sparta.btinternet.com...
> I am running an SAP system with approximately 250 users (150-170
> concurrent). The database is currently around 200Gb in size, running on
SQL
> 2000 standard edition and Windows 2000 standard server. The server is a
quad
> 500Mhz Xeon with 4Gb of ram, 1.7Gb assigned to SQL, SAP and OS having
whats
> left. What I am looking for is a rough guide to how much memory the server
> should have in relation to the database size. Anybody know of any good
> guides to sizing the server hardware? (I realise that to use over 2Gb I
need
> different versions of SQL and OS)
> thanks
> Gav
>|||You can also go to HP and IBM websites. They both have sizing tools. You
type in SQL Server, transaction load, write %, size, etc. It recommends a
machine. It's generally pretty accurate, if you're not running anything
wacky.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uJ#ccP1tEHA.904@.TK2MSFTNGP11.phx.gbl...
> Gav
> http://www.sql-server-performance.com/awe_memory.asp
>
http://www.sql-server-performance.c...ters_memory.asp">
> "Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
> news:cl80ka$m3s$1@.sparta.btinternet.com...
> SQL
> quad
> whats
server[vbcol=seagreen]
> need
>

Hardware sizing documentation?

I am running an SAP system with approximately 250 users (150-170
concurrent). The database is currently around 200Gb in size, running on SQL
2000 standard edition and Windows 2000 standard server. The server is a quad
500Mhz Xeon with 4Gb of ram, 1.7Gb assigned to SQL, SAP and OS having whats
left. What I am looking for is a rough guide to how much memory the server
should have in relation to the database size. Anybody know of any good
guides to sizing the server hardware? (I realise that to use over 2Gb I need
different versions of SQL and OS)
thanks
Gav
Gav
http://www.sql-server-performance.com/awe_memory.asp
http://www.sql-server-performance.co...ers_memory.asp
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cl80ka$m3s$1@.sparta.btinternet.com...
> I am running an SAP system with approximately 250 users (150-170
> concurrent). The database is currently around 200Gb in size, running on
SQL
> 2000 standard edition and Windows 2000 standard server. The server is a
quad
> 500Mhz Xeon with 4Gb of ram, 1.7Gb assigned to SQL, SAP and OS having
whats
> left. What I am looking for is a rough guide to how much memory the server
> should have in relation to the database size. Anybody know of any good
> guides to sizing the server hardware? (I realise that to use over 2Gb I
need
> different versions of SQL and OS)
> thanks
> Gav
>
|||You can also go to HP and IBM websites. They both have sizing tools. You
type in SQL Server, transaction load, write %, size, etc. It recommends a
machine. It's generally pretty accurate, if you're not running anything
wacky.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uJ#ccP1tEHA.904@.TK2MSFTNGP11.phx.gbl...
> Gav
> http://www.sql-server-performance.com/awe_memory.asp
>
http://www.sql-server-performance.co...ers_memory.asp[vbcol=seagreen]
> "Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
> news:cl80ka$m3s$1@.sparta.btinternet.com...
> SQL
> quad
> whats
server
> need
>

Hardware sizing documentation?

I am running an SAP system with approximately 250 users (150-170
concurrent). The database is currently around 200Gb in size, running on SQL
2000 standard edition and Windows 2000 standard server. The server is a quad
500Mhz Xeon with 4Gb of ram, 1.7Gb assigned to SQL, SAP and OS having whats
left. What I am looking for is a rough guide to how much memory the server
should have in relation to the database size. Anybody know of any good
guides to sizing the server hardware? (I realise that to use over 2Gb I need
different versions of SQL and OS)
thanks
GavGav
http://www.sql-server-performance.com/awe_memory.asp
http://www.sql-server-performance.com/performance_monitor_counters_memory.asp
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cl80ka$m3s$1@.sparta.btinternet.com...
> I am running an SAP system with approximately 250 users (150-170
> concurrent). The database is currently around 200Gb in size, running on
SQL
> 2000 standard edition and Windows 2000 standard server. The server is a
quad
> 500Mhz Xeon with 4Gb of ram, 1.7Gb assigned to SQL, SAP and OS having
whats
> left. What I am looking for is a rough guide to how much memory the server
> should have in relation to the database size. Anybody know of any good
> guides to sizing the server hardware? (I realise that to use over 2Gb I
need
> different versions of SQL and OS)
> thanks
> Gav
>|||You can also go to HP and IBM websites. They both have sizing tools. You
type in SQL Server, transaction load, write %, size, etc. It recommends a
machine. It's generally pretty accurate, if you're not running anything
wacky.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uJ#ccP1tEHA.904@.TK2MSFTNGP11.phx.gbl...
> Gav
> http://www.sql-server-performance.com/awe_memory.asp
>
http://www.sql-server-performance.com/performance_monitor_counters_memory.asp
> "Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
> news:cl80ka$m3s$1@.sparta.btinternet.com...
> > I am running an SAP system with approximately 250 users (150-170
> > concurrent). The database is currently around 200Gb in size, running on
> SQL
> > 2000 standard edition and Windows 2000 standard server. The server is a
> quad
> > 500Mhz Xeon with 4Gb of ram, 1.7Gb assigned to SQL, SAP and OS having
> whats
> > left. What I am looking for is a rough guide to how much memory the
server
> > should have in relation to the database size. Anybody know of any good
> > guides to sizing the server hardware? (I realise that to use over 2Gb I
> need
> > different versions of SQL and OS)
> >
> > thanks
> >
> > Gav
> >
> >
>

Hardware requirement

Hi !
We have a sql server 2000, IIS and windows 2000 op-sys.
1133 Mhz cpu, 512 Mb RAM, raid5 scsi disk.
One prod database size 1164 Mb
Two test databases size 45 Mb
The server have 4 user in production and 1 programmer.
Is this server to small ?
If yes, what should we upgrade ?
Regards Jan RockstedtConsidering that your database and number of users is within the limitations
for MSDE (2GB data, 5 concurrent users), and MSDE is designed to run on a
desktop machine, I would say that your server might be overspec'd rather
than underspec'd.
Do you have any performance issues with it?
--
Jacco Schalkwijk
SQL Server MVP
"Jan" <_NO_SPAM_@.telia.com> wrote in message
news:O2LAncL8DHA.1548@.tk2msftngp13.phx.gbl...
> Hi !
> We have a sql server 2000, IIS and windows 2000 op-sys.
> 1133 Mhz cpu, 512 Mb RAM, raid5 scsi disk.
> One prod database size 1164 Mb
> Two test databases size 45 Mb
> The server have 4 user in production and 1 programmer.
> Is this server to small ?
> If yes, what should we upgrade ?
> Regards Jan Rockstedt
>|||I have problem with the RAM.
The server is allocating 560 Mb of 512 Mb, and using the swap file.
Is the any recomendation of memory alocating in SQL server 2000 ?
//Jan
Jacco Schalkwijk wrote:
> Considering that your database and number of users is within the
> limitations for MSDE (2GB data, 5 concurrent users), and MSDE is
> designed to run on a desktop machine, I would say that your server
> might be overspec'd rather than underspec'd.
> Do you have any performance issues with it?
>
> "Jan" <_NO_SPAM_@.telia.com> wrote in message
> news:O2LAncL8DHA.1548@.tk2msftngp13.phx.gbl...
>> Hi !
>> We have a sql server 2000, IIS and windows 2000 op-sys.
>> 1133 Mhz cpu, 512 Mb RAM, raid5 scsi disk.
>> One prod database size 1164 Mb
>> Two test databases size 45 Mb
>> The server have 4 user in production and 1 programmer.
>> Is this server to small ?
>> If yes, what should we upgrade ?
>> Regards Jan Rockstedt|||F.Y.I the sql server i configure:
Dynamically configure SQL server memory
Minimum 41 MB
Maximum 388 MB
Minimum query memory (KB) 1024
Jan wrote:
> I have problem with the RAM.
> The server is allocating 560 Mb of 512 Mb, and using the swap file.
> Is the any recomendation of memory alocating in SQL server 2000 ?
> //Jan
> Jacco Schalkwijk wrote:
>> Considering that your database and number of users is within the
>> limitations for MSDE (2GB data, 5 concurrent users), and MSDE is
>> designed to run on a desktop machine, I would say that your server
>> might be overspec'd rather than underspec'd.
>> Do you have any performance issues with it?
>>
>> "Jan" <_NO_SPAM_@.telia.com> wrote in message
>> news:O2LAncL8DHA.1548@.tk2msftngp13.phx.gbl...
>> Hi !
>> We have a sql server 2000, IIS and windows 2000 op-sys.
>> 1133 Mhz cpu, 512 Mb RAM, raid5 scsi disk.
>> One prod database size 1164 Mb
>> Two test databases size 45 Mb
>> The server have 4 user in production and 1 programmer.
>> Is this server to small ?
>> If yes, what should we upgrade ?
>> Regards Jan Rockstedt|||If you want to avoid having the server use the page file (I guess that's
mostly IIS that does that) you can limit SQL Server to 340 MB or less, or
you can buy some more memory. Memeory is quiet cheap these days.
--
Jacco Schalkwijk
SQL Server MVP
"Jan" <_NO_SPAM_@.telia.com> wrote in message
news:OUgXrrU8DHA.2316@.TK2MSFTNGP09.phx.gbl...
> F.Y.I the sql server i configure:
> Dynamically configure SQL server memory
> Minimum 41 MB
> Maximum 388 MB
> Minimum query memory (KB) 1024
> Jan wrote:
> > I have problem with the RAM.
> > The server is allocating 560 Mb of 512 Mb, and using the swap file.
> >
> > Is the any recomendation of memory alocating in SQL server 2000 ?
> >
> > //Jan
> >
> > Jacco Schalkwijk wrote:
> >> Considering that your database and number of users is within the
> >> limitations for MSDE (2GB data, 5 concurrent users), and MSDE is
> >> designed to run on a desktop machine, I would say that your server
> >> might be overspec'd rather than underspec'd.
> >>
> >> Do you have any performance issues with it?
> >>
> >>
> >> "Jan" <_NO_SPAM_@.telia.com> wrote in message
> >> news:O2LAncL8DHA.1548@.tk2msftngp13.phx.gbl...
> >> Hi !
> >>
> >> We have a sql server 2000, IIS and windows 2000 op-sys.
> >> 1133 Mhz cpu, 512 Mb RAM, raid5 scsi disk.
> >>
> >> One prod database size 1164 Mb
> >> Two test databases size 45 Mb
> >>
> >> The server have 4 user in production and 1 programmer.
> >>
> >> Is this server to small ?
> >> If yes, what should we upgrade ?
> >>
> >> Regards Jan Rockstedt
>
>

Hardware configuration

Hi Friends,
This is my first post here. Hope for a good response from the gurus ;-)
here it is...
We have a medium size database (upto 50 tables) out of which one table is a massive. It gets populated online at very high speed (> 20,000 records a day) so it grows quite large in month. Now the problem is when it grows to that extent in a month at the end of 2 months any query fired on it takes toooo long to execute (to the magnitude of 10 minutes ). We have following hardware configuration

1. 1.2 GHz Pentium 4
2. 40 GB HDD
3. 384 mb RAM.

What should be the server configuration to boost the performance of the database??

Regards,
Vishal.It may not be your hardware configuration. How often do you Reorg the database?|||Currently we only backup the table( The Big one) and trim it to achieve the speed. Is this what U R talking about?? Is there any thing else we need to do in order to reorg the db??|||after 2 months you should have something like 1200000 recrods yes?

what sort of data is in these records?

what indexes do you have on the table?

what are the searches being performed?

I imagine that if you applied some suitable indexes and possibly did some restructuring you should have not problems with this amount of data.

Yes, eventually it will slow as more data comes on board but no where near the 10 mins you are talking about....|||What rokslide mentioned will help greatly. But Reorg is a quick fix to boost performance for a db with large amount of reads and writes because it rearrange the data and index pages. To do a reorg just simply use Database Maintenance Plan Wizard and check the Reorganize Data and Index Page. Execute the plan once a month, it will greatly help the performance of your database.