How do I reduce the database size?

This article applies to the following scenarios:

  • You are running out of disk space on the database server
  • You are getting error messages similar to: "Can't allocate space for object syslogs in database dbname because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment."

Concepts:

SQL Server uses transaction logs to keep modification of data in a consistent manner.  For a technical overview, please see: https://technet.microsoft.com/en-us/library/ms345419(v=sql.105).aspx

By default, SQL Server could be using the "Full Recovery Model" which means unless you have a maintenance plan to backup transaction logs, it would grow indeterminately.

Often times, it may be sufficient to use the "Simple Recovery Model".  To help you choose an appropriate recovery model, plese refer to: https://technet.microsoft.com/en-us/library/ms175987(v=sql.105).aspx

If the Simple Recovery Model is sufficient, you can follow the instructions here to set it up: https://technet.microsoft.com/en-us/library/ms189272(v=sql.105).aspx.  Under the simple recovery model, the transaction log is automatically truncated after a backup is done.

Truncation only deletes unnecessary transaction logs - the physical log file still consumes space.  You may also want to shrink the database to free up physical disk space.  While it is not recommended practice, you may also let SQL server automatically.  See https://technet.microsoft.com/en-us/library/ms189080(v=sql.105).aspx for more details.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk