DB getting large

[tonyvee1973]

Hi All
I have a sql2005 DB which is around 8GB in size and is getting hot to handle.
Queries are getting slow and access times are down.
I have a maintenance plan set but think that i need to shrink the DB but have never done this before - any pointers would be appreciated.
Also in my DB there are years running from 2005 to present, is there any way i can trim to only have the current DB showing just 2010 onwards but have the other years in another DB which we can connect to if needed?
Any help would really be appreciated
Thanks

 

[SQLBill]

First, check the options on your database. Is it set for SIMPLE or FULL recovery mode? If it is set for FULL recovery mode, you need to be doing transactional backups along with your Full backups. Or set it to SIMPLE and let SQL Server handle the tlog.

If it is just the data file growing due to the amount of data, then there are other things to consider.

Slowness - check the queries being run..are they using indexes? If they are SELECT statements, is is possible for them to use WITH (NOLOCK)?

Size - if it is just a size issue, you can create another database something like 'mydata_archive' and copy the data into it. Depending upon how much data there is (and it sounds like a lot), for the first time I would backup the database and restore it with the new name. Now you have two databases with the same data. On the original one, run delete statements to get rid of the old data. Once that completes and you confirm the data is correct, delete the new data on the archive database. Then once a year, or more often if necessary, run a script to copy the old data from the production database to the archive database.

Querying data, since both are on the same server it is easy to query either set of data or both. The only thing different is you will have to include the database name.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875