July 29, 2010 at 9:05 pm
As stated above, My database has no tables but is 86 GB used. My only thought is that it has something to do with Service Broker, but the transmission_Queue and conversation_endpoints are empty. Can anyone assist?
here is some of the output from sp_spaceused
Database_Size - 118490 MB
Unallocated space - 27242 MB
Thanks!
July 29, 2010 at 9:09 pm
Check whether Internal free space is there by right clicking on the database...
July 29, 2010 at 9:30 pm
Thanks for your reply. I'm not sure what you mean there. Right click and go to properties? When I look at shrink files, I can't shrink to any less than 85338
July 29, 2010 at 9:45 pm
sp_spaceused outputs two resultsets. The first, which you've quoted, includes the transaction log size. The second contains data size. Reserved is the total KB, er, reserved, and this is the sum of the other three columns - data (clustered indexes and heaps), indexes (non-clustered indexes) and unused (a tiny bit left over). What are these values?
Is it your transaction log or data file that is large? If you have no tables, it's likely to be your log.
July 30, 2010 at 4:16 am
Oh Ok, here's the rest of sp_spaceused
reserved- 88000048 KB
data- 76137600 KB
Index - 11880 KB
Unused - 11850568 KB
July 30, 2010 at 8:31 am
Just for inquiry purposes, was this a database that was recently created? Run this exec sp_helpdb 'database_name' it will tell you the size of the data and log files for the specific database.
July 30, 2010 at 8:36 am
No this database has been around for several months. below is the data and log size
113039360 KB
5222400 KB
One thing I've just discovered. My data file WAS 112 GB and it said I could shrink down to 86 GB. So I said to shrink the file to 100 GB. Now when I go to shrink again, it says I can shrink down to 75 GB.
July 30, 2010 at 8:42 am
I have shrunk the log before but never the database. If the recovery model was set to Full I would change it temporarily to Simple then perform the shrink which allowed me to reduce the size from a 13 GB to 128 MB, then I switched it back to Full. I suppose the same principals apply to the data file.
July 30, 2010 at 9:58 am
This is a long shot and I doubt it helps, but just in case -- have you tried:
DBCC UPDATEUSAGE (0);
GO
This has had impact on my free space and space used before.
July 31, 2010 at 9:10 am
If your database doesn't have any tables - then the easiest way to reclaim the space is to drop the database and create a new one.
If you have objects in the database that you need to keep, script them out before dropping - and run the scripts to recreate them in a new database.
If none of those are options (why not?) - then try a DBCC CLEANTABLE. It sounds like you might have dropped variable length columns that still have pages allocated.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 31, 2010 at 12:57 pm
These scripts will give you infomation on the size of your database files, including the used and unused space, and show the amount of space used by each table in a database.
Get Server Database File Information for all databases on a server
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058
Script to analyze table space usage within a database
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply