shrinking SQL database in version 2.6

DenSter

Hi all,

I am busy cleaning up our development SQL server by altering our development databases to simple recovery model and autoshrink. I've been able to free up about 200 GB already.

As it turns out, the recovery model is not available for set up in Navision 2.6, the way that it is available in file/database/alter in higher versions. I could alter the recovery model directly from the Enterprise Manager, but I am afraid that this could corrupt the database. We only have one remaining 2.6 database, so it's not a big deal, but I'd like to know if this is safe or not.

Does anybody know any reasons not to do this, or do you think I can go ahead and alter the database directly?

Thanks and happy new year!

fb

NaviSQL v2.6 was built during the MS SQL Server v7.0 time frame, prior to the 'Recovery Model' notion, which came with SQL Server 2000.

However, the Recovery Models map roughly to the combined state of two of the options that are available in the 2.6 client, as follows:

Full --> Select into / bulk copy = off AND Truncate log on checkpoint = off.

Bulk Logged --> Select into / bulk copy = on AND Truncate log on checkpoint = off.

Simple --> Select into / bulk copy = off AND Truncate log on checkpoint = on.

I'm not sure what happens if you set the fourth possible state (S.i./b.c. = on AND T.l.o.c.p. = on). This state can still be created in a SQL2K db (see 'sp_dboption'), but who knows what happens...?

In any event, to get to your result, you can use either tool: Enterprise Manager to change the Recovery Model to Simple; or use the v2.6 client and clear the Select into / bulk copy option and set the Truncate log on checkpoint option. Both methods are 'safe'.

However, I'm sure that you will 'do the right thing', and make a full db backup first, no?

DenSter

Thanks for the explanation, I didn't know the recovery model was not available before SQL 2000. When I get back to the office I'll try the checkboxes like you suggested first and if that doesn't take care of the transaction log, I'll alter it directly.