I had to write a stored procedure that writes the current database size to a text file on a shared network location. This needed to happen each month, and we will use SQL Jobs for that... I didn't find any good solutions on the net so I thought to share this one!

Stored procedure code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        CODit
-- Create date: 05/01/2010
-- Version: 1.1
-- Description:   Calculate Database Size and write to a file
-- =============================================
CREATE PROCEDURE [dbo].[qry_spaceused_toFile]
 @FullFolderPath varchar(255),
 @DatabaseName varchar(255),
 @SQLServerName varchar(255)
AS
BEGIN
declare @myDate varchar(255),
@QueryString varchar(4000),
@QueryString2 varchar(4000)

Select @myDate = convert(varchar, getdate(), 2)
Select @FullFolderPath = @FullFolderPath + @SQLServerName + '--' + @myDate + '.txt'
Select @QueryString = 'sqlcmd -S"'+@SQLServerName+'" -E -d '+@DatabaseName+' -Q"execute sp_spaceused" >>"'+@FullFolderPath+'" -s"" '
Select @QueryString2 = 'type "'+@FullFolderPath+'"'
create table #errorlog(line varchar(2000))
execute master.dbo.xp_cmdshell @QueryString

insert into #errorlog 

execute master.dbo.xp_cmdshell @QueryString2

Select @QueryString = 'sqlcmd -S"'+@SQLServerName+'" -E -d '+@DatabaseName+' -Q"print CHAR(13)" >>"'+@FullFolderPath+'" -s"" '
execute master.dbo.xp_cmdshell @QueryString
execute master.dbo.xp_cmdshell @QueryString

select line from #errorlog

drop table #errorlog
END

Usage (Make sure your path folder already exists):

exec qry_spaceused_toFile 'PathToFileLocation', 'DataBase', 'SQLServerName'

Possible Output:

database_name       database_size     unallocated space  
------------------------------------------------------------ 
CIPPlatform             6.75 MB             0.81 MB             

reserved          data              index_size        unused            
------------------------------------------------------------
3264 KB           1712 KB           1288 KB           264 KB

Explanation:

We are using the sqlcmd to write to a text file. This has some limitations, but it's fine to use in our situation. I didn't find any easy method to write to a file using sql. If you want to learn more about the sqlcmd utility, I suggest you to read this tutorial. As the name states, sqlCMD, you normally use this command in a command prompt. However, you can use it in SQL by using the master.dbo.xp_cmdshell procedure! We are using the built-in system query 'sp_spaceused' to calculate the space. After executing, you'll see two resultsets. In order to write those 2 resultsets to a file we write the results to a temporary table and write the contents that are stored in the table. The output file name is a concatenation of the database name and date.