January 17, 2013 at 1:51 am
Query to find out 'is data and log file splits to multiple files '?
January 17, 2013 at 1:56 am
you cant split a data or log file to multiple files.
data and log files are always seperate, you cant have an data file which is your log file, unless for some reason you have named your LDF a MDF NDF instead but as the log file doesnt have a filegroup SQL knows its a log file.
you can add a filegroup and then add multiple files to the file group.
logs dont have a file group and having a second log is useless, but you can get that from sys.master_files, using the data_space_id which referes to the filegroup in the database
0 = Log files
1 = Primary
>1 = User defined filegroup
If you have more than one file in the data_space_id you have move than 1 file in the filegroup
January 17, 2013 at 2:10 am
I MEAN ANY QUERY TO INDICATE THAT THERE IS MULTIPLE DATA FILES FOR SINGLE DB.....
January 17, 2013 at 2:12 am
sumith1andonly1 (1/17/2013)
I MEAN ANY QUERY TO INDICATE THAT THERE IS MULTIPLE DATA FILES FOR SINGLE DB.....
No need to shout.
Again sys.master_files, filter out all your log files (data_space_id = 0) and then count the remaining grouped by the database name anything bigger than 1 you have multiple data files in that DB.
January 17, 2013 at 2:17 am
THANK YOU MY DEAR FRIEND
January 17, 2013 at 2:18 am
sumith1andonly1 (1/17/2013)
THANK YOU MY DEAR FRIEND
Might want to switch your caps lock off
January 17, 2013 at 2:21 am
yes i will...friend
January 17, 2013 at 1:15 pm
SELECT *
FROM <db_name>.sys.database_files
WHERE
type_desc <> 'LOG'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply