Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

Managing MS SQL Server mdf files

Hello,

I have a database that is currently using two data files and one log file. I need to be able to utilize 1 data file and 1 log file.

When I go to properties, and then choose Data Files I see that there are two seperate files in two different file groups. The structure currently looks like this:

DataFile1= P:\Path\Dbname.mdf
space allocated 11 MB
File Group = PRIMARY

DataFile2= P:\Path\Dbname_MISC_DATA.mdf
space allocated = 2000 MB
File Group = MISC_DATA

I'm relatively new to MS SQL. I'm currently under the belief that if I simply delete the MISC_DATA file and reallocate my space over to the PRIMARY data file, I will cause some problems. I'm also thinking that there may be a way in QA to run a sql script that would 'merge' this data into my PRIMARY file, re-size the space that's allocated and THEN it would be safe to remove the MISC_DATA.mdf.  Any assistance consolidating these files is greatly appreciated !!!
0
romieb69
Asked:
romieb69
1 Solution
 
ptjcbCommented:
>> if I simply delete the MISC_DATA file and reallocate my space over to the PRIMARY data file, I will cause some problems.
Yes, that is not a good choice.

One option would be to create a duplicate database (dbName2) with the same structure (but only using one file) - copy the data from dbname into dbname2. Verify that everything is there. Delete dbname and rename dbname2 to dbname.

It depends on how much free space is available.
0
 
romieb69Author Commented:
That sounds like a reasonable solution. Diskspace is relatively limited but I might be able to free up some realestate. After copying the database structure over to dbName2... is it at that point that I remove the MISC_DATA mdf and then resize my primary mdf?  Then copy over the data?
0
 
ptjcbCommented:


In this scenario - create dbname2 with the same data structure as dbname. When you create dbName2 it has the single .mdf that you want.

If you were creating from different servers you could use the COPY DATABASE wizard in EM (Tools:Wizards:Managment). It will not work if the source and destination are the same server.

Copy the data from dbname to dbname2 (you could use DTS for this).
Script out the various database objects - views/stored procs/function/users/ etc and run them on dbname2.

You open various tables and verify the data.
Verify that all of the stored procs/functions/users/and other database objects are copied to  dbName2.

When you are satisfied that everything is correct make a full backup of dbname (just in case) and then delete it.

Then rename the database from dbname2 to dbname

You do not need to resize or merge the dbname mdf files because you will delete them when you delete dbname.

EXEC sp_dboption 'dbname2', 'Single User', 'TRUE'
EXEC sp_renamedb 'dbName2', 'dbname'
EXEC sp_dboption 'dbname', 'Single User', 'FALSE'
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LowfatspreadCommented:
>I'm relatively new to MS SQL.

why do you feel you need to consolidate to 1 db file and 1 log file?

do you understand why it was created like this originally?



0
 
romieb69Author Commented:
Because a backup of this db is delivered to a client each day though an automated process (DTS). They have conveniently let me know last minute that having 2 data files and one log file is a problem for their automated import procedures.  Whatever the reasons for creating two data files is a moot point now.
0
 
imran_fastCommented:
hi romieb69,

Consider creating new database say database2  
now generate script from your existing database which will include table, views, fk, pk , index everything,

replace [MISC_DATA] in the script with [primary]

run this script on database2
 incase you have any fk on your database2  run

declare @TableName varchar(100)

declare TablesList cursor for select name from sysobjects where xtype = 'U'

open TablesList

fetch next from TablesList into @TableName

while @@fetch_status = 0
begin
      exec ('alter table [' + @TableName + '] nocheck constraint all')
      
      fetch next from TablesList into @TableName
end

close TablesList
deallocate TablesList
go


use dts to move all data in database2

again run the above script by changing 'nocheck' with 'check'

drop your old database

take backup of your databse2

restore it as your primary database
and finally delete database2
0
 
romieb69Author Commented:
A duplicate of the database was very helpful... I found other steps to be necessary...
Alter Database:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_4e5h.asp
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now