?
Solved

SQL Server logs

Posted on 2009-05-13
16
Medium Priority
?
332 Views
Last Modified: 2012-05-06
Hello frineds,

How do i check the SQL server log sizes. and  shrink them. i want to know exact step how to shrink them. and also how to monitor them.
please help
0
Comment
Question by:aatishpatel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24376914
well, shrink transaction log frequently is not a good idea. sometime when it become exceptionaly big, it is ok to shrink. here is my small article to shrink log file

http://www.sqlhub.com/2008/05/sql-server-truncate-transaction-log.html
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24376948
to see log file, have a look at below query

select * from sys.sysfiles
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 total points
ID: 24377000
BTW, keep backing up your transaction log regularly so whenever you shrink your transaction log, you  won't loose any data.

If you want proper backup policy, you can read following thread in EE, I gave complete automize backup script at post #24184054

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24336568.html
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 31

Expert Comment

by:James Murrell
ID: 24377977
personally i would read http://support.microsoft.com/kb/272318 as they wrote the program it explains everything you need to shrink the log files...

re: see logs

for 1 db try sp_helpdb

for all  i use sp_MSForEachDB 'Use ? exec sp_HelpDB'




0
 
LVL 41

Expert Comment

by:graye
ID: 24379330
... and if you're up to even more reading, here is another article:  http://www.emmet-gray.com/Articles/SQL_LogMaintenance.htm
0
 

Author Comment

by:aatishpatel
ID: 24380820
Ritesh shah had written this code
-------------------------------------------------------------------------
USE P001
GO
DBCC SHRINKFILE(abc.ldf, 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE(abc.ldf, 1)
------------------------------------------------------------------------
 but gives me error. so what's wrong with this code. please rectify and also explain what the three lines does.
0
 
LVL 41

Expert Comment

by:graye
ID: 24381205
This is one of those situations where merely providing you an answer is, quite frankly, not enough...   I highly suggest that you read the articles listed in these responses first.
Only after you've got a good understanding of what's going on, can we really give you advice.  
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24381512
use below query to know name of your logfile.

select name from sys.sysfiles

after that, run below command

DBCC SHRINKFILE(NameOfYourLogFileWithOutExtenstionLDF, 1)

try running only first one line given above.



0
 

Author Comment

by:aatishpatel
ID: 24386405
its says all logical files are in use
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 24390090
1. open enterprise manager.
2. right click on the database u wanna shrink it.
3. click on properties.
4. from the data properties go to options.
5. in the middle u will see recovery model make it "simple" then click on "ok" and try to shrink the database.
it works 100%
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24392065
cs97jjm3,

"Simple" recovery model will not SHRINK log file, are you sure? have you did it?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24392068
>>its says all logical files are in use<<

can you please show me, which command are you using with logical file name?
0
 

Author Comment

by:aatishpatel
ID: 24406568
i was using
DBCC SHRINKFILE(NameOfYourLogFileWithOutExtenstionLDF, 1)

and that gave me
all logical files are in use
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 24407036
sorry that is for when  all logical files are in use
0
 
LVL 41

Expert Comment

by:graye
ID: 24434026
Our ability to provide you good advice is highly dependent upon you understanding some of the concepts involved.  For example we can suggest that you change the "recovery model" to make this problem go away, but if you don't understand the implications of such a suggesstion, you might not be happy with it does to your disaster recovery plans.
To answer your last question, I'll do a cut-n-paste from one of the articles that we suggested that you read.
I tried that, and it just won't shrink!

Yes, there are times when your log file will just refuse to shrink.  The error message is somewhat useless and typically says "Cannot shrink log file 2 (logfile) because all logical log files are in use"
It takes a bit of investigative work to figure out what's going on.  So, issue the following command to see the current "wait state" of each log file


Use msdb
 select name, log_reuse_wait, log_reuse_wait_desc from sys.databases

The most likely reason a shrink operation failed is shown in the following codes:
  • LOG_BACKUP - the log file has performed a "wrap around" and the free space is now in the middle of the file.  Rerun a log backup so that it moves the free space towards the beginning or end of the file.
  • ACTIVE_TRANSACTION - there is an "open transaction" that's holding the log file hostage.  Use the DBCC OPENTRAN command to find out what's going on (and perhaps kill the process).
  • CHECKPOINT - there has been no database checkpoint in the period of time covered by the transaction logs.  Manually run the CHECKPOINT command or perform a database backup.
Take a look at the following link for a more detailed explanation of each code: [b]http://msdn.microsoft.com/en-us/library/ms345414(SQL.90).aspx[/b]
Note:  In some cases, you might have to perform several log backups before you can successfully shrink the log file.
 
0
 

Author Closing Comment

by:aatishpatel
ID: 31581068
thanx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question