Solved

SQL Server logs

Posted on 2009-05-13
16
287 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 500 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now