Our production SQLServer has automated nightly reboots. Last week,
after one of these reboots, SQLServer came up, then immediately
crashed. The log has a bunch of errors saying things like "Problem
creating symptom dump file due to internal exception" and "Invalid
access to memory location". The SQLServer service restarted itself
within seconds, and SQLServer continued without error.
Since then, when I look at the data and log files in Windows
Explorer or from a command prompt, the file modification timestamps
and the byte count are "stuck" at the time of the crash or restart
(they were seconds apart, so I can't tell which). There are a
couple dozen databases on this server, and they're all stuck this
way. In some cases, it's only the data file; in others, it's only
the log file; in still others, both files are stuck. Of the files
that aren't stuck at the time of the crash, they've advanced once
or twice, but none are advancing when the server reboots.
That's the part that bothers me. The server has been rebooted each
night since then, and I would expect the files would be touched at
least as often as each reboot. The only one that seems to be
behaving is tempdb, which is of course recreated after each
restart.
There's a production app running against these databases, and I'm
not aware of any user complaints or problems with the app. I've
also found that if I either detach/attach or offline/online a
database, the log and data file timestamps advance to that point.
This doesn't fix them, though: the timestamps get "stuck" at that
new point. I think SQLServer is still writing to and reading from
these files, but maybe it's not closing them properly during the
nightly reboot?
I'm concerned that this is a predictor of a worse failure, maybe
something to come or something that's happened that I haven't
noticed yet. Any ideas?
> That's the part that bothers me. The server has been rebooted each
> night since then, and I would expect the files would be touched at
> least as often as each reboot.
No, that's not true. The stamps on the files will only change when the file
actually changes size (e.g. an autogrow event, or a manual DBCC
SHRINKDATABASE or DBCC SHRINKFILE, or a detach/attach).
Why on earth are you rebooting the server every night? I think you might
want to deal with those problems instead of worrying about the time and size
properties of the MDF and LDF files.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||> No, that's not true. The stamps on the files will only change
> when the file actually changes size (e.g. an autogrow event, or
> a manual DBCC SHRINKDATABASE or DBCC SHRINKFILE, or a
> detach/attach).
> Why on earth are you rebooting the server every night? I think
> you might want to deal with those problems instead of worrying
> about the time and size properties of the MDF and LDF files.
>
Thanks for the response, Aaron. Actually, it is true. SQLServer
must flush to the files and release filehandles on a normal
shutdown of the services. If you have a test server, restart the
SQLServer service and watch the timestamps on your data and log
files.
Why I'm rebooting the server each night doesn't enter into this.
I'd like to focus on the problem at hand, which started after the
SQLServer crash. Any other ideas? All thoughts are appreciated!
|||> Thanks for the response, Aaron. Actually, it is true. SQLServer
> must flush to the files and release filehandles on a normal
> shutdown of the services.
Whoops, you're right. I think I was cross-wired there for a few minutes.
|||> Whoops, you're right. I think I was cross-wired there for a few
> minutes.
>
No sweat. Interesting what it suggests, though, isn't it? SQLServer
may not be properly closing the files when the reboot happens. It
may be something with the way the service is stopping. Maybe
SQLServer is somehow aborting, instead of stopping gracefully.
Nothing shows in the logs, though.
Anyone have other thoughts?
|||> Anyone have other thoughts?
Reboot only after you have successfully brought SQL Server down gracefully?
You can stop SQL Server programmatically using NET STOP ...
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Friday, March 9, 2012
File timestamps not advancing
Our production SQLServer has automated nightly reboots. Last week,
after one of these reboots, SQLServer came up, then immediately
crashed. The log has a bunch of errors saying things like "Problem
creating symptom dump file due to internal exception" and "Invalid
access to memory location". The SQLServer service restarted itself
within seconds, and SQLServer continued without error.
Since then, when I look at the data and log files in Windows
Explorer or from a command prompt, the file modification timestamps
and the byte count are "stuck" at the time of the crash or restart
(they were seconds apart, so I can't tell which). There are a
couple dozen databases on this server, and they're all stuck this
way. In some cases, it's only the data file; in others, it's only
the log file; in still others, both files are stuck. Of the files
that aren't stuck at the time of the crash, they've advanced once
or twice, but none are advancing when the server reboots.
That's the part that bothers me. The server has been rebooted each
night since then, and I would expect the files would be touched at
least as often as each reboot. The only one that seems to be
behaving is tempdb, which is of course recreated after each
restart.
There's a production app running against these databases, and I'm
not aware of any user complaints or problems with the app. I've
also found that if I either detach/attach or offline/online a
database, the log and data file timestamps advance to that point.
This doesn't fix them, though: the timestamps get "stuck" at that
new point. I think SQLServer is still writing to and reading from
these files, but maybe it's not closing them properly during the
nightly reboot?
I'm concerned that this is a predictor of a worse failure, maybe
something to come or something that's happened that I haven't
noticed yet. Any ideas?> That's the part that bothers me. The server has been rebooted each
> night since then, and I would expect the files would be touched at
> least as often as each reboot.
No, that's not true. The stamps on the files will only change when the file
actually changes size (e.g. an autogrow event, or a manual DBCC
SHRINKDATABASE or DBCC SHRINKFILE, or a detach/attach).
Why on earth are you rebooting the server every night? I think you might
want to deal with those problems instead of worrying about the time and size
properties of the MDF and LDF files.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||> No, that's not true. The stamps on the files will only change
> when the file actually changes size (e.g. an autogrow event, or
> a manual DBCC SHRINKDATABASE or DBCC SHRINKFILE, or a
> detach/attach).
> Why on earth are you rebooting the server every night? I think
> you might want to deal with those problems instead of worrying
> about the time and size properties of the MDF and LDF files.
>
Thanks for the response, Aaron. Actually, it is true. SQLServer
must flush to the files and release filehandles on a normal
shutdown of the services. If you have a test server, restart the
SQLServer service and watch the timestamps on your data and log
files.
Why I'm rebooting the server each night doesn't enter into this.
I'd like to focus on the problem at hand, which started after the
SQLServer crash. Any other ideas? All thoughts are appreciated!|||> Thanks for the response, Aaron. Actually, it is true. SQLServer
> must flush to the files and release filehandles on a normal
> shutdown of the services.
Whoops, you're right. I think I was cross-wired there for a few minutes.|||> Whoops, you're right. I think I was cross-wired there for a few
> minutes.
>
No sweat. Interesting what it suggests, though, isn't it? SQLServer
may not be properly closing the files when the reboot happens. It
may be something with the way the service is stopping. Maybe
SQLServer is somehow aborting, instead of stopping gracefully.
Nothing shows in the logs, though.
Anyone have other thoughts?|||> Anyone have other thoughts?
Reboot only after you have successfully brought SQL Server down gracefully?
You can stop SQL Server programmatically using NET STOP ...
after one of these reboots, SQLServer came up, then immediately
crashed. The log has a bunch of errors saying things like "Problem
creating symptom dump file due to internal exception" and "Invalid
access to memory location". The SQLServer service restarted itself
within seconds, and SQLServer continued without error.
Since then, when I look at the data and log files in Windows
Explorer or from a command prompt, the file modification timestamps
and the byte count are "stuck" at the time of the crash or restart
(they were seconds apart, so I can't tell which). There are a
couple dozen databases on this server, and they're all stuck this
way. In some cases, it's only the data file; in others, it's only
the log file; in still others, both files are stuck. Of the files
that aren't stuck at the time of the crash, they've advanced once
or twice, but none are advancing when the server reboots.
That's the part that bothers me. The server has been rebooted each
night since then, and I would expect the files would be touched at
least as often as each reboot. The only one that seems to be
behaving is tempdb, which is of course recreated after each
restart.
There's a production app running against these databases, and I'm
not aware of any user complaints or problems with the app. I've
also found that if I either detach/attach or offline/online a
database, the log and data file timestamps advance to that point.
This doesn't fix them, though: the timestamps get "stuck" at that
new point. I think SQLServer is still writing to and reading from
these files, but maybe it's not closing them properly during the
nightly reboot?
I'm concerned that this is a predictor of a worse failure, maybe
something to come or something that's happened that I haven't
noticed yet. Any ideas?> That's the part that bothers me. The server has been rebooted each
> night since then, and I would expect the files would be touched at
> least as often as each reboot.
No, that's not true. The stamps on the files will only change when the file
actually changes size (e.g. an autogrow event, or a manual DBCC
SHRINKDATABASE or DBCC SHRINKFILE, or a detach/attach).
Why on earth are you rebooting the server every night? I think you might
want to deal with those problems instead of worrying about the time and size
properties of the MDF and LDF files.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||> No, that's not true. The stamps on the files will only change
> when the file actually changes size (e.g. an autogrow event, or
> a manual DBCC SHRINKDATABASE or DBCC SHRINKFILE, or a
> detach/attach).
> Why on earth are you rebooting the server every night? I think
> you might want to deal with those problems instead of worrying
> about the time and size properties of the MDF and LDF files.
>
Thanks for the response, Aaron. Actually, it is true. SQLServer
must flush to the files and release filehandles on a normal
shutdown of the services. If you have a test server, restart the
SQLServer service and watch the timestamps on your data and log
files.
Why I'm rebooting the server each night doesn't enter into this.
I'd like to focus on the problem at hand, which started after the
SQLServer crash. Any other ideas? All thoughts are appreciated!|||> Thanks for the response, Aaron. Actually, it is true. SQLServer
> must flush to the files and release filehandles on a normal
> shutdown of the services.
Whoops, you're right. I think I was cross-wired there for a few minutes.|||> Whoops, you're right. I think I was cross-wired there for a few
> minutes.
>
No sweat. Interesting what it suggests, though, isn't it? SQLServer
may not be properly closing the files when the reboot happens. It
may be something with the way the service is stopping. Maybe
SQLServer is somehow aborting, instead of stopping gracefully.
Nothing shows in the logs, though.
Anyone have other thoughts?|||> Anyone have other thoughts?
Reboot only after you have successfully brought SQL Server down gracefully?
You can stop SQL Server programmatically using NET STOP ...
File timestamps not advancing
Our production SQLServer has automated nightly reboots. Last week,
after one of these reboots, SQLServer came up, then immediately
crashed. The log has a bunch of errors saying things like "Problem
creating symptom dump file due to internal exception" and "Invalid
access to memory location". The SQLServer service restarted itself
within seconds, and SQLServer continued without error.
Since then, when I look at the data and log files in Windows
Explorer or from a command prompt, the file modification timestamps
and the byte count are "stuck" at the time of the crash or restart
(they were seconds apart, so I can't tell which). There are a
couple dozen databases on this server, and they're all stuck this
way. In some cases, it's only the data file; in others, it's only
the log file; in still others, both files are stuck. Of the files
that aren't stuck at the time of the crash, they've advanced once
or twice, but none are advancing when the server reboots.
That's the part that bothers me. The server has been rebooted each
night since then, and I would expect the files would be touched at
least as often as each reboot. The only one that seems to be
behaving is tempdb, which is of course recreated after each
restart.
There's a production app running against these databases, and I'm
not aware of any user complaints or problems with the app. I've
also found that if I either detach/attach or offline/online a
database, the log and data file timestamps advance to that point.
This doesn't fix them, though: the timestamps get "stuck" at that
new point. I think SQLServer is still writing to and reading from
these files, but maybe it's not closing them properly during the
nightly reboot?
I'm concerned that this is a predictor of a worse failure, maybe
something to come or something that's happened that I haven't
noticed yet. Any ideas?> That's the part that bothers me. The server has been rebooted each
> night since then, and I would expect the files would be touched at
> least as often as each reboot.
No, that's not true. The stamps on the files will only change when the file
actually changes size (e.g. an autogrow event, or a manual DBCC
SHRINKDATABASE or DBCC SHRINKFILE, or a detach/attach).
Why on earth are you rebooting the server every night? I think you might
want to deal with those problems instead of worrying about the time and size
properties of the MDF and LDF files.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||> No, that's not true. The stamps on the files will only change
> when the file actually changes size (e.g. an autogrow event, or
> a manual DBCC SHRINKDATABASE or DBCC SHRINKFILE, or a
> detach/attach).
> Why on earth are you rebooting the server every night? I think
> you might want to deal with those problems instead of worrying
> about the time and size properties of the MDF and LDF files.
>
Thanks for the response, Aaron. Actually, it is true. SQLServer
must flush to the files and release filehandles on a normal
shutdown of the services. If you have a test server, restart the
SQLServer service and watch the timestamps on your data and log
files.
Why I'm rebooting the server each night doesn't enter into this.
I'd like to focus on the problem at hand, which started after the
SQLServer crash. Any other ideas? All thoughts are appreciated!|||> Thanks for the response, Aaron. Actually, it is true. SQLServer
> must flush to the files and release filehandles on a normal
> shutdown of the services.
Whoops, you're right. I think I was cross-wired there for a few minutes.|||> Whoops, you're right. I think I was cross-wired there for a few
> minutes.
>
No sweat. Interesting what it suggests, though, isn't it? SQLServer
may not be properly closing the files when the reboot happens. It
may be something with the way the service is stopping. Maybe
SQLServer is somehow aborting, instead of stopping gracefully.
Nothing shows in the logs, though.
Anyone have other thoughts?|||> Anyone have other thoughts?
Reboot only after you have successfully brought SQL Server down gracefully?
You can stop SQL Server programmatically using NET STOP ...
after one of these reboots, SQLServer came up, then immediately
crashed. The log has a bunch of errors saying things like "Problem
creating symptom dump file due to internal exception" and "Invalid
access to memory location". The SQLServer service restarted itself
within seconds, and SQLServer continued without error.
Since then, when I look at the data and log files in Windows
Explorer or from a command prompt, the file modification timestamps
and the byte count are "stuck" at the time of the crash or restart
(they were seconds apart, so I can't tell which). There are a
couple dozen databases on this server, and they're all stuck this
way. In some cases, it's only the data file; in others, it's only
the log file; in still others, both files are stuck. Of the files
that aren't stuck at the time of the crash, they've advanced once
or twice, but none are advancing when the server reboots.
That's the part that bothers me. The server has been rebooted each
night since then, and I would expect the files would be touched at
least as often as each reboot. The only one that seems to be
behaving is tempdb, which is of course recreated after each
restart.
There's a production app running against these databases, and I'm
not aware of any user complaints or problems with the app. I've
also found that if I either detach/attach or offline/online a
database, the log and data file timestamps advance to that point.
This doesn't fix them, though: the timestamps get "stuck" at that
new point. I think SQLServer is still writing to and reading from
these files, but maybe it's not closing them properly during the
nightly reboot?
I'm concerned that this is a predictor of a worse failure, maybe
something to come or something that's happened that I haven't
noticed yet. Any ideas?> That's the part that bothers me. The server has been rebooted each
> night since then, and I would expect the files would be touched at
> least as often as each reboot.
No, that's not true. The stamps on the files will only change when the file
actually changes size (e.g. an autogrow event, or a manual DBCC
SHRINKDATABASE or DBCC SHRINKFILE, or a detach/attach).
Why on earth are you rebooting the server every night? I think you might
want to deal with those problems instead of worrying about the time and size
properties of the MDF and LDF files.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||> No, that's not true. The stamps on the files will only change
> when the file actually changes size (e.g. an autogrow event, or
> a manual DBCC SHRINKDATABASE or DBCC SHRINKFILE, or a
> detach/attach).
> Why on earth are you rebooting the server every night? I think
> you might want to deal with those problems instead of worrying
> about the time and size properties of the MDF and LDF files.
>
Thanks for the response, Aaron. Actually, it is true. SQLServer
must flush to the files and release filehandles on a normal
shutdown of the services. If you have a test server, restart the
SQLServer service and watch the timestamps on your data and log
files.
Why I'm rebooting the server each night doesn't enter into this.
I'd like to focus on the problem at hand, which started after the
SQLServer crash. Any other ideas? All thoughts are appreciated!|||> Thanks for the response, Aaron. Actually, it is true. SQLServer
> must flush to the files and release filehandles on a normal
> shutdown of the services.
Whoops, you're right. I think I was cross-wired there for a few minutes.|||> Whoops, you're right. I think I was cross-wired there for a few
> minutes.
>
No sweat. Interesting what it suggests, though, isn't it? SQLServer
may not be properly closing the files when the reboot happens. It
may be something with the way the service is stopping. Maybe
SQLServer is somehow aborting, instead of stopping gracefully.
Nothing shows in the logs, though.
Anyone have other thoughts?|||> Anyone have other thoughts?
Reboot only after you have successfully brought SQL Server down gracefully?
You can stop SQL Server programmatically using NET STOP ...
Wednesday, March 7, 2012
File System Error after Restore
Hi Guys,
I have issues in restoring our Analysis Services database. Any help will be great.
I took backups of our production olap database and Unfortunately, I had to restore next day and it didn't worked out very well.
Restore process was successful but when I tried to Query database I was getting error - "File system error: The following error occurred while the \\?\F:\Program File( x86)\ Microsoft SQL Server\MSSQL.2\OLAP\Data\<Olap db name>.0.db\<Cube name>.0.cub\...........\4.fact.data' ...
My cubes have 4 partitions. I used SSMS to back and restore. Used compressed option only. Let me know if I need to put more information.
Thank you - Ashok
Could you post the full error message?
Thanks,
Bryan
I tried to run restore again so I can copy message. I got error while running restore following. I will try to run again...
TITLE: Microsoft SQL Server Management Studio
The ddl2:MemberKeysUnique element at line 63, column 5183 (namespace http://schemas.microsoft.com/analysisservices/2003/engine/2) cannot appear under Load/ObjectDefinition/Dimension/Hierarchies/Hierarchy.
Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\?\G:\SQLServerData\MSSQL.2\OLAP\Data\Risk Reports Histroy.0.db\Credit Counterparty.1.dim.xml'.
Errors in the metadata manager. An error occurred when loading the Accounting cube, from the file, '\\?\G:\SQLServerData\MSSQL.2\OLAP\Data\Risk Reports Histroy.0.db\Accounting.1.cub.xml'.
(Microsoft.AnalysisServices)
BUTTONS:
OK
|||The problem seems to be an issue with the SP level on the target system. Take a look at Jon Galloway's blog-entry on this. He had the same issue and describes how he resolved it.
http://weblogs.asp.net/jgalloway/archive/2007/06/21/sql-server-analysis-services-quot-errors-in-the-metadata-manager-quot-when-restoring-a-backup.aspx
Hope that helps,
Bryan
I tried to run new backup in production to start new test but I get this error. I see there is backup file of 29G I think i didn't checked the compressed mode. Backup was not completed. I have lot of disk space. I will checked that linked it's blocked at my work but I have SP2 in procution.
TITLE: Microsoft SQL Server Management Studio
File system error: The following error occurred while the '\\?\F:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\OLAP\Data\Risk Reports History.0.db\Positions.0.cub\Positions.0.det\Positions 2006.0.prt\14.fact.data' file was being copied to the 'F:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\OLAP\Backup\Risk Reports History.abf' file: .
(Microsoft.AnalysisServices)
BUTTONS:
OK
|||Could be a lot of things. Make sure you are on the latest service pack. Make sure the paths listed are valid on your system. Make sure you and the service account have sufficient access to those paths. You may want to reprocess your cube and try again. Alternatively, you may want to simply reverse the database definition, deploy that to your other environment and reprocess it there.
Basically, I'm not sure there is a clearly identifiable answer to the problem.
B.
|||Now I double confirm that service pack 2 is installed and I restarted my backup / restore test.
This is what I get - for smaller olap databases (I don't have cut off size) I am fine I can backup / restore works fine.
For bigger olap databases Either I get following error while taking backup
File system error: The following error occurred while the '\\?\F:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\OLAP\Data\Risk Reports History.0.db\Positions.0.cub\Positions.0.det\Positions 2006.0.prt\14.fact.data' file was being copied to the 'F:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\OLAP\Backup\Risk Reports History.abf' file: .(Microsoft.AnalysisServices)
OR if I try to restore one of my old backup that means backup did works some time. Restore doesn't end with any error guess works fine but when I try to query measures
get this error:
The query could not be processed:
o File system error: The following file is corrupted: Physical file: \\?\G:\SQLServerData\MSSQL.2\OLAP\Data\RiskReportsHistoryTest.0.db\Scenario Analysis.0.cub\Scenario Analysis.0.det\Scenario Analysis 2006.0.prt\2.fact.data. Logical file .
Easy guess both has same problem comes back with "File system error". I guess I need to call Microsoft help.
Thank you - Ashok
|||Might be the best bet. Sorry I couldn't be of more help.
B.
Ashok, I am having this same problem.
I process the cube on one server, can browse the data fine, but when I backup and restore to the other server, get the file corrupt error, see below.
I have tried to clean out the data directory, tried restoring the backup from both local and network share locations. Same result each time. One note is that this project was first created by using a solution, then adding projects, but one developer opened the project by creating it from server inside of bids...so I ended up with solution files are more than one folder depth. I read one entry that said this might have caused the problem.
Error when browsing cube on target production server after restore from development server backup.
Any luck fixing it?
any help would be greatly appreciated.
Troy
Sunday, February 19, 2012
File Groups / Files
Hello
I have a production database consisting of 3 filegroups,
file group1 contains system data
file group2 contains user data
file group3 contains log data
I would like to restore this database onto a test server, the problem is
file group2 is larger than the largest disk on the test server, is there a
way on the restore to split file group2 on to 2 different disk or any way to
do this without restructuring the production database?
Thanks
Jim WileThis is a multi-part message in MIME format.
--=_NextPart_000_06A5_01C35D8A.DE0E6060
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I would add a file to group2 then shrink the existing file in group 2 to =half of its current size. This will push data from the existing file to =the new file. Then, when you restore the database, each file can go to =a separate drive.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"jim wile" <jimwile@.mopac.com> wrote in message =news:#FPXataXDHA.1204@.TK2MSFTNGP12.phx.gbl...
Hello
I have a production database consisting of 3 filegroups,
file group1 contains system data
file group2 contains user data
file group3 contains log data
I would like to restore this database onto a test server, the problem is
file group2 is larger than the largest disk on the test server, is there =a
way on the restore to split file group2 on to 2 different disk or any =way to
do this without restructuring the production database?
Thanks
Jim Wile
--=_NextPart_000_06A5_01C35D8A.DE0E6060
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I would add a file to group2 then =shrink the existing file in group 2 to half of its current size. This will =push data from the existing file to the new file. Then, when you restore the =database, each file can go to a separate drive.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"jim wile" wrote in message news:#FPXataXDHA.1204=@.TK2MSFTNGP12.phx.gbl...HelloI have a production database consisting of 3 =filegroups, file group1 contains system data file group2 =contains user data file group3 contains log dataI would =like to restore this database onto a test server, the problem isfile group2 =is larger than the largest disk on the test server, is there away on =the restore to split file group2 on to 2 different disk or any way todo =this without restructuring the production database?ThanksJim Wile
--=_NextPart_000_06A5_01C35D8A.DE0E6060--|||You can use the WITH MOVE option to specify alternate locations for
individual files. This is independent of filegroup membership. Your
text server will still need space to accommodate the largest single file
since files cannot be split during a restore.
If a file is too large for your test server disk configuration, you'll
need to change the prod database file allocations. To do this, add
addition files to the filegroup and shrink the large file using DBCC
SHRINKFILE. This will migrate data to the new files and release space
from the large file.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"jim wile" <jimwile@.mopac.com> wrote in message
news:%23FPXataXDHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hello
> I have a production database consisting of 3 filegroups,
> file group1 contains system data
> file group2 contains user data
> file group3 contains log data
> I would like to restore this database onto a test server, the problem
is
> file group2 is larger than the largest disk on the test server, is
there a
> way on the restore to split file group2 on to 2 different disk or any
way to
> do this without restructuring the production database?
> Thanks
> Jim Wile
>
>|||Thanks for all your help!
JIm Wile
"jim wile" <jimwile@.mopac.com> wrote in message
news:#FPXataXDHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hello
> I have a production database consisting of 3 filegroups,
> file group1 contains system data
> file group2 contains user data
> file group3 contains log data
> I would like to restore this database onto a test server, the problem is
> file group2 is larger than the largest disk on the test server, is there a
> way on the restore to split file group2 on to 2 different disk or any way
to
> do this without restructuring the production database?
> Thanks
> Jim Wile
>
>
I have a production database consisting of 3 filegroups,
file group1 contains system data
file group2 contains user data
file group3 contains log data
I would like to restore this database onto a test server, the problem is
file group2 is larger than the largest disk on the test server, is there a
way on the restore to split file group2 on to 2 different disk or any way to
do this without restructuring the production database?
Thanks
Jim WileThis is a multi-part message in MIME format.
--=_NextPart_000_06A5_01C35D8A.DE0E6060
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I would add a file to group2 then shrink the existing file in group 2 to =half of its current size. This will push data from the existing file to =the new file. Then, when you restore the database, each file can go to =a separate drive.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"jim wile" <jimwile@.mopac.com> wrote in message =news:#FPXataXDHA.1204@.TK2MSFTNGP12.phx.gbl...
Hello
I have a production database consisting of 3 filegroups,
file group1 contains system data
file group2 contains user data
file group3 contains log data
I would like to restore this database onto a test server, the problem is
file group2 is larger than the largest disk on the test server, is there =a
way on the restore to split file group2 on to 2 different disk or any =way to
do this without restructuring the production database?
Thanks
Jim Wile
--=_NextPart_000_06A5_01C35D8A.DE0E6060
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I would add a file to group2 then =shrink the existing file in group 2 to half of its current size. This will =push data from the existing file to the new file. Then, when you restore the =database, each file can go to a separate drive.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"jim wile"
--=_NextPart_000_06A5_01C35D8A.DE0E6060--|||You can use the WITH MOVE option to specify alternate locations for
individual files. This is independent of filegroup membership. Your
text server will still need space to accommodate the largest single file
since files cannot be split during a restore.
If a file is too large for your test server disk configuration, you'll
need to change the prod database file allocations. To do this, add
addition files to the filegroup and shrink the large file using DBCC
SHRINKFILE. This will migrate data to the new files and release space
from the large file.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"jim wile" <jimwile@.mopac.com> wrote in message
news:%23FPXataXDHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hello
> I have a production database consisting of 3 filegroups,
> file group1 contains system data
> file group2 contains user data
> file group3 contains log data
> I would like to restore this database onto a test server, the problem
is
> file group2 is larger than the largest disk on the test server, is
there a
> way on the restore to split file group2 on to 2 different disk or any
way to
> do this without restructuring the production database?
> Thanks
> Jim Wile
>
>|||Thanks for all your help!
JIm Wile
"jim wile" <jimwile@.mopac.com> wrote in message
news:#FPXataXDHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hello
> I have a production database consisting of 3 filegroups,
> file group1 contains system data
> file group2 contains user data
> file group3 contains log data
> I would like to restore this database onto a test server, the problem is
> file group2 is larger than the largest disk on the test server, is there a
> way on the restore to split file group2 on to 2 different disk or any way
to
> do this without restructuring the production database?
> Thanks
> Jim Wile
>
>
Subscribe to:
Posts (Atom)