SQL Server DBA: DBA Scripts

Get rowcount of all sql tables

A quick method of getting the rowcount of all tables in a database is to query the sys.objects and sys.partitions views – returning the sys.partitions rows column

The query filters on sys.objects : U = Table (user-defined) , V=View objects.

select sobj.name,spar.rows
FROM sys.objects sobj 
INNER JOIN sys.partitions spar
ON sobj.object_id = spar.object_id 
WHERE sobj.type IN ('U','V')
AND spar.index_id IN (0,1)
ORDER BY sobj.name

set-location E:\health\SQL_Server

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath


$instancepath=$basepath + "\config\instances.txt"
$outputfile="\logs\sql_server_db_sizes_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile


$filePath = ""

$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{
$instance
$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "
select @@servername as ServerName,
DB_NAME(mf.database_id) as [db_name],
type_desc,
str(convert(dec(14,2),size) / 128,14,2) as DATABASE_SIZE_MB,
str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00) as DATABASE_SIZE_GB
from sys.master_files as mf
INNER JOIN sys.databases as da ON da.database_id = mf.database_id
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
 
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle -body "SQL Server Database Sizes" | Set-Content $outputfilefull  

$filepath = $outputfilefull

Method 1 : Use SSMS to view Default Data and Log details

In the SSMS Object Explorer panel, right-click a server and click
Properties.
In the left panel,under Select A Page title, click the Database settings page.
In Database default locations, view the current default locations for new data files and new log files.

Method 2: T-SQL and xp_instance_regread

DECLARE @DataLoc nvarchar(2000);
DECLARE @LogLoc nvarchar(2000);

EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultData', 
    @DataLoc OUTPUT;

EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'DefaultLog', 
    @LogLoc OUTPUT;

SELECT @DataLoc AS 'Default Data Location',
       @LogLoc AS 'Default Log Location';

Method 1 - DBCC INPUTBUFFER

-----------------------------

DBCC INPUTBUFFER returns the last sql statement issued by a client. The command requires the SPID

DBCC INPUTBUFFER (SPID)

Method 2 - SYS.SYSPROCESSES and SYS.DM_EXEC_SQL_TEXT

-----------------------------------

Obtain the sql_handle from sys.sysprocesses and use the sql_handle to obtain the sql text from the DMV sys.dm_exec_sql_text.

DECLARE @sqlhandle VARBINARY(64)
SELECT @sqlhandle = sql_handle from sys.sysprocesses where spid = 987
SELECT text FROM sys.dm_exec_sql_text(@sqlhandle)

Method 3 – SYS.FN_GET_SQL

---------------------------------

Use sys.fn_get_sql to return SQL statement text based on a SQL handle. Microsoft intend deleting this function in an unspecified future version of SQL Server

DECLARE @sqlhandle varbinary(64);
SELECT @sqlhandle = sql_handle 
FROM sys.dm_exec_requests 
WHERE session_id = 987 and request_id = 0;
SELECT * FROM sys.fn_get_sql(@sqlhandle);

Use ::fn_dblog to read the active portion of the transaction log file. There is loads of interesting information within the Transaction Log file and it’s possible to use this information to discover , for example , the last DML operation and who dropped a table . I’ll post on this topic tommorow

The raw output from fn_dblog is hard to understand. Attempting to reverse engineer the meaning and structure of the transaction logs is complex. The main purpose of the Transaction Logs is to support SQL Server transaction and consistency levels. If there is a failure , the Transaction Logs play a critical part in recovery

There are 3^rd party tools that map the SQL Server Transaction Log format – creating a user-friendly output. For certain circumstances , these are very useful

Decreasing the amount of information returned in the data set makes ::fn_dblog useful. Once the data set is customised it can offer some good clues for investigation., in recent activity on the SQL Server database.

This query retuns the columns:

Login = Originator of Transaction

Operation = Type of Transaction

AllocUnitName = Object name

Conetxt = Resource affected by the transaction

Begin Time = Transaction start time

End Time = Transaction end time

SELECT suser_sname(convert(varbinary,"Transaction SID")) as 'Login', 
Operation,
AllocUnitName,
Context,
"Begin Time",
"End Time" 
FROM ::fn_dblog( null , null)

Search

Subscribe

Categories

SQLServer-DBA.com Links

Recent Posts

April 08, 2013

Get rowcount of all sql tables

Read More

April 02, 2013

SQL Database Size report using Powershell

Read More

January 25, 2013

SQL Server - Find Default Data location and Default Log Location

Method 1 : Use SSMS to view Default Data and Log details

Method 2: T-SQL and xp_instance_regread

Read More

January 14, 2013

SQL Server - Find last sql statement based on SPID

Method 1 - DBCC INPUTBUFFER

Method 2 - SYS.SYSPROCESSES and SYS.DM_EXEC_SQL_TEXT

Method 3 – SYS.FN_GET_SQL

Read More

December 04, 2012

SQL Server – Read sql transaction Logs with ::fn_dblog

Read More