Transaction Log Current Active Location

Question

Transaction Log Current Active Location

berniesprouster

SSC Enthusiast

Points: 144
More actions
May 19, 2014 at 1:58 am

#286834

Hi All,
I wondered if someone could help...
I am in the process of shifting most of our production data and log files to individual mount points on our SQL Servers, so as to achieve better performance. We use a NetApp SAN and the best practice for that is to dedicate a Volume/LUN for each critical database, or each database of significant size.
The problem I have is that I've undertaken most of the work, by setting the DBs to OFFLINE, moving the physical files, and setting them to ONLINE again afterwards. However, while I am as sure as I can be that the files have moved successfully, I want to adopt a belt-and-braces approach by checking that the new data and log file locations are the ones being used by SQL Server.
I realise I can query the sys.master_files table but as far as I understand, if I had made a mistake and only set the new locations (without the OFFLINE/copy/ONLINE part), the new location would not be in use. Looking at the physical files, the modified date in some circumstances is the date of the copy rather than anything more recent (maybe because the DBs in this instance are in SIMPLE recovery model).
My question is, is there a way I can find out which files are actually being written to while SQL Server is online, rather than which files it would be using after the next restart?
For what it's worth, the reason I haven't removed/changed the original files in any way is because this would result in the SAN picking it up as a data change and subsequently filling the snapshot volume.
Thanks in advance for any help folks. 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

you could check sysfiles within each individual database, or use virtualfilestats DMVs to see where the activity is going to (values will change between runs)

---------------------------------------------------------------------

berniesprouster SSC Enthusiast Points: 144 More actions · Answer 2

Hi George

Thanks for the reply. Regarding sysfiles, this doesn't seem to return the name of the files that are being used to write to at this moment in time. If I issue an ALTER DATABASE statement and modify the files, the results returned reflect the change I've made, which at this point does not equal the location where data is getting written to. If I were to subsequently take the db offline and bring it back online, this would indeed be accurate.

With regard to the virtual file stats DMV, it gives a file_id value but I suspect this will just join back to something like sysfiles or sys.master_files, again giving the name of the file after offline/online and not the files currently being written to. I notice there's also a file_handle in the virtual file stats DMV but I'm not sure if it's possible to resolve this to a file path/name.

Thanks again for your help.

dbalmf SSCrazy Points: 2063 More actions · Answer 3

I have ripped this out of Glenn Berry's excellent Diagnostic Information Queries (go grab a copy of the script)

Anyway this should give you what you need.

SELECT DB_NAME([database_id])AS [Database Name],

[file_id], name, physical_name, type_desc, state_desc,

CONVERT( bigint, size/128.0) AS [Total Size in MB]

FROM sys.master_files WITH (NOLOCK)

WHERE [database_id] > 4

AND [database_id] <> 32767

OR [database_id] = 2

ORDER BY DB_NAME([database_id]) ;

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

george sibbald SSC Guru Points: 104210 More actions · Answer 4

so you have run alter database but wish to check the database has actually been bounced? First of all don't get into that situation 🙂

If the database was not down at some point you would not have been able to copy the file

Any loop back to the log location via T_SQL is always going to pick the file name as held in the system tables so you are stuck there. I would use this query and see if the write count is increasing, If you just want logs filter out by file type (=1 for logs) or type_desc

SELECT DB_NAME(vfs.database_id) AS database_name ,

vfs.database_id ,

vfs.file_id ,

vfs.num_of_reads ,

vfs.num_of_bytes_read ,

vfs.num_of_writes ,

vfs.num_of_bytes_written ,

size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,

physical_name

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id

AND vfs.file_id = mf.file_id

ORDER BY vfs.num_of_writes DESC

does a rename of a file cause space usage in the snapshot? might be worth checking.

---------------------------------------------------------------------

berniesprouster SSC Enthusiast Points: 144 More actions · Answer 5

Hi

Thanks again for the info.

It's not that I'm in any situation as I'm as sure as I can be that the files are in the correct locations and that the dbs have been bounced. It's just that the process of taking the original lun/volume offline after this move is complete, is a kind of big bang approach and I want to be sure that no active dbs are using that lun/vol.

I've done the process for approximately 60 db's so far, so the reason for this check is to make absolutely sure I've followed my own processes correctly for every db. 🙂

I don't seem to be able to get a firm answer with regard to renaming the files from the vendor/support, so don't want to take the risk of renaming the files and putting the snapshot volume at risk. 🙁

Thanks for the code, I'll give it a try.

berniesprouster SSC Enthusiast Points: 144 More actions · Answer 6

I was just hoping that there would be a way of issuing some kind of checkpoint which may result in the modified date of the ldf file updating, and eventually the mdf/ndf.

🙂

george sibbald SSC Guru Points: 104210 More actions · Answer 7

berniesprouster (5/19/2014)
I was just hoping that there would be a way of issuing some kind of checkpoint which may result in the modified date of the ldf file updating, and eventually the mdf/ndf.
🙂

the modify dates don't change until a database\SQL restart or the file grows\shrinks

If you had not stopped the database, you would never have been able to copy it. And SQL will use the file specified in sys.master_files on restart.

Is this a SAN snapshot or a VM snapshot?

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 8

which gives me an idea, grow the files a bit see if the modify dates change.

---------------------------------------------------------------------

berniesprouster SSC Enthusiast Points: 144 More actions · Answer 9

That's a great idea.

Will give it a try tomorrow. 🙂

Thanks.

berniesprouster SSC Enthusiast Points: 144 More actions · Answer 10

Hi,

Just tried it by increasing the initial size values by 1MB and it does indeed change the modified date on the files.

Thanks so much for the help.

🙂