INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Database and Log Files

Shrinking Databases and Logs - SQL 7 and Higher by tlbroadbent
Posted: 4 Feb 02 (Edited 28 Mar 02)


SQL 7 and SQL 2000 allow automatic file growth. Therefore, the files can grow large. It is important to understand how to reduce the database and log files sizes.

There are two DBCC commands to help shrink databases and files. Both are needed to reduce and maintain file sizes.

DBCC shrinkdatabase
DBCC shrinkfile

The following links provide a lot of information about these commands as well as general information and code for performing shrinks.

DBCC SHRINKDATABASE
http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_3pd1.asp

DBCC SHRINKFILE
http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_8b51.asp

Truncating the Transaction Log
http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_7vaf.asp

Shrinking the Transaction Log
http://msdn.microsoft.com/library/psdk/sql/8_ar_da2_1uzr.htm

Check out the FAQ posted by FOXDEV - FAQ183-345 - to find more information about shrinking the log and the log file.

You can also read the following articles.

Transaction Log Backups
http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_565v.asp

Shrinking Active Log Files├╣Revisited
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9011

Quickly Shrinking the Transaction Log
http://www.swynk.com/friends/krishnan/tranlogshrink.asp

Note: the article above suggests using sp_detach_db and sp_attach_single_file_db to quickly shrink the log file. However, if a database has more than one physical log, this technique should not be used. Every database requires at least one log file. Additional log files can be added and removed using the ALTER database statement in T-SQL, from Enterprise Manager or with SQL-DMO. See the Microsoft online article about adding and deleting files.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_aa-az_4e5h.asp

The same information can be found in SQL BOL.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close