Financial Year Closing...any sugestions


I am a MSSQL DBA in a production environment in India.
I have a question, I am working on Navision 4.3 with SQL Server 2005 sp2.

Now the problem I have a pretty huge database say around 350 GB in Size
Our financial year closing is 2009-2010 which is exactly at March 31st.

I want to close the financial year for the same and start afresh
with a new database in the same server. Actually I want to close postings
in the old database and compress the same and keep it for reporting purpose.
Yes I want to access the old database for reference or reporting.
I would move the old database in a RAID 5 volume and then create a new database.

What are the best ways to do so? What considerations needs to be taken care of?
Is this easy I mean in Navision?

Your valuable inputs would be appreciated.

Best Regards,


It’s not a problem at all - normal size for Navision, I would even say it is relatively small (seriously). Actually, there is no need to as you described - there are different methods you could use.

For info about SQL DB optimization & maintenance the first place to look for advices might be this forum’s moderator and our SQL guru Jorg’s blog here

Additionally, read this same SQL forum, there are many threads about maintenance.

added: BTW standard license doesn’t allow more than one DB - you need additional granule per every additional DB…

Its just a thought …But can I do the same …like I mentioned above. Is that possible ? If yes then how does one about it.

The main objective is to compress the old data carry out the openings to the new database and start afresh.

Maintenance is not a problem …have visited/read Stryk’s blog/posts/comments upteen number of times.

It is damn useful for people like us coz when u r supporting a prod database with Navision as an ERP many DBA’s expertise go for a toss. even I have learnt the hard way.




Such compression is possible, but - it is not a task for you as DBA and can not be done by SQL tools - never manipulate Navision database bypassing Navision. This doesn’t mean simply deleting some older records from some tables, Navision has its own functionality built-in, how to compress historical data. In short - Navision replaces many records by a summarised one, or, there are some tables, where Navision really deletes old entries.

Look in Administration / Data deletion… There is what Navision calls Date Compression, too. But you as DBA should understand without saying, that these functions are irreversible - either make backup before, or make a copy DB - as you suggested.

However, not all data can be compressed in such a way, and not all discontinued data can be removed - as you already mentioned, ERP system’s database has it’s specific.