May 2, 2011 at 2:10 am
Hi all,
I have problem with database size. I have one database about 330GB(4 datafiles + 2 logfiles). I archived a lot of old records to archive database and now I have in every datafile about 50% free space. But I am not able to shrink it via GUI nor command. Problem is , that database is still being used. I would appreciate any help how to decrease size of db. Thanks a lot in advance.
Boris.
May 2, 2011 at 10:21 am
I had a similar problem one time and although I won't guarantee that my solution will work for you here it is:
I created a new file/filegroup, then created a new primary key located on the new filegroup, moved all of my indexes to the new filegroup etc, then once my old file group was empty I dropped the old file.
In my case at least part of the reason I was doing this is because the filegroups held partitioned data that was now read only, and I was making the index with a fill factor of 100, instead of the 90% that I had used previously.
However, all of that being said, why can't you shrink? Is the option grayed out or something? Or is it just taking a really really long time? Also I typically do my shrinks one file at a time when possible, this might help you out also.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
May 2, 2011 at 9:16 pm
boris.kovalcik (5/2/2011)
Hi all,I have problem with database size. I have one database about 330GB(4 datafiles + 2 logfiles). I archived a lot of old records to archive database and now I have in every datafile about 50% free space. But I am not able to shrink it via GUI nor command. Problem is , that database is still being used. I would appreciate any help how to decrease size of db. Thanks a lot in advance.
Boris.
I know it might look you odd but i would want to know why do you want to shrink your database?
Do you have disk space problem? Because database size is never a problem till it is properly managed and till you have sufficent disk space..
Moreover, it is always good that you have free space in your data files. it will ensure that data file will not grow in btw your Transact-SQL. 330 GB is not a very big database to manage.
On the otherside , if you see there are many disadvantages of shrinking, It causes a lot of fragmentation and degrades your query performance and also running a shrink command consumes huge system resources especially it hurts your IO subsytem badly..
So, it is trade-off between free disk space and performance.
Regards..
May 3, 2011 at 6:42 am
If the database is not in Simple Recovery mode, then you must do a transaction log backup before you can shrink the files. You should have regularly scheduled transaction log backups in between your full database backups to allow for point in time recovery as well as to prevent the transaction log from getting too big.
You will also want to rebuild your indexes after shrinking (which will cause the database to grow a bit).
--
JimFive
May 3, 2011 at 6:46 am
sachnam (5/2/2011)
boris.kovalcik (5/2/2011)
Hi all,I have problem with database size. I have one database about 330GB(4 datafiles + 2 logfiles). I archived a lot of old records to archive database and now I have in every datafile about 50% free space. But I am not able to shrink it via GUI nor command. Problem is , that database is still being used. I would appreciate any help how to decrease size of db. Thanks a lot in advance.
Boris.
I know it might look you odd but i would want to know why do you want to shrink your database?
Do you have disk space problem? Because database size is never a problem till it is properly managed and till you have sufficent disk space..
Moreover, it is always good that you have free space in your data files. it will ensure that data file will not grow in btw your Transact-SQL. 330 GB is not a very big database to manage.
On the otherside , if you see there are many disadvantages of shrinking, It causes a lot of fragmentation and degrades your query performance and also running a shrink command consumes huge system resources especially it hurts your IO subsytem badly..
So, it is trade-off between free disk space and performance.
Regards..
+1 000 000.
The only realy reason you'd do a shrink here is #1 you're short on HD AND you'll never need that space again for that database. You said you just did a big purge, but have you stop doing inserts in the DB, will you be purging monthly to keep the size under control (well actually to answer a business need).
May 3, 2011 at 6:50 am
I'll make it plus 2 million to Ninja's + 1 million 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 3, 2011 at 6:52 am
TheSQLGuru (5/3/2011)
I'll make it plus 2 million to Ninja's + 1 million 😀
I'm all in :hehe:.
May 3, 2011 at 7:28 am
Ninja's_RGR'us (5/3/2011)
TheSQLGuru (5/3/2011)
I'll make it plus 2 million to Ninja's + 1 million 😀I'm all in :hehe:.
Came over the top on me, eh?? Oh, I yield to the master! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 3, 2011 at 7:34 am
TheSQLGuru (5/3/2011)
Ninja's_RGR'us (5/3/2011)
TheSQLGuru (5/3/2011)
I'll make it plus 2 million to Ninja's + 1 million 😀I'm all in :hehe:.
Came over the top on me, eh?? Oh, I yield to the master! 😎
No way, I'm not MCM material. I'm probably 10 000 hours behind Gail and she still has lots to learn to be MCM :w00t:.
I still win that hand... which is nice!
May 4, 2011 at 3:01 am
You can try following scripts to shrink your database which I am using. However I only work with database having one ldf and one log file in MSSQL 2008.
Replace DB NAME with actual database name.
Alter Database [DB NAME] Set Recovery Simple
Alter Database [DB NAME] Set Recovery Full
DBCC SHRINKDATABASE ([DB NAME], 10)
May 4, 2011 at 3:04 am
navneetpatel (5/4/2011)
You can try following scripts to shrink your database which I am using. However I only work with database having one ldf and one log file in MSSQL 2008.Replace DB NAME with actual database name.
Alter Database [DB NAME] Set Recovery Simple
Alter Database [DB NAME] Set Recovery Full
DBCC SHRINKDATABASE ([DB NAME], 10)
Realy bad advise..
However I only work with database having one ldf and one log file in MSSQL 2008.
Is there any difference between LDF file and a log file..?
I hope it is typo..
May 4, 2011 at 5:20 am
navneetpatel (5/4/2011)
You can try following scripts to shrink your database which I am using. However I only work with database having one ldf and one log file in MSSQL 2008.Replace DB NAME with actual database name.
Alter Database [DB NAME] Set Recovery Simple
Alter Database [DB NAME] Set Recovery Full
DBCC SHRINKDATABASE ([DB NAME], 10)
Assuming you'd want to go the shrink route (especially with the datafiles) you'd need to plan a 20-30 hours window >>>
Full Backup,
test backup to make sure it's valid (restore + checkdb),
take the tail log backup (leaving db online)
stop log backup jobs
set to simple
shrink db file to reasonable amount. You may need to do it in a few smaller runs so you don't completely lock out the db
reindex the whole db
--may need to resize the log files after this
back to full
retake full backup
restart log backup jobs
retest new backup.
We have a decent san over here and doing all that on our 18 GB DB would take at least 90 minutes. You do the math for 300 GB.... it would take 25 hours on my system. Maybe yours is faster.
May 4, 2011 at 8:18 am
You can try following scripts to shrink your database which I am using. However I only work with database having one ldf and one log file in MSSQL 2008.
Replace DB NAME with actual database name.
Alter Database [DB NAME] Set Recovery Simple
Alter Database [DB NAME] Set Recovery Full
DBCC SHRINKDATABASE ([DB NAME], 10)
This is VERY BAD.
First, If you have to "SET RECOVERY SIMPLE" then this implies that you are not doing sufficient Transaction log backups. If you're not doing Transaction Log backups every hour why is it in FULL recovery mode anyway?
Second, Shrinking the files regularly causes two major performance problems:
1) Fragmentation of the data which slows down EVERYTHING
2) File Growth which STOPS everything.
You need to determine how much space you really need for your data and then allocate that much plus some growth factor.
The only time you should really need to shrink a database is when you have done very large one-time data manipulation. In the case of the Original Poster he has deleted ~50% of a 300G database. That may warrant a shrink IF space is an issue. Another possiblity is when a very large data import occurs, such as when converting data into a new system causes the log file to grow to a size that isn't warranted by normal use. Then you should shrink the log file only.
If you find yourself shrinking databases on even a semi-regular basis you need to stop it and fix your system.
An interesting demonstration is to take a reasonably sized database, shrink it and then reindex it. The database will grow back almost to its original size.
--
Jimfive
May 4, 2011 at 8:29 am
James Goodwin (5/4/2011)
You can try following scripts to shrink your database which I am using. However I only work with database having one ldf and one log file in MSSQL 2008.
Replace DB NAME with actual database name.
Alter Database [DB NAME] Set Recovery Simple
Alter Database [DB NAME] Set Recovery Full
DBCC SHRINKDATABASE ([DB NAME], 10)
This is VERY BAD.
First, If you have to "SET RECOVERY SIMPLE" then this implies that you are not doing sufficient Transaction log backups. If you're not doing Transaction Log backups every hour why is it in FULL recovery mode anyway?
Second, Shrinking the files regularly causes two major performance problems:
1) Fragmentation of the data which slows down EVERYTHING
2) File Growth which STOPS everything.
You need to determine how much space you really need for your data and then allocate that much plus some growth factor.
The only time you should really need to shrink a database is when you have done very large one-time data manipulation. In the case of the Original Poster he has deleted ~50% of a 300G database. That may warrant a shrink IF space is an issue. Another possiblity is when a very large data import occurs, such as when converting data into a new system causes the log file to grow to a size that isn't warranted by normal use. Then you should shrink the log file only.
If you find yourself shrinking databases on even a semi-regular basis you need to stop it and fix your system.
An interesting demonstration is to take a reasonably sized database, shrink it and then reindex it. The database will grow back almost to its original size.
--
Jimfive
Actually it'll grow bigger than the original size... takes more than 1X index size to reindex....
May 5, 2011 at 2:35 am
This is VERY BAD.
First, If you have to "SET RECOVERY SIMPLE" then this implies that you are not doing sufficient Transaction log backups. If you're not doing Transaction Log backups every hour why is it in FULL recovery mode anyway?
Perhaps more importantly, setting the recovery mode to Simple like that will break the transaction log backup chain, making it impossible to do a point-in-time restore until the next full or differential backup is taken. That's assuming he's actually taking any transaction log backups, which, as you say, is perhaps not very likely!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply