[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

Error when trying to shrink my Database in Enterperise Manager

Hi,

I am getting this message when trying to shrink my database using Enterprise Manager:

Error 3140: could not adjust the space allocation for file 'MT_Data;. DBBC execution completed.
If DBCC printed error messges, contact your system administrator.

I am worried if my database has not corrupted. Please help to fix it.

Thanks.

Nick
0
NickHoward
Asked:
NickHoward
  • 6
  • 4
1 Solution
 
MikeWalshCommented:
Nick,

What version of SQL are you running? That was a common error in SQL 7.0 with a SP version less than SP3. It was resolved in SQL Server 7 SP3.. Follow this link

http://support.microsoft.com/kb/254253


This is not necessarily a sign of corruption but a symptom of the way the shrink process works and the way data is stored.

To check for corruption first take a full backup of your database (just to ensure a safety net), then issue a DBCC Checkdb on your database.. Look up the syntax in Books Online for more information about the process, but in Query Analyzer you can run, "DBCC CHECKDB" on your database and check for corruption..

I doubt in your case corruption is the problem. Look at the KB article, tell me what version you are in, what service pack you have, and run the DBCC CHECKDB (after looking up and understanding the process in Books Online)
0
 
NickHowardAuthor Commented:
I am using SQL2000 with Service Pack 3 and ran DBCC CHECKDB with results:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'MT'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Nick
0
 
MikeWalshCommented:
Nick,

Well the latest service pack for SQL Server 2000 is SP4, try and load that.. You should be on it anyway as there are some crucial security fixes, that may also fix this problem.. You can get to the Sp link through selecting downloads at www.microsoft.com\sql 

Your DBCC results tell you that there is probably -not- any corruption in your database. This error is not indicating corruption, it is indicating that for some reason you are unable to shrink your database. Have you searched the web for the error number yet?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MikeWalshCommented:
Nick - which method are you using to shrink.. Are you right clicking, selecting all tasks --> shrink then going to individual files?

If so do any files shrink? Which ones don't?
0
 
MikeWalshCommented:
Nick,

A few more things...

I have looked this up and can't seem to really find a consistent cause.. What will be good to know are the following:

1.) Which files are you trying to shrink (data or log or just the entire database, have you tried individual files do any work?)

2.) What is the total and used size of the file you are trying to shrink?

3.) How much free space is on the drives?

4.) Do you have a lot of Text/Image columns in your database? How many?


Then a last comment.. Why do you need to shrink the database? Typically you want to keep your database at it's current size. Doing a lot of shrinking and autogrowing and shrinking and autogrowing, etc. leads to faster data/index fragmentation. It is actually best to keep your data file at a good size to allow the logical file to continue to grow without beating on the physical size. Every time a file growth (data or log) occurs, a cost is also dealt out.. Each file growth takes I/O and CPU resources away from other database activities.

If free space is a concern, that is one thing, and you should look at better allocating your physical space, but if you are just trying to trim your files when free space on disk is not at issue, it is better to just let the file stay at it's currnet size.
0
 
NickHowardAuthor Commented:
Mike,

Let me install SP4 and if it does not solve the problem, I will answer all your questions in order you asked.

Thanks.

Nick
0
 
MikeWalshCommented:
no problem.
0
 
NickHowardAuthor Commented:
Mike,

After installation of SP4, there is no more error appearing. However the purpose to shrink my log file is not solved. I tried from EM both SHRINKING complete database MT and also on File level my log file MT2_log.LDF which is 29GB.

I want to cut this file as it is taking lot of my disk space.

Thanks.

Nick
0
 
MikeWalshCommented:
Nick -

So SP4 allowed you to shrink without error, but not far enough??

It sounds like you have a large log file.. What recovery mode are you in? (right click on the database in EM and go to properties then options)..

If it is Full, are you taking Transaction Log backups on a regular basis? If not, then I would suggest doing so, as not taking them when you are in Full means that your transaction log will continue to grow.

If you don't need to be in Full Recovery mode (because you don't care about up to the minute recoveries) you can change to Simple recovery mode and just rely on Full Backups..

The reason you can't shrink your TLOG right now is probably because there is data still using the full space. What you need to do is first backup the log then do your shrink. If you don't need the data in there for log backups, take a full backup and then issue the following in query analyzer:

BACKUP LOG [databaseName] WITH TRUNCATE_ONLY.. this will free up the used space in the log.. Then go do your shrink.
0
 
NickHowardAuthor Commented:
Worked as desired.

Thanks Mike.

Nick
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now