[dba-SQLServer] Log file

On Thu, 19 Aug 2004 16:08:08 -0400, John W. Colby
<jwcolby at colbyconsulting.com> wrote:
> Is the ldf the log file?  If so I understand it is possible to place that
> file on a different drive from the data file?  How do I limit the size of
> the log file and dictate the location?

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.



-- 
-Francisco