DB will not shrink

[jamminjaymeyer]

When I try to shrink my DB the data file will shrink fine but the t-log will not shrink. It is only using 100 MG but it has 1.5 gig allocated for it. I would like to be able to free up that space.

Thanks.

 

[yeltom]

I think this may help.
In the query analayzer type

USE "name of DB"
go

dbcc shrinkfile ("Name of xaction log&quot
go

 

[JamesLean]

Shrinking Databases and Logs - SQL 7 and Higher
faq183-1534

--James

 

[SQLSister]

from Books online:
"You cannot shrink an entire database to be smaller than its original size."

Perhaps this is your problem.

 

[SonOfEmidec1100]

I used the detatch , delete log file and re-attached method. Im sure there are earlier threads on this.

SQLSisters quote prompts the question why have such a large original size? In my case the large original sizes were caused by upgrading from 6.5 to 7 - the new initial log size was the physical size of the log device, used or not.

 

[jackfl]

I had the same problem two years ago with SQL 7. Microsoft said it was a short-coming of 7.0. After opening a ticket with Premier Support I was given a multi-step fix. Let me know if you want a copy...

 

[jamminjaymeyer]

Yes I would like a copy please.

Thanks,
Jason

 

[jackfl]

The transaction log does not automatically shrink after the SHRINKFILE is done. Actual file shrinking is done periodically by the server and depends on :
1. The location of the last log entry on the transaction log. If the last log entry is near the end of the transaction log file, then the file cannot be
shrinked beyond it. Note that log shrinking is done from the end of the file until it encounters the last log entry. More information in BOL DBCC
SHRINKFILE.
2. The target log file size as defined by a SHRINKFILE parameter.
3. The server has heuristics keeping track of the amount of log space being consumed by the server (ie. the amount of log space used before a checkpoint) and uses this info to determine by how much (increments) to shrink the transaction log file.
Please go through the following steps :
1. USE <dbname>
GO
2. DBCC SHRINKFILE (<File_name>, 500, NOTRUNCATE)
As per BOL, The only effect of the DBCC SHRINKFILE is to relocate used pages from above the target_size line to the front of the file. Note the NOTRUNCATE does not free any space for the operating system, hence, Step3.
3. DBCC SHRINKFILE (<File_name>, 500, TRUNCATEONLY)
Causes any unused space in the files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data.
No attempt is made to relocate rows to unallocated pages. target_size is ignored when TRUNCATEONLY is used.

Since DBCC Shrinkfile, does not automatically shrink the size of the file, we need to do some transactions to move the active portion of the log and do several dumps (step 4).
4. <<Script>>
use Yourdatabase
go
create table t1 (char1 char(4000))
go
declare @i int
select @i = 0 while (1 = 1)
begin
while (@i < 100)
begin
insert into t1 values ('a')
select @i = @i +1
end
truncate table t1
backup log TemplateV3 with truncate_only
end
go
Let Step4 run several iterations, until you feel that the enough log entries have been processed (the active Virtual file is 105MB in size)

5. Check the size of your transaction log.