How to copy bulk data from any data source to SQL Server

You can use System.Data.SqlClient.SqlBulkCopy .NET component to load data from any data source to sql server directly without using any intermediate file or in memory object. However, there should be a datareader .NET component supporting data source. Here is an example to load data from oracle (from table TABLEORA) to sql server (into table TABLESQL):

OracleConnection myConn;
SqlConnection dest = new SqlConnection("Data Source...");
myConn = new OracleConnection("...");
try
{
myConn.Open();
dest.Open();
OracleCommand sourceCommand = new OracleCommand(
"select ... from TABLEORA ... ", myConn);

// Use bulk copy to copy data to sql server
SqlBulkCopy s = new SqlBulkCopy(dest);
s.BulkCopyTimeout = 3600;
using (OracleDataReader dr = sourceCommand.ExecuteReader())
{
using (s)
{
s.DestinationTableName = "TABLESQL";
s.WriteToServer(dr);
s.Close();
}
}
myConn.Close();
}
catch (Exception e)
{
....
}

SQL Server 2000 Server Side Performance Tracing

Here is a method, i usually use to collect sqls running on a sql server 2000 with their CPU, duration, read and write info and then calculate cumulative executions, HW usage and time spent statistics of these sqls.

Lets assume there is a database named DBA used for intermediate tables and a path for trace file "S:\MSSQL\trace\batchcompleted".

First of all create trace table on DBA:

