How to identify the Primary Transaction Log file on a SQL Server

17 June,2014 by Jack Vamvas

Question: How can I  identify the Primary Transaction Log file on a SQL Server database ?

Answer:  To identify the Primary Transaction Log file on a SQL Server database use this sql statement

Use DB_NAME
GO
select fileid,filename from sys.sysfiles where fileid = 2

By design, you can’t drop the Primary Transaction Log file, this message appears :

The primary data or log file cannot be removed from a database. (Microsoft SQL Server, Error: 5020) You can add Transaction Log files

To add extra Transaction Log files  - read this post - Add an extra SQL Transaction log file – ALTER DATABASE ADD LOG

Read More

Modify sql transaction log file size - increase or decrease

SQL Server - Find Default Data location and Default Log Location

SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size

SQL Server – Monitor SQL Transaction Log AutoGrowth for performance issues


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to identify the Primary Transaction Log file on a SQL Server


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer