error 21335 the new DBFile size must be larger than the current size

Expert,

I want to reduce the log file size from current space allocated 7000 mb to 3500 MB my DB size is 17GB. I tried to give space allocated is 3500 but it poped out error like
Error 21335 The new DBFile size must be larger than the current size.

Can you help?

Thanks!
yrcdba7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Faiga DiegelSr Database EngineerCommented:
You have to make sure that the log file is not less than 3.5 GB. You wont be able to reduce it to 3500 MB if the log file size is more than 3.5 GB
0
yrcdba7Author Commented:


My log file used 100 MB, space allocated 7GB, I want to reduce space allocated to 3.5 GB.
0
MikeWalshCommented:
It is likely the case that it was initially setup at a certain level (7GB) and you are unable to shrink it past it's initially set value. You can try with the DBCC SHRINKFILE command specifyin the size in MBs for the new logfile.

Depending on how you setup the log file you won't be able to shrink it as small as you like, basically the smallest you can get to is the size of the Virtual Log Files.

Try it with the shrink command. Please note that shrinking data and log files is not a normally recommended practice. It is best to pre-allocate to the right size you expect to need and leave it at that point. There are times, however, where perhaps your initial estimates are wrong or you are in a dev environment where shrinking is necessary.


0
MikeWalshCommented:
See these 3 Books Online articles for a better understanding:

DBCC SHRINKFILE: http://msdn.microsoft.com/en-us/library/ms189493%28SQL.90%29.aspx

Shrinking the Transaction Log: http://msdn.microsoft.com/en-us/library/ms178037%28SQL.90%29.aspx

Transaction Log Physical Architecture: http://msdn.microsoft.com/en-us/library/ms179355%28SQL.90%29.aspx

Run this command in your existing database: DBCC LOGINFO()

how many rows are returned? Of those rows, which row has/have the status of 2, out of curiosity?

If you have shrunk your log to the smallest it can go (i.e. you only have the initial two VLFs left, based on how you initially set it up) your options are a bit difficult/restricted. The best bet is really a new database and move the data into it with the log file setup to the proper size to begin with, unfortunately.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.