0

I'm facing a strange problem about a staging database used by my ETL (to update rows).

Only rows to update are stored in the database, then a script is executed to update the destination database. At the end of the process, It truncates the staging database. It removes all data, however the allocated size for my database grows every execution time of my SSIS package. So, is there a way to reduce the allocated size and to limit the maximum allocated size ? In SQL Server Management Studio, there is a wizard to reduce data size and database size.

Is there the same command in T-SQL ?

Thanks !

K4timini
  • 711
  • 2
  • 14
  • 34

2 Answers2

2

Don't.

If your staging needs a database of size X, then size the database at X and leave it so. Attempting to shrink it is misguided at best. By shrinking it all you achieve is just invite an opportunity for your ETL to fail tomorrow, because it runs out of required disk space. Do not fool yourself that 'I only need space X for ETL'. You need space X, period.

I'm not even going to go into all the performance problems related to shrink and re-growth.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
1

There is a command in T-SQL. Look here [http://msdn.microsoft.com/de-de/library/ms189493.aspx][1]

DBCC SHRINKFILE (Transact-SQL): Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.

But take the answer from Remus in consideration

OliC
  • 175
  • 6