Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Log files

Posted on 2013-10-29
5
Medium Priority
?
424 Views
Last Modified: 2013-10-31
Hi guys,


How to find free log space available in sql server using sql query?

eg: in ldf files how much space available for use ...
0
Comment
Question by:Sagir87
[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
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Big Monty
ID: 39608599
here's a script that'll get Data & log file size, space used & free space

------------------------------Data file size---------------------------- 
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') 
drop table #dbsize 
create table #dbsize 
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0)) 
go 
  
insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) 
exec sp_msforeachdb 
'use [?]; 
  select DB_NAME() AS DbName, 
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,  
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),  
sum(size)/128.0 AS File_Size_MB, 
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, 
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB  
from sys.database_files  where type=0 group by type' 
  
  
  
  
  
go 
  
-------------------log size-------------------------------------- 
  if exists (select * from tempdb.sys.all_objects where name like '#logsize%') 
drop table #logsize 
create table #logsize 
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0)) 
go 
  
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) 
exec sp_msforeachdb 
'use [?]; 
  select DB_NAME() AS DbName, 
sum(size)/128.0 AS Log_File_Size_MB, 
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, 
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB  
from sys.database_files  where type=1 group by type' 
  
  
go 
--------------------------------database free size 
  if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%') 
drop table #dbfreesize 
create table #dbfreesize 
(name sysname, 
database_size varchar(50), 
Freespace varchar(50)default (0.00)) 
  
insert into #dbfreesize(name,database_size,Freespace) 
exec sp_msforeachdb 
'use [?];SELECT database_name = db_name() 
    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'') 
    ,''unallocated space'' = ltrim(str(( 
                CASE  
                    WHEN dbsize >= reservedpages 
                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 
                    ELSE 0 
                    END 
                ), 15, 2) + '' MB'') 
FROM ( 
    SELECT dbsize = sum(convert(BIGINT, CASE  
                    WHEN type = 0 
                        THEN size 
                    ELSE 0 
                    END)) 
        ,logsize = sum(convert(BIGINT, CASE  
                    WHEN type <> 0 
                        THEN size 
                    ELSE 0 
                    END)) 
    FROM sys.database_files 
) AS files 
,( 
    SELECT reservedpages = sum(a.total_pages) 
        ,usedpages = sum(a.used_pages) 
        ,pages = sum(CASE  
                WHEN it.internal_type IN ( 
                        202 
                        ,204 
                        ,211 
                        ,212 
                        ,213 
                        ,214 
                        ,215 
                        ,216 
                        ) 
                    THEN 0 
                WHEN a.type <> 1 
                    THEN a.used_pages 
                WHEN p.index_id < 2 
                    THEN a.data_pages 
                ELSE 0 
                END) 
    FROM sys.partitions p 
    INNER JOIN sys.allocation_units a 
        ON p.partition_id = a.container_id 
    LEFT JOIN sys.internal_tables it 
        ON p.object_id = it.object_id 
) AS partitions' 
----------------------------------- 
  
  
  
if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%') 
drop table #alldbstate  
create table #alldbstate  
(dbname sysname, 
DBstatus varchar(55), 
R_model Varchar(30)) 
   
--select * from sys.master_files 
  
insert into #alldbstate (dbname,DBstatus,R_model) 
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases 
--select * from #dbsize 
  
insert into #dbsize(Dbname,dbstatus,Recovery_Model) 
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online' 
  
insert into #logsize(Dbname) 
select dbname from #alldbstate where DBstatus <> 'online' 
  
insert into #dbfreesize(name) 
select dbname from #alldbstate where DBstatus <> 'online' 
  
select  
  
d.Dbname,d.dbstatus,d.Recovery_Model, 
(file_size_mb + log_file_size_mb) as DBsize, 
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, 
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace 
from #dbsize d join #logsize l  
on d.Dbname=l.Dbname join #dbfreesize fs  
on d.Dbname=fs.name 
order by Dbname 

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39609165
With

use db
exec sp_helpfile

you will get the files info, including the size. If teh log file size is big you can try to shrink it like this:

checkpoint
dbcc shrinkfile(log_file_name,0)

where log_file_name is the ldf file name as it can be seen in the first column(name) from sp_helpfile procedure execution.

after you run the shrink command you will check again the sp_helpfile to see if it had effect.

If the file didn't shrink you will have to backup the transaction log and then run a new shrink.

Article for log backup: http://www.mssqltips.com/sqlservertutorial/21/sql-server-backup-log-command/

A log backup should be done in concordance with a maintenance plan with full, differential and log backups.

However, shrinking the log file doesn't help much unless it is done after some big load/import process or after some big table operations. If it is just after regular usage the log file will grow back to the size it needs for that usage so you're back at square one. If you permanently shrink the log file will have a negative impact on overall performance because SQL will have to grow it back every time it needs to. Ideally you don't touch the log file size for regular usage and also is preferable to give it a bigger grow rate in MB and not %. If you don't have enough room on your drive then you better add more or add a second log file on a different driver.
0
 
LVL 3

Author Comment

by:Sagir87
ID: 39612400
hi,

I want short query because it is a production server and we want to check about free space available in log files and i do not want not used space that i already know.

thnx....
0
 
LVL 34

Accepted Solution

by:
Big Monty earned 1200 total points
ID: 39614039
here's a simple script you can run in query analyzer that'll give you the info you need on the currently selected database:

select
      name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
    from dbo.sysfiles a

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39614115
To find the % used in a log file you run this query:

DBCC sqlperf(LOGSPACE) WITH NO_INFOMSGS
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 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