What is the best way of replacing the log file? 1

[aolb]

I have a SQL Server 2000 db that has a log file that I need to make smaller. I know I can’t shrink it, what I need to do is either replace the log file with another or create a new db and copy all the objects into in.

I have tried to create a new log file, detach the db, delete the original log file and then tried to reattach the db with the original data file and new log file. This didn’t work.

I am now considering creating a new db and copying all the objects over but I am not too keen on doing this as I always have problems with the roles and user objects.

What is the best way of replacing the log file?

 

[dbomrrsm]

Why cant you shrink it ? If you are going to get rid of it then you obviously dont need it so to shrink it would be the obvious answer.

If you are having trouble shrinking it you need to back it up then shrink it. You can then delete the backup.

DBomrrsm

 

[aolb]

I can't shrink it because when the log file grows beyond the space allocated, new disk space is allocated to the log file and you can't shrink the log file to a smaller size than the allocated disk space.

 

[RobertT687]

To clarify, you cannot shrink the file below its ORIGINAL allocated size. Your log file may have grown to say 1.2gb, but I'll bet it wasn't originally allocated the 1.2gb.

 

[aolb]

for example: I have allocated 100mb to the log file and 50mb for the file growth.

If the log file grows has less than 100mb of space used then I can truncate and shrink the log file and have a log file of 100mb.

If the log file grows to say 125mb then a further 50mb of disk space is allocated to the log file. when I truncate and shrink the log file the log file is cleared down but I still have 150mb of log file disk space allocated.

I've solved the problem by creating a new db and importing all the object into it.

 

[SQLBill]

You did it the hard way. But it worked. Next time try this:

1. Stop all transactions and KILL all connections except for yours.

2. Detach the database.

3. Rename the log file <dbname>.ldf to <dbname>.old

4. Attach the database using the command sp_attach_single_file_db This command will attach the .mdf (datafile) and create a new .ldf.

Refer to the BOL for more information on attaching and detaching the database.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[aolb]

Thanks I will try this next time. No doubt I'll have lots of opportunities