Solved

Querying log size

Posted on 2009-04-01
4
327 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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

689 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