Solved

Querying log size

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now