[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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