[dba-SQLServer] Log file

Here it is again...

One way is to detach your current database from QA like this:
(remember to be in the "master" database when running these commands)

EXEC sp_detach_db 'MyDB', True

then reATTACH it by running the sp_attach_db sproc,

EXEC sp_attach_db @dbname = N'MyDB',
   @filename1 = N'd:\SqlServer\data\MyDB.mdf',
   @filename2 = N'f:\FastLogDisk\MyDB_log.ldf'

to LIMIT your log file to a specific size then do this:
IN EM:
   right click and go into the database properties
   Click on the Transaction Log TAB
   Deselect Automatically Grow File

WARNING: you will NOW need to backup your transaction log more often
in order to reuse some wasted space, one good way is to backup the
transaction log when it reaches 60% of it's utilization space: you can
do this by adding an Alert:

IN EM:
  Under the Management folder and under the SQL Server Agent icon
click on Alerts and create a new Alert.  Give your alert a meaningful
name

In the General tab:
Type choose: Sql Server Performance condition alert (enabled)
Object: SqlServer:Databases
Counter: Percent Log Used
Instance: MyDb
Alert If Counter: rises above
Value: 60

In the Response Tab
(Check Execute Job) and create a job (the three ... dots)
your job should have the following TSQL job for backup:
BACKUP LOG [MyDB] TO [LogBackupDeviceName] WITH  INIT

Then OK to save all your settings...

I hope this helps you out.

On Thu, 26 Aug 2004 10:14:01 -0400, Colby, John <jcolby at dispec.com> wrote:
> Nope, I didn't get that.  Every time I try it just shows the missing ldb in
> red and refuses to attach saying that the db isn't valid.
> 
> JWC
> 
> 
> 
> -----Original Message-----
> From: Francisco Tapia [mailto:fhtapia at gmail.com]
> Sent: Wednesday, August 25, 2004 8:54 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Log file
> 
> John,
>   Did you not get a copy my message on a single file re-attach command from
> QA?

-- 
-Francisco