How to Compress .ldf File ?

Question

I have writen a program that are take database name and reduce the size of .ldf file

ali tuncer · Answer

if you want to shrink a log file, I don' think you need to alter database, get your fileId from this query : SELECT * FROM sys.database_files; Then you can run ShrinkFile command : DBCC SHRINKFILE (fileID, targetSize); That error sounds like either you don't have permission, or you are sending wrong parameters..

krishna kanhaiya · Answer

Hi Ali, that is error " Msg 5011, Level 14, State 5, Procedure sp_CompressDataBaseLogFile, Line 16 User does not have permission to alter database '@DBName', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Procedure sp_CompressDataBaseLogFile, Line 16 ALTER DATABASE statement failed. Msg 8985, Level 16, State 1, Procedure sp_CompressDataBaseLogFile, Line 17 Could not locate file '@LogName' for database '6245_QMS_ToolTest' in sys.database_files. The file either does not exist, or was dropped. Msg 5011, Level 14, State 5, Procedure sp_CompressDataBaseLogFile, Line 18 User does not have permission to alter database '@DBName', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Procedure sp_CompressDataBaseLogFile, Line 18 ALTER DATABASE statement failed. "

ali tuncer · Answer

What is the error message you are getting? Looking at your stored procedure I can see one problem, when you create parameter, you need to give size, like @DBName nvarchar(50), if you don't give size, it will create nvarchar(1)...

krishna kanhaiya · Answer

Hi Tejas, that code are not run give error " Could not locate file 'Database' for database 'Database' in sys.database_files"

krishna kanhaiya · Answer

My database name and .ldf file name put in procedure but did't execute .... Create procedure sp_CompressDataBaseLogFile @DBName nvarchar, @LogName nvarchar AS BEGIN ALTER DATABASE [@DBName] SET RECOVERY SIMPLE WITH NO_WAIT DBCC SHRINKFILE('@LogName', 1) ALTER DATABASE [@DBName] SET RECOVERY FULL WITH NO_WAIT END "That Store Procedure is Successfully Created but when we execute this store procedure they are given error" exec sp_CompressDataBaseLogFile 'DatabaseName',Database_log' How Could do this Thanks in Advance

ali tuncer · Answer

I think you can use DBCC SHRINKFILE to compress log files.. https://msdn.microsoft.com/en-us/library/ms189493.aspx

Tejas Trivedi · Answer

Hi Krishna, You can write a program which execute the DBCC query on the target sql server to shrink the database. DBCC SHRINKDATABASE {DATABASENAME} Hope, this will help you out. Don't forget to mark as answer if this helps.

How to Compress .ldf File ?

Answers (7)