June 17, 2009 at 10:34 am
Hi,
I have one share point database size as below:
WSS_Content 18162.1875 1268.75 16893.4375
WSS_Content_log 9870.875 38.0703125 9832.8046875
Because of some reason, from this production database, we deleted lot of data and now the free space is 16 GB for data file and 9GB for log file?
Can I shrink this production database like 5 GB in data file and 1 GB in log file??
will it effect any thing? Is it recommended?
June 17, 2009 at 10:47 am
right click Database->tasks->shrink->files or
USE [WSS_Content]
GO
DBCC SHRINKFILE (N'WSS_Content_log' , 0, TRUNCATEONLY)
GO
DBCC SHRINKFILE (N'WSS_Content' , 0, TRUNCATEONLY)
GO
first try this and let us know if it works:(replace the files names with your log file and db file name)
Maninder
www.dbanation.com
June 17, 2009 at 12:36 pm
You CAN shrink it. Doesn't mean you SHOULD shrink it. Shrinking causes fragmentation.
Leave it alone and as your database grows again the space is already allocated, so you don't lose performance having to re-grow it.
June 17, 2009 at 1:07 pm
What if the following steps are performed
1. Shrink database to required size (include enough room to rebuild all indexes)
2. Rebuild all indexes
3. Backup database
4. Drop the database
5. Create new database with the new size (ensure no file system level fragmentation)
6. Restore the database from step 3 backup
In this case do you end up with a fragmented database (index and filesystem) or should it be ok?
June 17, 2009 at 1:31 pm
SA (6/17/2009)
What if the following steps are performed1. Shrink database to required size (include enough room to rebuild all indexes)
2. Rebuild all indexes
3. Backup database
4. Drop the database
5. Create new database with the new size (ensure no file system level fragmentation)
6. Restore the database from step 3 backup
In this case do you end up with a fragmented database (index and filesystem) or should it be ok?
You could eliminate step 5 - because a restore will overwrite the existing files anyways. Other than that, this process will reduce index and file fragmentation.
If you have the time - it is not a bad process to go through.
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
June 17, 2009 at 1:40 pm
I agree, though would again recommend discrete usuage.
I recently purged ~ 26GB staging data from 31 GB database and didn't want to leave 26GB of free space in the database and used the above steps.
June 17, 2009 at 1:46 pm
SA (6/17/2009)
I agree, though would again recommend discrete usuage.I recently purged ~ 26GB staging data from 31 GB database and didn't want to leave 26GB of free space in the database and used the above steps.
Oh - absolutely. This kind of process should *never* be scheduled on a regular basis. This should *only* be done after some extraordinary event has taken place.
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply