Making database smaller
-
Hi,
I know this is a common question, but I have tried everything. Basically, I have a SQL Server 2000 database that has an MDF file that is over 8.5GB in size. I know there is empty space in there, but I cannot make the database file any smaller. I have removed thousands of rows of data, rebuilt indexes to bring their sizes down, have brought the database into single user mode and run both:
DBCC SHRINKFILE(bogus_data, 10)
and
DBCC SHRINKFILE(bogus_data, 10,TRUNCATEONLY)
And it hardly got any smaller at all. Is there anyway to make this database smaller? It seems that even if I truncate tables and delete data from large tables the MDF file does not get any smaller, even if I shrink it.
This is causing problems because the backups are over 7GB and storing and I am running out of space.
Any help would be appreciated.
Thanks,
Joe
-
In most cases, your full backup file will be close to the size of the utilized part of the data file plus the utilized part of the transaction log. So, if your backup is 7GB, you can be fairly certain that your data file and transaction log file together contain very close to 7GB of data. In Enterprise Manager, right click on the database name, select view, and then task pad. At the bottom of the display, you'll see two bar graphs that show total file size, and amount utilized. This will let you know how much free space is in your database. You didn't mention your transaction log file at all. What is your recovery model (simple, bulk logged, full)? If full, are you taking transaction log backups? How big is your transaction log file, and how full is it? If you have the full recovery model, and you are not taking transaction log backups, your transaction log will continue to grow until it fills the disk.
Steve
-
Thanks for the info. I never new that was there in all the years I have been working with SQL Server. Apparently I need to find more data to remove.
Thanks,
Joe
-
I ABSOLUTELY LOVE this site...I didn't know of the taskpad existence either (which's why I "hang out" at this site - to see what I can learn)...what's even "cooler" about the taskpad is to go to the table info tab...it lists everything from #of rows, index size etc..
THANK YOU! THANK YOU! THANK YOU!
**ASCII stupid question, get a stupid ANSI !!!** -
It's strange, I can't seem to view the taskpad. When I right click on the database name under view I don't even have a taskpad option. I actually found if I click on View...New Window from Here, then if I right click on the database name I see the option for taskpad, but that just brings up a blank details window on the right hand side when I enable it. Any of this make sense?
Corey Pudhorodsky -
Yes - this is exactly what happened to me the first time except I didn't pay much attention to it...the funny thing is that when I right-clicked the first time I didn't even see the view option..I went to properties instead & "poked" around there...when I got out of the properties window and rightclicked again, there it was...view..and then taskpad...
Maybe all those experts out there have some explanation for this ?!?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
You need to left click on the database name first before the View menu appears. The reason is that it is not an action, but a view setting for the right hand side of the window which is based upon the current context. I had noticed this view a couple years ago, but had no use for that information at the time and since it is notably slower turned it off and had eventually completely forgotten about it. It was a nice surprise to rediscover this now that I can put it to use.
-
Thanks Aaron, That explains why the view didn't show up right away. Left clicking on the database name first did make the view option appear when right clicking after that.
Problem is that I still don't see any information after enabling the Taskpad. Here's a screen image as an example:
Corey Pudhorodsky -
I can't see your hard drive, so the image doesn't show, but the screen does take a few seconds to fill in (about 6 seconds for me). What I see in the mean time is some gray backround at the top for what will be a menu and three horizontal rules spaced out a bit vertically with a small, round, yellow button on the left side of the HR. The database name is on the upper right, but that about covers it. The details don't fill in for a while. Perhaps you need to just let it set for a minute or two.
-
Occasionally, Taskpad will throw an error, and ask if you wish to "continue running scripts on this page". It seems some installations of Enterprise Manager are more prone to this than others. It may be that you need to reinstall your client tools to get taskpad to populate the screen.
-
This is a known problem with taskpad. Change the view to detail, then taskpad again and it should be better. Hopefully a fix for this will be in SP4
Francis
-
I don't think this is your issue, but there is a condition where a transaction can get hung up and sql server thinks there is something going on and won't allow the database to shrink beyond a certain point. There is a query that will show you such 'undead' transactions and how to clear them, but I couldn't find the article on the MS site.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply