Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

SQL Server lof giles issue

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
aatishpatel
Asked:
aatishpatel
  • 10
  • 8
  • 3
  • +1
1 Solution
 
Anthony PerkinsCommented:
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
 
bull_riderCommented:
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
 
aatishpatelAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
bull_riderCommented:
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
 
aatishpatelAuthor Commented:
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
 
aatishpatelAuthor Commented:
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
 
bull_riderCommented:
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
 
aatishpatelAuthor Commented:
and so shrinking the size of the log file wouldn't create any problem?  right
0
 
bull_riderCommented:
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
 
aatishpatelAuthor Commented:
when i gave
GO
DBCC SHRINKFILE (abc, 50)
GO


its shrinked, did not gave path is it ok. ?????????
0
 
bull_riderCommented:
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
 
aatishpatelAuthor Commented:
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
 
bull_riderCommented:
no the size shoud be in MB, so convert 25 GB to MB, I mean 25 * 1024 MB and specify it there.
0
 
aatishpatelAuthor Commented:
so 25600 is right?  just want to make sure sir.........
0
 
bull_riderCommented:
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
 
aatishpatelAuthor Commented:
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
 
aatishpatelAuthor Commented:
The best solution i had ever had and very very exact one tooo for a  dumb guy like me.   thanx again
0
 
bull_riderCommented:
Aatish one more thing can you please change the zone of this question to SQL Server 2005?
0
 
aatishpatelAuthor Commented:
i have no idea how do one change zones
0
 
chapmandewCommented:
didn't acperkins answer your question in his first post?
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
Actually there are three other threads from the same author on the same subject.  If only they had listened.

Oh well...
0
 
chapmandewCommented:
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
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 10
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now