Advice on deleting all rows and reclaiming space of very large table I'm not a dba...let's start with that :) I'm an app developer. But our database has an archive table which is 252 million rows and 170GB, and I've been tasked to fix it (actually, there are multiple tables in this category that I need to clean up). But we've decided we do not need to keep any of this data. As this table is an archive table, by which I mean there is a primary transaction table for this data, but the application has a way to keep the transaction table smaller by archiving, or moving, older records to this archive table. 

Being an archive table, it has no dependencies per SSMS (no indexes or foreign keys). This db is set to full recovery mode.

We have short maintenance windows, so need a process that is fast, and we have limited disk space, so we need to reclaim the disk space from this table.

We can truncate the table, but then how to reclaim the space? We don't want to do a db shrink, as it shrinks all tables and hence then we'd need to run index rebuilds...at least that is my understanding, which could be incorrect.

I could save a script from SSMS (Script Table as Create To...). Then drop the table, then recreate it... Is this risky? Will it reclaim the space?

Other ideas?
Because you have limited experience with database administration, be extra careful. You have a test copy of your database server set up, right?

You mentioned the database uses full recovery model. This is VERY important, because if the transaction logs have not been getting backed up often enough, there is no way to shrink them. You'd have to put the database in simple recovery mode (which for all intents and purposes gets rid of the transaction log) and wait while that processes, then you could move forward. Given the amount of data, I'd imagine it would take a while. For information on the recovery models, go here:  https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server This article gives a basic overview.

To answer your question, truncating or dropping the archive table will not free up space - that's what shrinking the database does. All the truncate/drop does is mark the appropriate pages in the database as unused. There's little sense in dropping the table and re-creating it. A truncate basically gets the same end result and is less prone to error. 

Keep in mind, if this application sends more data to this archive table, the database will expand after you've run the shrink procedure! (Are you really using SQL Server 2005? If so, seriously consider upgrading to a supported version, and getting newer hardware if it's a physical server.) So, just like the application moves data to the archive table, you should consider implementing a way to remove unneeded data from the archive table to avoid this situation in the future.  But first things first.

If you haven't already, read this: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql It's a Microsoft doc that describes shrinking a database in some good detail. This is a decent overview, and contains some best practices as well as caveats.

If you don't want to shrink the database, your other option is to create a new database, transfer to it what you want to keep from the old database, then drop the old database. Of course, this will take up quite a bit of disk space and time. It will also put the server under considerable load.

You are correct in that a database shrink can increase index fragmentation. Having said that, such fragmentation may not have a noticeable effect on performance. That's why you have a test server. :-)

You may have to adjust your maintenance window for this operation. It will take as long as it takes. While the truncate is in progress, the database will be online. You can keep the database online during a shrink as well. Performance may suffer noticeably depending on usage, how queries are tuned, hardware, and operating system configuration. Again, test before doing anything in production! A shrink can be stopped and started at a later time, however, if space is at a premium starting and stopping defeats the purpose.

One more thing: depending on how fast data is added to this database, truncating and shrinking may only delay the inevitable. Consider adding storage capacity.

If you have further details or clarification, I'll update my answer accordingly. In the meantime, best of luck, and let us know how things go!