SQL Server Performance Forum – Threads Archive
how to find log file usage ?
Hi all,When i checked graphicaly(through shink file dialog box), i found..
** log file size is 11GB
** space used is 3GB >How can i check same from QA ?
sp_helpfile….
it seems that you need to free up unused space using <b>dbcc shrinkfile </b> statements….<br /><br /><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />][<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />][:I]<br /><br />hsGoswami<br />[email protected]
*sp_helpfile will only display total file size not the usage of log file , any other suggestions? *Yes, i shunk and truncated the log .Now i can see Total fize size as 4 GB and used is 84MB .I have srunk+truncated the log, But Y by default its trucating only up to 4GB , as used is only 84MB .
Hi,<br />check <b>dbcc log(yrdatase)</b> for more information….<br /><br /><br /><br />[<img src=’/community/emoticons/emotion-4.gif’ alt=’
‘ />]<br /><br />HTH.<br /><br /><br /><br /><br />hsGoswami<br />[email protected]
Hi can anyone give reply to my pervious post ? THanks in advance
1. What do you mean ‘usage’?
2. Maybe because:
BOL, DBCC SHRINKFILE:
Because a log file can only be shrunk to a virtual log file boundary, it may not be possible to shrink a log file to a size smaller than the size of a virtual log file, even if it is not being used. For example, a database with a log file of 1 GB can have the log file shrunk to only 128 MB. For more information about truncation, see Truncating the Transaction Log. For more information about determining virtual log file sizes, see Virtual Log Files. WBR, Vlad A. Scherbinin
Hi Ray D ,<br /><br />Please check my first post .<br /><br />there are two things, i saw through GUI ,<br />Total size = Physical file size<br />File usage = Amount of file, DB using to record log<br /><br />Eg<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />uppose your log file siz was 300MB,and you are doin a batch update, and log grown upto 1GB.<br />After taking a log backup, your Logfile usage will come down to some few MB ,But the Physical file size still will be 1GB.<br /><br />I just need somethin to check Physical log size and its file usage, from QA. Still I am searching for the solution <br /><br />Thanks’
Ok. Sorry, I misunderstood you.<br />EM do it with the following commands for DB:<br />select ceiling((sum(size) * (8192.0/1024.0)) / 1024.0) from dbo.sysfiles<br />exec sp_spaceused<br />DBCC SQLPERF(LOGSPACE)<br /><br />and this for individual files:<br />SELECT o.fileid, o.name, o.filename, o.groupid, o.size, o.maxsize, o.growth, o.status FROM dbo.sysfiles o WHERE o.groupid = (SELECT u.groupid FROM dbo.sysfilegroups u WHERE u.groupname = N’PRIMARY’) and (o.status & 0x40) = 0<br /><br />dbcc showfilestats( 1 )<br /><br />SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0<br /><br />You can check this (and many other things, that EM do <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /> ) with the use of the SQL Profiler. Hope this helps <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />WBR, Vlad A. Scherbinin
]]>