Michael Maddison
michael at ddisolutions.com.au
Wed Aug 18 23:22:09 CDT 2004
John, The db wont be hurt if you stop the transaction. What I would do... YMMV... Change Recovery mode to simple (cuts logging down) Drop all indexes inc PK before attempting to add the identity column. Script them using QA so you can put them back after. Turn off all unnecessary services. Have nothing else running. Buy or upgrade to a bigger box ;-))) You need multiple drives and processes. regards Michael M Francisco, This was until recently my desktop machine for Access dev. No need for more memory really. I "retired" it when I got my laptop which is a P64 with 512m ram (desktop replacement laptop). I will now be pulling it back into service as a (temporary) SQL Server machine. The db will hold a 60+ million record flat file of name/address/ demographics with 14+ million more to come immediately, and possibly merging in a couple of other dbs that are currently hosted elsewhere. My intention is to build a server since I am "bootstrapping" this operation (little cash). The current machine is an AMD 2.5g Barton with 512 mb ram running Windows2K Pro and SQL Server 2K. Awhile back I bought a RAID card and a couple of 120gb hard disks for the main c: drive (Raid1) which is where the current db resides. I purchased a couple of 200gb Maxtors with 8m cache which I intended to mirror, then throw the db out there. We'll just have to wait and see how big this mutha gets. I can see however that an immediate memory upgrade would be good if I intend to use this thing for long. Long term I'm looking at building a dual Opteron. I have been looking at how to get lots of memory and processing power though reading the web page you provided brings to doubt my ability to use said memory. 8-( John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco Tapia Sent: Wednesday, August 18, 2004 5:32 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Using task manager to close SQL Server On Wed, 18 Aug 2004 16:56:21 -0400, John W. Colby <jwcolby at colbyconsulting.com> wrote: > I am trying to add a "PK" autoincrement to this huge db I'm working > with. There are around 6 million records at the moment. I went in > design view and added the field, then "saved" the table and it took > off adding the new field and putting values in the table. The problem > is it is taking FOREVER (running for many hours so far) and is not > giving any status indicator to say when it will be done. I absolutely > must "get back to work doing some stuff in the db. > > If I "close" enterprise manager in the middle, will it damage my db? > I am assuming not but don't want to take any chances. > > It appears that the reason it is taking so long is extensive use of > the swap file. Enterprise manager is currently using 421 mbytes on a > 512m machine. I would go somewhere and buy some 512mb sticks if it > will help (and it appears it will). You know what, I also forgot to mention that you ought ot look into how you arrange the new hdds for your pc as well, Since you are DOING some Sql Server development, it is to your advantage to have your 2ndary data hdd on a seperate I/O card, this is important in a production environment, but also in a development one as well. What you'll find is that the system may become busy doing it's updates, but your PC will continue running like a champ, because all the heavy I/O process are offloaded to a seperate card/ channel. So if you don't have a second ATA or SATA card go out and look for one when you are out shopping for your 1gb of ram. You'll notice that the performance of the database increases like nuts, IIRC you just recently bought a 200 or 250gb drive w/ 8mb of cache did you not? Adding the I/O card will enhance the performance on that drive (or your 2ndary drives you have) -- -Francisco _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com