SQL SERVER – Find the Size of Database File – Find the Size of Log File

I encountered the situation recently where I needed to find the size of the log file. When I tried to find the script by using Search@SQLAuthority.com I was not able to find the script at all. Here is the script, if you remove the WHERE condition you will find the result for all the databases.

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
GO

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Solution – Log File Very Large – Log Full

I have been receiving following question again and again either through email or through comments on this blog.

My log file is too big, what should I do?

Answer to this question is in three steps.

  • Backup the log file to any device.
  • Truncate the log file.
  • Shrink the log file.

I have previously written two article about this issue. Refer them for additional information and details.

SQL SERVER – Shrinking Truncate Log File – Log Full(Script)

SQL SERVER – Shrinking Truncate Log File – Log Full – Part 2(Management Studio)

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Shrinking Truncate Log File – Log Full

UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.

Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

[Update: Please note, there are much more to this subject, read my more recent blogs. This breaks the chain of the logs and in future you will not be able to restore point in time. If you have followed this advise, you are recommended to take full back up right after above query.]

UPDATE: Please follow link for SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008.

Reference : Pinal Dave (http://www.SQLAuthority.com), BOL