How do you shrink a database (whether Navision or SQL)?

kishi_g

How do you shrink a database (whether Navision or SQL)?


:-k

Thanks & Regards,
kishore.

joycopl_me

If you want to shrink the space on database, you try to use alter database(NAV or SQL also). if you want to shrink data on database, how can you do that?

PEzechiels

Hi,

What you can try is to make a databackup in navision.
After the backup is done, you create another database ,expand this database to how big you want it to be and then restore the backup in this database.

patrick

ssingla

SQL database can by trunicated to reduce the used size of the database by deleting the old transaction log. Further there are lot of delete functionalities in Navision for removing old data. Data in the table can be compressed also.

kishi_g

How to use alter database. Could you please explain ...

joycopl_me

Hi,

You can go to SQL server --> All tasks --> shrink database (if on the NAV you can not change the Log file) try it

bbrown

Why do you need to shrink your DB?

Mbad

Probably to make development database smaller for transfering purposes, since you dont need any data but setup?

bbrown

Shrinking does not remove any data. It just reduces the free space.

headley27

I use this script (or a variation) in SQL Query Analyzer a lot but pretty much exclusively on development databases.

USE mydatabasename DBCC SHRINKDATABASE (mydatabasename) DBCC SHRINKFILE(mydatabasename_log, 10) BACKUP LOG mydatabasename WITH TRUNCATE_ONLY

You can search the web for information about the following functions to learn about usage:

DBCC SHRINKDATABASE DBCC SHRINKFILE BACKUP LOG

******************************************************************************************
BE CAREFUL !!!
Use of these functions can produce excessive logical fragmentation and can SEVERELY affect performance.
******************************************************************************************

If you must shrink a production database, you should always rebuild indexes afterwards (DBCC DBREINDEX). You need to keep in mind that DBCC DBREINDEX is an offline process as it can take some time to run and will lock tables.

Another consideration when using DBCC DBREINDEX is what fill factor to use. The fill factor will depend on the read to write ratio for a given table. The more write intensive the table is, the higher the fill factor.

There is a great document called 'Tuning Navision For Better Performance' that explains what fill factors to use. See http://www.mibuso.com/dlinfo.asp?FileID=356.

Alternately, you can use DBCC INDEXDEFRAG to defragment the indexes. This is an online process, but it can cause performance issues while the command is running and it does not work near as well as DBCC DBREINDEX does.

But again, as others have mentioned, it depends on WHY kishi_g wants to shrink a database.

Hope this helps.

DenSter

The production database file should never be shrunk. You should have a dedicated drive for the data files anyway, so there is absolutely no problem if the data files take up most of the space, even though it is not completely used. By keeping the data files one size as much as possible, you prevent fragmentation, which can really affect your performance, like Alex said.

There's really no reason to shrink data and log files. Performance is not affected by the size of those files.

escultor1999

Yes, but what happen with the log. I have some log of 33 GB, and i would like to reduce this because the backup is very big.

i had been thinking to delete it with
BACKUP LOG DB_Name WITH TRUNCATE_ONLY

and reduced it with
DBCC SHRINKDATABASE (DB_Name , TRUNCATEONLY)

but then, what happen? It is possibly that in the future i obtein (in lees than week) the same size. and i will loos performance during this week because autogrowing option of file.

is it possible to put no autogrowing option in the file and truncate the file with a backup of transanction log every 1 hour?

Marije_Brummel

Never shrink a logfile smaller than the normal working size.

If the working size is to big, then you probably have some transaction in your database that causes it.

It can be an interface or posting process.

Watch you logfile and see what happens to try to find out or hire profesional help.

Good lock.

headley27

Using SQL Enterprise Manager, I created a data maintenance plan to backup my transaction log to a secondary drive at regular intervals (approx 300-500 MB per day).

I also backup my database (12GB) once a day to the secondary drive.

The database backups and log files are archived to tape approximately once per month and I keep the most recent 2 weeks readily accessible at anytime.

This way I can restore a database to any point in time that I want, right down to a specific millisecond. All I have to do is restore the previous database backup and then 'replay' the logs (from the point of the database backup until the point in time that I would like to restore to).

I use these backups all the time for testing and troubleshooting purposes as well as for looking up old transactions that are no longer in the database.

If you don't have the logs you can not restore to any point in time, you are limited to your database backup only which has a set point in time.

If you are backing up your log files regularily, your transaction log will consist mostly of free space. For example, my transaction log only has 64 MB used right now (my last transaction log backup was approximately an hour ago). You can then use DBCC SHRINKFILE to remove the free space ... but as Mark said you should 'Never shrink a logfile smaller than the normal working size'.

