February 25, 2005 at 11:19 am
Our database size has grown to 6 GB and we back it up every week. We want to reduce the size of the database but I am not sure if SQL server has something like access where you could do "Compact and repair Database". I know you can backup the ldb file using bulk-logged which reduces its size which we already do. Also if move my older records to new database so I can reduce the size of my actual database what the best and fastest way to do it. the reason I want to do this to speed it up and also have a smaller size so that copying files from external source is faster in case it fails. We are also scared that it will grow to a size we cannot easily handle in a few months. Whats the maximum size a SQL database can grow upto without giving any problems. We are also using replication and don't want that to get affected.
February 26, 2005 at 2:05 pm
how large SQl Server can be is directly related to the space available on the server. You are not in danger of maxing out SQl but you might be in danger of maxing out the diskspace.
There are several methods to shrink the database. See BOL for dbcc_shrinkdb and dbcc_shrinkfile.
Performance is also related to how it is configured. Would suggest doing some reading on the internals of SQl server to better manage the space. You are right to manage the database early on but you need to understand the whole picture - fixing it later can be a nightmare.
There are several methods to migrate data depending on the amt of data and where it is going including DTS and BCP. BCP is faster but has less options. DTS is easier to set up so I'd probably look into that first.
-B
February 27, 2005 at 8:55 pm
Check to see if the data file or the log file is growing. If the log is growing them it may be because the recovery mode is full and the log is not being backed up regularly. Also be aware of the autogrowth increments to make sure that they are not too small.
March 1, 2005 at 4:57 am
Hey,
6GB isn't a huge database by any means and SQL Server can easily handle DB's of that size. As was mentioned, the space is more a factor.
You mentioned creating a archive database for older data. Yes, possible but will you need to query this data often? If so, I don't see much merit in creating a seperate database. My recommendation would be to create an archive table in the same database.
You mention the worry of a database failure. You also mention that you only back up once a week. I would change this ASAP! Assume your server fails a couple hours before the backup is due to run....A weeks data is gone. I don't know your system etc, but off the top of my head I would say do a full backup every night. For extra protection do a differential backup every 4-6 hours and also do log backups every hour or so. I'd back them up to the local server and then copy them to a network resource or to tape.
March 1, 2005 at 5:20 am
Have a look here for a large database in SQL Server:
http://www.microsoft.com/sql/techinfo/administration/2000/rosetta.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply