Log space question

Question

Log space question

sunny.tjk

SSCertifiable

Points: 5041
More actions
January 9, 2012 at 8:09 am

#249302

I need to drop tables from a static database to save around 3TB space, should I go with dropping 1 table at a time approach instead of all at once in order to avoid filling out the log space?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You're probably OK unless you're dropping thousands of tables or really, really, really huge ones. Table drops just log the deallocation of pages (and the required modifications to the system tables and allocation structures), not the deletion of the rows in the tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 2

Thanks Gila.

Do I've to run DBCC Shrinkdatabase command to claim the space after dropping the tables?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If you do you'll have to rebuild all your indexes afterwards (and that's not trivial on a huge DB). Try a shrink with truncate_only first, see if that has enough effect.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

I would run dbcc shrinkfile for the log file specifically, if it grew beyond what I needed. Same for the data files.

You always need to have some free space to allow for maintenance operations and data growth, so do not shrink too small.

If you are dropping the tables as a complete object, then the log shouldn't be a problem as Gail noted. In that case, don't mess with it. The log is there for logging changes, and it's size relates to the aggregate changes (insert/update/delete) during the period of time between log backups.

If you want to reclaim data space, you ought to have an idea of what the data growth is for 3-4 months. Leave that much space free in the database.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

And only run the shrink one time after dropping all the tables. If you run it multiple times you're going to be fragmenting your files a lot.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning