| Author |
Topic |
|
MasterP
Starting Member
6 Posts |
 Posted - 2011-12-29 : 15:04:26
|
| I read the following link, but wasn't sure if it is applicable to SQL 2005. I'm an extremely new DBA and have minimal experience, so excuse my newbness and ignorance.[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59972[/url]I have a database (Primavera P6) that is 90GB (Log 700MB) and had a systems guy ask me if we could shrink this, but the repercussions I've read about seem to advise against this unless in extreme circumstances. I appreciate your assistance in advance and let me know if you need more details.There is always a way... |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-29 : 15:33:31
|
| Top and Bottom of it is that if the database has grown wildly (e.g. by accident due to a specific IMPORT), or if there has been an UNUSUAL deletion of (e.g. "stale") data, then it is OK to do a one-time shrink.You may want to take some precautions not to shrink it to the minimum size possible (as causing it to then regrow to "comfortable operating size" may create some fragmentation), or you may just decide to use a Bog Standard Shrink command (i.e. shrink as tight as possible) and then let the database grow back to "comfortable operating size"If you start doing this more than once-in-a-Blue-moon then you need to do something more scientific; for a one off I wouldn't loose much sleep over it (but the "grow back" could cause some database slowdown which could, I suppose, be critical on a really busy database.) |
 |
|
|
MasterP
Starting Member
6 Posts |
Posted - 2011-12-29 : 15:44:50
|
quote:
Originally posted by Kristen
Top and Bottom of it is that if the database has grown wildly (e.g. by accident due to a specific IMPORT), or if there has been an UNUSUAL deletion of (e.g. "stale") data, then it is OK to do a one-time shrink.You may want to take some precautions not to shrink it to the minimum size possible (as causing it to then regrow to "comfortable operating size" may create some fragmentation), or you may just decide to use a Bog Standard Shrink command (i.e. shrink as tight as possible) and then let the database grow back to "comfortable operating size"If you start doing this more than once-in-a-Blue-moon then you need to do something more scientific; for a one off I wouldn't loose much sleep over it (but the "grow back" could cause some database slowdown which could, I suppose, be critical on a really busy database.)
First, thank you for your reply Kristen :)If I perform the proposed shrink, what should I set the autogrow size to? Currently it is set to 10%, which I hear is not a good setting. Looking in the Management Studio, I find the initial size for the MDF is 687MB and the LDF is at 93435MB. This seems strange now that I'm looking at it.I just found out that this server is running thin on HD space from our systems guys.There is always a way... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-29 : 15:53:28
|
| 10% is not good because as the databse grows it becomes "a large number". Better to use a fixed size. Leave it at 10% for now, see how much it grows over the next week / month and then form an opinion on a sensible amount. I try to set it so that our databases only grow once a month (say)MDF=700MB, LDF=100GB is definitely not a good ratio!Likelihood is that either there was a one-time runaway transaction, or LOG Backups are not being made at all, or not often enough.If you can find the physical log backup files have a look and see how large they are (you can interrogate the MSDB database to find that information if files on disk are not readily available to you). If you have a single 100GB log backup, once a week (probably early on Sunday morning) its probably the database maintenance that is causing it (there is a workaround for that, just ask if you find that that is the case).If all your log backups are "modest" sizes then shrink the LDF file to about twice the largest routine Log Backup that you found |
|
|
|
MasterP
Starting Member
6 Posts |
Posted - 2011-12-29 : 16:35:31
|
quote:
Originally posted by Kristen
10% is not good because as the databse grows it becomes "a large number". Better to use a fixed size. Leave it at 10% for now, see how much it grows over the next week / month and then form an opinion on a sensible amount. I try to set it so that our databases only grow once a month (say)MDF=700MB, LDF=100GB is definitely not a good ratio!Likelihood is that either there was a one-time runaway transaction, or LOG Backups are not being made at all, or not often enough.If you can find the physical log backup files have a look and see how large they are (you can interrogate the MSDB database to find that information if files on disk are not readily available to you). If you have a single 100GB log backup, once a week (probably early on Sunday morning) its probably the database maintenance that is causing it (there is a workaround for that, just ask if you find that that is the case).If all your log backups are "modest" sizes then shrink the LDF file to about twice the largest routine Log Backup that you found
The bolded part is the other way around, the MDF is 90GB and the LDF is 700MB. Does that still make for a bad ratio?@GailI'll be sure and read the articles you've posted, thank you :)There is always a way... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-29 : 16:39:11
|
| You said:"Looking in the Management Studio, I find the initial size for the MDF is 687MB and the LDF is at 93435MB. This seems strange now that I'm looking at it."So is it actually MDF 93435MB?If that's the case, then the log articles aren't as important. How full is that data file?--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-29 : 16:51:17
|
What Gail said  |
|
|
|
MasterP
Starting Member
6 Posts |
Posted - 2011-12-29 : 17:36:07
|
| Wow, sorry, I'm low on sleep and multitasking like a madman. I'll do some more reading and post any further questions I have, thanks again.There is always a way... |
|
|
|
MasterP
Starting Member
6 Posts |
Posted - 2011-12-29 : 17:40:13
|
quote:
Originally posted by GilaMonster
You said:"Looking in the Management Studio, I find the initial size for the MDF is 687MB and the LDF is at 93435MB. This seems strange now that I'm looking at it."So is it actually MDF 93435MB?If that's the case, then the log articles aren't as important. How full is that data file?--Gail ShawSQL Server MVP
Just saw your question after my last post....how do I check that?There is always a way... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-12-30 : 05:01:21
|
| Err, well you're the one who told us what the size is, then later you told us that the sizes were the other way around. So check where you looked the first time and see whether the data file is 700 MB with a 10GB log, or whether the data file is 10GB with a 700 MB log.--Gail ShawSQL Server MVP |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-12-30 : 09:46:02
|
quote:
I'm an extremely new DBA and have minimal experience, so excuse my newbness and ignorance..... stuff
From the thread you seem a little unsure of what's going on.1st priority is to have a functioning backup of the database that you know will restore. Do you have this? And you've tested that it will restore successfullyDon't worry about anything else (that isn't stopping you taking such a backup) until you have one.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
Database Shrinking