SQL Server database file size

Keeping track of database size and growth is a critical part of a DBA’s job which I wrote about in an earlier blog post. But now I have improved my scripts that reveal the status of all my db files (I say ‘files’ as each SQL Server database is made up of at least two files).
Many of my db’s are set to autogrow but I also have maximum sizes that I allow these to grow. This is important to me because I want to know about out of control database growth and not just let them fill up my storage. So I need to know, periodically, where my db’s are at. The 25% is arbitrary, you can pick any number that works for you. Also, if you have HUGE db’s you may need to adjust the decimal precision for the output.

Here’s my script:

--all dbs over 25% full with sizes too
select db.name as [db_name], mf.name,
'dbSizeinMB' =
cast (cast (mf.size*1.0/128 as decimal(9,2)) as nvarchar(30)),
'MaxSizeinMB' =
case mf.max_size
when 0 then 'no growth is allowed.'
when -1 then 'autogrowth is on.'
when 268435456
then 'log file will grow to a maximum size of 2 tb.'
else cast (cast (mf.max_size*1.0/128 as decimal(9,2)) as nvarchar(30))
end,
'PercentageSize' =
case mf.max_size
when 0 then 'no growth is allowed.'
when -1 then 'autogrowth is on.'
else cast (cast(((mf.size*1.0)/(mf.max_size*1.0))*100 as decimal(9,2)) as nvarchar(30))
end
from sys.master_files mf, sys.databases db
where mf.database_id = db.database_id and ((mf.size*1.0)/(mf.max_size*1.0)*100) > 25.0
order by db.name

Of course you don’t always have time to run a script to check up this so I have a maintenance plan set up with this content that e-mails be the report every day at 6AM:

--all db files over 25% full with sizes too
DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'<H1>db file Percentage Size Check</H1>' +
N'<table border="1">' +
N'<tr><th>db_name</th><th>name</th>' +
N'<th>dbSizeinMB</th><th>MaxSizeinMB</th><th>PercentageSize</th></tr>' +
CAST ( ( SELECT td = db.name, '',
td = mf.name, '',
td = cast (cast (mf.size*1.0/128 as decimal(9,2)) as nvarchar(30)), '',
td = case mf.max_size
when 0 then 'no growth is allowed.'
when -1 then 'autogrowth is on.'
when 268435456
then 'log file will grow to a maximum size of 2 tb.'
else cast (cast (mf.max_size*1.0/128 as decimal(9,2)) as nvarchar(30))
end, '',
td = case mf.max_size
when 0 then 'no growth is allowed.'
when -1 then 'autogrowth is on.'
else cast (cast(((mf.size*1.0)/(mf.max_size*1.0))*100 as decimal(9,2)) as nvarchar(30))
end
FROM sys.master_files mf, sys.databases db
where mf.database_id = db.database_id and ((mf.size*1.0)/(mf.max_size*1.0)*100) > 25.0
order by db.name
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name ='My Profile',
@recipients=N'me@provider.com;you@provider.com',
@subject = 'MyServer db files over 25% max size',
@body = @tableHTML,
@body_format = 'HTML' ;

This applies to SQL Server 2005 and newer.

SQL Server copy_only backup and restore

Previously I wrote an article listing information on SQL Server backups. I want to expand a little on the practical use of the new and useful copy_only backup available in SQL Server 2005 and 2008. You can’t get to copy_only backups/restores through the Management Studio for SQL Server 2005 (you have to use T-SQL) but you can using the new Management Studio for SQL Server 2008.

This is an ideal way to test production db’s on a development server without disrupting your normal backup schedule. In the following example I am doing a full db backup to the production backup drive:

BACKUP DATABASE [my_db]
TO DISK = N'Z:\backup\my_db_copyonly.bak'
WITH
NOFORMAT,
INIT,
copy_only,
NAME = N'my_db-Full Database CopyOnly Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

But when I restore it I would want to be sure that I’m moving the db files to the correct location on my development SQL Server, hence the WITH MOVE subcommands below.   I always want it to have a completely new name:

RESTORE DATABASE [my_db_copy]
FROM DISK = N'Z:\backup\my_db_copyonly.bak'
WITH
MOVE 'my_db_dat' TO 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_db_copydat.mdf',
MOVE 'my_db_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_db_copylog.ldf',
FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

So now I have an up-to-date version of the production db on my system. Bear in mind that any logins for applications or users that are necessary would need to be recreated to be able work with this new copy. I keep a special login/user script handy and run it to recreate these.

Autocompletion and Intellisense in SQL Server

I understand you can use third party tools to add autocompletion and intellisense to Management Studio 2005 (for example, Redgate offers SQL Prompt Pro) but I haven’t tried it yet. I have installed SQL Server Express 2008 to play with.  I tried the new intellisense feature and I have to say I’m impressed with how nicely it’s built in to Management Studio.

For example, if you wait long enough after entering ‘use ‘ (don’t forget the space) it’ll give you a drop down of available db’s. That’s cool.
But even more nifty is typing ‘exec usp’ and then picking one of my own stored procedures from the list. Then hit space and there’s a list of the necessary parameters.  Wow!
And if you name all your tables with a ‘tbl’ prefix you can try ‘select * from tbl’ and you should see a list of the tables. And, again if you use a standard naming convention you can pick a table and then a likely prefix for a column in a where clause like ‘select * from tblnappy where str’ and get all the string columns you have listed. Or functions close to that spelling.
For more information access BOL on the MS website and look at the following topics:

It certainly makes be eager to get the new computer that’s coming to me at work so I can install the Developer edition and start working with it.