0

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?

crichavin
  • 403
  • 6
  • 15
  • You need to be a bit more specific with your statements/requirements, because ...we need to reclaim the disk space... and We don't want to do a db shrink seem to contradict each other. How do you reclaim space if not by issuing a DBCC SHRINKFILE... or DBCC SHRINKDATABASE...? – John K. N. Jul 27 '17 at 06:07
  • @hot2use not sure...if there is no other way? In researching I saw the following post mentioning an OPTIMIZE TABLE command? https://dba.stackexchange.com/questions/64134/deleting-reclaiming-space-from-innodb-table – crichavin Jul 27 '17 at 14:51
  • Yes, but that is on innodb and not on SQL Server. If data is deleted in SQL Server then the pages are marked as free, to ensure that the space in the database (-file) can be used again. However, if you want to resize the database to a smaller size, then this incurs shrinking the database files, which you don't seem inclined on doing. Hence the contradiction. What do you want to achieve precisely? – John K. N. Jul 27 '17 at 14:58
  • @hot2use thanks for the clarification on innodb. We will do a shrink on the entire DB if that is the only option to regain space. Curious though, if we don't shrink, will the db backups be any smaller with the data deleted? What about 7z compressed backups...will they be smaller? – crichavin Jul 27 '17 at 16:07

1 Answers1

1

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://learn.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://learn.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!

S M
  • 196
  • 6
  • Thanks you for the amount of time you have invested in answering my question! This is actually a client of mine. They may not have a viable test environment (investigating that now). We do tran log backups every 15 min. We are setting the application to not send any more data to this table, but if we did, I get that it would grow. Any ideas on how long a truncate would take on a table this size...I've seen some reports that it is very fast and only would take several minutes (< 10). I understand it could vary...but are we talking min or hours? – crichavin Jul 27 '17 at 16:04
  • 1
    @chadrichardson: that depends on the environment. If it's not configured well, busy, and running on hardware that's barely adequate, it will take a while. Initially, it will take a few seconds to a minute, because the actual truncate happens in the background. Check this Microsoft doc for a description: https://technet.microsoft.com/en-us/library/ms177495(v=sql.105).aspx Once the deferred drop process starts, activity on the database will increase. So your client may have a few minutes to a few hours of underwhelming performance, if they notice anything at all. – S M Jul 27 '17 at 16:16
  • thanks. I'll have a larger window on the weekend, so I think we'll be ok then. So I'll do a full db backup, truncate table on all these types of tables (5 or so), then do a db shrink, then run index rebuild/reorg (I have a script that does one vs the other based on fragmentation levels). How does that sound? – crichavin Jul 27 '17 at 17:00
  • 1
    The actual TRUNCATE TABLE command should complete under a minute; all it basically does is mark all the space used by rows in the table as free space. – RDFozz Jul 27 '17 at 17:03
  • 1
    @chadrichardson: that sounds like a good plan. – S M Jul 28 '17 at 04:42