Solved

SQL Server lof giles issue

Posted on 2009-05-16
23
382 Views
Last Modified: 2012-06-21
Friends its very urgent....
I have an I(Index drive):  which only stores .ldf (log files). now the space of that drive is 65 gb and left space is 10MB. what should i do. can i do something like below

 can i shrink the log files for SQL Server---right click database --- then----task-----shrink-----files  .
0
Comment
Question by:aatishpatel
  • 10
  • 8
  • 3
  • +1
23 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24403676
First let me tell you what has happened:
You have a database in the default Full Recovery Model for which you have for whatever reason failed to backup the Transaction Log.

Solution:
1.  Start backing up the Transaction Log on a scheduled basis (for example once an hour) or
2.  Convert to Simple Recovery Model and lose the possibility of point-in-time restores.

Once you have done that we can talk about shrinking your database.
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24403698
I have answered as to how to shrink log file here in this link. Please go through it and let me know if it helps.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24398614.html
0
 

Author Comment

by:aatishpatel
ID: 24403707
but we do not have replication. but can i just right click on database--- task--and shrink--files and then log file.
and i also did simple recovery mode also.
0
 
LVL 6

Accepted Solution

by:
bull_rider earned 500 total points
ID: 24403723
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

Yes you can what you said, I thought you need a detailed process so I have listed it again.

Hope that helps.
0
 

Author Comment

by:aatishpatel
ID: 24403758
sir please let me tell you that i am a new bee and please show me detail steps my database name is P001,  and Log file name is NextGen_log, size of file is 65 MB.  so what do i include in the parameters.

i get errors in the parameters from these steps below. but the above steps are running fine

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
     

0
 

Author Comment

by:aatishpatel
ID: 24403777
ok got it sir
but i have a 38 GB ldf file so what figure do you suggest me in the parameter. eg:-
USE UserDB
GO
DBCC SHRINKFILE (DataFil1, ??????)
GO
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24403794
USE [P001]
GO
DBCC SHRINKFILE 'Log File name with path',<size in MB>
G

Log File name with path = The log file path which you want to shrink

Size in MB = Size in MB to which you want to reduce the file supposing 500 MB
0
 

Author Comment

by:aatishpatel
ID: 24403796
and so shrinking the size of the log file wouldn't create any problem?  right
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24403799
no it wont. Trust me. If you still wanna be safe you can take a backup of the database or copy that file to someother location.
0
 

Author Comment

by:aatishpatel
ID: 24403812
when i gave
GO
DBCC SHRINKFILE (abc, 50)
GO


its shrinked, did not gave path is it ok. ?????????
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24403830
yep its ok. Just double check the size of the log file

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

Hope it helped you in your cause,
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:aatishpatel
ID: 24403882
so if the file is 38 GB what should i specify

here

USE [IBM_90]
    GO
    DBCC SHRINKFILE (ibm_log, 25)
    GO


so 25 means would shrink to 25 GB or MB.   but actually i want only 25 GB. so writing 25 only would be ok
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24403890
no the size shoud be in MB, so convert 25 GB to MB, I mean 25 * 1024 MB and specify it there.
0
 

Author Comment

by:aatishpatel
ID: 24403926
so 25600 is right?  just want to make sure sir.........
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24403929
Yes its correct. And please dont call me sir, I am just a normal person like you. I get embarassed when someone calls me sir. :)
0
 

Author Comment

by:aatishpatel
ID: 24403938
ok sorry   n thankyou very very much. now the size is 25 GB and the empty size of my disk also increased.  Thanx a lot....... you save me.......
0
 

Author Closing Comment

by:aatishpatel
ID: 31582266
The best solution i had ever had and very very exact one tooo for a  dumb guy like me.   thanx again
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24403945
Aatish one more thing can you please change the zone of this question to SQL Server 2005?
0
 

Author Comment

by:aatishpatel
ID: 24404001
i have no idea how do one change zones
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24404804
didn't acperkins answer your question in his first post?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24408682
Thanks, Tim.  I thought I had, too.  But as you can see the saga continues:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24416284.html
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24408686
Actually there are three other threads from the same author on the same subject.  If only they had listened.

Oh well...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24408796
Apparently my suggestions today to the same author aren't really registering either...

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24416284.html?cid=1066#a24408636
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we have discussed the manual scenarios to recover data from Windows 10 through some backup and recovery tools which are offered by it.
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
This video discusses moving either the default database or any database to a new volume.

760 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

15 Experts available now in Live!

Get 1:1 Help Now