sp_ to find log file usage | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

sp_ to find log file usage

Hi any stored procedure to find logfile usage.I want the output as logile
*Space allocated : x GB
*current usage : Y GB Thanks
dbcc sqlperf(logspace) this will give you % filled from transaction log file. Deepak Kumar –An eye for an eye and everyone shall be blind
Yes i ran the script, and got following output DATABASE LOG FILE SIZE %USED
CallTrack4121.55470.83998901%
can you please tell me how to bring down physical log file size to 512MB.Please tell me command for that.
dbcc shrinkfile (db_log, 100) — This will reduce transaction log file size then alter it for 512MB Note: db_log is the logical name log file for the database.
For more informations, pls read from below post : – http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8018 Deepak Kumar –An eye for an eye and everyone shall be blind
Deepak, in EM i found logfile size is 3.7Gb but usedspace is showing only 34MB . I already made a default logshrink from EM . tell me how can i alter it for alter it for 512MB

There is a brute force method too.. but you need to take full backup as soon as you execute below code…
Use [sales]
Go
alter database [sales] set recovery simple
go
dbcc shrinkfile (sales_log, 100)
go
checkpoint
go
dbcc shrinkfile (sales_log, 100)
alter database [sales] set recovery full
go
— Change database name and log file name as per your database. I would recommand you to read http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8018 for thorough informations…. Deepak Kumar –An eye for an eye and everyone shall be blind
Can you please explain this script?Mine is Production database,
*)simply can i switch Recovery models?
*)What is tht 100 indicates? in my case is it 512? Simply shrinkin file wil reduce the physical
file size?
*)Y two shrink statements Please advice me
if you have a few minutes.. visit below link..under that we have already discuss all your queries<br /><br /<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8018>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8018</a> <br /><br />Still if you find something to ask.. MOST WELCOME<br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Deepak Kumar<br /><br />–An eye for an eye and everyone shall be blind
Deepak,<br /><br />If i am not wrong none of my Questions are answered there.You were asking JooZH to execut the script without briefing about it <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> .I would like to know the things happenin <br /><br />From EM i shrunk but still phy logfile size is 3Gb and space used is only 34MB. By using again two shrink statments in ur script will help?? I am totally confused. Please brief me the mechanism.<br /><br />Thanks in advance .<br /><br />rajiv
Actually there are 2 pages in link i gave you.. on second page i explained like: – ********************************************************************************************** alter database [sales] set recovery simple
go
dbcc shrinkfile (sales_log, 100) When we change database recovery model to simple.. SQL engine drops all inactive transactions (read simple recovery model characteristics) and then we try to shrink database.. checkpoint
go
dbcc shrinkfile (sales_log, 100) Issuing a checkpoint forces all dirty pages for the current database to be written to disk. So log file get new entries. Again executing shrink file again removes free space from log file. alter database [sales] set recovery full
go –This put back recovery model to full. Having a full backup is required because sql engine has dropped all transaction without tran backup and its equivalent to have a database in simple recovery since the last full backup you took. So if you need to recover database till last transaction.. You can’t. Taking a new full backup will start a new backup process from their onwards.. As per your backup and recovery strategy. ********************************************************************************************** If you still confuse or in problem.. let us know.. we will try our level best to help you
Deepak Kumar –An eye for an eye and everyone shall be blind
From EM i shrunk but still phy logfile size is 3Gb and space used is only 34MB. By using again two shrink statments in ur script will help?? I am totally confused.
In above coding.. twice executing DBCC Shrink is not importent..but importent is changing recovery model of database (its a workaround). if you are not convince with this script.. take transaction log or full backup and try to shrink log file from enterprises manager.. this will also reduce file size. Deepak Kumar –An eye for an eye and everyone shall be blind
Krishn I would suggest you to go with QA results rather than believing in ENterprise Manager, as it meant to be flaky at times. I’m sure Deepak’s tip will work as per your requirement. As BOL specifies
For log files, SQL Server uses target_size to calculate the target size for the entire log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file. DBCC SHRINKFILE attempts to shrink each physical log file to its target size immediately. If no part of the logical log resides in the virtual logs beyond the log file’s target size, the file is successfully truncated and DBCC SHRINKFILE completes with no messages. However, if part of the logical log resides in the virtual logs beyond the target size, SQL Server frees as much space as possible and then issues an informational message. The message tells you what actions you need to perform to move the logical log out of the virtual logs at the end of the file.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>