Make a transaction log smaller?

[MesaMarshall]

Hello, I have a transaction log from part of a share point database that has reached 15 gigs on a 25 gig drive. I need to make it smaller. What can I do to accomplish this task?
(I made a backup of the transaction log, and that didn’t help)

Thanks in Advance!


-Mesa

 

[mrdenny]

After backing up the transaction log, you'll need to shrink the transaction log.
First you'll need to get the log name.

select name 
from sysfiles
go

The one that ends in Log is the log file. Take that name and put it in this script.

use {Database}
dbcc shrinkfile ({FileName}, 150)
go

That will shrink the file to 150 Megs. If you want it larger or smaller, change the 150 as needed. Keep in mind, that if you make it very small, it will simply start to grow as it fills. It's best to leave it a little larger than the amount of data that gets to into it between transaction log backups.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[MesaMarshall]

Thank you very much, I am not sure where to put those commands in. Can you tell me how you got to the interface I need?

Thanks!



-Mesa

 

[SQLBill]

You say you have a backup of the transaction log....how about a full backup of the database? Have you done that? If not, do that first.

Then take a look at the FAQs in Forum183. There are some directions for shrinking your log file.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

Those commands go in Query Analyzer.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[MesaMarshall]

I want to thank everyone for their help. I was able to shrink it easily enough. I made a backup of the transaction log after hours when the company closed for the night. Then after the backup was complete I right clicked the database and chose all tasks and “shrink” . This made the 15 gigabyte database go down to 700mb, which is okay by me!

I am going to do this process again with the normal database not just the Transaction logs and hopefully it will shrink from 3 gig to something more reasonable.

Thanks MrDenny and SQLBill!


-Mesa

 

[mrdenny]

Don't shrink the data file it self to much. If you do the file will simply grow to make more space as more data is inserted into the database.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[MesaMarshall]

Here is another question along the same lines. Is there a safe way to detach the file. Start a new file and move the old file somewhere for safe keeping?

Thanks Everyone!



-Mesa

 

[SQLBill]

Yes....

Two ways...

1. In Enterprise Manager, expand until you see the database. Right Click on the db, select All Tasks>Detach database. Find the files and rename the .ldf (log file) with a different extension (I use .old - mydb.ldf.old). Then right click on Databases and select All Tasks>Attach database. You will only pick the .mdf file. (If this last step causes problems, use Query Analyzer and run sp_attach_single_db - see item 2).

2. In Query Analyzer, run the stored procedure sp_detach_db (refer to the BOL). Rename the file. Then run sp_attach_single_file_db (again refer to the BOL).

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

What result are you trying to get? If you detach the database, and move the transaction log file off somewhere and reattach the database and create a new transaction log file, the old file is useless.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[MesaMarshall]

I just wanted to start off with a smaller file that I can manage instead of constantly working with a transaction log that I cannot get smaller then 1500 mb.

-Mesa

 

[Catadmin]

Make sure to do a complete database backup before you do any TransLog renaming or, as Denny mentioned, you will lose the data in that Transaction Log.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???