Oct 31, 2023 · This tip explains three methods that can be used to change the default location of data files, transaction log files, and backup files. Using the SSMS GUI is probably the simplest way to do this, but if you need to script and automate the process using xp_instance_regwrite can make it much easier.

Apr 19, 2018 · If you ever need to know where your database files are located, run the following T-SQL code: USE master; SELECT name 'Logical Name', physical_name 'File Location' FROM sys.master_files; This will return a list of all data files and log files for the SQL Server instance.

Jun 17, 2009 · To move to this configuration, it will be necessary to detach and attach the database. These steps can be accomplished with either T-SQL commands or the SQL Server management studio (SSMS). We will walk through an example of each technique in this tip.

Jul 29, 2024 · Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database. If there's a system failure, you need that log to bring your database back to a consistent state.

Jun 10, 2024 · In Object Explorer, right-click on your server and select Properties. In Database default locations, view the current default locations for new data files and new log files. To change a default location, enter a new default pathname in the Data or Log field, or select the browse button to find and select a pathname.

Oct 18, 2014 · I am trying to write a T-SQL routine that shrink the transaction log file using DBCC SHRINKFILE based on the logical name of the database. The DB_NAME() function gives you the logical name of the database. Is there an equivalent one for the transaction log? If not, is there some other way to get this information?

Apr 19, 2018 · Here’s how to find the location of all data files and log files in SQL Server. That will display a list of all data files and log files along with their full paths.

Feb 9, 2015 · The SQL Server transaction log is a single file which usually has an .LDF file extension. Although possible to have multiple log files for a database, the transaction log is always written sequentially and multiple physical log files get treated as one continuous circular file.

An alternate method to see if the log file really exists, and perhaps where it actually is, would be to use the xp_cmdshell extended stored procedure to run a dir command on the SQL Server itself, in the context of the service account.

Nov 5, 2011 · This post goes through how one might go about changing the SQL Server 2008 transaction log or data file location, commit this file move, and also diagnose and resolve any errors that could arise from performing such a move.