Multiple Data and Log Files

Question

Multiple Data and Log Files

amTryingSoHard

Mr or Mrs. 500

Points: 594
More actions
April 9, 2013 at 5:59 am

#291936

Hi Guys,
I wanted to know if it's possible to go back to a single data and log files from multiple files. Here's the scenario,
a disk and data/log file becomes full. I decided to add another file on different disk to create another space. After some time, as data are removed from the file, the first file have some space again. Is it possible to delete the second file I created?
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Sure. Data file or log file?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 233847 More actions · Answer 2

you will never be able to remove the primary data and log files they must always remain.

To remove an unwanted log file use DBCC LOGINFO against the database in question and ensure that for the log file you are removing the status column is 0 for each VLF (each record in the result set)

If it is then remove the file and perform a log backup afterwards if you're a recovery model other than SIMPLE.

If you want to remove a data file you must first empty it using

DBCC SHRINKFILE (logicalfilename or fileid, EMPTYFILE)

Delete the file and again run a log backup afterwards.

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

"Ya can't make an omelette without breaking just a few eggs" 😉

amTryingSoHard Mr or Mrs. 500 Points: 594 More actions · Answer 3

Sure. Data file or log file?

Both.

Thank you Gail and Perry. I just have to try it on my own. 🙂

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 4

Not meaning to hijack the thread (but I have a question that is directly-related). If dealing with 2 logs files (the primary LDF file and an additional) and log shipping is configured to a remote server...will removing the small log file break log shipping or will it be unaffected?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Perry Whittle SSC Guru Points: 233847 More actions · Answer 5

It will be unaffected providing the log doesn't then run out of space afterwards.

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

"Ya can't make an omelette without breaking just a few eggs" 😉

dan-572483 SSCoach Points: 15870 More actions · Answer 6

Perry Whittle (4/9/2013)
If you want to remove a data file you must first empty it using
DBCC SHRINKFILE (logicalfilename or fileid, EMPTYFILE)
Delete the file and again run a log backup afterwards.

By delete the file do you mean something like

ALTER DATABASE <dbname> REMOVE FILE <logical file name> ?

Deleting a SQL DB or log file in Windows Explorer seems like a good way to break the database, even if there's no data in it at the time.

Perry Whittle SSC Guru Points: 233847 More actions · Answer 7

dan-572483 (4/9/2013)
By delete the file do you mean something like
ALTER DATABASE <dbname> REMOVE FILE <logical file name> ?

Yes, that's how you remove a file in a sql server database.

dan-572483 (4/9/2013)
Deleting a SQL DB or log file in Windows Explorer seems like a good way to break the database, even if there's no data in it at the time.

With the database online you won't be able to delete the OS file.

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

"Ya can't make an omelette without breaking just a few eggs" 😉