I had to write a stored procedure that writes the current database size to a text file on a shared network location. We also need to do this each month, but we’ll use SQL Jobs for that… I didn’t find any good solutions on the net so I will share this one!
Now let’s get to the 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.