May 12, 2010 at 4:35 am
Hi All,
I've taken over a SQL 2005 Enterprise Edt server. It has a database that has been created with the AUTOGROWTH disabled and it's allocated an inital size of 75 gb. I've had a look at this db and it has 99% free space. The db is in SIMPLE recovery mode. I need to create a backup for it . The disk that I need to save the backup doesn't have alot of free space available. I was thinking that I need to shrink the database to free up some space on the disk to allow me to start doing Full backups. I've read many forums that say one should not Shrink a database has it causes fragmentation. However I see no other way of doing this. Adding in another disk is not an option. I was thinking after the Shrink I would rebuild all indexes.
Any feed back on this would be greatly appreciated.
Thanks
Denesh
May 12, 2010 at 5:33 am
Just my 2ct.
Leave your db as it is until you are 100% sure it is actually over sized !
(Someone will have put it to that size for a reason, figure out who and why)
Your backup file will only contain the used pages of your db. (and some ongoing transactions information of the dblog)
So a 75GB db which only uses 1000 pages worth of data will only generate a backup file containing these 1000 pages and some backup related data, totaling to a size of maybe even less than 10MB.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 12, 2010 at 5:45 am
There's no need to shrink to reduce the backup size. A backup only backs up allocated extents. If 99% of the db is empty space, then the backup will be around 1% of the size of the database.
Any idea why it's got so much unused space?
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
May 12, 2010 at 6:00 am
Thanks all for the feed back.
As to why all that unused space...not sure. This db is for SCOM. If I'm not mistaken, SCOM allows you to maintain the database and delete data as per needs. To my knowledge they are not keeping a lot of information for a long period of time
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply