Forcefully Reducing the Size of the SQL Server Log File

Occasionally the log file on a database will grow to an alarming size, or disk space will be short, and you find that running DBCC SHRINKFILE and BACKUP LOG do not reduce the log file’s size.

Run: DBCC LOGINFO (database name) and check the last entry; if the last entry’s status is 2, then the end of the transaction log is the active portion. This can have the effect of preventing the log file’s reduction. To reduce the log file we need to somehow force the active part of the log back to the beginning, thus setting the end of the transaction log as being unused.

To reduce the file, follow these steps:

Step 1

1. run: DBCC SHRINKFILE (logfile, TRUNCATEONLY )
2. run: BACKUP LOG database WITH TRUNCATE_ONLY

Step 2

1. Create a dummy table and insert a record to MyTable
• CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
• INSERT Mytable (PK) VALUES (1)
• GO

Step 3

Create and run the following script:

SET NOCOUNT ON

DECLARE @Index INT

SELECT @Index = 0

WHILE (@Index < 20000)

BEGIN

   UPDATE MyTable SET MyField = MyField WHERE PK = 1

   SELECT @Index = @Index + 1

END

SET NOCOUNT OFF

If this is run successfully, the log rows with a status of 2 will wrap around to the beginning (or some other unused part) of the log file. The later parts of the log file will become marked as unused, and the next DBCC SHRINKFILE and BACKUP LOG will truncate the log:

Step 4

Run these commands again:

1. DBCC SHRINKFILE (logfile, truncateonly )
2. BACKUP LOG database name WITH TRUNCATE_ONLY

The log file should now be smaller.

 

Transaction log size increase continuing

Problem:

transaction log size of WSSContentMOSS increase continuing, even after backup transaction log still cannot shrink the log file.

Research:

1. Execute ‘DBCC loginfo’ found there are a lot of entries with status=2 that means the log is used.

2. Select * from sys.databases – log_reuse_wait_desc=’REPLICATION’

3. DBCC opentran –

Transaction information for database ‘WSSContentMOSS’.

Replicated Transaction Information:

Oldest distributed LSN: (0.0.0)

Oldest non-distributed LSN: (92353:9934:1)

4. Did not setup replication on this database

5. Check if there is a mirroring on the database

SELECT

A.name,

CASE

      WHEN B.mirroring_state is NULL THEN ‘Mirroring not configured’

      ELSE ‘Mirroring configured’

END as MirroringState

FROM

sys.databases A

INNER JOIN sys.database_mirroring B

ON A.database_id=B.database_id

WHERE a.database_id > 4

ORDER BY A.NAME

Resolution:

1. Use [db_name]

      Sp_removedbreplication

2. Check log_reuse_wait_desc again, it changed to ‘NOTHING’

3. Backup transaction log and shrink it, the size reduced.

 

Script-How to Obtain the size of all tables

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

– DB size.
EXEC sp_spaceused
– Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused “?”‘

SELECT *
FROM #t

– # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #t

DROP TABLE #t

 

Script-List rows for all tables

CREATE TABLE #counts
(
table_name varchar(255),
row_count int
)

EXEC sp_MSForEachTable @command1=’INSERT #counts (table_name, row_count) SELECT “?”, COUNT(*) FROM ?’
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC

 

Script-Rebuild indexes

– reindex all indexes
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘base table’
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Reindexing [' + @TableName + ']‘
DBCC DBREINDEX(@TableName,’ ‘)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

– ===========================================================================–
DECLARE @Comm varchar(8000)
DECLARE Cur CURSOR FAST_FORWARD FOR
SELECT ‘UPDATE STATISTICS [' + o.name + '] WITH FULLSCAN’
FROM sysobjects o
WHERE ( OBJECTPROPERTY(o.id, N’IsUserTable’) = 1
OR OBJECTPROPERTY(o.id, N’IsUserView’) = 1 )
AND OBJECTPROPERTY(o.id, N’IsMSShipped’) = 0
ORDER BY o.name
OPEN Cur
FETCH NEXT FROM Cur INTO @Comm
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Comm
EXEC (@Comm)
FETCH NEXT FROM Cur INTO @Comm
END
CLOSE Cur
DEALLOCATE Cur

 

Script-Monitor Per Session UGA, PGA and Cursor Usage

set pages500 lines110 trims on
clear col
col name format a30
col username format a20
break on username nodup skip 1

select vses.username||’:'||vsst.sid||’,'||vses.serial# username, vstt.name, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in
(‘session pga memory’,'session pga memory max’,'session uga memory’,'session uga memory max’,
‘session cursor cache count’,'session cursor cache hits’,'session stored procedure space’,
‘opened cursors current’,'opened cursors cumulative’) and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name
order by vses.username, vsst.sid, vses.serial#, vstt.name;

 

Run The Same SQL Command Against All SQL Server Databases

DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Command = ‘Use ‘ + @DB_Name +Char(13)+’SELECT ‘ + ”” + @DB_Name + ”” + ‘, SF.filename, SF.size FROM sys.sysfiles SF’
EXEC sp_executesql @Command

FETCH NEXT FROM database_cursor INTO @DB_Name
END

CLOSE database_cursor
DEALLOCATE database_cursor

 

Script-How to find a SQL Server database object from all databases

– Part 1
Declare @sqlstr nvarchar(200)

– Part 2
/* 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)
)

– Part 3
/*assign string value to variable */
Select @sqlstr = ‘sp_msforeachdb ”Insert #tblDBObjects select ””?”” as DBName, name, xtype From ?..sysobjects”’
/* execute SQL string */
Exec sp_executesql @sqlstr

– Part 4
/* select from temp table */
Select * From #tblDBObjects where objName=’%ClientInvoice%’
RETURN

 

Script-How to find the last update date for a object

select * from sys.dm_db_index_usage_stats

 

Script – Memory use in SQL Server

For whole instance:

select count(*) AS Buffered_Page_Count, count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB

from sys.dm_os_buffer_descriptors

For each database:

SELECT LEFT(CASE database_id

                       WHEN 32767 THEN ‘ResourceDb’

                       ELSE db_name(database_id)

        END, 20) AS Database_Name,

        count(*) AS Buffered_Page_Count,

        count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB

FROM sys.dm_os_buffer_descriptors

GROUP BY db_name(database_id) ,database_id

ORDER BY Buffered_Page_Count DESC

For each object:

SELECT TOP 25

        obj.[name],

        i.[name],

        i.[type_desc],

        count(*)AS Buffered_Page_Count ,

        count(*) * 8192 / (1024 * 1024) as Buffer_MB

    — ,obj.name ,obj.index_id, i.[name]

FROM sys.dm_os_buffer_descriptors AS bd

    INNER JOIN

    (

        SELECT object_name(object_id) AS name

            ,index_id ,allocation_unit_id, object_id

        FROM sys.allocation_units AS au

            INNER JOIN sys.partitions AS p

                ON au.container_id = p.hobt_id

                    AND (au.type = 1 OR au.type = 3)

        UNION ALL

        SELECT object_name(object_id) AS name  

            ,index_id, allocation_unit_id, object_id

        FROM sys.allocation_units AS au

            INNER JOIN sys.partitions AS p

                ON au.container_id = p.hobt_id

                    AND au.type = 2

    ) AS obj

        ON bd.allocation_unit_id = obj.allocation_unit_id

LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id

WHERE database_id = db_id()

GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]

ORDER BY Buffered_Page_Count DESC