How to identify which query is filling up the tempdb transaction log? - Database Administrators Stack Exchange. From As Martin pointed out in a comment, this would not find active transactions that are occupying space in tempdb, it will only find active queries that are currently utilizing space there (and likely culprits for current log usage). You could change the inner join on sys.dm_exec_requests to a left outer join, then you will return rows for sessions that aren't currently actively running queries. The query Martin posted... SELECT database_transaction_log_bytes_reserved,session_id FROM sys.dm_tran_database_transactions AS tdt INNER JOIN sys.dm_tran_session_transactions AS tst ON tdt.transaction_id = tst.transaction_id WHERE database_id = 2; ...would identify session_ids with active transactions that are occupying log space, but you wouldn't necessarily be able to determine the actual query that caused the problem, since if it's not running now it won't be captured in the above query for active requests.
Sp_MSforeachdb. I need to loop the following script through all databases. I have tried almost everything I have researched online without any success. Any suggestions? DECLARE @table_name VARCHAR(500) DECLARE @schema_name VARCHAR(500) DECLARE @tab1 TABLE( tablename VARCHAR (500) collate database_default ,schemaname VARCHAR(500) collate database_default CREATE TABLE #temp_Table ( [tablename] [sysname] NOT NULL, [row_count] [int] NULL, [reserved] [varchar](50) NULL, [data] [varchar](50) NULL, [index_size] [varchar](50) NULL, [unused] [varchar](50) NULL) INSERT INTO @tab1 SELECT Table_Name, Table_Schema FROM information_schema.tables DECLARE c1 CURSOR FOR SELECT Table_Schema + '.' + Table_Name FROM information_schema.tables t1 OPEN c1 FETCH NEXT FROM c1 INTO @table_name SET @table_name = REPLACE(@table_name, '[',''); SET @table_name = REPLACE(@table_name, ']',''); -- make sure the object exists before calling sp_spacedused IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name)) CLOSE c1 DEALLOCATE c1 SELECT t1.
Run same command on all SQL Server databases without cursors. Sp_DBINFO. Sp_SOS (like SP_SpaceUsed) Sp_spaceused is a stored procedure that ships with SQL Server and is used to display the disk space taken by a SQL Server object. Very often, I find it inadequate to meet my intentions. For instance, when I want an overview of user table sizes in a given SQL Server database, or wish to see the top 10 biggest indexed objects or need to summarize the total space owned by a group of tables, sp_spaceused is of no use.
So, I created stored procedure sp_SOS, an expanded version of sp_spaceused, which can be used to calculate SQL Server object space and to perform other functions. With sp_SOS I wanted to retain the core functionality of sp_spaceused -- i.e., the algorithm to sum up data, index and reserved and unused spaces for an object. I knocked out the database sizing part and will come up with a separate stored procedure solely for a database size report. Click here to download Listing 1: The complete T-SQL definition for sp_SOS. Sp_SOS works on SQL Server 2000, 2005 and 2008.
SP_SDS determine "SQL Database Space" Knowing the size of a SQL Server database is one of the many DBA responsibilities that you can accomplish easily with the stored procedure sp_SDS. Not only will sp_SDS determine "SQL Database Space," but it can also be used to monitor database growth, alert a DBA on data or log file growth, execute a transaction log backup and even provide a detailed breakdown at the file level so a DBA can then shrink files with the most empty space.
This tip shows you the complete sp_SDS and its computing algorithm. It takes you a step further than the stored procedure sp_SOS that finds the size of database objects, including SQL Server tables. Click here to download the complete Data Definition Language (DDL) for sp_SDS. Listing 1: T-SQL definition for sp_SDS. Now, I'll explain how this SQL Server stored procedure finds the size of a database and how to use it. Most of the input variables for sp_SDS are pretty self-explanatory.
In Figure 1, you see a screenshot executing the code in Listing 2.