For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- Machttp://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>
Showing posts with label physical. Show all posts
Showing posts with label physical. Show all posts
Friday, March 23, 2012
HELP on How to move database files
For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- MacIf you are able to detach/ attach succesfully, whats the
problem?
>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>|||I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> >--Original Message--
> >For performance reason, I have decided to move my
> database files around. I
> >have tried using Attach and Detach database to move
> physical location of the
> >files successfully.
> >
> >What I want to do is to move the transaction log file to
> a different
> >location or even better get rid of it and create a new
> log file in its new
> >location. I have tried dbcc shrinkfile to truncate the
> log file but does
> >not allow me to remove it or empty it into another log
> file that I have
> >created in a new location. Any suggestion on
> moving/removing database
> >files are appreciated.
> >
> >- Mac
> >
> >
> >.
> >|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file
to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>
>.
>|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> > If Im not mistaken, you would:
> >
> > Detach.
> > Turn off SQL.
> > Delete log file.
> > Turn on SQL.
> > Attach single file with sp_attach_single_file_db.
> >
> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.
>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
>> ChrisR wrote:
>> > If Im not mistaken, you would:
>> >
>> > Detach.
>> > Turn off SQL.
>> > Delete log file.
>> > Turn on SQL.
>> > Attach single file with sp_attach_single_file_db.
>> >
>> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>> I've never had any problems doing this. I'd certainly
make a copy of both
>> the MDF and LDF files before doing it, though.
>> John.
>>
>
>.
>|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
> >I want to drop the log file altogether and start with a new one in a new
> > location. I could not do this with Detach/Attach as far as I can tell.
> >
> >
> > "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> > news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> >> If you are able to detach/ attach succesfully, whats the
> >> problem?
> >>
> >>
> >> >--Original Message--
> >> >For performance reason, I have decided to move my
> >> database files around. I
> >> >have tried using Attach and Detach database to move
> >> physical location of the
> >> >files successfully.
> >> >
> >> >What I want to do is to move the transaction log file to
> >> a different
> >> >location or even better get rid of it and create a new
> >> log file in its new
> >> >location. I have tried dbcc shrinkfile to truncate the
> >> log file but does
> >> >not allow me to remove it or empty it into another log
> >> file that I have
> >> >created in a new location. Any suggestion on
> >> moving/removing database
> >> >files are appreciated.
> >> >
> >> >- Mac
> >> >
> >> >
> >> >.
> >> >
> >
> >
>
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- MacIf you are able to detach/ attach succesfully, whats the
problem?
>--Original Message--
>For performance reason, I have decided to move my
database files around. I
>have tried using Attach and Detach database to move
physical location of the
>files successfully.
>What I want to do is to move the transaction log file to
a different
>location or even better get rid of it and create a new
log file in its new
>location. I have tried dbcc shrinkfile to truncate the
log file but does
>not allow me to remove it or empty it into another log
file that I have
>created in a new location. Any suggestion on
moving/removing database
>files are appreciated.
>- Mac
>
>.
>|||I want to drop the log file altogether and start with a new one in a new
location. I could not do this with Detach/Attach as far as I can tell.
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> If you are able to detach/ attach succesfully, whats the
> problem?
>
> >--Original Message--
> >For performance reason, I have decided to move my
> database files around. I
> >have tried using Attach and Detach database to move
> physical location of the
> >files successfully.
> >
> >What I want to do is to move the transaction log file to
> a different
> >location or even better get rid of it and create a new
> log file in its new
> >location. I have tried dbcc shrinkfile to truncate the
> log file but does
> >not allow me to remove it or empty it into another log
> file that I have
> >created in a new location. Any suggestion on
> moving/removing database
> >files are appreciated.
> >
> >- Mac
> >
> >
> >.
> >|||If Im not mistaken, you would:
Detach.
Turn off SQL.
Delete log file.
Turn on SQL.
Attach single file with sp_attach_single_file_db.
DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>--Original Message--
>I want to drop the log file altogether and start with a
new one in a new
>location. I could not do this with Detach/Attach as far
as I can tell.
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file
to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>
>.
>|||ChrisR wrote:
> If Im not mistaken, you would:
> Detach.
> Turn off SQL.
> Delete log file.
> Turn on SQL.
> Attach single file with sp_attach_single_file_db.
> DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
I've never had any problems doing this. I'd certainly make a copy of both
the MDF and LDF files before doing it, though.
John.|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>|||Thank you Chris for the suggestion and John confirming the approach.
What do you mean by turn off SQL ? When I Detach a database, isn't it off
?
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
> ChrisR wrote:
> > If Im not mistaken, you would:
> >
> > Detach.
> > Turn off SQL.
> > Delete log file.
> > Turn on SQL.
> > Attach single file with sp_attach_single_file_db.
> >
> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
> I've never had any problems doing this. I'd certainly make a copy of both
> the MDF and LDF files before doing it, though.
> John.
>|||I mean in order to delete the .ldf file after you detach
the db I beleive your going to need to turn off the SQL
service.
>--Original Message--
>Thank you Chris for the suggestion and John confirming
the approach.
>What do you mean by turn off SQL ? When I Detach a
database, isn't it off
>?
>"John McLusky" <jmclusky@.community.nospam> wrote in
message
>news:OOOGklFjEHA.3972@.tk2msftngp13.phx.gbl...
>> ChrisR wrote:
>> > If Im not mistaken, you would:
>> >
>> > Detach.
>> > Turn off SQL.
>> > Delete log file.
>> > Turn on SQL.
>> > Attach single file with sp_attach_single_file_db.
>> >
>> > DO THIS IN TEST FIRST. IVE NEVER DONE IT!!!
>> I've never had any problems doing this. I'd certainly
make a copy of both
>> the MDF and LDF files before doing it, though.
>> John.
>>
>
>.
>|||Why do you need to start a new log file? If the log file is too large, you
can make it smaller with DBCC SHRINKFILE. You can relocate the log file
using sp_attach_db.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
>I want to drop the log file altogether and start with a new one in a new
> location. I could not do this with Detach/Attach as far as I can tell.
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
>> If you are able to detach/ attach succesfully, whats the
>> problem?
>>
>> >--Original Message--
>> >For performance reason, I have decided to move my
>> database files around. I
>> >have tried using Attach and Detach database to move
>> physical location of the
>> >files successfully.
>> >
>> >What I want to do is to move the transaction log file to
>> a different
>> >location or even better get rid of it and create a new
>> log file in its new
>> >location. I have tried dbcc shrinkfile to truncate the
>> log file but does
>> >not allow me to remove it or empty it into another log
>> file that I have
>> >created in a new location. Any suggestion on
>> moving/removing database
>> >files are appreciated.
>> >
>> >- Mac
>> >
>> >
>> >.
>> >
>|||Thanks Dan for your reply. You are right, I do not need to remove it any
longer. I just did not know how to move the file.
-- Mac
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e%23sHGYjjEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Why do you need to start a new log file? If the log file is too large,
you
> can make it smaller with DBCC SHRINKFILE. You can relocate the log file
> using sp_attach_db.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:cgnof3$1uat$1@.si05.rsvl.unisys.com...
> >I want to drop the log file altogether and start with a new one in a new
> > location. I could not do this with Detach/Attach as far as I can tell.
> >
> >
> > "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> > news:15fd01c48c53$0680b910$a601280a@.phx.gbl...
> >> If you are able to detach/ attach succesfully, whats the
> >> problem?
> >>
> >>
> >> >--Original Message--
> >> >For performance reason, I have decided to move my
> >> database files around. I
> >> >have tried using Attach and Detach database to move
> >> physical location of the
> >> >files successfully.
> >> >
> >> >What I want to do is to move the transaction log file to
> >> a different
> >> >location or even better get rid of it and create a new
> >> log file in its new
> >> >location. I have tried dbcc shrinkfile to truncate the
> >> log file but does
> >> >not allow me to remove it or empty it into another log
> >> file that I have
> >> >created in a new location. Any suggestion on
> >> moving/removing database
> >> >files are appreciated.
> >> >
> >> >- Mac
> >> >
> >> >
> >> >.
> >> >
> >
> >
>
HELP on How to move database files
For performance reason, I have decided to move my database files around. I
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- Mac
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>
sql
have tried using Attach and Detach database to move physical location of the
files successfully.
What I want to do is to move the transaction log file to a different
location or even better get rid of it and create a new log file in its new
location. I have tried dbcc shrinkfile to truncate the log file but does
not allow me to remove it or empty it into another log file that I have
created in a new location. Any suggestion on moving/removing database
files are appreciated.
- Mac
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:cgnlre$1sd6$1@.si05.rsvl.unisys.com...
> For performance reason, I have decided to move my database files around.
I
> have tried using Attach and Detach database to move physical location of
the
> files successfully.
> What I want to do is to move the transaction log file to a different
> location or even better get rid of it and create a new log file in its new
> location. I have tried dbcc shrinkfile to truncate the log file but does
> not allow me to remove it or empty it into another log file that I have
> created in a new location. Any suggestion on moving/removing database
> files are appreciated.
> - Mac
>
sql
Monday, March 12, 2012
Help Needed on Hardware Setup
I have been tasked with setting up a brand new PowerEdge 2650 for
SharePoint, SQL 2000 and Windows 2003. This server has 5 physical disks,
all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
My question is in regards to the best possible RAID setup for SQL
performance as well as retaining as much disk space as possible.
If I read the docs correctly SQL is best suited to have the tempdb,
application files and log files all on separate physical disks.
I cannot find a way to do this without losing a lot of my available disk
space.
The way I see it, as well as my SQL admin, there are 3 factors to consider.
1. Speed (performance), 2. Reliability 3. Capacity.
If I configure everything in one big RAID 5 array I would have excellent
reliability, and have (73x4) 292GB free for data, probably 270GB after
application installations.
If I configure disk 1 for OS and apps, disk 2 for log files and disks 3,4
and 5 for data I would have 219GB available for data with no redundancy, if
I add RAID 5 across those 3 disks I would get 146GB useable space,
dramatically less than the 292GB from the other config.
A third option is to create a 10GB (or 20GB) partition on disk 1, mirror
that to disk 2 and configure all 5 disks in a RAID 5 configuration. The
problem with this is that I can only use 63GB of each disk, giving me 315GB
minus parity drive, for a total useable size of 252GB. However, I don't
think this configuration would give me much of a performance gain in SQL.
So my questions are this : what would you recommend, taking into account
that this machine will be running W2k3, SQL 2000 and SharePoint 2003. The
server will have about 50GB of local data *eventually*, and will be indexing
around 150GB of data residing on a NAS device.
Also, I have heard that SharePoint doesn't really tax SQL that bad and that
I'm over-analyzing the problem. Just set it up as one big RAID 5 and be
done with it. Thought?
Sorry for the long post.Hi,
Follow the below URL which gives idea on What RAID Levels
to choose for SQL Boxes and why so. Of Course More replies
to come from experts.
http://www.sql-server-performance.com/q&a38.asp
HTH
THIRUMAL REDDY MARAM
System Admin / SQL DBA
>--Original Message--
>I have been tasked with setting up a brand new PowerEdge
2650 for
>SharePoint, SQL 2000 and Windows 2003. This server has 5
physical disks,
>all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
>My question is in regards to the best possible RAID setup
for SQL
>performance as well as retaining as much disk space as
possible.
>If I read the docs correctly SQL is best suited to have
the tempdb,
>application files and log files all on separate physical
disks.
>I cannot find a way to do this without losing a lot of my
available disk
>space.
>The way I see it, as well as my SQL admin, there are 3
factors to consider.
>1. Speed (performance), 2. Reliability 3. Capacity.
>If I configure everything in one big RAID 5 array I would
have excellent
>reliability, and have (73x4) 292GB free for data,
probably 270GB after
>application installations.
>If I configure disk 1 for OS and apps, disk 2 for log
files and disks 3,4
>and 5 for data I would have 219GB available for data with
no redundancy, if
>I add RAID 5 across those 3 disks I would get 146GB
useable space,
>dramatically less than the 292GB from the other config.
>A third option is to create a 10GB (or 20GB) partition on
disk 1, mirror
>that to disk 2 and configure all 5 disks in a RAID 5
configuration. The
>problem with this is that I can only use 63GB of each
disk, giving me 315GB
>minus parity drive, for a total useable size of 252GB.
However, I don't
>think this configuration would give me much of a
performance gain in SQL.
>So my questions are this : what would you recommend,
taking into account
>that this machine will be running W2k3, SQL 2000 and
SharePoint 2003. The
>server will have about 50GB of local data *eventually*,
and will be indexing
>around 150GB of data residing on a NAS device.
>Also, I have heard that SharePoint doesn't really tax SQL
that bad and that
>I'm over-analyzing the problem. Just set it up as one
big RAID 5 and be
>done with it. Thought?
>Sorry for the long post.
>
>.
>|||Every case is unique per the Budget , Scope, SLAs etc... to come up with teh
right configuration.
It also depends upon the Access Patterns, Number of Data files and groups,
Index and TempDB Placement.
I would recommend that you start at the MSFT Operations guide at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
And look at Chapter 6, which talks about Capacity Planning.
--
HTH
Satish Balusa
Corillian Corp.
"cyberpunk" <blah@.anon.com> wrote in message
news:eONEAKr3DHA.1404@.TK2MSFTNGP11.phx.gbl...
> I have been tasked with setting up a brand new PowerEdge 2650 for
> SharePoint, SQL 2000 and Windows 2003. This server has 5 physical disks,
> all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
> My question is in regards to the best possible RAID setup for SQL
> performance as well as retaining as much disk space as possible.
> If I read the docs correctly SQL is best suited to have the tempdb,
> application files and log files all on separate physical disks.
> I cannot find a way to do this without losing a lot of my available disk
> space.
> The way I see it, as well as my SQL admin, there are 3 factors to
consider.
> 1. Speed (performance), 2. Reliability 3. Capacity.
> If I configure everything in one big RAID 5 array I would have excellent
> reliability, and have (73x4) 292GB free for data, probably 270GB after
> application installations.
> If I configure disk 1 for OS and apps, disk 2 for log files and disks 3,4
> and 5 for data I would have 219GB available for data with no redundancy,
if
> I add RAID 5 across those 3 disks I would get 146GB useable space,
> dramatically less than the 292GB from the other config.
> A third option is to create a 10GB (or 20GB) partition on disk 1, mirror
> that to disk 2 and configure all 5 disks in a RAID 5 configuration. The
> problem with this is that I can only use 63GB of each disk, giving me
315GB
> minus parity drive, for a total useable size of 252GB. However, I don't
> think this configuration would give me much of a performance gain in SQL.
> So my questions are this : what would you recommend, taking into account
> that this machine will be running W2k3, SQL 2000 and SharePoint 2003. The
> server will have about 50GB of local data *eventually*, and will be
indexing
> around 150GB of data residing on a NAS device.
> Also, I have heard that SharePoint doesn't really tax SQL that bad and
that
> I'm over-analyzing the problem. Just set it up as one big RAID 5 and be
> done with it. Thought?
> Sorry for the long post.
>
SharePoint, SQL 2000 and Windows 2003. This server has 5 physical disks,
all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
My question is in regards to the best possible RAID setup for SQL
performance as well as retaining as much disk space as possible.
If I read the docs correctly SQL is best suited to have the tempdb,
application files and log files all on separate physical disks.
I cannot find a way to do this without losing a lot of my available disk
space.
The way I see it, as well as my SQL admin, there are 3 factors to consider.
1. Speed (performance), 2. Reliability 3. Capacity.
If I configure everything in one big RAID 5 array I would have excellent
reliability, and have (73x4) 292GB free for data, probably 270GB after
application installations.
If I configure disk 1 for OS and apps, disk 2 for log files and disks 3,4
and 5 for data I would have 219GB available for data with no redundancy, if
I add RAID 5 across those 3 disks I would get 146GB useable space,
dramatically less than the 292GB from the other config.
A third option is to create a 10GB (or 20GB) partition on disk 1, mirror
that to disk 2 and configure all 5 disks in a RAID 5 configuration. The
problem with this is that I can only use 63GB of each disk, giving me 315GB
minus parity drive, for a total useable size of 252GB. However, I don't
think this configuration would give me much of a performance gain in SQL.
So my questions are this : what would you recommend, taking into account
that this machine will be running W2k3, SQL 2000 and SharePoint 2003. The
server will have about 50GB of local data *eventually*, and will be indexing
around 150GB of data residing on a NAS device.
Also, I have heard that SharePoint doesn't really tax SQL that bad and that
I'm over-analyzing the problem. Just set it up as one big RAID 5 and be
done with it. Thought?
Sorry for the long post.Hi,
Follow the below URL which gives idea on What RAID Levels
to choose for SQL Boxes and why so. Of Course More replies
to come from experts.
http://www.sql-server-performance.com/q&a38.asp
HTH
THIRUMAL REDDY MARAM
System Admin / SQL DBA
>--Original Message--
>I have been tasked with setting up a brand new PowerEdge
2650 for
>SharePoint, SQL 2000 and Windows 2003. This server has 5
physical disks,
>all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
>My question is in regards to the best possible RAID setup
for SQL
>performance as well as retaining as much disk space as
possible.
>If I read the docs correctly SQL is best suited to have
the tempdb,
>application files and log files all on separate physical
disks.
>I cannot find a way to do this without losing a lot of my
available disk
>space.
>The way I see it, as well as my SQL admin, there are 3
factors to consider.
>1. Speed (performance), 2. Reliability 3. Capacity.
>If I configure everything in one big RAID 5 array I would
have excellent
>reliability, and have (73x4) 292GB free for data,
probably 270GB after
>application installations.
>If I configure disk 1 for OS and apps, disk 2 for log
files and disks 3,4
>and 5 for data I would have 219GB available for data with
no redundancy, if
>I add RAID 5 across those 3 disks I would get 146GB
useable space,
>dramatically less than the 292GB from the other config.
>A third option is to create a 10GB (or 20GB) partition on
disk 1, mirror
>that to disk 2 and configure all 5 disks in a RAID 5
configuration. The
>problem with this is that I can only use 63GB of each
disk, giving me 315GB
>minus parity drive, for a total useable size of 252GB.
However, I don't
>think this configuration would give me much of a
performance gain in SQL.
>So my questions are this : what would you recommend,
taking into account
>that this machine will be running W2k3, SQL 2000 and
SharePoint 2003. The
>server will have about 50GB of local data *eventually*,
and will be indexing
>around 150GB of data residing on a NAS device.
>Also, I have heard that SharePoint doesn't really tax SQL
that bad and that
>I'm over-analyzing the problem. Just set it up as one
big RAID 5 and be
>done with it. Thought?
>Sorry for the long post.
>
>.
>|||Every case is unique per the Budget , Scope, SLAs etc... to come up with teh
right configuration.
It also depends upon the Access Patterns, Number of Data files and groups,
Index and TempDB Placement.
I would recommend that you start at the MSFT Operations guide at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
And look at Chapter 6, which talks about Capacity Planning.
--
HTH
Satish Balusa
Corillian Corp.
"cyberpunk" <blah@.anon.com> wrote in message
news:eONEAKr3DHA.1404@.TK2MSFTNGP11.phx.gbl...
> I have been tasked with setting up a brand new PowerEdge 2650 for
> SharePoint, SQL 2000 and Windows 2003. This server has 5 physical disks,
> all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
> My question is in regards to the best possible RAID setup for SQL
> performance as well as retaining as much disk space as possible.
> If I read the docs correctly SQL is best suited to have the tempdb,
> application files and log files all on separate physical disks.
> I cannot find a way to do this without losing a lot of my available disk
> space.
> The way I see it, as well as my SQL admin, there are 3 factors to
consider.
> 1. Speed (performance), 2. Reliability 3. Capacity.
> If I configure everything in one big RAID 5 array I would have excellent
> reliability, and have (73x4) 292GB free for data, probably 270GB after
> application installations.
> If I configure disk 1 for OS and apps, disk 2 for log files and disks 3,4
> and 5 for data I would have 219GB available for data with no redundancy,
if
> I add RAID 5 across those 3 disks I would get 146GB useable space,
> dramatically less than the 292GB from the other config.
> A third option is to create a 10GB (or 20GB) partition on disk 1, mirror
> that to disk 2 and configure all 5 disks in a RAID 5 configuration. The
> problem with this is that I can only use 63GB of each disk, giving me
315GB
> minus parity drive, for a total useable size of 252GB. However, I don't
> think this configuration would give me much of a performance gain in SQL.
> So my questions are this : what would you recommend, taking into account
> that this machine will be running W2k3, SQL 2000 and SharePoint 2003. The
> server will have about 50GB of local data *eventually*, and will be
indexing
> around 150GB of data residing on a NAS device.
> Also, I have heard that SharePoint doesn't really tax SQL that bad and
that
> I'm over-analyzing the problem. Just set it up as one big RAID 5 and be
> done with it. Thought?
> Sorry for the long post.
>
Help Needed on Hardware Setup
I have been tasked with setting up a brand new PowerEdge 2650 for
SharePoint, SQL 2000 and Windows 2003. This server has 5 physical disks,
all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
My question is in regards to the best possible RAID setup for SQL
performance as well as retaining as much disk space as possible.
If I read the docs correctly SQL is best suited to have the tempdb,
application files and log files all on separate physical disks.
I cannot find a way to do this without losing a lot of my available disk
space.
The way I see it, as well as my SQL admin, there are 3 factors to consider.
1. Speed (performance), 2. Reliability 3. Capacity.
If I configure everything in one big RAID 5 array I would have excellent
reliability, and have (73x4) 292GB free for data, probably 270GB after
application installations.
If I configure disk 1 for OS and apps, disk 2 for log files and disks 3,4
and 5 for data I would have 219GB available for data with no redundancy, if
I add RAID 5 across those 3 disks I would get 146GB useable space,
dramatically less than the 292GB from the other config.
A third option is to create a 10GB (or 20GB) partition on disk 1, mirror
that to disk 2 and configure all 5 disks in a RAID 5 configuration. The
problem with this is that I can only use 63GB of each disk, giving me 315GB
minus parity drive, for a total useable size of 252GB. However, I don't
think this configuration would give me much of a performance gain in SQL.
So my questions are this : what would you recommend, taking into account
that this machine will be running W2k3, SQL 2000 and SharePoint 2003. The
server will have about 50GB of local data *eventually*, and will be indexing
around 150GB of data residing on a NAS device.
Also, I have heard that SharePoint doesn't really tax SQL that bad and that
I'm over-analyzing the problem. Just set it up as one big RAID 5 and be
done with it. Thought?
Sorry for the long post.Every case is unique per the Budget , Scope, SLAs etc... to come up with teh
right configuration.
It also depends upon the Access Patterns, Number of Data files and groups,
Index and TempDB Placement.
I would recommend that you start at the MSFT Operations guide at
http://www.microsoft.com/technet/tr...ide/default.asp
And look at Chapter 6, which talks about Capacity Planning.
HTH
Satish Balusa
Corillian Corp.
"cyberpunk" <blah@.anon.com> wrote in message
news:eONEAKr3DHA.1404@.TK2MSFTNGP11.phx.gbl...
consider.
if
315GB
indexing
that
SharePoint, SQL 2000 and Windows 2003. This server has 5 physical disks,
all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
My question is in regards to the best possible RAID setup for SQL
performance as well as retaining as much disk space as possible.
If I read the docs correctly SQL is best suited to have the tempdb,
application files and log files all on separate physical disks.
I cannot find a way to do this without losing a lot of my available disk
space.
The way I see it, as well as my SQL admin, there are 3 factors to consider.
1. Speed (performance), 2. Reliability 3. Capacity.
If I configure everything in one big RAID 5 array I would have excellent
reliability, and have (73x4) 292GB free for data, probably 270GB after
application installations.
If I configure disk 1 for OS and apps, disk 2 for log files and disks 3,4
and 5 for data I would have 219GB available for data with no redundancy, if
I add RAID 5 across those 3 disks I would get 146GB useable space,
dramatically less than the 292GB from the other config.
A third option is to create a 10GB (or 20GB) partition on disk 1, mirror
that to disk 2 and configure all 5 disks in a RAID 5 configuration. The
problem with this is that I can only use 63GB of each disk, giving me 315GB
minus parity drive, for a total useable size of 252GB. However, I don't
think this configuration would give me much of a performance gain in SQL.
So my questions are this : what would you recommend, taking into account
that this machine will be running W2k3, SQL 2000 and SharePoint 2003. The
server will have about 50GB of local data *eventually*, and will be indexing
around 150GB of data residing on a NAS device.
Also, I have heard that SharePoint doesn't really tax SQL that bad and that
I'm over-analyzing the problem. Just set it up as one big RAID 5 and be
done with it. Thought?
Sorry for the long post.Every case is unique per the Budget , Scope, SLAs etc... to come up with teh
right configuration.
It also depends upon the Access Patterns, Number of Data files and groups,
Index and TempDB Placement.
I would recommend that you start at the MSFT Operations guide at
http://www.microsoft.com/technet/tr...ide/default.asp
And look at Chapter 6, which talks about Capacity Planning.
HTH
Satish Balusa
Corillian Corp.
"cyberpunk" <blah@.anon.com> wrote in message
news:eONEAKr3DHA.1404@.TK2MSFTNGP11.phx.gbl...
quote:
> I have been tasked with setting up a brand new PowerEdge 2650 for
> SharePoint, SQL 2000 and Windows 2003. This server has 5 physical disks,
> all 73GB, 2GB of RAM and a 3.06Ghz Xeon processor.
> My question is in regards to the best possible RAID setup for SQL
> performance as well as retaining as much disk space as possible.
> If I read the docs correctly SQL is best suited to have the tempdb,
> application files and log files all on separate physical disks.
> I cannot find a way to do this without losing a lot of my available disk
> space.
> The way I see it, as well as my SQL admin, there are 3 factors to
consider.
quote:
> 1. Speed (performance), 2. Reliability 3. Capacity.
> If I configure everything in one big RAID 5 array I would have excellent
> reliability, and have (73x4) 292GB free for data, probably 270GB after
> application installations.
> If I configure disk 1 for OS and apps, disk 2 for log files and disks 3,4
> and 5 for data I would have 219GB available for data with no redundancy,
if
quote:
> I add RAID 5 across those 3 disks I would get 146GB useable space,
> dramatically less than the 292GB from the other config.
> A third option is to create a 10GB (or 20GB) partition on disk 1, mirror
> that to disk 2 and configure all 5 disks in a RAID 5 configuration. The
> problem with this is that I can only use 63GB of each disk, giving me
315GB
quote:
> minus parity drive, for a total useable size of 252GB. However, I don't
> think this configuration would give me much of a performance gain in SQL.
> So my questions are this : what would you recommend, taking into account
> that this machine will be running W2k3, SQL 2000 and SharePoint 2003. The
> server will have about 50GB of local data *eventually*, and will be
indexing
quote:
> around 150GB of data residing on a NAS device.
> Also, I have heard that SharePoint doesn't really tax SQL that bad and
that
quote:
> I'm over-analyzing the problem. Just set it up as one big RAID 5 and be
> done with it. Thought?
> Sorry for the long post.
>
Friday, February 24, 2012
Help me please! (production DB marked as suspicious)
Hi everybody,
DBMS - MS SQL 6.5
I have removed physical files: Name.DAT and NameLog.DAT
from folder and put older files there with the same names.
After restarting the engine has marked my DB as suspicious.
Then I moved previous files back - database still suspitious!!!
I have no idea how to restore data base status after removing phisical files?
I need help as my MS SQL 6.5 marked as suspicious my production
database.If you can replace the DB by this way,why MS give some backup,restore function?|||If you don't have a valid backup and all you have is the .Dat and .Log files. Try attaching those rather than just replacing the old files. Otherwise have a look at sp_resetstatus.
Cheers|||Sorry, I just noticed that you are on version 6.5. I'm not sure if the info will be valid for that version, you will have to try it to find out.
Thanks,
Originally posted by bmalar
If you don't have a valid backup and all you have is the .Dat and .Log files. Try attaching those rather than just replacing the old files. Otherwise have a look at sp_resetstatus.
Cheers|||True you should use SP_RESETSTATUS, before that use:
sp_configure 'allow updates',1
go
reconfigure with override
go
After the procedure is created, immediately disable updates to the system tables.
sp_configure 'allow updates',0
go
reconfigure with override
go
... then use SP_RESETSTATUS and reboot the server.
DBMS - MS SQL 6.5
I have removed physical files: Name.DAT and NameLog.DAT
from folder and put older files there with the same names.
After restarting the engine has marked my DB as suspicious.
Then I moved previous files back - database still suspitious!!!
I have no idea how to restore data base status after removing phisical files?
I need help as my MS SQL 6.5 marked as suspicious my production
database.If you can replace the DB by this way,why MS give some backup,restore function?|||If you don't have a valid backup and all you have is the .Dat and .Log files. Try attaching those rather than just replacing the old files. Otherwise have a look at sp_resetstatus.
Cheers|||Sorry, I just noticed that you are on version 6.5. I'm not sure if the info will be valid for that version, you will have to try it to find out.
Thanks,
Originally posted by bmalar
If you don't have a valid backup and all you have is the .Dat and .Log files. Try attaching those rather than just replacing the old files. Otherwise have a look at sp_resetstatus.
Cheers|||True you should use SP_RESETSTATUS, before that use:
sp_configure 'allow updates',1
go
reconfigure with override
go
After the procedure is created, immediately disable updates to the system tables.
sp_configure 'allow updates',0
go
reconfigure with override
go
... then use SP_RESETSTATUS and reboot the server.
Subscribe to:
Posts (Atom)