how to shrink the logfile

hi,
My log file is of more than 2gb,
i want to shrink the size of this , without taking my server offline.
i've tried
DBCC SHRINKFILE ( 2, EMPTYFILE )
still no use.
This is the info of my database
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
thanks in advance
regards
LaraHi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
John
"Lara" wrote:
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
>|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transactio­n_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
SQL Server MVP
"Lara" <lara@.hotmail.com> wrote in message
news:%23uQQ0KKqFHA.3404@.TK2MSFTNGP11.phx.gbl...
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>

How to shrink my database

hello

i know how to do shrinking DBCC SHRINKDATABASE( XXX,10).

But my question is how we know minumum shrinking Database value through stroed procedure.

please help me i need to shrink Database.

thanx

kiran

Kiran,

With DBCC SHRINKDATABASE, you specify the percentage of free space to leave after the shrink. i.e. : in your example,"10" will leave about 10 % free space after the shrink.

DBCC SHRINKFILE allows you to shrink a specific file within the database (i.e. : the transaction log or one of the data files). If the value you specify for target size is less than the minimum size, it will shrink the file to the minimum possible, therefore, if you specify "1", then it will shrink to the minimum possible.

Examples ;

DBCC SHRINKDATABASE (northwind, 10) -- Leaves about 10 % free space after the shrink

DBCC SHRINKFILE (datafile, 5) -- Shrinks "datafile" to 5 MB (or the minimum possible if more than 5 MB used)

Rod.

|||

thanx for you response rod.

but i have some small question can u we put defaulut "1" every time

like DBCC SHRINKDATABASE (northwind, 1) ?

regards

kiran

|||I wouldn't do that if I were you. If your autogrow property is not enabled, you end up with a non-functional database in no time.

how to shrink a file fast

I had a 100GB database and i truncated some few tables and now the database
used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
40GB and its over 1 hr now and its still running . It is running and not
being blocked. Is there any way to make this run faster ?
Using SQL 2K
Hassan,
If you use TRUNCATEONLY clause with the DBCC command, it will free up
the space but won't move any data around. The drawback is that you might
not meet your shrink target. Worth a try anyway.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Hassan wrote:
> I had a 100GB database and i truncated some few tables and now the database
> used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
> 40GB and its over 1 hr now and its still running . It is running and not
> being blocked. Is there any way to make this run faster ?
> Using SQL 2K
>

how to shrink a file fast

I had a 100GB database and i truncated some few tables and now the database
used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
40GB and its over 1 hr now and its still running . It is running and not
being blocked. Is there any way to make this run faster ?
Using SQL 2KHassan,
If you use TRUNCATEONLY clause with the DBCC command, it will free up
the space but won't move any data around. The drawback is that you might
not meet your shrink target. Worth a try anyway.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Hassan wrote:
> I had a 100GB database and i truncated some few tables and now the databas
e
> used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
> 40GB and its over 1 hr now and its still running . It is running and not
> being blocked. Is there any way to make this run faster ?
> Using SQL 2K
>sql

how to shrink a file fast

I had a 100GB database and i truncated some few tables and now the database
used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
40GB and its over 1 hr now and its still running . It is running and not
being blocked. Is there any way to make this run faster ?
Using SQL 2KHassan,
If you use TRUNCATEONLY clause with the DBCC command, it will free up
the space but won't move any data around. The drawback is that you might
not meet your shrink target. Worth a try anyway.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Hassan wrote:
> I had a 100GB database and i truncated some few tables and now the database
> used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
> 40GB and its over 1 hr now and its still running . It is running and not
> being blocked. Is there any way to make this run faster ?
> Using SQL 2K
>

How to setup a script to automatically monitor log files

Is there any way to setup a script that can monitor sql
server 2000 log files automatically by using something
like dbcc sqlperf (logspace) go.
Any help will be appreacited.
Thanks,
Aboki.Look at performance condition alerts in BOL. You can define an alert on =a percentage full level on whichever log file you want, then take some =action, like backup log when it happens.
Mike John
"Aboki" <waco361@.hotmail.com> wrote in message =news:01df01c34d68$c7984ad0$a501280a@.phx.gbl...
> > Is there any way to setup a script that can monitor sql > server 2000 log files automatically by using something > like dbcc sqlperf (logspace) go.
> > Any help will be appreacited.
> > Thanks,
> Aboki.sql