i am converting a native DB to SQL . Native DB size is 8 GB . when i am converting this to SQL the size is 48 GB . why is that. can i reduce the size. is there is any methord to reduce the size of the SQLDB . please help me guys
Is that including the transaction log or just the data files? If the transaction log files are included, then those need to be truncated. Also, just because the data file size is 48GB does not mean that it is all in use.
Large database size for NAV databases is usually because the database is massively over indexed, and there are many redundant SIFT levels. One of the ways to reduce the size is to tune the indexes and to remove those redundant SIFT levels. It’ll perform better as a result too.
Since the log file should be on its own DEDICATED physical drive it’s size should not be a major concern. Provided it is not running out of space on the drive.
You can find out how to adjust your transaction log in SQL Books Online. Basically the steps are:
Full DB backup (optional as a precaution)
backup log with truncate only - where databasename is the name of your database
dbcc shrinkfile() - where filename is the file name of the transaction log, you can get that from DB properties
You will then want to do either:
a) Set the database recovery model to simple
b) Or set it to full and setup a database maintenance plan to backup the log at regular intervals. This combined with a regular full or differential backups provides a great backup/recovery setup.