Determine the file size of a database using t-sql

Determine the file size of a database using t-sql

In SQL Server there are a number of ways to determine how large a database is. Right clicking and selecting properties > files in management studio for example is probably the easiest. However if you do not have very high permissions on the database you are interested in this t-sql may be what you need. 

This script requires the least permissions of all the different methods I could find and is fairly simple which is a bonus.

select a.FILEID,
      [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),
      [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
      [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)),
      NAME = left(a.NAME,15),
      FILENAME = left(a.FILENAME,30)
from dbo.sysfiles a

Tested in SQL Server 2005+ 

Shrink all SQL Server Databases

A quick way to ensure all the databases on your dev pc/server are all set to simple backup and log files are shrunk to avoid wasting disk space.

You can easily add databases to the list you don't want affected.

declare @ssql nvarchar(4000)

set @ssql= '

        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'')

        begin

                  use [?]

                  Alter database [?] SET Recovery simple

        end'

exec sp_msforeachdb @ssql

set @ssql= '

        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'')

        begin    

                  use [?] 

                  Declare @LogFileLogicalName sysname

                  select @LogFileLogicalName=Name from sys.database_files where Type=1

                  --print @LogFileLogicalName

                  DBCC Shrinkfile(@LogFileLogicalName,1)

        end'

exec sp_msforeachdb @ssql

Script to kill all connections to a database

When restoring/moving databases its a pretty common requirement to kill all the connections to the database in question otherwise you can't get a lock on it. Killing all the connections to a database can be a pain though if you have to use the UI. This script is much quicker and can be a time saver. If you’re super keen you could modify the script to make it a stored procedure so it’s always ready to go. If you do want to use the UI here is a blog post on how to do it.

--
-- Kill connections to a given sql server database
--

declare @execSql nvarchar(MAX), @databaseName varchar(100)
set @databaseName = '<your database name here>'

set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec(@execSql)