Saying that, I believe TRUNCATE_ONLY won't do much of anything for you if you haven't done any backups of the logs. This is because you will have very little free space. BACKUP LOG DB_Name WITH NO_LOG will BUT you will lose the logs completely.

I am fairly proficient with SQL Server but I am by no means a SQL Expert so you may want to confirm what I say is 100% true. I believe that it is, but it's been a while since I setup my database maintenance plan.

I hope this helps.

headley27

This is a good article that explains what happens:
http://doc.ddart.net/mssql/sql70/bkprst_17.htm

So what I gather from looking at this is that BACKUP LOG DB_Name WITH TRUNCATE_ONLY will in fact create free space in your log file and will leave the active portion of the log intact. Hence, BACKUP LOG :oops:.

Then you could perform the DBCC SHRINKFILE function which should allow you to recapture your disk space.

Still .... I would highly recommend backing up the logs to a recoverable source to anyone. It really helps to have them.

DenSter

SHRINK will reduce the file size to just a little bit bigger than the sum of all transactions in the log file.
TRUNCATE will remove all logs from the log file, regardless of whether you backed them up or not.

When you do a transaction log backup, SQL Server should automatically truncate the log file. You don't need to backup the TL AND make backups of the log files themselves, that's redundant. You can recreate a database up to a certain point by restoring a full backup plus all TL backups made after that full backup was taken.

headley27

Hi DenSter, can you explain what you mean?

DenSter wrote:

You don't need to backup the TL AND make backups of the log files themselves, that's redundant.

Waldo

He means that it's the same thing.
Just back up the TL (from within a maintenance plan) to a seperate disk, and you're done.

headley27

That's what I thought.

Perhaps I was a little confusing with my wording.

I never implicitly run BACKUP LOG on my production database, my data maintenence plan takes care of it all for me. The maintenance plan backs up my database and my logs to a secondary HDD drive. These logs are periodically backed up to tape for historical purposes and are then deleted from the HDD.

It works like a charm.

DenSter

alrighty when you said you run backups and you move logs to tape, I thought you meant that you run backup routines (which should also take care of backup up the logs), and you ALSO copied the actual logs to tape.

You used the words 'back up the logs to tape', and that's when I thought you copied them directly. Apparently you mean copy the log backups to tape.

Waldo

What's in a name ...

slmaluwa

hi there

By the way, a newbie question

is it necessary to do any kind DEFRAGMENTATION on navision NATIVE database periodically? Or any kind of "re-indexing" necessary?

Waldo

Typically on a native db is that it doesn't need maintenance.

You can optimize tables, but that's all... .

Roi

Hi All,

I want to know after installed SQL Server (2000) , will be able to change the Transaction log file path to seperate drive and the the Match case option(currently it selected)???????? ](*,) ](*,) ](*,) ](*,)

Roi :-k

Waldo

These are statements to put the tempdb on another drive. YOu can use these statements to move your LOG file...

use master 
go 
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf') 
go 
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf') 
go

Roi

Hi Waldo,

Thanks for information.I want to know another thing as wel.When the SQL DB is setting up we have put Match Case option yes in Colliasion Tab.

Therefor each and every time when we search any thing from the Navision using Find dialog box we have to give exact letters other wise it won't find the particular record. for eg. If name strat like Raj we have to type Raj .If we type raj it won't come.

Is there any way to recorrect that without installing SQL server again?

Thanks
Roi

Waldo

You can change the collation (in SQL Server), but frankly (and you make me doubt) I don't think you 're going to solve this with that. Isn't it a normal behaviour of NAV?

Kisu

Our client had 3200Megs database and its on Database Server is it possible to shrink it without Sql tools since they dont have licence to it or do we have to copy it over our server and shrink it? The database is not more than 70Megs its just dumbly allocated when they created it :-k

Waldo

You mean it's a native database, which was expanded to 3,2 Gb, but only consumes 0,07Gb??

70Mb doesn't look right to me. Can you explain a little bit more what's the problem?

Kisu

Waldo wrote:

You mean it's a native database, which was expanded to 3,2 Gb, but only consumes 0,07Gb??
70Mb doesn't look right to me. Can you explain a little bit more what's the problem?

Its not much a problem, I fixed new db for them.
But the database was created 3,2giga size (not expanded) and it was just about a base for the company which made me think if it was possible to shrink it via database server.

edit: after shrink it was 89megs and I expanded it 300 megs when I put it back up. I did the shrink in our sql server

Waldo

So I guess it's SQL, right?

Why would you shrink the database? It's always a good idea to have have a database file that's big enough, so that the database server doesn't have to 'grow' the database all the time. Every "grow" is a performance hit... .