Should I expand my database file .MDF=92mg backup=78mg?

[mb22]

My current .MDF file is about 92MG. ... when I run my nightly backups the backup size is about 79MG.

Is there enough free space left in my database .... can I use this to determine? Or is it better to expand my database to say 125MG for performance?

Does more free space speed up database access or read/writes?

Also I had several large tables in the database which I deleted .... Does that free space automatically for the database to use? Or do I have to run a shrink file on my database. kinda "Compact database" ? ...

Just trying to see if I can improve performance on the database.

Also the date on my actual MDF file stays the same even though records are being added daily .... Occasionally it will update the date but the size does not seem to change. Does the date generally only change if the file has to automatically grow? In other words adding records does not update the MDF file? .. I know the records go to the log ... but sometime down the line when the log issues a checkpoint .... shouldn't that update the date on the MDF file?

Just learning! .. thanks guys

 

[JayKusch]

OK ... Here we go - haha

The best practice for setting a database's(DBs) size is to create it to the maximum size you feel it will ever grow. Sometimes this does not work because we may not know ahead of time what the transactional volume will be once the DB is put into production. That being said, if you have enough room on your physical disc that the MDF resides on, kick it out to say 300MB or more. Once again, it is a matter of how much free room you have.

What you are gaining by making the DB larger is the advantage of not having the DB create another extennt w/in the MDF when it needs to grow. This would be waht you are calling Auto Growing. Since your DB is rather small the Auto Grow, based on a MB size or percent of current DB size, will not take too long and you will not realize much of a performance hit. Now if you have MDF as I do that are 500GB or better, this Auto Grow can really cause some headaches because of the length of time it may take to grow. When it is growing, the DB is locked down and no transactional operations can happen.

The above info also applies to your transaction log, TLog or what you are calling the LDF.

Freeing up space by shrinking the DB really does not give a performance increase. In fact, it will cause a performance loss because you have elimiated all the free space w/in the MDF and now an extent will need to be created soon as the DB needs more room.

Best practice to get some performance benefits is to have you MDF and LDF on diffrent drives so that they are not competing for read/write operations. Same for your TempDB. If you can put that DB on another drive you will get a boost.

As for deleting transactions for give the MDF more room and better performance, you will once again not realize much there. The only thing you will realize is a LARGER TLog, LDF because every delete operation is logged into the TLog. If you need to remove ALL the records of a table, it is best to use the TRUNCATE TABLE command.

Hope this give you a little more info to chew on. If you have any other questions concerning this post, please continue. If you have other issues on other SQL operations and issues, start up another thread and let us help you out.

Have a great weekend!

Thanks

J. Kusch