CREATE TABLE [dbo].[TRACELOG] (
[EventClass] [int] NULL ,
[TextData] [ntext] COLLATE SQL_Latin1_General_CP1254_CS_AS NULL ,
[NTUserName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1254_CS_AS NULL ,
[ClientProcessID] [int] NULL ,
[ApplicationName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1254_CS_AS NULL ,
[LoginName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1254_CS_AS NULL ,
[SPID] [int] NULL ,
[Duration] [bigint] NULL ,
[StartTime] [datetime] NULL ,
[Reads] [bigint] NULL ,
[Writes] [bigint] NULL ,
[CPU] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Lets create a job with 3 steps.

1. On failure action "Go to Next Step" should be set for this step:
declare @trace_id int
select @trace_id = traceid FROM :: fn_trace_getinfo(default) where value='S:\MSSQL\trace\batchcompleted'
exec sp_trace_setstatus @trace_id,0
exec sp_trace_setstatus @trace_id,2

2. On failure action "Go to Next Step" should be set for this step:
insert into DBA.dbo.TRACELOG SELECT EventClass,TextData,NTUserName,ClientProcessID,ApplicationName,LoginName,SPID,Duration,StartTime,Reads,Writes,CPU FROM ::fn_trace_gettable('S:\MSSQL\trace\batchcompleted.trc', default)
exec master.dbo.xp_cmdshell 'del /Q S:\MSSQL\trace\batchcompleted.trc'

3.
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1000
exec @rc = sp_trace_create @TraceID output, 0, N'S:\MSSQL\trace\batchcompleted', @maxfilesize, NULL
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
set @bigintfilter = 1
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go

Here is the sql code of my job:

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') <>
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'PERFSTAT_COLLECT')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''PERFSTAT_COLLECT'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'PERFSTAT_COLLECT'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'PERFSTAT_COLLECT', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'stop_trace', @command = N'declare @trace_id int
select @trace_id = traceid FROM :: fn_trace_getinfo(default) where value=''S:\MSSQL\trace\batchcompleted''
exec sp_trace_setstatus @trace_id,0
exec sp_trace_setstatus @trace_id,2
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'save_remove_trace', @command = N'insert into DBA.dbo.TRACELOG SELECT EventClass,TextData,NTUserName,ClientProcessID,ApplicationName,LoginName,SPID,Duration,StartTime,Reads,Writes,CPU FROM ::fn_trace_gettable(''S:\MSSQL\trace\batchcompleted.trc'', default)
exec master.dbo.xp_cmdshell ''del /Q S:\MSSQL\trace\batchcompleted.trc''
', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'start_trace', @command = N'declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1000
exec @rc = sp_trace_create @TraceID output, 0, N''S:\MSSQL\trace\batchcompleted'', @maxfilesize, NULL
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N''SQL Profiler''
set @bigintfilter = 1
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'stat_sched', @enabled = 1, @freq_type = 4, @active_start_date = 20060717, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 30, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Schedule the job as running every 30 minute whole day. After collecting trace data for a hour, run following to remove parameters in sql texts (in order to be able to group same sqls running with different parameters) and insert into another table.

select
case when substring(TextData,1,7) = 'declare'
Then substring(
cast(TextData as varchar(200)),
charindex('exec',cast(TextData as varchar(300))),200)
else
cast(TextData as varchar(200))
end TextData,
Duration, StartTime, Reads, Writes, CPU into TBLRESULT from (
select * from dbo.TRACELOG
where StartTime between (getdate()- cast(cast(1 as float)/cast(24 as float) as float)) and getdate() and CPU > 100
)a --where TextData like '%coalesce%'
order by Duration desc

Then a sql like this can help you get cumulative data:

SELECT TOP 100 PERCENT CAST(TextData AS varchar(100)) AS TEXT, COUNT(*) AS EXECUTIONS,AVG(Duration) AVR_Duration, AVG(Reads) AS AVR_READ, SUM(Reads) AS SUM_READ,
SUM(Reads) * 100 /
(SELECT SUM(Reads)
FROM dbo.TBLRESULT) AS PER_READ, AVG(Writes) AS AVR_WRITE, SUM(Writes) AS SUM_WRITE, SUM(Writes) * 100 /
(SELECT SUM(Writes)
FROM dbo.TBLRESULT) AS PER_WRITE, AVG(CPU) AS AVR_CPU, SUM(CPU) AS SUM_CPU, SUM(CPU) * 100 /
(SELECT SUM(CPU)
FROM dbo.TBLRESULT) AS PER_CPU
FROM dbo.TBLRESULT WITH (nolock)
GROUP BY CAST(TextData AS varchar(100))
HAVING (COUNT(*) > 0)
ORDER BY SUM_CPU DESC

At the end, dont forget to stop server side trace. You can use code in first step of the job to stop it.

MS Windows Debug

In order to debug windows processes or dumps you can install following file:
http://msdl.microsoft.com/download/symbols/debuggers/dbg_x86_6.7.05.1.exe
How to dump:
http://support.microsoft.com/kb/286350/
How to use Windbg:
http://support.citrix.com/article/CTX106566

SQL Server Standby Recovery

Recover can be done with a similar command as following:

restore log {db_name} from DISK=N'...\{db_name}_tlog_{datetime}.TRN' with standby = '...\UNDO_{db_name}.DAT'

Warning: Do not lost undo file. It is required to apply next log.
When a database or transaction log is restored in standby mode, recovery needs to roll back any uncommitted transactions so that the database can be left in a logically consistent state and used, if necessary, for read-only purposes. Pages in the database affected by the uncommitted, rolled back transactions are modified. This undoes the changes originally performed by the uncommitted transactions. The undo file is used to save the contents of these pages before recovery modifies them to prevent the changes performed by the uncommitted transactions from being lost. Before a subsequent transaction log backup is next applied to the database, the uncommitted transactions that were previously rolled back by recovery must be reapplied first. The saved changes in the undo file are reapplied to the database, and then the next transaction log is applied

If prod-standby database files are at different paths/drives and new datafiles are added to prod database, log shipping will fail with the following error:

Device activation error. The physical file name ...\MSSQL\DATA... may be incorrect.

You should apply these logs manually by moving logical datafiles to valid physical locations at standby server:

sp_resetstatus 'db_name'
restore log db_name from DISK=N'...\{db_name}_tlog_{datetime}.TRN' with standby = '...\UNDO_{db_name}.DAT',
MOVE '{Newly_Added_Datafile_Logical_Name}' to '{Valid_Path}\{Physical_Datafile_Name}',
MOVE '{Newly_Added_Datafile_Logical_Name}' to '{Valid_Path}\{Physical_Datafile_Name}',
MOVE '{Newly_Added_Datafile_Logical_Name}' to '{Valid_Path}\{Physical_Datafile_Name}',
...

How to change service accounts for a clustered SQL Server computer

(SQL Server 2000 and SQL Server 2005)
Although the account used is automatically assigned the appropriate privileges during the installation process, if the account is changed, it (or the Administrator group) must have the following attributes:

• It must be a domain account.
• It must be a member of the local Administrators group (Windows NT 4.0 only).
• It must be granted the following policies:

a. Act as part of the operating system.
b. Logon as a service.
c. Replace a process-level token.
d. Lock Pages in memory

• The service account for the Cluster service must have the right to log in to SQL Server. If you accept the default, the account [NT Authority\System] must have login rights to SQL Server so that the SQL Server resource DLL can run the isAlive query against SQL Server.
• If the service account for SQL Server is not an administrator in a cluster, the administrative shares cannot be deleted on any nodes of the cluster. The administrative shares must be available in a cluster for SQL Server to function.

WARNING: If you need to change the account under which the SQL Server virtual server runs, you must use SQL Server Enterprise Manager. Using this tool to change the service password will make the change on all the nodes and grant the necessary permissions to the selected user account.
If you do not use SQL Server Enterprise Manager to change passwords, full-text search may not function properly and you may be unable to start SQL Server.
If you are using Kerberos Security Support Provider Interface (SSPI) authentication in a SQL Server 2000 and Windows 2000 environment, you must drop your old service principal name (SPN), and then create a new one with the new account information. See the "Security Account Delegation" topic in SQL Server 2000 Books Online for details about using SETSPN to do this.
http://support.microsoft.com/kb/239885

How to carry "linked servers" from one server to another

Following script extracts linked servers info as sql script output

select 'exec sp_addlinkedserver @server=''' + srvname + '''' + isnull(',@srvproduct=''' + nullif(srvproduct, '')+ '''', '') + isnull(', @provider=''' +nullif(providername, '')+ '''', '') + isnull(', @datasrc=''' + nullif(datasource,'')+ '''', '') from master..sysservers

You can run output script at destination database to create linked servers. However you should carry sacurity settings of each linked server manually

Sql Server 2005 TempDB

Script to print object based space usage:

use tempdb
declare @id int
declare @dt smalldatetime
create table #spt_space_all
(
id int,
name varchar(500),
rows varchar(200) null,
reserved varchar(200) null,
data varchar(200) null,
index_size varchar(200)null,
unused varchar(200) null,
create_date smalldatetime null,
)

declare TMP_ITEMS CURSOR LOCAL FAST_FORWARD for
select id from sysobjects
where xtype='U'
open TMP_ITEMS
fetch next from TMP_ITEMS into @id
declare @pages int
WHILE @@FETCH_STATUS = 0
begin
create table #spt_space
(
id int,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
,
create_date smalldatetime null,
)
set nocount on
if @id is not null
set @dt = (select crdate from sysobjects where id=@id)
begin
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
update #spt_space set create_date=@dt
end
insert into #spt_space_all
select name = @id,object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB'),create_date
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
drop table #spt_space
FETCH NEXT FROM TMP_ITEMS
INTO @id
end
CLOSE TMP_ITEMS
DEALLOCATE TMP_ITEMS
select * from #spt_space_all where [name] not like '%#spt_space_all%'
drop table #spt_space_all
GO

Script to print session based space usage:

SELECT *
FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

How to shrink:

sql server 2005 tempdb shrink

http://support.microsoft.com/default.aspx?scid=KB;EN-US;307487

Sql Server 2000 & 2005 sql scripts for space usage

Script to print space usage of tables :

Works on both sql server 2000 & 2005
DBCC UPDATEUSAGE('db_name') --Run this command to update usage statistics. Be careful at very large dbs

create table #space_usage(
OBJ_NAME varchar(255),
OBJ_ROWS varchar(20),
OBJ_RESERVED varchar(20),
OBJ_DATA varchar(20),
OBJ_INDEX_SIZE varchar(20),
OBJ_UNUSED varchar(20))
--collect usage stats
declare @tname varchar(255)
declare c_tables cursor for select name from sysobjects where xtype='U'
open c_tables
fetch next from c_tables into @tname
while @@FETCH_STATUS = 0
BEGIN
insert into #space_usage execute sp_spaceused @tname
fetch next from c_tables into @tname
END
close c_tables
deallocate c_tables
--print usage stats
select sum(CAST(REPLACE(OBJ_RESERVED,'KB','') as int))/1024 Total_Size_in_MB from #space_usage --Log size is not included
select * from #space_usage order by cast(replace(OBJ_RESERVED,' KB','') as decimal(18,0)) desc
drop table #space_usage

Script to print space usage of tables :

Works only on sql server 2005, DBCC UPDATEUSAGE('db_name') is required to update stats.

WITH table_space_usage ( schema_name, table_name, used, reserved, ind_rows, tbl_rows, fg_name )
AS (SELECT
s.Name
, o.Name
, p.used_page_count * 8
, p.reserved_page_count * 8
, p.row_count
, case when i.index_id in ( 0, 1 ) then p.row_count else 0 end
, ds.name
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects as o ON o.object_id = p.object_id
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.data_spaces as ds on i.data_space_id = ds.data_space_id
WHERE o.type_desc = 'USER_TABLE' and o.is_ms_shipped = 0)
SELECT t.schema_name
, t.table_name
, t.fg_name
, sum(t.used) as used_in_kb
, sum(t.reserved) as reserved_in_kb
,sum(t.tbl_rows) as rows
FROM table_space_usage as t
GROUP BY t.schema_name , t.table_name,t.fg_name
ORDER BY used_in_kb desc

Script to print space usage of filegroups & data files :

Works on both sql server 2000 and 2005.

declare @cmd nvarchar(1024)
set @cmd='dbcc showfilestats'
create table #tmpstats(
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
)
insert into #tmpstats execute (@cmd)
select fg.groupname TS_Name,
sum(s.totalextents)*64/1024 TS_Total_MB,
(sum(s.totalextents)-sum(s.usedextents))*64/1024 TS_Free_MB,
cast(cast(sum(s.usedextents) as real)/cast(sum(s.totalextents) as real)*100 as decimal(5,2))TS_Used_Perc
from #tmpstats s,sysfilegroups fg where s.filegroup = fg.groupid group by fg.groupname order by TS_Used_Perc
select fg.groupname TS_Name,s.name File_Log_Name,s.filename Filename,
s.fileid,s.totalextents*64/1024 File_Total_MB,
(s.totalextents-s.usedextents)*64/1024 File_Free_MB,
cast(cast(s.usedextents as real)/cast(s.totalextents as real)*100 as decimal(5,2))File_Used_Perc
from #tmpstats s,sysfilegroups fg where s.filegroup = fg.groupid order by File_Used_Perc
drop table #tmpstats

Another scriopt

create table #Data(
[Fileid] int NOT NULL,
[FileGroupId] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[FileName] sysname NOT NULL,
[FilePath] varchar(max) NOT NULL,
[FileGroup] varchar(MAX) NULL
)
create table #Results(
db sysname NULL ,
FileType varchar(4) NOT NULL,
[FileGroup] sysname not null,
[FileName] sysname NOT NULL,
TotalMB numeric(18,2) NOT NULL,
UsedMB numeric(18,2) NOT NULL,
PctUsed numeric(18,2) NULL,
FilePATH nvarchar(MAX) NULL,
[Fileid] int null
)
create table #Log(
db sysname NOT NULL,
LogSize numeric(18,5) NOT NULL,
LogUsed numeric(18,5) NOT NULL,
Status int NOT NULL,
[FilePath] varchar(max) NULL
)
INSERT into #Data ([Fileid], [FileGroupId], TotalExtents ,
UsedExtents , [FileName] , [FilePath])
EXEC ('DBCC showfilestats')

update #Data
set #data.Filegroup = sysfilegroups.groupname
from #data, sysfilegroups
where #data.FilegroupId = sysfilegroups.groupid

INSERT INTO #Results (db ,[FileGroup], FileType , [FileName], TotalMB ,
UsedMB , PctUsed , FilePATH, [Fileid])
SELECT DB_NAME() db,
[FileGroup],
'Data' FileType,
[FileName],
TotalExtents * 64./1024. TotalMB,
UsedExtents *64./1024 UsedMB,
UsedExtents*100. /TotalExtents UsedPct,
[FilePath],
[Fileid]
FROM #Data
order BY 1,2

insert #Log (db,LogSize,LogUsed,Status )
exec('dbcc sqlperf(logspace)')

insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, [Fileid])
select DB_NAME() db,
'Log' [FileGroup],
'Log' FileType,
s.[name] [FileName],
s.Size/128. as LogSize ,
FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
(s.Size/128. - FILEPROPERTY(s.name,'spaceused')/8.00 /16.00) UsedPct,
s.FileName FilePath,
s.[Fileid] [Fileid]
from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
where f.dbid = DB_ID()
and (s.status & 0x40) <> 0
and s.[Fileid]=f.[Fileid]
and l.db = DB_NAME()

SELECT r.*,
CASE WHEN s.maxsize = -1 THEN null
else CONVERT(decimal(18,2), s.maxsize /128.)
END MaxSizeMB,
CONVERT(decimal(18,2), s.growth /128.) GrowthMB
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.[Fileid] = s.[Fileid]
ORDER BY 1,2,3,4,5

DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log


Script to print db log space usage :

dbcc sqlperf (logspace)