Solved

Space available appears as 0.00 MB in Sql Server 2000 database

Posted on 2009-04-08
15
1,587 Views
Last Modified: 2012-05-06
Hi all.

I have a database 'myDB' when I right click it and select properties. In the first tab 'General' it says the following:

Size: 2196.38MB
Space available: 0.00 MB

In the second tab 'Data Files': 1151 Space allocated (MB)
In the third tab 'Transaction Log': 1046 Space allocated (MB)

When I go to where the files are on my server, the .mdf file shows 1,178,368KB, and the .LDF file shows 1,070,720KB.

How can I have space available for this database?

Thank you in advance.
0
Comment
Question by:printmedia
  • 7
  • 7
15 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097397
Do you have your database setup to Auto Grow?  All you have to do is, on the second and third table respectively, increase the size to whatever you feel you need.  If the data segment fills up, it will cause the backup to occur in the log segment.  So it is likely that increasing your data segment to "an appropriate value" (which you must determine) will alleviate your log segment issues.
0
 

Author Comment

by:printmedia
ID: 24097535
On SQL Server 2000, where would I find the option for Auto Grow?, in the Options tab I see a setting for Auto Shrink.

Would it be the Automatically grow file options found on the Data Files and Transaction Log tabs?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24097730
I would advise against auto-grow.  Because it can mask problems in your database.  I am always in favor of monitor and manually grow.

But if you must.

In enterprise manager:
right click the db > properties
Data Files (Transaction Log) table.
Check the box at the bottom that says "automatically grow file" with the options you want.
0
 

Author Comment

by:printmedia
ID: 24097983
Ok. I gave my Data Files tab: 4000 Space allocated (MB) and Transaction Log tab: 2050 Space Allocated (MB)

Now the General tab shows:

Size: 6050.00 MB
Space Available: 1113.60 MB

And the file .mdf file shows: 4,096,000 KB
and the .LDF file shows: 2,099,200 KB

That .LDF file is pretty big, this database is used by 15 people non-stop. But how can I keep it controlled? If I have a scheduled job that does the following on a daily basis:

DBCC SHRINKDATABASE (N'myDB', TRUNCATEONLY) will this help?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24098073
you should NOT be shrinking your database.  That's why your free space is 0MB.  You need to be managing your backups of your database.

Here's an article that chapmandew has written about managing database backups and log file usage.

http://blogs.techrepublic.com.com/datacenter/?p=448
0
 

Author Comment

by:printmedia
ID: 24099089
Thanks for the article Brandon, it cleared up a lot.

As stated in the article, our database is in FULL recovery mode, so we have to start doing various transaction log backups throughtout the day.

We are in SQL Server 2000 and the article assumed SQL 2005, so my question is how would I do this in SQL 2000? Is it a scheduled job that runs let's say 4 times a day? What would the command in the job be?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24099356
The same basic principles for how and when to backup a database still apply.  Also for what recovery mode you should be in.  Since it would seem that you aren't doing transaction log backups now, do you even need to?  

There are several reasons to be in FULL recovery mode.  Replication and point in time restore being two of them.  If you aren't doing either, you may want to CONSIDER (I'm not saying you should or shouldn't) changing our recovery mode to simple.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:printmedia
ID: 24099927
I just checked the database properties again, and it went back to
Size 3399.00 MB
Space available:  0.00 MB

And in the Data Files tab it changed the Space Allocated (MB) back to 1349 MB. Even after I changed it to 6050 MB earlier.

Why is it cutting it down? I checked my scheduled jobs, and there hasn't been a job run in the past 4 hours.
0
 

Author Comment

by:printmedia
ID: 24099977
Actually, I looked at the maint\log folder, that log file is being backed up every 3 hours. So we did have a maintenance plan set, so that file grew...but that still doesn't explain why it keeps lowering the Space Allotted for the Data Files.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24099985
Either you executed "DBCC SHRINKDATABASE (N'myDB', TRUNCATEONLY) "

or

Autoshrink is enabled.
0
 

Author Comment

by:printmedia
ID: 24100006
Yes you are right, Autoshrink is enabled, so the trouble is not my Data Files but rather the transaction logs, and those are being being backed up every 3 hours. So maybe I should wait until the next time the Maintenance Plan runs which is at 3:00pm EASTERN TIME to see if the size of the log file goes down?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24100037
If auto-shrink is enabled, and you are in full recovery mode, the log will shrink after there is free space in it (when it is backed up).  The data segment will shrink when there is free space as well.

You can't manually expand a database that is set to auto-shrink.  Because it will just see that it can be shrunk and shrink it for you.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24100041
Translation: Don't enable auto-shrink.
0
 

Author Comment

by:printmedia
ID: 24100863
Thanks for all your help and patience Brandon.
0
 

Expert Comment

by:lwilliams1809
ID: 33693492
After turning off the auto-shrink AND NEXT doing   DBCC updateusage (@DBname) and refreshed my  view, everything worked fine. I'm  now able to see the Space Available 599.65 MB  vs. 0.00
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now