May 4, 2002 at 7:05 pm
I have a database that has 12 GB mdf file and 2 logs on 2 drives, one is 14GB and other is 4 GB. The space remaining on the two drives is approximately 2 GB each.
My first goal is to minimize the logs to 1GB each (better if I can make them smaller). My system is not transaction oriented and is mostly retrieval intensive. But looks like the batches that run monthly filled the logs and apparently they have never been cleaned.
My second task is to copy the database onto another server. I tried this earlier and it was asking for the same log file names on the new server. (The directory structure is different on both the servers).
Am new to SQL Server. So please help me out.
Thanks a lot
SB
May 5, 2002 at 7:44 pm
1st, you should be able to truncate and empty the logs to shrink them. However if using 7 they may not shrink all the way and you should look to http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=26&CategoryNm=Maintenance%20and%20Management%20&CategoryID=1 for a script to handle this. Also if 7 the make sure Selct Into/Bulk Copy is turned on and might sinc emostly reads want to set trunc log on checkpoint. If 2000 then check recovery options and make sure are using one that does not log the bulk transactions. See BOL but
quote:
RECOVERY FULL | BULK_LOGGED | SIMPLEWhen FULL is specified, database backups and transaction log backups are used to provide full recoverability from media failure. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged. For more information, see Full Recovery.
When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery. For more information, see Bulk-Logged Recovery.
When SIMPLE is specified, the database can be recovered only to the last full database backup or last differential backup. For more information, see Simple Recovery.
SIMPLE is the default setting for SQL Server Personal Edition and the desktop engine, and FULL is the default for all other editions.
The status of this option can be determined by examining the Recovery property of the DATABASEPROPERTYEX function.
As for copying over look at BOL at sp_attach_db about attaching a database and logs and how you set the path. If from a backup set you must use the with move option.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 6, 2002 at 10:42 am
Antares has great advice. If you use EM to restore on the new server from backup, you change the path's for the files on the "Options" tab.
Steve Jones
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply