Solved

SQL Server Log files

Posted on 2013-10-29
5
415 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 33

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 26

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 33

Accepted Solution

by:
Big Monty earned 300 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 26

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

733 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