[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server logs

Posted on 2009-05-13
16
Medium Priority
?
338 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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 shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

830 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