Solved

Querying log size

Posted on 2009-04-01
4
320 Views
Last Modified: 2012-05-06
I'm about to write a script to tell me the log size before/after some events.
So I was looking into the sys.xxx to see where I could find the one for log size and came up with
select * from sys.database_files
This tells me the log is 3163192 (31GB) yet when I double check this with windows explorer the log (file size) is 25GB.
I have right clicked and ensured the size on disk is also 25GB.
When I look at the disk space usage reports it says the log is 24.7GB and 99.3% unused.

Why the huge size difference between what windows explorer tells me and what database_files says.
0
Comment
Question by:QPR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24046750
which column of sys.database_files did you check in order to determine the size?
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 250 total points
ID: 24046824
sys.database_files stores size in pages. Page size is 8KB

To get size in GB use this:
select (siZe  * 8)/(1024.0*1024.0)AS SizeInGB, *    from sys.database_files
0
 
LVL 8

Assisted Solution

by:vinurajr
vinurajr earned 250 total points
ID: 24046835
sp_helpdb DBName will tells the exact file size or the sysdatabases
you Interpreted wrongly.... The size in the sys.database_files is nothing but Current size of the file, in 8-KB pages.


select (3163192*8)/(1024.0*1024.0)
0
 
LVL 29

Author Comment

by:QPR
ID: 24046880
ahhh I see I was using select size/1000 and assumed I was dealing with actual file size.
Points split:
Rimvis for pointing out my problem and vinurarj for reminding me about sp_helpdb which I will now use
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query help 2 66
INSERT DATE FROM STRING COLUMN 18 66
Can Unique column have more than one Null? 8 65
Urgent help needed! Unable to to paste in query designer 29 57
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question