28. January 2009 by Bennett.

Multiply SQL database pages by 0.0078125 to get space in Megabytes
Multiply SQL database pages by 0.00000762939453125 to get space in Gigabytes

Here are a couple of examples of where this is useful:

This query uses a DMV to return TempDB utilization by object category such as user, internal, and version store:

SELECT
SUM(user_object_reserved_page_count)*0.0078125 as usr_obj_mb,
SUM(internal_object_reserved_page_count)*0.0078125 as internal_obj_mb,
SUM(version_store_reserved_page_count)*0.0078125  as version_store_mb,
SUM (unallocated_extent_page_count)*0.0078125 as freespace_mb,
SUM (mixed_extent_page_count)*0.0078125 as mixedextent_mb
FROM sys.dm_db_file_space_usage 

The next query returns space utilization , in Megabytes, by filegroup:

select name, filename, cast(size * 0.0078125 as int)as size_mb
from sysfiles
ORDER BY FILENAME
compute sum(cast(size * 0.0078125 as int))

Posted in T-SQL, SQL Server | Print | No Comments »

7. January 2009 by Bennett.

I am frequently asked to refresh development databases with production data.  The usual way to do this is to  back up the production database and then restore over the development database.  SQL Server 2005 has a new backup option, “WITH COPY_ONLY”.   This option allows you to perform a full backup without truncating the log and breaking the log chain.  The COPY_ONLY option is not supported in the SQL Server Management Studio (SSMS) GUI, so you have to perform the backup via a script.

It’s important to note that a backup that is created with the COPY_ONLY option cannot be restored with the SSMS GUI.  Instead you have to restore via a script.

SQL Server 2008 supports the COPY_ONLY option in the SSMS GUI.

Posted in SQL Server | Print | 1 Comment »