April 9, 2008 at 3:18 am
Hi
How to clear the log file without detaching the database?
Is there any command to do it?
April 9, 2008 at 3:23 am
Bilichi (4/9/2008)
HiHow to clear the log file without detaching the database?
Is there any command to do it?
Why do you want to do this? Has it grown too much?
To shrink the log file you can use DBCC SHRINKFILE.
If you are running in full or bulk recovery mode, you need to back up your log first, or if disaster recovery is not important, switch to simple recovery mode (in which case DBCC SHRINKFILE can reduce the size of your log file assuming there are no long running active transactions, (a few other things). For examples see Books Online (and search for the above commands)
Regards,
Andras
April 9, 2008 at 3:36 am
May i know what is the use of dump tran command in database?
April 9, 2008 at 3:38 am
Bilichi (4/9/2008)
May i know what is the use of dump tran command in database?
It backs up your transaction log.
More info is in books online under "BACKUP LOG" http://msdn2.microsoft.com/en-us/library/ms186865.aspx
Regards,
Andras
April 9, 2008 at 3:46 am
i am having a database of size 60Gb of that (28GB of MDF & 32 GB of LDF).
Is it possible to reduce the Log file size?if possible please provide the command to that.
April 9, 2008 at 3:58 am
Bilichi (4/9/2008)
i am having a database of size 60Gb of that (28GB of MDF & 32 GB of LDF).Is it possible to reduce the Log file size?if possible please provide the command to that.
First check what recovery mode you are in:
SELECT recovery_model_desc
FROM sys.databases
WHERE database_id = DB_ID()
If it says simple, then proceed to the next. If not, i.e. it says bulk or full, then when did you take a log backup? If not recently, take a log backup (backup log ....)
Find out the name of the log file:
SELECT name FROM sys.database_files WHERE type = 1
using this name execute:
DBCC SHRINKFILE (<FileName>, <TargetSize>)
Fill in the filename (you got that in the previous query), and the target size (in megabytes)
Regards,
Andras
April 9, 2008 at 4:30 am
I may add that you cannot achieve a file shrink by running DBCC SHRINKFILE if the database is in simple recovery mode.
The recovery model of a db can be viewed/modified by right-clicking on your db, then properties, then options.
If it's set to simple then change it to full, then take a full backup, then a transaction log backup, then apply dbcc shrinkfile as per the post above. After the shrink, do not forget to change the recovery model back to simple.
Again, if the db is in simple mode, an increase of the log file size may require re-sizing the db file that way to avoid further growth. And it's important do not have growth by procent, instead having growth in MB (10 of 100 or 200, based on transaction types you run)
If the db is in full recovery and you're on development then it is recommended to switch to simple as recovery model. If you're talking about production then consider running transaction log backups periodically.
Hope this helps.
April 11, 2008 at 1:35 am
while i use DB mirror, the log file increased too much ,about 7 G every hour ,
so i want to shrink the log in primary server .
command is below :
backup log test with truncate_only
DBCC SHRINKFILE (filename)
but error message show :
Msg 3048, Level 16, State 1, Line 1
BACKUP LOG WITH TRUNCATE_ONLY cannot operate on database 'test' because it is configured for database mirroring.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
how to reduce the log file when recovery mode is full for DB mirror ?
hope to help ! thanks a lot
JackeyCheng
April 11, 2008 at 1:53 am
JACKEY CHENG (4/11/2008)
while i use DB mirror, the log file increased too much ,about 7 G every hour ,so i want to shrink the log in primary server .
command is below :
backup log test with truncate_only
DBCC SHRINKFILE (filename)
but error message show :
Msg 3048, Level 16, State 1, Line 1
BACKUP LOG WITH TRUNCATE_ONLY cannot operate on database 'test' because it is configured for database mirroring.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
how to reduce the log file when recovery mode is full for DB mirror ?
hope to help ! thanks a lot
JackeyCheng
Hi,
on a mirrored database you cannot "backup log ... with truncate_only".
When you use DBCC SHRINKFILE only, does it reduce the size of your tranasaction log? If not, then do you have any long running transactions (check DBCC OPENTRAN). Is your mirror running behind?
Could you tell us the result of:
select log_reuse_wait_desc from sys.databases where database_id = db_id()
Regards,
Andras
April 11, 2008 at 2:13 am
hi ,
after run the script, the result is below :
DATABASE_MIRRORING
because we are running mirror on the primary DB server ,
if i only run DBCC command , the log file is not reduced
DBCC SHRINKFILE (filename) , size
so i hope to backup the log firstly ,then run dbcc ,
but it is not support while mirror .
would you give me the solution,
thank you !
Regards,
Jackey
April 11, 2008 at 2:39 am
I have a doubt :
because our Database is production environment, and recovery mode is "FULL".
some application is running , and execute command to DB high frequency (insert, create, delete ,trans ..) , so the transaction log increased speed is 1 G every hour
but when i run mirror in the primayr DB server , the transaction log increased speed is 7 G every hour
i want to know the reason about the speed is defferent very much while exist mirror or not .
Thanks,
JackeyCheng
April 11, 2008 at 2:47 am
Sorry, next question, is your mirroring actually running?
Can you check the result of
select * from sys.database_mirroring where database_id=db_id()
Regards,
Andras
April 11, 2008 at 3:19 am
the result is :
5C6D0AADA-25D0-4A07-8AE8-7A5A7650BAED2SYNCHRONIZING1PRINCIPAL11OFF2TCP://HI0-Server1.BQC.CORP.COM:5022HI0-Server1\CIMSQL20050UNKNOWN119925300000051200000110NULLUNLIMITED
i want to know how to SHRINK log FILE when mirror i running
happy weekend !
Thanks a lot !
Jackey Cheng
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply