ERROR -> Cannot shrink log file requested size is larger 1

[GoodDay]

I have a Database (MS SQL 2000 Simple Recovery Mode)
then execute thats operations:
1 - DBCC SQLPERF(LOGSPACE) - log size for my db is 1727 MB
2 - BACKUP LOG <myDbName> WITH TRUNCATE_ONLY
3 - DBCC SHRINKFILE ( logFileName,1536) ---less then current size - 1727
and get error message:
Cannot shrink log file because requested size is larger than the start of the last logical log file.
Please - help ,How can I shrink log file

Thank you very much and sorry for poor English

 

[mrdenny]

Your log size may be out of date. Check the log size in Enterprise Manager.
You can also try to update the space used into within the database.

use {database}
sp_spaceused @updateusage = 'TRUE'

Your English is fine. It looks better than mine, and I'm a native English speaker.

Denny

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

http://www.mrdenny.com

(My very old site)

 

[GoodDay]

Thank you Denny (especially for a good words about my English)!
I try to execute your code and then DBCC SHRINKFILE
Error ("Cannot shrink log file......") appears again.


Thank you

 

[gradley]

I don't believe you can shrink a log or data file smaller than it's original size.

Since you are using the SIMPLE RECOVERY MODEL, what I would do is detatch the database and delete the log file. When you re-attach the database, it will prompt you to create the log file because it cannot locate the original one. It will then create this log file with the default size definition. I believe it uses what is defined in the model database.

Hope this helps!

 

[gradley]

Speaking of English, mine leaves something to be desired "DETATCH?" Where's spell-check when you need it?

 

[GoodDay]

Thanks, gradley!

 

[mrdenny]

If you use the task pad view in Enterprise Manager it will show you how much data is in the log file. You can shrink the file down to a couple of megs unless the file contains data.

The delete and reattach will work, however I recommend moving the log file not deleting it, in case it doesn't recreate the log file correctly.

Denny

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

http://www.mrdenny.com

(My very old site)

 

[GoodDay]

Denny , my log file data size (or more correctly logical size)
is 5.24 MB , physical size is 1727.44 MB
I made shrink from EM (Compress pages and truncate free space from the file). After that logical size decreased. But DBCC SHRINKFILE ( logFileName,1536) execution failed.
I don't understand - what happened....
Can you tell me about moving log file more distinctly (step by step)?

Thank you!!!

 

[gradley]

Hi GoodDay,

MrDenny's suggestion to save the log file first is very good advice in case something was to go wrong. You may want to try this on a less critical database first so that you can get comfortable with the process.

Here is what you need to do:

1. Detach the database (In EM, right click the database, select "All Tasks", Select "Detach Database"
2. Locate the log file .ldf
3. Physically move it to another folder or drive
4. Re-Attach database (In EM, right click database folder, select "All Tasks", select "Attach Database", locate the database .mdf
5. You should see a checkmark next to the .mdf file and a red X next to the log file.
6. Make sure the Attach As name and database owner name are correct.
7. Select ok.
8. You will be prompted with a message that SQL could not find the log file and would you like it to create a new one.
9. Select yes.
10. If the database was successfully attached you should be all set and can remove the log file from the system.

Good Luck!

 

[GoodDay]

Hi gradley!
Thank you very much for help and understanding !!!!!!!

 

[gradley]

Anytime!!

 

[GoodDay]

MrDenny - thank you 2!!!!!!

 

[mrdenny]

no problem.

Denny

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

http://www.mrdenny.com

(My very old site)