May 1, 2018 at 8:28 am
Is there any way to tell what log file sql server is actually writing to, not just what's in sys.master_files?
During a maintenance window, I recently used ALTER DATABASE...MODIFY FILE to set a new log file location on an alternate drive, stopped the sql service, copied the log file to the new location in the OS, and restarted the sql server service. Now a few days later I'm finding that SQL Server isn't indicating it was restarted; sqlserver_start_time in sys.dm_os_sys_info is showing months earlier. The only thing I can figure is that the service indicators were wonky and since I copied the file instead of moved I didn't get the OS lock warning.
I know that ALTER DATABASE...MODIFY FILE doesn't take effect until the next restart, but is there a way in the OS to tell which log file is actually being written to? The OS's "date modified" field doesn't seem to help, as it just shows the old file's "date modified" as two minutes earlier than the new file's.
May 1, 2018 at 9:02 am
Well you can try to rename the file 😛
May 1, 2018 at 11:34 am
Onan Salad - Tuesday, May 1, 2018 8:28 AMIs there any way to tell what log file sql server is actually writing to, not just what's in sys.master_files?During a maintenance window, I recently used ALTER DATABASE...MODIFY FILE to set a new log file location on an alternate drive, stopped the sql service, copied the log file to the new location in the OS, and restarted the sql server service. Now a few days later I'm finding that SQL Server isn't indicating it was restarted; sqlserver_start_time in sys.dm_os_sys_info is showing months earlier. The only thing I can figure is that the service indicators were wonky and since I copied the file instead of moved I didn't get the OS lock warning.
I know that ALTER DATABASE...MODIFY FILE doesn't take effect until the next restart, but is there a way in the OS to tell which log file is actually being written to? The OS's "date modified" field doesn't seem to help, as it just shows the old file's "date modified" as two minutes earlier than the new file's.
You can use process monitor and filter where process is sqlservr.exe and path begins with your path to the log files. You would see which file has the activity.
Process Monitor
Sue
May 1, 2018 at 12:03 pm
I just confirmed with the windows logs that the sql server service was successfully restarted. Apparently [sys.dm_os_sys_info].[sqlserver_start_time] actually represents the OS start time, or it's simply inconsistent. Also confirmed that SQL Server will not let you copy the log file while the sql server service is running. Posting this here in case anyone's server shows the same contradictory data.
May 1, 2018 at 5:40 pm
Onan Salad - Tuesday, May 1, 2018 12:03 PMI just confirmed with the windows logs that the sql server service was successfully restarted. Apparently [sys.dm_os_sys_info].[sqlserver_start_time] actually represents the OS start time, or it's simply inconsistent. Also confirmed that SQL Server will not let you copy the log file while the sql server service is running. Posting this here in case anyone's server shows the same contradictory data.
sqlserver_start_time is the time SQL Server started, not the OS start time. I'm not aware of any issues with it - never heard of it being off by months. I'd try executing things that give similar values for the start time to make sure you aren't accidentally executing against a different instance or if something else is going on. You could compare these - the times should be within a second or so:
SELECT sqlserver_start_time
FROM sys.dm_os_sys_info
SELECT create_date
FROM sys.databases WHERE [name] = 'tempdb'
SELECT login_time
FROM sys.dm_exec_sessions WHERE session_id = 1
Sue
May 2, 2018 at 9:41 am
I was wrong: the windows log actually reads "starting up database," not the service. So the series of events was as follows:
1) database set offline
2) log file set to new location using alter database
3) log file copied to new location in OS
4) database set online
Apparently the service does not need to be restarted for the db to start using the new log file location, because I'm allowed to copy the log file from the old location, but SQL Server has the log file at the new location locked.
May 2, 2018 at 11:08 am
Onan Salad - Wednesday, May 2, 2018 9:41 AMI was wrong: the windows log actually reads "starting up database," not the service. So the series of events was as follows:1) database set offline
2) log file set to new location using alter database
3) log file copied to new location in OS
4) database set onlineApparently the service does not need to be restarted for the db to start using the new log file location, because I'm allowed to copy the log file from the old location, but SQL Server has the log file at the new location locked.
Looks like there was no restart of SQL Server - it was just an offline and then online of a database.
The files SQL Server is actively using will be locked by SQL Server so you can't copy, move or delete those for a database when it's online. You can when the database is offline as the files aren't in use when a database is offline.
Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply