Sql-server – How to manage a large SQL Server database: transaction log, reduce size, alter table

size;sql servertransaction-log

Hello I have some confusion on how to manage a large SQL Server database.

Let's say size is 15GB, with 3GB available, in simple recovery mode.

Server is not managed by me, I cannot change the size, not even temporarily, nor detach/reattach backup/restore.

Let's say I can only manage the data and the structure of the database.

If I want to alter a table, to add a column, or delete only certain rows (before a datetime), or want to NULL a few nvarchar(MAX) columns to save space, how does this impact?

Often I receive the error "no more log space".

Then I shrink the DB, nothing has changed, and I have less space free…

I don't need the transaction log, so what can I do to perform these operations without incurring in error messages regarding the transaction log size?

Thanks!

Best Answer

Points, in no particular order:

  • You do need the transaction log for a whole bunch of reasons I'm not going to go into. That said, if you're running out of transaction log space while you're in simple recovery mode, that probably means you have long-running transactions. Make your transactions as short as possible (gather all required user input before you start a transaction). (When SQL Server is trying to free log space, it can only free up to the start of the oldest current transaction.)
  • Making NVARCHAR(MAX) columns nullable will not necessarily save you much space (off the top of my head it'll be 2 bytes per row, per column).
  • Deleting/archiving old data will help you - with a caveat that it has to pass through the transaction log first.
  • Shrinking your database will not help you, as a matter of fact it will kill performance (both while it's running and afterward, since DBCC SHRINKDATABASE uses a naive algorithm that almost perfectly fragments your indexes) and won't regain you much if any space.
Related Question