How To Reduce Size of Transaction Log (Sql Server 7)

[JohnBates]

Hello SQL experts -

The drive where my db resides is bulging - only has 122 MB free space.

The Transaction Log size is a total of 144 MB:
Used : only 19 MB Free : 125 MB !

I want to recover some of the 125 MB

I ran Truncate Trans Log... completed successfully.
Next, in the datbase's properties (Enterprise Manager), I tried to change the Trans Log's Space Allocated from 144 MB
to 100 MB. I got an error message ....
"Error 21335 The new DB file size must be larger than the current size"

What am I doing wrong. How can I get the Transaction Log to allocate less space ?
This is SQL Server 7

Thanks, John

 

[stoolpigeon]

You need to set up a new device for a transaction log that is smaller and then move your transaction log from the old- large device to the new smaller device.

You can make transaction logs bigger, you cannot make them smaller.
(Well not that I know of I use 6.5 so I could be out of date- but it sounds like that this is the case for you as well)

 

[JohnBates]

Thanks for your reply Theologian -

I have plenty of space on this server's D drive.

My .mfd is on the C drive.
Are you saying the .ldf (Trans log file) can be on a different drive than the database is on?

If so, great !

Can you think of any problems that might occur with having the 2 files on separate drives?

Thanks alot. John

 

[tlbroadbent]

Which version of SQL Server are you running? If you run SQL 7 or higher, then Shrink the database. See the following article on ways to shrink the transaction log.

http://www.swynk.com/friends/krishnan/tranlogshrink.asp

If you have plenty of room on the D: drive, by all means move the transaction log to the D: drive and free up all the space you can on the C: drive. Even 144 MB isn't much room for growth. If possible, move the Entire database off of the C: Drive.

Here is a script that will move the database files.

-- Change the script to match your DB and file names
-- Detach the database
-- Will only work if no other users in DB
exec sp_detach_db N'DBName'

-- Move files to different location
exec xp_cmdshell 'move c:\mssql7\data\DBName_data.MDF d:\mssql7\data\DBName_Data.MDF'
exec xp_cmdshell 'move c:\mssql7\data\DBName_log.LDF d:\mssql7\data\DBName_Log.LDF'

-- Attach DB using new file locations
exec sp_attach_db
@dbanme=N'DBName',
@filename1=N'd:\mssql7\data\DBName_Data.MDF',
@filename2=N'd:\mssql7\data\DBName_Log.LDF'
Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[RickCole]

If you are also running your hardware with multiple disc controllers you will get much better performance if you log is on a different drive to your database

Rick.