Jim Lawrence
jlawrenc1 at shaw.ca
Wed Nov 1 11:56:30 CST 2006
Hi John: It does tie up everthing... Here is a step-by-step list for running shrink process that I pulled off a MS SQL site: 1. Create a file which is as large as the data in your primary file (call it "buffer") 2. Empty the primary file (DBCC SHRINKFILE (<FILENAME>,EMPTYFILE)) 3. Restart SQL Server Engine 4. Shrink the primary file to the Data size divided by the number of files you're gonna create (DBCC SHRINKFILE(<FILENAME>,<NEWSIZE>) 5. Create all the new files with the size of data divided by the number of files 6. Restrict their growth in order to fill the primary file in the next operation 7. Empty the buffer file (DBCC SHRINKFILE(BUFFER,EMPTYFILE)) 8. Delete the buffer file (ALTER DATABASE REMOVE FILE (NAME=BUFFER)) 9. Set final size of data files and unrestrict their growth according to the final configuration needed HTH Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Wednesday, November 01, 2006 9:27 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] What is going on Yep Yep Yep AFAIK Almost certain but it is in the middle of a shrink file and I can't get control at the moment. Just an update, I am doing a shrink because it told me that the container file was 280 gb but only 100gb used. Speaking of which, is there any way to shrink in the background? That seems like a given but a shrink locks up my database tight which seems rather unhelpful. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Wednesday, November 01, 2006 11:40 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] What is going on Hi John: Just to confirm: 1. You are using the standard bulk raw import procedures or DTS Text import? 2. No joins, indexes, database logging, datatype conversions, identity fields, etc. etc... just an unattached single staging table... 3. No alerts or other services on... 4. Unlimited unrestricted growth on. Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Tuesday, October 31, 2006 9:27 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] What is going on Just an update, I started a new import wizard processing the exact same file (a copy on the E: drive) into an empty table and it is running at the expected 3000 records / second, so far. So it appears to have something to do with the number of records already in the main table as opposed to a general SQL Server slow down, raid drive slowdown etc. I am canceling the sloooow import. The import into a clean table should be finished within 15 minutes or so, at which point I will try an append from the smaller table and see if that happens quickly. Something funky is definitely going on though. The cancel takes a long time, and while that is happening, the other data import has slowed to a crawl. This really sucks! John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Wednesday, November 01, 2006 12:12 AM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] What is going on This afternoon, soon after posting how fast the system is, SQL Server "locked up". I had imported 18 files over the afternoon, and then suddenly the three that were processing just froze. In task manager I would see SQL Server activity, the processors would alternate between no activity and 50% utilization, and when that happened SQL Server was the task using the CPU time. However NO records were importing. I decided to let it run, went away for a meeting, returned several hours later to find NO cpu activity and the exact same number of records imported (still frozen). I aborted, then restarted a single import process. It progressed, but slowly. The next progressed but even more slowly. The last file that needs to be imported is just dragging. As in will take 12 hours or more to do just the one file (20 minutes was the average before). I stopped that last process, moved the file physically out onto the smaller raid to test whether it was a comms issue between machines. No help. My C: (system) drive has about 10g out of 80 used. My D: (SQL system databases there) has about 10g used out of 200 total. My E: drive (the main database file for this database) has used 298 G with 901 gig free (one of two Raid 6 drives) . My F: drive (the main database log file for this database) has used 45 Gb with 330gb free (the second Raid 6 drive). I have huge amounts of free space on the raid drives, well over 700 gb on the one, and 300 gb on the other. The main db file is now up to about 280 mb but by my calcs that is about normal (correct) given the amount of data input so far. At this point I am on the last file to import, sitting at about 1 million records imported out of 3 million to be imported. It is importing them about 1000 records every three seconds. There are about 63 million records in the table. No indexes, no triggers, nothing like that. Just raw data. This thing should be flying. Instead it is crawling. CPU usage is running 12% to 25%, with SQL Server.exe and MMC.exe using that. What the heck is going on? John W. Colby Colby Consulting www.ColbyConsulting.com _______________________________________________ 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 _______________________________________________ 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