I am running a small-ish install of Navision Attain 3.01B with the SQL option. I’m wondering what the recommended SQL Maintenance Plans are? My vendor doesn’t seem to be very proficient in this area.
Very dependent on the situation I would say. But I would make a daily full backup of the customised database, transaction log and master table. Let the maintenance plan keep the last 7 backups, and let it send a mail to the DBA. Furthermore you might want to create some alerts on database size (since autoexpand might lead to full disk situations) and things like that (deadlocks and so on).
You may want to do transaction log backups during the day, depending on the workload and concurrent users. Heavy systems should try for hourly transaction log backups. Its not really necessary to back up the master database, since Attain doesn’t use it for anything other than the storage of the licsense. Doesnt hurt though, since it will be so small. You should also occasionally (again depending on workload, specifically update transactions) optimize your indexes, either from Attain or in SQL Server. How frequently is very hard to say in general but I would say once a month at least.
Thanks for your help. As part of my regular db plan for all of the db’s on that server I dump the tlogs every hour and do a db dump daily. Any comments about transaction log size. Mine is getting quite large (650MB on a db of 1.2GB). We backup/restore companies into the db sometimes - is it this operation that’s growing the tlog? Should I put a cap on its growth? Are there any docs on this?
Have a look at functions such as Dbshrink.
Look in SQL Server books online for the DBCC SHRINKDATABASE and DBCC SHRINKFILE statements, along with “Shrinking the transaction log”. You can shrink the transaction log only depending on the state of the ‘virtual logs’ that are created within it, which is essentially something that you don’t have much control over because it depends on your transaction activity and the internal sizes of the virtual logs. But, since after backing up the transaction log, SQL Server truncates it automatically (removing inactive portions of the log), you can then try to shrink it and any inactive portions sizes can perhaps be removed from the total log file. Anyway take a look at BOL because this subject is rather complicated. By the way, don’t limit the transaction log growth (unless the size is huge and never reached) - you will get an error if/when SQL Server tries to continue writing to the log beyond the size you have set (SQL Server does not ‘reuse’ the log and therefore maitain it beneath the size). Getting this error means the current transaction is aborted and you must immediately attend to the log problem. You effectively halt your system. Similarly if you run out of disk space for an unlimited log file.