Shrink Log File

[jluost1]

I created a database with initial 10MBs and incremental is 10MB for SQL7 databse LOG file. Then the log file grows to 490MB. I just truncated the log file and now the space available for database is more than 350MB.

What I want to do is to keep the log file within 150MB. My questions are:

(1) How to set the max for the existing Database (I know I can set the max for the growth of log file during database creation, but how after it is created?)

(2) In this case, the max (150MB) is less than the existing allocated space (490MB). Can I do that and what's the impact?

(3) I tried to shrink the database but it doesn't make the database size any smaller. Why and how to make database size smaller?

Thanks a million.

 

[tlbroadbent]

Check faq183-1534 and other FAQs about shrinking the LOG in the FAQ area of this forum. After shrinking the log, you can open the database properties in Enterprise Manager, click on the Transaction Log tab and set the Restrict File Growth option. However, I wouldn't recommend this. We regularly backup logs and run a shrinkdatabase and shrinkfile on a scheduled basis. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[outofservice]

-- Copy and paste this into query analyzer.
-- Run these two sql commands separetly.
-- Run part 1, check for new log size to shrink
-- to and then run part 2

-- 1. TRUNCATE LOG
-- Truncates all inactive portions of the log.
-- Note: you won't see any change in log size at
-- this point.

USE [database_name]
BACKUP LOG [database_name] WITH TRUNCATE_ONLY
GO

-- 2. SHRINK LOG FILE
-- Code below specifys that the log should be shrunk
-- to 5mb
DBCC SHRINKFILE ([log_file_name], 5)
GO