February 14, 2014 at 9:32 am
I'm trying to make a minor adjustment to my backup stored procedure but I'm struggling to get the result that I want!
Basically I want to find out the amount of space used by a database (which should be roughly be the same as the size of the backup file) to decide whether I should split my backup into multiple files or not. But for some reason (Friday afternoon thing?!) I can't seem to work out how to do it!
I know there is the sp_spaceused sproc but I don't know how to store the result from this into a table (without making a copy of the sproc and making the changes so it spits out the result into one row!
Please advise.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 14, 2014 at 9:58 am
Use <dbanme>
go
SELECT case
when sysfilegroups.groupname is NULL then 'LOG'
else sysfilegroups.groupname
end filegroup_name
,sum(convert(decimal(12,2),round(sysfiles.size/128.000,2))) as calc_total_mb
,sum(convert(decimal(12,2),round(fileproperty(sysfiles.name,'SpaceUsed')/128.000,2))) as calc_used_mb
,sum(convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,'SpaceUsed'))/128.000,2))) as calc_free_mb
,(sum(convert(decimal(12,2),round(fileproperty(sysfiles.name,'SpaceUsed')/128.000,2))) / sum(convert(decimal(12,2),round(sysfiles.size/128.000,2))))*100 calc_used_pc
FROM sys.sysfiles
LEFT OUTER JOIN sys.sysfilegroups
ON sysfiles.groupid = sysfilegroups.groupid
group by sysfilegroups.groupname
Let me know if it helps
February 14, 2014 at 10:24 am
Thanks very much for the query but in the end I cannibalised the sp_spaceused sproc to come up with the below:
It gives back the reserved size in MB, and when I tested it against the actual size of the backup it's almost the same give or take a megabyte or two.
DECLARE @Database_Name VARCHAR(50) = 'msdb'
EXEC ('select ltrim(str(sum(a.total_pages) * 8192 / 1024.,15,0) / 1024 )
from ' + @Database_Name + '.sys.partitions p
INNER JOIN ' + @Database_Name + '.sys.allocation_units a
on p.partition_id = a.container_id
left join ' + @Database_Name + '.sys.internal_tables it
on p.object_id = it.object_id')
Now I can use this in my loop so I can work out the size of each database I want to backup
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 14, 2014 at 10:47 am
Here's another option if you are interested
http://jasonbrimhall.info/2011/12/05/database-data-and-log-size-info/
and more granular detail
http://jasonbrimhall.info/2011/11/21/table-space-cs-part-deux/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 14, 2014 at 12:23 pm
I usually make use of the FileProperty function. It has served me pretty well.
select fileproperty(name, 'SpaceUsed')/128, size/128
from sys.database_files
Feel free to group by data_space_id, or file_id, if your backups go that way.
February 14, 2014 at 12:58 pm
Here is another alternative to get the info for all databases.
CREATE TABLE #FileProp (dbid INT,FILE_ID INT, SpaceUsed DECIMAL(14,2));
GO
EXECUTE sp_MSforeachdb 'USE ?; Insert Into #FileProp (dbid,FILE_ID,SpaceUsed)
SELECT database_id,file_id,FILEPROPERTY(name,''SpaceUsed'') from sys.master_files where DB_NAME(database_id) = ''?'''
SELECT DB_NAME(database_id) AS DBName,physical_name,CONVERT(DECIMAL(14,2),SIZE)/128 AS FileSize, growth
,CONVERT(DECIMAL(14,2),max_size)/128 AS MaxFileSize,FP.SpaceUsed/128 as SpaceUsed,mf.type_desc
FROM sys.master_files mf
INNER JOIN #FileProp FP
ON FP.dbid = mf.database_id
AND FP.FILE_ID = mf.FILE_ID;
DROP TABLE #FileProp;
GO
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 14, 2014 at 3:45 pm
Get Server Database File Information
This script gets the file information for every database on a server, and inserts it into temp table which is then queried multiple ways to give various levels of analysis of file space usage.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058
If you want to look at what tables are using the space in a particular database, try this:
Script to analyze table space usage
February 15, 2014 at 5:37 am
As you want the size of the backup file why don't you go straight to the backup information in msdb..backupset rather than estimate from file information?
something on these lines?
declare @dbname sysname
set @dbname = db_name()
select top 1 backup_start_date, backup_size/1048576 as 'size in MB'
from msdb..backupset
where database_name = @dbname and type = 'D'
order by backup_start_date desc
---------------------------------------------------------------------
February 17, 2014 at 4:10 am
Thanks very much all. This is all great code which I have found very useful!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply