General

General use queries

SQL Server

Version

SELECT @@version 

Oracle

Version

SELECT * FROM v$version 

Top N rows

SELECT * FROM (SELECT * FROM TABLE ORDER BY fieldName DESC) WHERE Rownum <= N 

Transactions

Information about current transactions

SQL Server 2005

List open transactions of selected database

dbcc opentran 

Database size

SQL Server 2005

Size of the database and extra information about unused space

exec sp_spaceused 

Data files information

SELECT file_id,name,physical_name,state_desc,size,max_size,growth FROM sys.database_files WHERE type_desc = 'ROWS' 

Database Maintenance

SQL Server

Delete Database Rows by blocks of 5000 version 1

Declare @rowsAffected AS int SET @rowsAffected = 1 While @rowsAffected > 0 Begin DELETE FROM T4EVENTLOG WHERE ID IN ( SELECT TOP 5000 ID FROM T4EVENTLOG WHERE (<your condition goes here>) ) SET @rowsAffected = @@rowcount; Checkpoint end 

Delete Database Rows by blocks of 5000 version 2

SET ROWCOUNT 5000 GO Declare @rowsAffected AS int SET @rowsAffected = 1 While @rowsAffected > 0 Begin DELETE FROM T4EVENTLOG WHERE <Your condition goes here>; SET @rowsAffected = @@rowcount; Checkpoint end; go; 

 

Data files information

SELECT file_id,name,physical_name,state_desc,size,max_size,growth FROM sys.database_files WHERE type_desc = 'ROWS' 

Oracle

Delete Database Rows by blocks

 BEGIN loop DELETE <OWNER>.<TABLE_NAME> WHERE <YOUR_CRITERIA> AND rownum < 5000; exit when SQL%rowcount < 4999; end loop; END Commit; 

Log Files

SQL Server 2005

Size of log files of selected database

SELECT file_id,name,physical_name,state_desc,size,max_size,growth FROM sys.database_files WHERE type_desc = 'LOG' 

Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: October 23, 2018