A useful undocumented function: fn_dblog

A useful undocumented function: fn_dblog:
========================================
A useful undocumented function: fn_dblog
Posted by decipherinfosys on November 12, 2008

A friend of mine recently told me about this undocumented function: ::fn_dblog when I was talking to him about Lumigent’s Log Explorer product. He pointed me to this post by Andrew Novick which mentions about this function which has existed since quite some time. I have always been using DBCC SQLPERF(LogSpace) when I wanted to capture used and free space and at times DBCC LOGINFO with help from different sites out there to understand the output. I am a fan of Lumigent’s product and have used it quite a bit in our client engagements but the fact that something like this existed all along, was a welcome surprise to me so I thought that we would share this on the blog with our readers as well.

As the post from Andrew Novick points out, the syntax is :

::fn_dblog(@StartingLSN, @EndingLSN)And if you provide Null as the value for both the parameters, then you will get a running record of everything from the start to the end (not a good idea in a production environment but something to play around with in your development/test environment to get an understanding of the output). The post from Andrew goes into some of the details of what the different columns are for.

SQL server services didn't started after min & max server memory configuration

http://www.sqlservercentral.com/articles/Administering/2963/

The SQL Server tools are top notch & one of the tools is "sqlservr.exe" which starts, stops, pauses, and continues Database Engine from the command prompt. This article describes how to start an instance of the Database Engine.

Over-Committing Memory
I had an issue recently where SQL server services didn't started after min & max server memory configuration of SQL server was changed. The Server got configured for AWE along with min & max server memory configuration. The SQL Server didn't start after stopping the services. What I intend to show you here is how problem came to happen & how it got resolved. In this article I'd like to talk about how this over committed memory issue is been addressed when value defined for "max server memory" (RAM) is not physically present on the server. Below topic will take you from Server configuration to Boot.INI file & to enable AWE & configuring server memory using system store procedure sp_configure & finally sqlservr.exe coming in rescue to start SQL server.

My Server Configuration:
1> Windows 2000 Datacenter

2> Physical memory 36GB

3> SQL Server 2000 Enterprise Edition

Configuring AWE for SQL Server 2000

Use of the /PAE switch in the Windows Boot.INI configuration file provides access to physical memory above the 4 GB limit. This is a requirement for AWE memory access above 4 GB.

Any change to Boot.INI file requires server restart\reboot. Now is time to enable AWE on SQL Server 2000 by running "sp_configure" from query analyzer.

The LOCK PAGE IN MEMORY permission must be granted to the SQL Server account before enabling AWE (SQL SERVER 2005); this may be enabled if Windows 2003 is on SP1

(USE MASTER)
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
-- Note: - max server memory is in MB
sp_configure 'min server memory', 1024
RECONFIGURE
GO
-- 30GB is 30720MB but accidentally I typed 307200, which is 300GB
sp_configure 'max server memory', 307200
RECONFIGURE
GO
I stopped SQL Server & when I started SQL Server 2000, it didn't start, I tried other possibilities but that didn't help.

Solution
To work around the problem, start SQL Server 2000 in minimal configuration mode by using Sqlservr.exe with the -c -f option and reconfigure "max server memory"

For a SQL Server 2000 Default Instance: Navigate to the Binn folder where the SQL Server 2000 default instance is installed and run the following command:

sqlservr.exe -c -f
For a SQL Server 2000 Named Instance: Navigate to the Binn folder where the SQL Server named instance is installed and run the following command:

sqlservr.exe -c -f -s Instance_Name
Connect to SQL Server through Query Analyzer, and then run this code:

(USE MASTER)
sp_configure 'max server memory', 30720 --- (Which is now 30GB)
RECONFIGURE
GO
Navigate to the command prompt and then press CTRL+C. To shut down the SQL Server 2000 instance, type:

Y
After that when you start again SQL Server it will come up fine & your"max server memory" issue is been resolved.

SQL Server 2005: Get full information about transaction locks

SQL Server 2005: Get full information about transaction locks :
================================================================
http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx
================================================================
Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...

With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:



SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id


Let's look at it one DMV at a time from top to bottom:

sys.dm_tran_locks:

Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.

sys.partitions:

Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values 'HOBT', 'Page', 'RID' and 'Key'. With this join we get the object_id of our locked table.

sys.objects:

Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.

sys.dm_exec_sessions:

Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.

sys.dm_tran_session_transactions:

Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.

sys.dm_tran_active_transactions:

Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.

sys.dm_exec_connections:

Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.

sys.dm_exec_sql_text:

Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.



By applying the filter in the where clause you get the answers to questions like:

- What SQL Statement is causing the lock?

- Which user has executed the SQL statement that's holding the locks?

- What objects/tables are being locked?

- What kinds of locks are being held and on which pages, keys, RID's?

- etc...

usp_FindObject

Create PROC usp_FindObject (
@objname varchar(200) = Null
, @objtype varchar(20) = Null
)
As
Declare @sqlstr nvarchar(200)
-- Insert wildcard, if exact search is not required.
-- Set @objname = '%' + @objname + '%'
-- Its better to supply custom wild card in the input parameter @objname
/* drop the temporary table if already exists */
If Object_Id('tempdb..#tblDBObjects') is Not Null
Drop table #tblDBObjects
/* create temporary table */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)
Begin
If @objtype = 'CHECK'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''C'''''''
If @objtype = 'Default'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''D'''''''
If @objtype = 'FOREIGN KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''F'''''''
If @objtype = 'Log'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''L'''''''
If @objtype = 'Scalar function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''FN'''''''
If @objtype = 'Inlined table-function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''IF'''''''
If @objtype = 'Stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''P'''''''
If @objtype = 'PRIMARY KEY'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''PK'''''''
If @objtype = 'Replication filter stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''RF'''''''
If @objtype = 'System table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''S'''''''
If @objtype = 'Table function'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TF'''''''
If @objtype = 'Trigger'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''TR'''''''
If @objtype = 'User table'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''U'''''''
If @objtype = 'UNIQUE constraint'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''UQ'''''''
If @objtype = 'View'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''V'''''''
If @objtype = 'Extended stored procedure'
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects where xtype = ''''X'''''''
If (@objtype = '') Or (@objtype is Null)
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''
End
/* execute SQL string */
If (@sqlstr <> '') Or (@sqlstr is Not Null)
Exec sp_executesql @sqlstr
/* If @objname is not supplied it should still return result */
If (@objname = '') Or (@objname is Null)
Select * From #tblDBObjects
Else
Select * From #tblDBObjects Where objName like @objname
RETURN

Get sqlcmd output into a table?

CREATE TABLE ServerList_SSIS([Server] NVARCHAR(512),
[Connect] bit,
[DMZ] bit);
INSERT INTO ServerList_SSIS([Server])
EXEC master..xp_cmdshell 'sqlcmd /Lc';

Delete from ServerList_SSIS where [Server] IS NULL;
Select * from ServerList_SSIS