Database Size

lovelyk

Dear All,

my client's database size is around 450 GB which i want to reduce.

client is using NAV 4.0 sp3 with sql server 2005.

kindly advice

idiot

Try the jobs under Administration -> IT Administration -> Data Deletion followed by database shrink if needed, consequences wise it's up to you to find out... [-X [-X [-X [-X

Belias

no, don't start with data deletion: maybe the problem is somewhere else: did you check how large is your transaction log?

kriki

Do you also have a correct fillfactor (+ 90%) and a good maintenance plan to rebuild the indexes?
Do you have done some SQLtuning? A sideeffect of that is that the DB decreases in size.

Belias

and also upgrading to 5 sp will cut out sift tables (but you have to be very careful because of performance issues)

lovelyk

Dear Belias,

My database size is around 200 GB & Log is around 250 GB.

could you kindly guide me If I truncate the log the i will not harm the processing of my operation.

Regards

Belias

lovelyk wrote:

Dear Belias,

My database size is around 200 GB & Log is around 250 GB.

could you kindly guide me If I truncate the log the i will not harm the processing of my operation.

Regards

there are a plenty of topics in mibuso written by sql experts (not me ): search mibuso for "truncate log" or "full recovery model"...you should find the answer with explanation, too.
edit: truncating and shrinking log, should reduce it TO (not "BY") some MB (you can choose how big you want it, but take a look to other topics in order to find the best solution for you)

David_Singleton

idiot wrote:

Try the jobs under Administration -> IT Administration -> Data Deletion followed by database shrink if needed, consequences wise it's up to you to find out...

Wow nice advise. =; NOT!!!!

Although most of us will interpret this as sarcasm, you should have added a smiley or something, since lovelyk might take you seriously and do this.

lovelyk, note that the data compression routines are very dangerous, and you need to do extensive testing before you even think of running them. If you do run them, it may be impossible to upgrade you system later.

David_Singleton

lovelyk wrote:

My database size is around 200 GB & Log is around 250 GB.

could you kindly guide me If I truncate the log the i will not harm the processing of my operation.

lovelyk you seriously need to get some NAV/SQL expert to help you ASAP. What has your partner said about this? If your partner can not help you, then maybe try contacting Microsoft directly to see if they can help, Microsoft are doing a lot in India right now to help resolve issues like this with partners and customers.

idiot

David Singleton wrote:

...
Although most of us will interpret this as sarcasm, you should have added a smiley or something, since lovelyk might take you seriously and do this. ...

Ok done boss.

stryk

Hi!

With NAV 4.0 you should look into SIFT optimization first (see http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx)
this would erase a lot of SIFT records, thus reducing the db size (usually remarkably).

As you TLog is so big:
Are you running TLog Backups? If not you should. Or change the Recovery Model to simple.
Once the TLog is truncated you could shrink the physical file using a DBCC SHRINKFILE command (see Books Online about details)