SQL Server logs

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
aatishpatelAsked:
Who is Participating?
 
RiteshShahConnect With a Mentor Commented:
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
 
RiteshShahCommented:
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
 
RiteshShahCommented:
to see log file, have a look at below query

select * from sys.sysfiles
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
James MurrellProduct SpecialistCommented:
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
 
grayeCommented:
... and if you're up to even more reading, here is another article:  http://www.emmet-gray.com/Articles/SQL_LogMaintenance.htm
0
 
aatishpatelAuthor Commented:
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
 
grayeCommented:
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
 
RiteshShahCommented:
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
 
aatishpatelAuthor Commented:
its says all logical files are in use
0
 
James MurrellProduct SpecialistCommented:
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
 
RiteshShahCommented:
cs97jjm3,

"Simple" recovery model will not SHRINK log file, are you sure? have you did it?
0
 
RiteshShahCommented:
>>its says all logical files are in use<<

can you please show me, which command are you using with logical file name?
0
 
aatishpatelAuthor Commented:
i was using
DBCC SHRINKFILE(NameOfYourLogFileWithOutExtenstionLDF, 1)

and that gave me
all logical files are in use
0
 
James MurrellProduct SpecialistCommented:
sorry that is for when  all logical files are in use
0
 
grayeCommented:
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
 
aatishpatelAuthor Commented:
thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.