SQL2000 database control help

[ash2000in]

I am a beginner with SQL, so need any tips that can help me manage databases.
I have a database which is currently about 2.6GB in size, with almost same amount of log file-on a 8GB partition. I had a problem with the log-file size growth and so i 'detached' the db, moved the log file to other partition, 're-attached' the db pointing to the new location of the log, and it worked.
But the database is growing too fast. Worried about this, i went to database-size settings and made the "Allowable size" of database to 5GB - not aware how to reduce it(if needed). To my unpleasant surprise, all the 5GB space shows as occupied now, though the database is abt 2.6GB as of now!!
I read somewhere that backing-up the transaction log file will reduce its file-size to zero and start logging entries again, so i now have a backup of transaction log-which is also ~5GB. The log file did start from zero and its working ok.
My concerns are,
1) How long should i keep the backed-up copy of that log file? And is it really needed at all?
2) Is it possible to reduce the allocated file-sizes of the database data file and that of its transaction file?
3) The database is creating a backup on its own every morning 6am, which is in the same 8GB partition as that 5GB database is; so i have to always move this backup in the morning while i start using the database - to leave sufficient free space for its working. I dont want this automatic backup, i can manage with manual backups. How to stop this?
4)This SQL2000 is on a Win2000 Server, with a hardware RAID5 configuration spread over 3SCSI hard-disks of 18GB each, with partitions of 8GBs. Can this affect the SQL performance since i have database on one partition & its log on other??

All tips and suggestions are highle appreciated, Thanks.
Ash

 

[Warlocks]

In SQLServer, when you allocate a specific size to a database, SQLServer will actually "steal" that space from your hard-disk to prevent the space being taken by other files/databases. For instance, you may have an 8GB drive and create a database with an allocation of 5GB. If the space wasn't taken straight away, you would be able to create other databases and still allocate to them 5GB. This would obviously cause problems when the 2 databases reached 4GB as no more space is available. By taking the space, this is prevented making DB management that little bit easier...

On the log-file issue, you can truncate (shrink) the log manually by using the "backup log..." command. This will obviously make a backup of the log so if storing on the same partition, may not free up any space as you are in effect copying it. (This may need to be checked as it may free a little space if compacted). If you don't want to actually save a copy of the log file (as this may be backed up anyway as a scheduled job), you can use the BACKUP LOG command using a "switch" at the end. i.e.

BACKUP LOG <dbname> WITH TRUNCATE_ONLY which will truncate the log to only those transactions without a checkpoint (not committed to database).

Point 1 - How long to keep backup? That depends on your database plan. The transaction log is needed for uncommitted transactions. Without it, if the database goes down, you will have to restore to the last FULL database backup. If you had a backup of a transaction log, you could then apply any changes made up until the database went down.
Normally, you only need the last saving of the transaction log so you could set the backup job to Overwrite and NOT append... although I assume this is the case anyway otherwise other problems would have arisen before now with space!!

Point 2 - I have already discussed ways to reduce the transaction Log but as you have set an allocation for the database to 5GB, even SHRINKing the database will leave that allocation in place.

Point 3 - All scheduled jobs are held in the Jobs section of the SQLServer Agent. Go into Enterprise Manager and expand the folder of the actual server. Expand Management and also expand SQLServer Agent. Click on the Jobs folder and on the right-hand side is listed all the jobs. Delete the appropriate one.

Point 4 - PASS?!? I think performance may actually be improved as the data itself will also be transparently distributed over the disks so if information is requested from different users, different disks can be accessed at the same time. If on a single disk, obviously the requests will have to be queued so a delay may happen.

Hope this helped...

Woody.

 

[MaffewW]

Worth adding to Point 4) It is highly recommended to have the database file and transaction log file on separate physical disks. In addition, performance can further be enhanced by for example having database files and indexes on separate disks, or having heavily queried tables on their own disk, though how best to configure the data on your drives depends on your particular database/querys run etc etc

 

[SQLBill]

On Point 3 and Woody's suggestion, I would like to add that there is an option in Jobs to DISABLE the job. I suggest that you disable any job and wait a couple of weeks before deleting it. If you delete it, goodbye, it's gone. If you disable it and find out you really need it, just enable it again.

-SQLBill

 

[tlbroadbent]

A good backup stttrategy is needed in order to regulate the size of the transaction log. SQL BOL contains good information about backup strategies, how the transaction log is used and shrinking databases and logs. The SQL Server 2000 Operations Guide is another valuable resource.

I recommend that you read three FAQs in this forum.

faq183-1062 - &quot;SQL Server 2000 Operations Guide&quot;


faq183-1784 - &quot;How Do I Develop a Backup and Restore Strategy?&quot;

faq183-1534 - &quot;Shrinking Databases and Logs - SQL 7 and Higher&quot;
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[SQLSister]

First, I suspect in a database this size, you would be better off with larger disks. Unless you know more records are not going to be added, it will only continue to grow, so I'd be looking at investing in more hardware now before you desperately need it.

Indexes and log files should be kept on different physical drives than the database. This will speed performance.

You should review your backup strategy. The best way to keep the log size down is to do frequent transaction log backups. I don't mean daily, I mean every hour or half-hour. Don't keep the logs on the same drive as the database, index or transaction log. How would you recover if your log and database were both on the drive that failed? Talk to your management about data loss and find out in writing from them exactly how much data can they afford to lose if the system were to fail. Right now, you would probably lose a whole days worth and that would not be acceptable in any office I ever worked in. But make sure they know that none is not an acceptable answer either. Don't promise what you can't deliver.