Category: SQL Server

31 Aug 2016 03:22

Joined:	26/07/2008
Level:	Gold
Points:	855

Points:	2

How many times the rebuild and reorganize can be done?

Hi,

I want to know, how many times we can rebuild and reorganize the database, as our database size is around 25GB. Also, is it advisable to Shrink the database with these two processes all the time?

Regards
Vivek Sharma

#767715

31 Aug 2016 03:58

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	4

Hi
you can try this Query for shrink Database



DBCC SHRINKDATABASE (UserDB, 10);  
GO  

USE YourDB
GO
ALTER DATABASE YourDB SET RECOVERY SIMPLE
DBCC SHRINKFILE(2,1)  --Shrink the log file to 1MB
ALTER DATABASE YourDB SET RECOVERY FULL
GO

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE (MyDatabase_Log, 5)

GO

ALTER DATABASE MyDatabase SET RECOVERY FULL

GO

you can go through Below link

"help.fogcreek.com/8686/how-to-shrink-sql-server-transaction-logs"

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#767717

31 Aug 2016 04:18

Joined:	26/07/2008
Level:	Gold
Points:	855

Points:	1

Sir, kindly read the question.

#767718

31 Aug 2016 04:20

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	4

Re-build and shrink will not run at a time as shrink will re-build and identify un-used spaces.
to shrink it do the following thing.
First you need to identify OLD records and take its backup and delete them from database, and then A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
Delete LDF file if it is heavy, it will automatically re-create it again, use 'DBCC SHRINKDATABASE ' command or login to SSMS (MS SQL Server Management Studio.) and Right-Click the DB -> TASKS -> SHRINK -> FILES -> select the LOG file
I think take a Archive backup and Delete some OLD record will do the trick, cause shrink will only make a certain space for you not all.

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

How many times the rebuild and reorganize can be done?

About dotnetspider.com

Quick Links

General

Services