Solved

SQL Serevr log flie issue

Posted on 2009-05-17
12
302 Views
Last Modified: 2012-05-07
I have an error of the log file:-

Backup- IHS-MAIN-DB-13 V-79-57344-33938 -
An error occurred on a query to database P66.
V-79-57344-33938 - The transaction log for database 'P66' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
An unknown or unexpected database error occurred while attempting a DBCC.
The transaction log for database 'P67' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
An unknown or unexpected database error occurred while attempting a DBCC.
The transaction log for database 'P66' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases


now i know how to shrink the log (ldf) file but until now i was dealing with a single log file stored but i see two log files in the disk storage so how do i shrink both together. and also see the above error and please help me out.
0
Comment
Question by:aatishpatel
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 5

Expert Comment

by:Aanvik
ID: 24406308
try this. to truncate your log files.
Use Master

backup log P67 with no_log
 

Use P67

dbcc shrinkfile(<your log fine name>, 0, truncateonly)

Open in new window

0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24406338
You should backup and truncate
Have a look at the following:

http://support.microsoft.com/kb/317375

Good Luck!

Gary
0
 

Author Comment

by:aatishpatel
ID: 24406345
is it USE Master or
USE P67  

because log for P67 is full

and one more thing i will tell you what i usually do. see below steps---------------

USE [YourDatabaseNameHere]
    GO
    SELECT *
    FROM sysfiles
    WHERE name LIKE '%LOG%'
    GO
     

Second, set the database recovery model to 'simple' if its not simple.

    USE [YourDatabaseNameHere]
    GO
    ALTER DATABASE [YourDatabaseNameHere] SET RECOVERY SIMPLE
    GO
     

Third, issue a checkpoint against the database to write the records from the transaction log to the database.

    USE [YourDatabaseNameHere]
    GO
    CHECKPOINT
    GO
     

Fourth, truncate the transaction log.

    USE [YourDatabaseNameHere]
    GO
    BACKUP LOG [YourDatabaseNameHere] WITH NO_LOG
    GO
     

Fifth, record the logical file name for the transaction log to use in the next step.

    USE [YourDatabaseNameHere]
    GO
    SELECT Name
    FROM sysfiles
    WHERE name LIKE '%LOG%'
    GO

Sixth, to free the unused space in your transaction log and return the space back to the operating system, shrink the transaction log file.

    USE [YourDatabaseNameHere]
    GO
    DBCC SHRINKFILE ([FileNameFromPreviousStep], [NeededFileSize])
    GO
     

Seven, review the database transaction log size to verify it has been reduced.

    USE [YourDatabaseNameHere]
    GO
    SELECT *
    FROM sysfiles
    WHERE name LIKE '%LOG%'
    GO


but as i said earlier disk D stores all mdf files
and Disk L stores all ldf files. but in P67 folder there are two ldf files P66_1  and P66_2 so when i shrink thru above steps the P66_1 got shrinked but the second file P66_2 is the same size. so what should i do. i have only deal with one files until now but now i see two ldf files.
0
 
LVL 22

Expert Comment

by:dportas
ID: 24406490
Shrinking the log will NOT help create any new log space. It just removes UNUSED space from the file, which generally won't help at all. You first need to do what the error message says: check log_reuse_wait_desc to find out what the problem is.

Is your database supposed to be in SIMPLE or FULL recovery mode?
0
 

Author Comment

by:aatishpatel
ID: 24406514
the databases are in full recovery modes
0
 
LVL 22

Expert Comment

by:dportas
ID: 24406631
OK. Did you determine the cause of the problem yet? See:
http://technet.microsoft.com/en-us/library/ms345414.aspx
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 6

Expert Comment

by:bull_rider
ID: 24407286
Aatish, you can still shrink your log files one by one. Firs go for one and then go for the second one. Hope this resolves your issue.
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24407305
Is your database enabled for replication. Could you please double check? the replication might be hung and therefore it might be growing up your log file size.
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24407312
And last but not the least are you taking a backup of your transaction log files, if so how frequently?
0
 

Author Comment

by:aatishpatel
ID: 24408387
now i am not taking backup of the trans logs.
0
 
LVL 6

Accepted Solution

by:
bull_rider earned 500 total points
ID: 24408632
You need to take backup of transaction logs regularly. This reduces the size of the transaction log file.
0
 

Author Closing Comment

by:aatishpatel
ID: 31582365
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
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

864 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

19 Experts available now in Live!

Get 1:1 Help Now