May 10, 2011 at 6:32 pm
I have a database that was about 845MB 4 months ago. It was set to Autogrow at 10%. The database is up to 11GB now. I've adjusted the autogrow to 100MB. Is there anything I can do about the size at this point? When I run sp_spaceused, it's showing the database_size=16935MB and unallocated space=10898MB.
Thanks for your help!! I obviously need it!
May 10, 2011 at 7:02 pm
Sounds like a process went berzerk on you. Check your transaction log and recovery model settings. Also check your jobs and processes to see if there is anything that does inserts into the database on a regular basis.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 10, 2011 at 9:11 pm
As Jason Mentioned, Check the Recovery Model of the database. If the Recovery mode id FULL and if you don't take Log backups or regular Log Backups , then the Size of the log grows very large.
Check the the amount of free space in the Transaction Log file and see if you can shrink the log file. However shrink should not be done frequently as it causes fragmentation. You can do it on a one time basis.
Also try to set the auto growth option to 100 MB OR 200 MB etc depending on the user activity, not 10% which may result in space issues.
Thank You,
Best Regards,
SQLBuddy
May 10, 2011 at 10:52 pm
First shrink the database to release the space for Operating System at free work hours and truncate the size of log ,then this process will release the unallocated space
Shrink Database process take time and performance must be degrade at the time of shrink
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 10, 2011 at 11:48 pm
aehoward73 (5/10/2011)
I have a database that was about 845MB 4 months ago. It was set to Autogrow at 10%. The database is up to 11GB now. I've adjusted the autogrow to 100MB. Is there anything I can do about the size at this point? When I run sp_spaceused, it's showing the database_size=16935MB and unallocated space=10898MB.
Do you expect the DB to continue to grow at that rate? If so, leave that space where it is.
It does no harm to have free space in the data or log file, in fact SQL needs free space for regular operations (including index rebuilds). Unless you are critically low on disk space as a result (space something else needs), leave it for now, monitor the data growth over a month, monitor log file usage over a month then you'll be in a far better position to decide whether or not to shrink.
Shrinking is generally not recommended. It causes massive fragmentation requiring index rebuilds, if done too far it's just going to make the DB grow again immediately causing delays as the files are allocated
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 11, 2011 at 3:02 pm
Syed Jahanzaib Bin hassan (5/10/2011)
First shrink the database to release the space for Operating System at free work hours and truncate the size of log ,then this process will release the unallocated spaceShrink Database process take time and performance must be degrade at the time of shrink
Once again, it looks like you have recommended an approach to solving the problem without detailing the risks or issues with this.
First, if you decide that you really are not ever going to use that space again and really do need to shrink the database, perform a one time shrink of the data file using DBCC SHRINKFILE. When you shrink the file, make sure you leave enough space in the data file to rebuild indexes and for future growth - in other words, do not shrink down to as small as you can get it.
Now, if you decide to shrink the file - understand that this will cause all indexes to become highly fragmented. Once the shrink is complete, you will need to rebuild all indexes in the database (which could end up growing the data file some).
Second, only shrink the log file once you have determined that an unusual event has taken place where the log file was expanded beyond what is needed for normal operations. This assumes that you are already managing the transaction logs correctly (e.g. frequent transaction log backups for databases in full/bulk_logged recovery models).
You should not shrink either file if that space is just going to be used again.
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply