INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Navigation

Join Us!
Keyword Search
Browse Forums
Jobs
Start A Group
Tell A Friend
Donate
Whitepapers

Come Join Us!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
Turn Off Ad Banners
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.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Member Feedback

"...I have tons of books, have book marked tons of tutorials, which have helped, but this forum has answered those "impossible to find" solutions. I am thrilled with this site..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming > FAQs

Microsoft SQL Server: Programming FAQ

Database and Log Files

Shrinking Databases and Logs - SQL 7 and Higher

faq183-1534

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 FAQ Archive

Email This FAQ To A Friend

My Archive

My FAQ Archive