Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
2  
Yes - you do need the transaction log - you just might not know it yet..... –  marc_s Feb 17 '12 at 11:48
    
Large= 15gb? "My phone has more space" large? Try terabyte, please. –  TomTom May 31 '13 at 11:00
add comment

migrated from stackoverflow.com Feb 17 '12 at 22:02

This question came from our site for professional and enthusiast programmers.

1 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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.