How to Compress .ldf File ?

krishna kanhaiya

9y1.4kLearn iOS Programming

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

ali tuncer9y ago
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 kanhaiya9y ago
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 tuncer9y ago
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 kanhaiya9y ago
Hi Tejas, that code are not run give error
"
Could not locate file 'Database' for database 'Database' in sys.database_files"
krishna kanhaiya9y ago
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 tuncer9y ago
I think you can use DBCC SHRINKFILE to compress log files..
https://msdn.microsoft.com/en-us/library/ms189493.aspx
Tejas Trivedi9y ago
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.