Reclaiming the Log space

Question

Post reply

Reclaiming the Log space

tthyagu

SSC Journeyman

Points: 84
More actions
June 2, 2005 at 2:20 am

#113924

I work in SQL server 7. The properties of the data base says that :
size 1596 MB,
space available 1465 MB.
However this is small database with size of 80 MB after taking a full backup. I want to reclaim this space. How to do it? Please help me.
TT(UserID=46393)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Rick Davis SSChasing Mays Points: 632 More actions · Answer 1

1) Create a backup of the database.

2) Drop the database.

3) Recreate the database.

4) Restore the backup to the database.

tthyagu SSC Journeyman Points: 84 More actions · Answer 2

To restore the backup, I need to have minimum of 1.5 GB of free space,equivalent to the size of the database, in the hard disk. So still the problem is not solved.

--TT

binu john SSCommitted Points: 1987 More actions · Answer 3

1.Shrink log file to required size.

2.Restrict log file growth.

Regds

Binu John

tthyagu SSC Journeyman Points: 84 More actions · Answer 4

Thanks for the reply.

I used the commands like

DBCC shrinkdatabase

DBCC shrinkfile.

But still I could not shrink the log file. Is there any other way out.Please give suggestions

Regards

TT

binu john SSCommitted Points: 1987 More actions · Answer 5

Use EM.

A.To shrink Log file

1.Right click database.goto All Tasks.Goto shrink database

2.Select box shrink files.And click on files button.

3.In the database file select the log file.

4.In the shrink action select shrink file to the required size.

5.Then click on ok.

:::::::::::::::::::::::::::::::::::::::

B.To restrict the file size

Select properties of database.

Goto Transaction Log Tab.

In maximum file Size choose restrict the file growth(In MB).

Hope this will work

Regards

Binu John

Okafor Mr or Mrs. 500 Points: 596 More actions · Answer 6

Adding to Binu John's steps, sometimes I would also need to backup or truncate the log before and after shrinking the file. For some reason it would not reclaim the space until the log committed transactions were cleared.

Glauco Costa Mr or Mrs. 500 Points: 547 More actions · Answer 7

try

backup log "database_name" with truncate_only

and after

dbcc shrinkdatabase

and after

resctrict file grow

John Anson Newbie Points: 4 More actions · Answer 8

First backup the database then back up the log with the truncate option then shrink the data base.

I have see this thake a couple of times before the files shrink to the min size plus the % free

JP de Jong-202059 SSCommitted Points: 1674 More actions · Answer 9

Hi all,

Just shrinking won't do you any good. You're having the issue of the active log pointer being in the end of the transaction log file. (see my script http://www.sqlservercentral.com/scripts/contributions/1461.asp to figure out how much data can be reclaimed by shrinking.)

To shrink the file to a smaller size - generate changes:

use [your_database}

create table T1 (id char(8000))

Insert T1 (id) values (' ')

--repeat next line a few times, do a Tran Log backup,

--check if & how much data can be reclaimed, shrink datafile

select * into T1 from T1

--finally drop table

drop table T1

Good Luck

JP

mise13 SSC Enthusiast Points: 128 More actions · Answer 10

After you sucessfully reclaim the log space, back up your trans log regularly. Otherwise your log will grown again. Do not just do full backups- do trans log backups as well.

I always back up the trans log before the shrink, and that usually works.

tthyagu SSC Journeyman Points: 84 More actions · Answer 11

Dear Friends

Of all the methods suggested by different friends, the method suggested by JP de Jong has reduced the log space.

Any way I learnt many commands from all of you.

Thanking you all

--TT