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.