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.

Jun 10, 2024 · 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.

Sep 11, 2008 · There should be a command in Managment Studio: "Click to Shrink Log" and you're done. Making a log file smaller should really be reserved for scenarios where it encountered unexpected growth which you do not expect to happen again. If the log file will grow to the same size again, not very much is accomplished by shrinking it temporarily.

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.

What does a SQL Server transaction log store? A transaction log stores every transaction made to a SQL Server database, except some which are minimally logged like BULK IMPORT or SELECT INTO. Internally it is split into the smaller parts called Virtual Log Files (VLFs).

Feb 5, 2017 · Is your SQL Server Transaction Log File too big? Running out of space? Confused on what to do? Some tips for common log file best practices.

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.

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.