SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Getting data and log size of all databases

Hi guys.

The procedure bellow get the data and log size of all databases in current instance.


CREATE PROCEDURE dbo.uspDatabaseSize
@tbl table (Database_Id int, DataUsedMB Float)

insert into @tbl
exec sp_MSforeachdb
'select db_id(''?'') , (SUM(ps.reserved_page_count)*8)/1024 from ?.sys.dm_db_partition_stats ps';
WITH DataSize
        DB_ID(d.instance_name)… Read more

3 comments, 1,469 reads

Posted in Adeilson Brito on 3 December 2011

Script Drop all tables of a database

Hi people, following a script that deletes all tables in a database. Enjoy.


(    Seq INT,
    Id INT,
    Name sysname);


-- While there ForeignKey exclude objects that are not referenced recursively


0 comments, 2,005 reads

Posted in Adeilson Brito on 27 December 2010

Grant permission to the profile

The Profiler is a powerful tool available to the dba, but it can also be very useful for system developers. Of course I'm thinking about development environments (production never). Thus, the profiler can help developers monitor the queries and stored procedures developed in the applications.


Follow the instruction to… Read more

0 comments, 712 reads

Posted in Adeilson Brito on 18 December 2010

Last restart of SQL Server

Occasionally I need to see what the date of last restart of SQL Server. The old method isto check the creation date of tempdb database. However, in SQL 2008 we have a DMV that can be used for this purpose: sys.dm_os_sys_info. Here's how:


1) Searching creation of tempdb

SELECT… Read more

1 comments, 976 reads

Posted in Adeilson Brito on 17 December 2010

Number of sessions per database

Sometimes when we need to do some maintenance on a SQL server, you need to verify which users are connected or which databases are being used. A quick and easy wayto check the server utilization is simply to identify which databases are being accessedat that time. Thus, I present two… Read more

1 comments, 1,138 reads

Posted in Adeilson Brito on 16 December 2010

Using DMV to identify members roles

Some times is necessary retrieve a list of members of the roles of a database. Doing this is simple, using the DMV sys.database_role_members. Below follow an example of how to do this.


Read more

5 comments, 2,409 reads

Posted in Adeilson Brito on 12 August 2010

Retrieving last backups of the databases

The script below is a simple and quick way to find out the last backup performed successfully to the databases an SQL server.


    , s.media_set_id
    , s.database_name
    , s.server_name
    , s.name
    , s.recovery_model
    , s.backup_start_date

0 comments, 716 reads

Posted in Adeilson Brito on 14 July 2010