October 6, 2010 at 2:28 am
Hi,
I have a database with ldf file size more than 300GB and my datadase size is about 70 gb. Can someone show me how can I shink down the size of these two file.
Thanks.
Regards,
October 6, 2010 at 2:36 am
Take a look through this article - http://qa.sqlservercentral.com/articles/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 3:16 am
using SSMS-- right click on database--tasks--shrink--files--select the file type as log and shrink.
Make sure you take the fresh full backup after this activity, if you taking regular log backup.
----------
Ashish
October 6, 2010 at 3:19 am
crazy4sql (10/6/2010)
using SSMS-- right click on database--tasks--shrink--files--select the file type as log and shrink.
Won't help if the log is full.
Make sure you take the fresh full backup after this activity, if you taking regular log backup.
Why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 3:25 am
Won't help if the log is full.
But will help if its not.
Why?
If the log chain is broken after shrinking the log.
Have a look of
http://support.microsoft.com/kb/873235
saying
Important After you manually truncate the transaction log files, you must create a full database backup before you create a transaction log backup.
----------
Ashish
October 6, 2010 at 3:37 am
crazy4sql (10/6/2010)
If the log chain is broken after shrinking the log.
No it is not.
Have a look of
http://support.microsoft.com/kb/873235
saying
Important After you manually truncate the transaction log files, you must create a full database backup before you create a transaction log backup.
That kb article, and the quote you took from it refer to truncating the log. Not shrinking the log. completely different operations, totally different behaviour.
Truncate log:
Backup Log ... With Truncate_only (or switch to simple recovery and run Checkpoint). Marks the inactive portions of the log as reusable and discards log records in them. Does not change the size of the file.
Shrink log:
DBCC ShrinkFile. Releases unused (or reusable) portions of the file to the OS. Reduces the file size. Does not discard log records or break the log chain.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 3:38 am
Hi Ashish,
Sorry to ask that should I choose the file or Database? I am new to SQL 2005 and just takeover the this new job. And only recover that I have a so huge log file....
We have a schedule job to do a full backup on the database, but not these two file, i.e. MDF and LDF files. How to create a transaction log backup??
Is the following SQL command applicable to shrinking log file in SQL 2005 server:
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Please guide me on how to shink this two huge files. Is there a procedure of doing this? I really need help on this... Thanks.....
Regards
crazy4sql (10/6/2010)
Won't help if the log is full.
But will help if its not.
Why?
If the log chain is broken after shrinking the log.
Have a look of
http://support.microsoft.com/kb/873235
saying
Important After you manually truncate the transaction log files, you must create a full database backup before you create a transaction log backup.
October 6, 2010 at 3:45 am
liewsb (10/6/2010)
Sorry to ask that should I choose the file or Database?
Neither initially.
We have a schedule job to do a full backup on the database, but not these two file, i.e. MDF and LDF files. How to create a transaction log backup??
That will explain why your log file is so large. You're in full recovery and running no log backups. In that scenario, the log will grow until it fills the drive.
Is the following SQL command applicable to shrinking log file in SQL 2005 server:
USE DatabaseNameGO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Please do NOT run that at this point.
Please read and understand the article I linked to, and then make a decision as to what you want to do - start log backups or switch to simple recovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 3:55 am
Thanks. WIll read the article and...
GilaMonster (10/6/2010)
liewsb (10/6/2010)
Sorry to ask that should I choose the file or Database?Neither initially.
We have a schedule job to do a full backup on the database, but not these two file, i.e. MDF and LDF files. How to create a transaction log backup??
That will explain why your log file is so large. You're in full recovery and running no log backups. In that scenario, the log will grow until it fills the drive.
Is the following SQL command applicable to shrinking log file in SQL 2005 server:
USE DatabaseNameGO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Please do NOT run that at this point.
Please read and understand the article I linked to, and then make a decision as to what you want to do - start log backups or switch to simple recovery.
October 6, 2010 at 4:47 am
GilaMonster (10/6/2010)
liewsb (10/6/2010)
Sorry to ask that should I choose the file or Database?Neither initially.
Gail, Why cant he shrink the file(log)?
----------
Ashish
October 6, 2010 at 5:00 am
crazy4sql (10/6/2010)
GilaMonster (10/6/2010)
liewsb (10/6/2010)
Sorry to ask that should I choose the file or Database?Neither initially.
Gail, Why cant he shrink the file(log)?
Because if this is a case of full recovery, no log backups, that log will be full and shrinking it will not help. Even if it does help, it is not the right thing to do initially.
It's taking action without understanding what is wrong and what is the correct long-term solution to the problem. It is a knee-jerk reaction without due investigation. It is incorrectly giving the impression, to a new DBA, that the solution to a large log file is to shrink it. It is not.
The solution to a large log file is to investigate why it is large, to resolve whatever is causing it to be large (if it's not just a case of expected database activity) and then, if necessary, to shrink the log to a reasonable size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2010 at 5:16 am
Definitely follow Gail's advice. She's rescued more databases than the Humane Society has rescued pets.
FYI: If you're not doing transaction log backups and you're in FULL or Bulk-Logged recovery model, start doing the transaction log backups.
There's no excuse not to and it'll help keep the log from growing even larger. Especially if someone adds disk space to the server or alters the .ldf files Max Size to something larger.
October 6, 2010 at 5:18 am
liewsb (10/6/2010)
We have a schedule job to do a full backup on the database, but not these two file, i.e. MDF and LDF files. How to create a transaction log backup??
I do have to ask this. You have full database backups scheduled, but it's not backing up the MDF file?
How do you know it's not backing up the MDF file? What software is backing up the database?
October 6, 2010 at 5:19 am
Yes the amount of advice that is given to Shrink a transaction log is bit worrying, do t-log backups and manage the size of the log correctly. Shrinking on a regular basis should never really be considered.
October 6, 2010 at 5:28 am
Brandie Tarvin (10/6/2010)
I do have to ask this. You have full database backups scheduled, but it's not backing up the MDF file?How do you know it's not backing up the MDF file? What software is backing up the database?
Note that he is a new DBA. Probably worried about not having file-level backups of the files, or not understanding what a database backup is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply