October 7, 2008 at 9:34 am
Do I have to be offline to do a shrink server?
October 7, 2008 at 9:40 am
Shrink Server?? can you please explain a bit more clear 🙂
October 7, 2008 at 9:47 am
We are running out of space, I thought it could be a good ideai shrink databases and we could have more space
October 7, 2008 at 10:15 am
you dont have to be offline to shrink database 🙂
October 7, 2008 at 10:48 am
Will I loose anytime for users to use databases? I can loose anytime, my server needs to operate all the time. Can you shrink server or just db? Thank you:)
October 7, 2008 at 11:20 am
You can't shrink entire server all at once. You can shrink one database at a time (DBCC shrinkdatabase). But I would better recommend you one file at a time (DBCC shrinkfile). Your users will be still connected to a database, but due to high resouces comsumption by your shrink operations, server will be too slow which will increase response time. I recommend you to schedule it off-hours.
October 7, 2008 at 11:30 am
We don't have off hours. Our server for the web and we have to operate all the time. So, I am trying to see my options. What else can I do? Thank you
October 7, 2008 at 11:31 am
Why would you better recommend me one file at a time (DBCC shrinkfile) and not (DBCC shrinkdatabase)?Thank you
October 7, 2008 at 11:44 am
yulichka (10/7/2008)
We don't have off hours. Our server for the web and we have to operate all the time. So, I am trying to see my options. What else can I do? Thank you
Even if it's for the web and needs to be up 24x7, you should have some metrics that tell you that you have less users between x time and y time. These are operations you will want to run during times when there are less users. That's what Mark meant by off hours. If you don't have these types of metrics, you should be able to get them from your hosting provider, webmaster or even perhaps your firewall operators. If you routinely have 10 users between 3 and 4 am Sunday morning, that might be a good time to do these types of things. That may also be a good time to do your server migration as it will impact less users.
-Luke.
October 7, 2008 at 12:44 pm
Luke clearly explained what I mean by off hours.
I recommend DBCC shrinkfile over shrinkdatabase because you can do shrinking more granularly. As you have indicated above, you are runninng out of space. I see it as temporary problem, you can't solve it by constantly shrinking databases, you have to buy new hard-disks to solve it.
Shrinking in general is not recommended at all because it can add OS level fragmentation, it should be considered only as emergency tool in production environment, until new hard drive will arrive. With shrinking by file you may shrink one files more or less then the others, you don't need to shrink entire unused space.
October 7, 2008 at 12:55 pm
You can use sp_msforeachdb to execute the shrink database command against every database very easily if you are certain that is really what you want to do. There is an article at http://www.sqlservercentral.com/articles/Stored+Procedures/62868/ that explains how to use sp_msforeachdb.
Of course, the real question is being certain that is what you want to do. You do not need to take anything offline to do it, but it will create an enormous amount of locking and use a lot of server resources while it is running. Also, if your databases really do need the space they currently have it will cause more slowdown as they gradually reacquire it.
Depending on the details of your situation you may want to consider adding more harddrive space. Another thing to look at if you are using full transaction logging is that you have transaction log backups happening frequently enough. If you do not do transaction log backups often enough so that it can recycle the space in the transaction log, they will grow very large.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
October 9, 2008 at 5:40 am
I don't know if this what I want to do, I need an advise. I am trying to transfer servers from 1 location to another in total of 300 GB data. I thought it would be a good idea to shrink databases before I start moving them, so would speed up the process. Thank you
October 9, 2008 at 5:52 am
🙂
Mark Shvarts (10/7/2008)
Luke clearly explained what I mean by off hours.I recommend DBCC shrinkfile over shrinkdatabase because you can do shrinking more granularly. As you have indicated above, you are runninng out of space. I see it as temporary problem, you can't solve it by constantly shrinking databases, you have to buy new hard-disks to solve it.
Shrinking in general is not recommended at all because it can add OS level fragmentation, it should be considered only as emergency tool in production environment, until new hard drive will arrive. With shrinking by file you may shrink one files more or less then the others, you don't need to shrink entire unused space.
October 9, 2008 at 5:57 am
Can you specify the database details, size of mdf file and ldf file and what recovery model is that in 🙂 and which version of SQL you are using
🙂
October 9, 2008 at 8:31 am
Name Data Log Size Recovery
Database1 512 384 7168 Simple
Database2 3456 184488 Simple
This is just 2 db, do you need all of them?
2005 Enviroment
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply