[dba-SQLServer] Log file

You want to run the EXEC statements from QA (Query Analyzer).

btw, I forgot I had this script which is much nicer, in order to
shrink your log file
USE MyDB
BACKUP LOG MyDB WITH TRUNCATE ONLY
DBCC SHRINKFILE (MyDB_log, 10)

Where the Log will be shrunk down to 10megs... the Backup doesn't
acctually backup any data at all it simply just checks all open
transactions back to the db so that the log file is clear to shrink.

As far as books go?, I've used "Mastering SQL Server 2000" as my main
guide, but then BOL for all my other needs.  I'm also subscribed to
sswug.org where there are "real" gurus about this sort of stuff.

On Thu, 26 Aug 2004 12:01:57 -0400, Colby, John <jcolby at dispec.com> wrote:
> Wow.
> 
> I already deselected the Automatically Grow File, but the file has already
> frown from 1.x gig starting size to over 10g now.
> 
> Do I run these EXEC statements from the query window?  Remember, I am a
> TOTAL neophyte here.  In fact the only books I have are for SQL Server 7.  I
> will be going to the bookstore though.  Any recommendations for good SQL
> Server 2K books?
> 
> ALSO...
> 
> Last night I built a raid 0 array using 2 200gb drives, and it appears that
> my 2.5g AMD Barton (overclocked to '3.0g') with 2gb RAM is barely sufficient
> to handle this size db.  I haven't managed to load the entire database yet
> although it appears that I now have the ability to at least do that.  I can
> just see that a table scan on 65 million records pulling result sets on
> Yes/No where clauses is going to take a LONG time.
> 
> I am looking at putting up a multi-processor 64 bit server if this client
> gives me enough business.  From my readings, XP currently limits memory to 2
> gb and the 64 bit machines limit the max to 4g.  Above that we revert back
> to the good old days of EMS memory mappings crapola which slows things way
> down.
> 
> Apparently only Windows Server 2003 is currently 64 bit, although it appears
> that SQL Server 2K can use a 64 bit machine.  Does anyone know anything
> about this 64 bit stuff?  Can anyone provide insight on speed gains on
> processing large databases using 64 bit (windows) OS and DB software?
> 
> It appears that I can build a dual Opteron machine for well under $2K but
> since I have no experience in this I am hesitant to go that route without
> reasonable assurances of significant speed gains.
> 
> JWC
> 
> 
> 
> -----Original Message-----
> From: Francisco Tapia [mailto:fhtapia at gmail.com]
> Sent: Thursday, August 26, 2004 11:43 AM
> To: dba-sqlserver at databaseadvisors.com; John Colby
> Subject: Re: [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
> 


-- 
-Francisco