Database size trouble

Question

Database size trouble

Panks-913490

SSCrazy

Points: 2131
More actions
January 16, 2009 at 5:47 am

#383744

I have a SQL SERVER 2000 database which has 27 GB size.
The total space used by data in the tables is less than 3 GB.
When i ran sp_spaceused i got the below result:
database_namedatabase_sizeunallocated space
abce 27195.63 MB5455.61 MB
reserved dataindex_sizeunused
22234960 KB2522032 KB3176 KB 19709752 KB
Now, my question is what is this reserved space?
I have to shrink this database, what is the best way?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

"reserved space" is actually the amount of space allocated by objects in the database. So, if the database is not in production or you don't need any information from the logs then you can simply shrink the database, and take the full backup of the database to retain the log chain.

BTW, you need to run EXEC sp_spaceused @updateusage = N'TRUE'; to make sure the the results returned by "sp_spaceused" is accurate.

--Ramesh

joeroshan SSChampion Points: 10377 More actions · Answer 2

Right click the database in Enterprise Manager> all task >Shrink Database

You can also use DBCC command

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ramesh (1/16/2009)
So, if the database is not in production or you don't need any information from the logs then you can simply hrink the database, and take the full backup of the database to retain the log chain.

Shrinking the database does not affect the log chain in any way. That's truncating the log that you're thinking of.

Be sure to rebuild all of your indexes after you shrink the database. The shrink process badly fragments indexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

GilaMonster (1/16/2009)
Ramesh (1/16/2009)
So, if the database is not in production or you don't need any information from the logs then you can simply hrink the database, and take the full backup of the database to retain the log chain.
Shrinking the database does not affect the log chain in any way. That's truncating the log that you're thinking of.
Be sure to rebuild all of your indexes after you shrink the database. The shrink process badly fragments indexes.

Thanks again Gail, but I was thinking about the shrinking the logs. Does it break the log chain of the database?

--Ramesh

John.Sansom SSCertifiable Points: 7335 More actions · Answer 5

Hi,

Shrinking the log file does not alter the LSN or break the existing chain, thereby leaving the log sequencing intact.

Truncating the log file, on the other hand, does impact the current chain.

John Sansom (@sqlBrit) | www.johnsansom.com

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

Shrinking the log does not impact the log chain, but truncating the log does.

Log files don't shrink much unless you just did a log backup or truncated the log.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Ramesh (1/16/2009)
Thanks again Gail, but I was thinking about the shrinking the logs. Does it break the log chain of the database?

No. Shrinking logs does not break the log chain. Truncating the log breaks the log chain.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 8

Thanks guys, I've just verified it in Books Online...

--Ramesh