Please help me shrink the .ldf file 2

[newyorkny]

Hello, all:

I inherited a Sql Server 2000 project. I have a certificate in administration for this fine MSFT product, but I'm finding real life is different from tests!

In simple terms, could someone explain how I get control of a massive .ldf file?

I have a nightly shrink job in place, but it doesn't buy me much. The file is riiiiight up against the limits of my drive.

So, first step is that I shrink the .ldf, then I...back it up? Can some kind soul remind me in broad terms how I do this latter?

And then the size should be reduced?

Many thanks.

JJ

 

[SQLSister]

Read about backing up the log in Books Online. YOu need the Truncate_only command as part of your backup statment.

To prevent this from happening in the future, perform regular transacation log backups or set the database to simple recovery mode.

Questions about posting. See faq183-874

 

[newyorkny]

Thanks.

This is what I came up with based on the research. Does this seem a prudent course?

BACKUP LOG foo WITH TRUNCATE_ONLY

DBCC SHRINKFILE ( logical_log_file_name_for_db )

Thanks again and Season's Greetings.

 

[SQLBill]

By the way have you ever done a full backup? Do you do log backups? If not, then shrinkdatabase isn't going to do any good.

Anyways, immediately after you truncate the log, do a FULL BACKUP. Then run the shrinkfile command.

When a log is truncated it can no longer be applied to the full backup, so it's worthless for restore purposes. That's why you need to do a full backup...it starts the 'chain' again.

-SQLBill

 

[newyorkny]

Thanks SQLBill.

I inherited this project yesterday, so I have much familiarization before I could answer those questions off the cuff (and I am feeling a little sorry for meself since I never claimed I was a DBA )

So:

1) Truncate the log.
2) Perform full backup of the DATABASE.
3) Run shrinkfile.

Thanks. Sounds like a plan...unless I misunderstood anything you've said.

NY

 

[SQLBill]

Nope, you've got it.

Couple of things to consider.

How much data can you afford to lose? Do you need the ability to restore your database to a 'point-in-time'?

The purpose of backing up the Transaction Log (TL) is to do 'point-in-time' recovery. If a user deletes something at 1pm and you have TL backups, you can restore everything up to 1259am and the database will be back to the condition before the delete happened. If this isn't a concern, consider changing the RECOVERY MODE to SIMPLE. That checkpoints the log and shrinks it more often.

If you need to be able to recover to a 'point-in-time', then you need to do at least one Full backup. TL backups and Differential backups MUST be applied to a full backup. And they must be in the proper order. This is what I meant by a 'chain'. You doing a full backup after truncating will start a new 'chain' of backups. Then any TL backups you do apply to that Full backup. When you need to restore, you will restore the Full backup and then any TL backups in their order (using WITH NORECOVERY until the last backup is restored). Any time you do a Full backup, it 'breaks' the chain and all TL backups from that point apply to the new Full backup.

If you don't do frequent full backups and you do a lot of TL backups; consider doing Differential backups. They can reduce the amount of time you need for a Restore.

A Differential backup backs up the changes since the last Full backup. A TL backup backs up the changes since the last backup of any kind.

SO....lets say you do one Full backup a week, one Differential backup a day and 3 TL backups a day (TL at 6 am, noon, 6 pm. Diff at midnight).

Sunday - Full Backup
Monday - Diff + 3 TL
Tues - Diff + 3 TL
Weds - Diff + 3 TL
Thurs - Diff + 3 TL
Fri - Diff + 3 TL
Sat - Diff + 3 TL

On Thurs, you database crashes at 1pm.

If you don't do the DIFF backups, you have to restore the Full backup and 10 TL backups - a total of 11 restores.

If you do the DIFF backups, you restore the Full backup, the Weds (midnight) DIFF, and the Thurs 6 and noon TL backups - a total of 4 restores.

Which would you rather do????

-SQLBill

 

[newyorkny]

I'd rather the database didn't crash?

Thanks for the detailed gouge. Very helpful.

I will do something like a Full once a week on SUN, just as you suggest.

Ok....I'm going in. Cover me...NY

 

[newyorkny]

Hey Bill, Sister or others:

How do I get the logical_logile_name as in the below?

DBCC SHRINKFILE ( logical_log_file_name_for_db )

I did EXEC sp_helpfile but I don't think it gave me the correct logical log file name! It was something quite generic and the sizes didn't match.

I did the various steps, then shrunk the LDF through the ALL TASKS option on right-clicking the database.

It didn't really shrink much, even though I successfully truncated and backed-up. I used the first truncate option when doing the shrinkfile.

Can I constrain the logfile to a smaller space and thus reclaim the wads of terroritory the doggone thing is taking up?

Any thoughts on these disjointed questions? Many thanks.

N

 

[SonOfEmidec1100]

Right click the database, select properties and look at the filename on the TransactionLog tab.

My understanding is that you cannot shrink the transaction log to a size smaller than the larger of
a) the default log size (as per the Model database)
b) the original size the transaction log was created.

The way round this is to force the ldf file to be recreated.

You can get the names and locations of the files from the appropriate properies tabs.

1 EXEC sp_detach_db 'dbname', 'True'

2 Rename the ????.ldf file (Just in case, delete it later)

3 EXEC sp_attach_db @dbname = 'dbname'),@filename1 = '?????.mdf'

A new ldf of the default size will be created.

 

[newyorkny]

Alright. Thanks, Son Of!

I'll try it out first thing tomorrow!

Best, NY

 

[newyorkny]

Thanks, SonOf.

I like the clear directions.

Merry Christmas to all!

NY

 

[nigelrivett]

http://www.nigelrivett.net/TransactionLogFileGrows_1.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.