We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
server used for JDE application. Database size is 100 Gig. This DB is
configured for replication (only 25 tables). This database is also
configured for Log ship to a stand by server where we run our reports.
When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
file start growing crazy, which makes replication and log ship to break.
Our concern is how can we avoid growing log file while DBREINDEX or
INDEXDEFRAG running?
Your response is appreciated.
Thanks,
AbbasBackup or truncate the transaction log frequently during
the processes is the only thing I can think of. Or set
the Database Recovery model to Simple. Any other ideas?
>--Original Message--
>We have SQL 2K Enterprise Edition with SP3. Server is
dedicated database
>server used for JDE application. Database size is 100
Gig. This DB is
>configured for replication (only 25 tables). This
database is also
>configured for Log ship to a stand by server where we run
our reports.
>When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this
database, the log
>file start growing crazy, which makes replication and log
ship to break.
>Our concern is how can we avoid growing log file while
DBREINDEX or
>INDEXDEFRAG running?
>Your response is appreciated.
>Thanks,
>Abbas
>
>.
>|||*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You can't. These actions, like all in the server are logged. These type of
actions will send a lot of data to the log files. I suggest doing them in
small batches so the logs can recover in between the reindexing. If done
often or there is little fragmentation INDEXDEFRAGmay produce less log
entries.
--
Andrew J. Kelly
SQL Server MVP
"Moh Abb" <mabbas@.aligntech.com> wrote in message
news:%23kkvKL%23cDHA.1828@.TK2MSFTNGP10.phx.gbl...
> We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
> server used for JDE application. Database size is 100 Gig. This DB is
> configured for replication (only 25 tables). This database is also
> configured for Log ship to a stand by server where we run our reports.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
> Your response is appreciated.
> Thanks,
> Abbas
>
>|||Hello
You can't avoid this, but you can accommodate yourself to this :-)
I'm using the system of two connected jobs. One of them runs
DBCC INDEXDEFRAG and second periodically (every minute)
checks log state and stops first job when log have more than 70%
of space filled. And the system waits for the next log backup and
starts again. In general controlling job can start log backup instead
of stopping defragmentation.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
Serge Shakhovsql
Showing posts with label enterprise. Show all posts
Showing posts with label enterprise. Show all posts
Friday, March 30, 2012
log in failed for user [servername]\ASPNET
can anyone help me with this error message, i follow the instruction of someone in other forum, but i cant find where the SQL Server Enterprise manager is, i am using MSDE.One way To see SQL Server Enterprise manager is to install SQL server :)|||If you don't have Enterprise Manage, you can use the command-line utility OSQL that comes with SQL Server.
You will have a 1> prompt.
Go to the command prompt and type in:
osql -S [servername]\instancename -E -q
You will have a 1> prompt.
use yourdatabasename
goEXEC sp_grantlogin '[servername]\ASPNET'
goEXEC sp_grantdbaccess '[servername]\ASPNET''
go
You can also read this post by David Wier:Error Using Trusted Connection in DB Connection String. I don't recommend adding the ASPNET account as a db_owner, however, so I'd leave off that command at the end.
Terri
Wednesday, March 28, 2012
Log free space question
I have tried all through enterprise manager and dbcc
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads
>--Original Message--
>Hi
>I am using sql server 7.0 and one of DB is relatively
>small. The size is about 2 MB however the log size is
>about 30GB the problems is the log used space is 38 MB
>but the log free space is about 37 GB so the whole
backup
>and restore takes some time as the toal og size is the
>sum of these two.
>I tried to shrink the file with truncate_only option but
>cant get it to a small size
>Can anyone help
>.
>Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...[vbcol=seagreen]
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
> backup|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.
>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
the[vbcol=seagreen]
but[vbcol=seagreen]
>
>.
>|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
>
> checked to see if you
> have Truncate Log
> the
> but
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads
>--Original Message--
>Hi
>I am using sql server 7.0 and one of DB is relatively
>small. The size is about 2 MB however the log size is
>about 30GB the problems is the log used space is 38 MB
>but the log free space is about 37 GB so the whole
backup
>and restore takes some time as the toal og size is the
>sum of these two.
>I tried to shrink the file with truncate_only option but
>cant get it to a small size
>Can anyone help
>.
>Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...[vbcol=seagreen]
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
> backup|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.
>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
the[vbcol=seagreen]
but[vbcol=seagreen]
>
>.
>|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
>
> checked to see if you
> have Truncate Log
> the
> but
Log free space question
I have tried all through enterprise manager and dbcc
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads
>--Original Message--
>Hi
>I am using sql server 7.0 and one of DB is relatively
>small. The size is about 2 MB however the log size is
>about 30GB the problems is the log used space is 38 MB
>but the log free space is about 37 GB so the whole
backup
>and restore takes some time as the toal og size is the
>sum of these two.
>I tried to shrink the file with truncate_only option but
>cant get it to a small size
>Can anyone help
>.
>
Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...[vbcol=seagreen]
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
> backup
|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.
>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log[vbcol=seagreen]
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
the[vbcol=seagreen]
but
>
>.
>
|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
> checked to see if you
> have Truncate Log
> the
> but
sql
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads
>--Original Message--
>Hi
>I am using sql server 7.0 and one of DB is relatively
>small. The size is about 2 MB however the log size is
>about 30GB the problems is the log used space is 38 MB
>but the log free space is about 37 GB so the whole
backup
>and restore takes some time as the toal og size is the
>sum of these two.
>I tried to shrink the file with truncate_only option but
>cant get it to a small size
>Can anyone help
>.
>
Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...[vbcol=seagreen]
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
> backup
|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.
>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log[vbcol=seagreen]
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
the[vbcol=seagreen]
but
>
>.
>
|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...[vbcol=seagreen]
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
> checked to see if you
> have Truncate Log
> the
> but
sql
Friday, March 23, 2012
Log file size gets reduced
Hi,
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size a
s
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Check if the autoshrink database option is set and also of you have any main
t job or similar that
perform shrink operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB fro
m
> Enterprise Manager. I check it in Enterprise manager and it shows the size
as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>sql
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size a
s
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Check if the autoshrink database option is set and also of you have any main
t job or similar that
perform shrink operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB fro
m
> Enterprise Manager. I check it in Enterprise manager and it shows the size
as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>sql
Log file size gets reduced
Hi,
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size as
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Check if the autoshrink database option is set and also of you have any maint job or similar that
perform shrink operations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB from
> Enterprise Manager. I check it in Enterprise manager and it shows the size as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>> Hi,
>> I increased the total log file size of a database from 30 MB to 300 MB
>> from
>> Enterprise Manager. I check it in Enterprise manager and it shows the
>> size as
>> 300 MB. However, every time I do that it gets reduced to 30 MB after some
>> time. What am I doing wrong?
>
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size as
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Check if the autoshrink database option is set and also of you have any maint job or similar that
perform shrink operations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB from
> Enterprise Manager. I check it in Enterprise manager and it shows the size as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>> Hi,
>> I increased the total log file size of a database from 30 MB to 300 MB
>> from
>> Enterprise Manager. I check it in Enterprise manager and it shows the
>> size as
>> 300 MB. However, every time I do that it gets reduced to 30 MB after some
>> time. What am I doing wrong?
>
Log file size gets reduced
Hi,
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size as
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?
Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?
|||Check if the autoshrink database option is set and also of you have any maint job or similar that
perform shrink operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB from
> Enterprise Manager. I check it in Enterprise manager and it shows the size as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?
|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>
I increased the total log file size of a database from 30 MB to 300 MB from
Enterprise Manager. I check it in Enterprise manager and it shows the size as
300 MB. However, every time I do that it gets reduced to 30 MB after some
time. What am I doing wrong?
Hi,
You should check your maintenance plan. Maybe you set "remove unused space"
from logfile.
Regards,
Janos
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB
> from
> Enterprise Manager. I check it in Enterprise manager and it shows the size
> as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?
|||Check if the autoshrink database option is set and also of you have any maint job or similar that
perform shrink operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
> Hi,
> I increased the total log file size of a database from 30 MB to 300 MB from
> Enterprise Manager. I check it in Enterprise manager and it shows the size as
> 300 MB. However, every time I do that it gets reduced to 30 MB after some
> time. What am I doing wrong?
|||Hello,
If it is a production server and if the option Tibor mentioned is enabled
then it is recommended to uncheck the option. This will degrade the
performance if the
file gets auto shunk and then grow automatically.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$Uxm4njHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Check if the autoshrink database option is set and also of you have any
> maint job or similar that perform shrink operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:CFF80442-D600-40E8-BCEE-3AF516192E34@.microsoft.com...
>
Wednesday, March 21, 2012
Log File Partition
I am innstalling SQL 2000 Enterprise on Windows 2000 server. I would like
to setup a separate partition for the log file. I would like to know how to
determine the partition size of the log file. Is it acceptable to setup
the partition size of the log file the same partition size as the the
operating system?
Thanks.Diane
A lot of that really depends on your DB. Do you expect a lot of transaction
? How large is the DB going to be? How large is your OS Partition?
I usually have an 8 gb OS
and if my Data Partition is around 100 GB
Then I put my Log Partition around 25 GB.
But that is my rule of thumb. If you have a lot of transactions that could
be large then you need to adjust accordingly.
Jeff|||Thanks for your quick response.
The SQL database will be for the Help Desk and inventory software. We are
going to implement this new software in May. I expect the database to be
10GB by the end of December. I don't expect the database to grow more than
20GB per year. I expect a lot of transaction. My OS partition is 10GB.
Do you install SQL on on OS partition or do you have a separate partition
for SQL?
Please let me know if you need additional information.
Thanks.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:02034A00-FCD4-4C27-A7CA-DA1ABAA91571@.microsoft.com...
> Diane
> A lot of that really depends on your DB. Do you expect a lot of
transaction? How large is the DB going to be? How large is your OS
Partition?
> I usually have an 8 gb OS
> and if my Data Partition is around 100 GB
> Then I put my Log Partition around 25 GB.
> But that is my rule of thumb. If you have a lot of transactions that
could be large then you need to adjust accordingly.
> Jeff|||Diane,
The standard 3 drive setup is to put the operating system on the C drive, th
e application(SQL Server in this case) on the D drive and the data on the E
drive, in your case the .mdf files would be on E and your .ldf files would b
e on another drive( F for e
xample).
Ed|||Looks like what you recommend was to setup 4 partitions. I only have two
drives (72.8 GB each) and setup RAID 1.
My OS partition is 10GB. Would you setup the partition for the log file the
same size as your OS partition? In this case, it is 10 GB. Is there a
rule of thumb on how to setup the log file partition?
Thank you.
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eFvkrylJEHA.3628@.TK2MSFTNGP12.phx.gbl...
> I don't quite understand the reasons for the "standard 3 drive setup"? can
> someone explain it a bit more for me? (who came up with such "standard"?)
> if c is for OS
> d is for sql excutables
> e is for data, then my questions are:
> 1. in sql 2k, even specify program files being installed on d, there are
> some files still being installed on c:\program files\mssql
Yep. Most are system databases which are in simple mode anyway. The only
things you might want to move are the tempdb devices. Once SQL is up and
running you can change the default log and data file locations.
> 2. if all data go to e (both mdf, ldf i assume), is that considered a good
> performance (I/O) and fault tolerence (if the d drive gose bad, what
happen
> to trans log?) strategy?
Again, the system databases with the exception of tempdb are now volume and
simple recovery only. They are also fairly small and can be backed up
daily.
> at my previous company, the set up is
> C is for os and sql excutables
> D is for logs
> and F is for data and backup files.
> c and d are one partitioned mirror drive. (for fault tolerance)
> F drive is raid 5.
Ooh, lousy performance AND all the eggs in one basket. Worst practices run
rampant.
> can someone tell me if the "standard 3 drive setup" is better than this
set
> up? and why?
>
Three drives are for fault tolerance and performance. All transactions are
held up until the log entry is physically committed to disk. Also, log
files are sequential writes and database files tend to be random writes.
Disk head movement optimization algorithms can sometimes cause log writes to
be delayed if data is on the same partition.
The biggest reason to separate them is recovery. As long as you have a
decent backup rotation and FULL recovery for the user databases, a three
drive system will always allow you to recover all transactions up to the
last moment before a hardware failure if it is a single partition failure.
> Steve
>
> "Ed" <anonymous@.discussions.microsoft.com> wrote in message
> news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
drive,[vbcol=seagreen]
> the application(SQL Server in this case) on the D drive and the data on
the
> E drive, in your case the .mdf files would be on E and your .ldf files
would
> be on another drive( F for example).
>
>|||Steve
Most sites (and most people) have different views on the perfect disk set up
.
The way we do it.
'C' Operating system (NT4 or Windows 2000/2003)
'D' Install SQL Server, System databases, User databases primary filegroups
containing system tables only
'E' User databases user tables
'F' User databases transaction logs
'G' Backups
'H' Application Data
D, E and F are usually RAID 1 + 0 all other drives are mirrored.
We find this gives us good performance and good resilience.
Regards
Johnsql
to setup a separate partition for the log file. I would like to know how to
determine the partition size of the log file. Is it acceptable to setup
the partition size of the log file the same partition size as the the
operating system?
Thanks.Diane
A lot of that really depends on your DB. Do you expect a lot of transaction
? How large is the DB going to be? How large is your OS Partition?
I usually have an 8 gb OS
and if my Data Partition is around 100 GB
Then I put my Log Partition around 25 GB.
But that is my rule of thumb. If you have a lot of transactions that could
be large then you need to adjust accordingly.
Jeff|||Thanks for your quick response.
The SQL database will be for the Help Desk and inventory software. We are
going to implement this new software in May. I expect the database to be
10GB by the end of December. I don't expect the database to grow more than
20GB per year. I expect a lot of transaction. My OS partition is 10GB.
Do you install SQL on on OS partition or do you have a separate partition
for SQL?
Please let me know if you need additional information.
Thanks.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:02034A00-FCD4-4C27-A7CA-DA1ABAA91571@.microsoft.com...
> Diane
> A lot of that really depends on your DB. Do you expect a lot of
transaction? How large is the DB going to be? How large is your OS
Partition?
> I usually have an 8 gb OS
> and if my Data Partition is around 100 GB
> Then I put my Log Partition around 25 GB.
> But that is my rule of thumb. If you have a lot of transactions that
could be large then you need to adjust accordingly.
> Jeff|||Diane,
The standard 3 drive setup is to put the operating system on the C drive, th
e application(SQL Server in this case) on the D drive and the data on the E
drive, in your case the .mdf files would be on E and your .ldf files would b
e on another drive( F for e
xample).
Ed|||Looks like what you recommend was to setup 4 partitions. I only have two
drives (72.8 GB each) and setup RAID 1.
My OS partition is 10GB. Would you setup the partition for the log file the
same size as your OS partition? In this case, it is 10 GB. Is there a
rule of thumb on how to setup the log file partition?
Thank you.
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eFvkrylJEHA.3628@.TK2MSFTNGP12.phx.gbl...
> I don't quite understand the reasons for the "standard 3 drive setup"? can
> someone explain it a bit more for me? (who came up with such "standard"?)
> if c is for OS
> d is for sql excutables
> e is for data, then my questions are:
> 1. in sql 2k, even specify program files being installed on d, there are
> some files still being installed on c:\program files\mssql
Yep. Most are system databases which are in simple mode anyway. The only
things you might want to move are the tempdb devices. Once SQL is up and
running you can change the default log and data file locations.
> 2. if all data go to e (both mdf, ldf i assume), is that considered a good
> performance (I/O) and fault tolerence (if the d drive gose bad, what
happen
> to trans log?) strategy?
Again, the system databases with the exception of tempdb are now volume and
simple recovery only. They are also fairly small and can be backed up
daily.
> at my previous company, the set up is
> C is for os and sql excutables
> D is for logs
> and F is for data and backup files.
> c and d are one partitioned mirror drive. (for fault tolerance)
> F drive is raid 5.
Ooh, lousy performance AND all the eggs in one basket. Worst practices run
rampant.
> can someone tell me if the "standard 3 drive setup" is better than this
set
> up? and why?
>
Three drives are for fault tolerance and performance. All transactions are
held up until the log entry is physically committed to disk. Also, log
files are sequential writes and database files tend to be random writes.
Disk head movement optimization algorithms can sometimes cause log writes to
be delayed if data is on the same partition.
The biggest reason to separate them is recovery. As long as you have a
decent backup rotation and FULL recovery for the user databases, a three
drive system will always allow you to recover all transactions up to the
last moment before a hardware failure if it is a single partition failure.
> Steve
>
> "Ed" <anonymous@.discussions.microsoft.com> wrote in message
> news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
drive,[vbcol=seagreen]
> the application(SQL Server in this case) on the D drive and the data on
the
> E drive, in your case the .mdf files would be on E and your .ldf files
would
> be on another drive( F for example).
>
>|||Steve
Most sites (and most people) have different views on the perfect disk set up
.
The way we do it.
'C' Operating system (NT4 or Windows 2000/2003)
'D' Install SQL Server, System databases, User databases primary filegroups
containing system tables only
'E' User databases user tables
'F' User databases transaction logs
'G' Backups
'H' Application Data
D, E and F are usually RAID 1 + 0 all other drives are mirrored.
We find this gives us good performance and good resilience.
Regards
Johnsql
Log File Partition
I am innstalling SQL 2000 Enterprise on Windows 2000 server. I would like
to setup a separate partition for the log file. I would like to know how to
determine the partition size of the log file. Is it acceptable to setup
the partition size of the log file the same partition size as the the
operating system?
Thanks.Thanks for your quick response.
The SQL database will be for the Help Desk and inventory software. We are
going to implement this new software in May. I expect the database to be
10GB by the end of December. I don't expect the database to grow more than
20GB per year. I expect a lot of transaction. My OS partition is 10GB.
Do you install SQL on on OS partition or do you have a separate partition
for SQL?
Please let me know if you need additional information.
Thanks.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:02034A00-FCD4-4C27-A7CA-DA1ABAA91571@.microsoft.com...
> Diane
> A lot of that really depends on your DB. Do you expect a lot of
transaction? How large is the DB going to be? How large is your OS
Partition?
> I usually have an 8 gb OS
> and if my Data Partition is around 100 GB
> Then I put my Log Partition around 25 GB.
> But that is my rule of thumb. If you have a lot of transactions that
could be large then you need to adjust accordingly.
> Jeff|||Diane
The standard 3 drive setup is to put the operating system on the C drive, the application(SQL Server in this case) on the D drive and the data on the E drive, in your case the .mdf files would be on E and your .ldf files would be on another drive( F for example).
Ed|||Looks like what you recommend was to setup 4 partitions. I only have two
drives (72.8 GB each) and setup RAID 1.
My OS partition is 10GB. Would you setup the partition for the log file the
same size as your OS partition? In this case, it is 10 GB. Is there a
rule of thumb on how to setup the log file partition?
Thank you.
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed
to setup a separate partition for the log file. I would like to know how to
determine the partition size of the log file. Is it acceptable to setup
the partition size of the log file the same partition size as the the
operating system?
Thanks.Thanks for your quick response.
The SQL database will be for the Help Desk and inventory software. We are
going to implement this new software in May. I expect the database to be
10GB by the end of December. I don't expect the database to grow more than
20GB per year. I expect a lot of transaction. My OS partition is 10GB.
Do you install SQL on on OS partition or do you have a separate partition
for SQL?
Please let me know if you need additional information.
Thanks.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:02034A00-FCD4-4C27-A7CA-DA1ABAA91571@.microsoft.com...
> Diane
> A lot of that really depends on your DB. Do you expect a lot of
transaction? How large is the DB going to be? How large is your OS
Partition?
> I usually have an 8 gb OS
> and if my Data Partition is around 100 GB
> Then I put my Log Partition around 25 GB.
> But that is my rule of thumb. If you have a lot of transactions that
could be large then you need to adjust accordingly.
> Jeff|||Diane
The standard 3 drive setup is to put the operating system on the C drive, the application(SQL Server in this case) on the D drive and the data on the E drive, in your case the .mdf files would be on E and your .ldf files would be on another drive( F for example).
Ed|||Looks like what you recommend was to setup 4 partitions. I only have two
drives (72.8 GB each) and setup RAID 1.
My OS partition is 10GB. Would you setup the partition for the log file the
same size as your OS partition? In this case, it is 10 GB. Is there a
rule of thumb on how to setup the log file partition?
Thank you.
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed
Log File Partition
I am innstalling SQL 2000 Enterprise on Windows 2000 server. I would like
to setup a separate partition for the log file. I would like to know how to
determine the partition size of the log file. Is it acceptable to setup
the partition size of the log file the same partition size as the the
operating system?
Thanks.
Diane
A lot of that really depends on your DB. Do you expect a lot of transaction? How large is the DB going to be? How large is your OS Partition?
I usually have an 8 gb OS
and if my Data Partition is around 100 GB
Then I put my Log Partition around 25 GB.
But that is my rule of thumb. If you have a lot of transactions that could be large then you need to adjust accordingly.
Jeff
|||Thanks for your quick response.
The SQL database will be for the Help Desk and inventory software. We are
going to implement this new software in May. I expect the database to be
10GB by the end of December. I don't expect the database to grow more than
20GB per year. I expect a lot of transaction. My OS partition is 10GB.
Do you install SQL on on OS partition or do you have a separate partition
for SQL?
Please let me know if you need additional information.
Thanks.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:02034A00-FCD4-4C27-A7CA-DA1ABAA91571@.microsoft.com...
> Diane
> A lot of that really depends on your DB. Do you expect a lot of
transaction? How large is the DB going to be? How large is your OS
Partition?
> I usually have an 8 gb OS
> and if my Data Partition is around 100 GB
> Then I put my Log Partition around 25 GB.
> But that is my rule of thumb. If you have a lot of transactions that
could be large then you need to adjust accordingly.
> Jeff
|||Diane,
The standard 3 drive setup is to put the operating system on the C drive, the application(SQL Server in this case) on the D drive and the data on the E drive, in your case the .mdf files would be on E and your .ldf files would be on another drive( F for e
xample).
Ed
|||Looks like what you recommend was to setup 4 partitions. I only have two
drives (72.8 GB each) and setup RAID 1.
My OS partition is 10GB. Would you setup the partition for the log file the
same size as your OS partition? In this case, it is 10 GB. Is there a
rule of thumb on how to setup the log file partition?
Thank you.
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed
|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed
|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed
|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eFvkrylJEHA.3628@.TK2MSFTNGP12.phx.gbl...
> I don't quite understand the reasons for the "standard 3 drive setup"? can
> someone explain it a bit more for me? (who came up with such "standard"?)
> if c is for OS
> d is for sql excutables
> e is for data, then my questions are:
> 1. in sql 2k, even specify program files being installed on d, there are
> some files still being installed on c:\program files\mssql
Yep. Most are system databases which are in simple mode anyway. The only
things you might want to move are the tempdb devices. Once SQL is up and
running you can change the default log and data file locations.
> 2. if all data go to e (both mdf, ldf i assume), is that considered a good
> performance (I/O) and fault tolerence (if the d drive gose bad, what
happen
> to trans log?) strategy?
Again, the system databases with the exception of tempdb are now volume and
simple recovery only. They are also fairly small and can be backed up
daily.
> at my previous company, the set up is
> C is for os and sql excutables
> D is for logs
> and F is for data and backup files.
> c and d are one partitioned mirror drive. (for fault tolerance)
> F drive is raid 5.
Ooh, lousy performance AND all the eggs in one basket. Worst practices run
rampant.
> can someone tell me if the "standard 3 drive setup" is better than this
set
> up? and why?
>
Three drives are for fault tolerance and performance. All transactions are
held up until the log entry is physically committed to disk. Also, log
files are sequential writes and database files tend to be random writes.
Disk head movement optimization algorithms can sometimes cause log writes to
be delayed if data is on the same partition.
The biggest reason to separate them is recovery. As long as you have a
decent backup rotation and FULL recovery for the user databases, a three
drive system will always allow you to recover all transactions up to the
last moment before a hardware failure if it is a single partition failure.
[vbcol=seagreen]
> Steve
>
> "Ed" <anonymous@.discussions.microsoft.com> wrote in message
> news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
drive,
> the application(SQL Server in this case) on the D drive and the data on
the
> E drive, in your case the .mdf files would be on E and your .ldf files
would
> be on another drive( F for example).
>
>
|||Steve
Most sites (and most people) have different views on the perfect disk set up.
The way we do it.
'C' Operating system (NT4 or Windows 2000/2003)
'D' Install SQL Server, System databases, User databases primary filegroups containing system tables only
'E' User databases user tables
'F' User databases transaction logs
'G' Backups
'H' Application Data
D, E and F are usually RAID 1 + 0 all other drives are mirrored.
We find this gives us good performance and good resilience.
Regards
John
to setup a separate partition for the log file. I would like to know how to
determine the partition size of the log file. Is it acceptable to setup
the partition size of the log file the same partition size as the the
operating system?
Thanks.
Diane
A lot of that really depends on your DB. Do you expect a lot of transaction? How large is the DB going to be? How large is your OS Partition?
I usually have an 8 gb OS
and if my Data Partition is around 100 GB
Then I put my Log Partition around 25 GB.
But that is my rule of thumb. If you have a lot of transactions that could be large then you need to adjust accordingly.
Jeff
|||Thanks for your quick response.
The SQL database will be for the Help Desk and inventory software. We are
going to implement this new software in May. I expect the database to be
10GB by the end of December. I don't expect the database to grow more than
20GB per year. I expect a lot of transaction. My OS partition is 10GB.
Do you install SQL on on OS partition or do you have a separate partition
for SQL?
Please let me know if you need additional information.
Thanks.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:02034A00-FCD4-4C27-A7CA-DA1ABAA91571@.microsoft.com...
> Diane
> A lot of that really depends on your DB. Do you expect a lot of
transaction? How large is the DB going to be? How large is your OS
Partition?
> I usually have an 8 gb OS
> and if my Data Partition is around 100 GB
> Then I put my Log Partition around 25 GB.
> But that is my rule of thumb. If you have a lot of transactions that
could be large then you need to adjust accordingly.
> Jeff
|||Diane,
The standard 3 drive setup is to put the operating system on the C drive, the application(SQL Server in this case) on the D drive and the data on the E drive, in your case the .mdf files would be on E and your .ldf files would be on another drive( F for e
xample).
Ed
|||Looks like what you recommend was to setup 4 partitions. I only have two
drives (72.8 GB each) and setup RAID 1.
My OS partition is 10GB. Would you setup the partition for the log file the
same size as your OS partition? In this case, it is 10 GB. Is there a
rule of thumb on how to setup the log file partition?
Thank you.
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed
|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed
|||I don't quite understand the reasons for the "standard 3 drive setup"? can
someone explain it a bit more for me? (who came up with such "standard"?)
if c is for OS
d is for sql excutables
e is for data, then my questions are:
1. in sql 2k, even specify program files being installed on d, there are
some files still being installed on c:\program files\mssql
2. if all data go to e (both mdf, ldf i assume), is that considered a good
performance (I/O) and fault tolerence (if the d drive gose bad, what happen
to trans log?) strategy?
at my previous company, the set up is
C is for os and sql excutables
D is for logs
and F is for data and backup files.
c and d are one partitioned mirror drive. (for fault tolerance)
F drive is raid 5.
can someone tell me if the "standard 3 drive setup" is better than this set
up? and why?
Steve
"Ed" <anonymous@.discussions.microsoft.com> wrote in message
news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
> Diane,
> The standard 3 drive setup is to put the operating system on the C drive,
the application(SQL Server in this case) on the D drive and the data on the
E drive, in your case the .mdf files would be on E and your .ldf files would
be on another drive( F for example).
> Ed
|||Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:eFvkrylJEHA.3628@.TK2MSFTNGP12.phx.gbl...
> I don't quite understand the reasons for the "standard 3 drive setup"? can
> someone explain it a bit more for me? (who came up with such "standard"?)
> if c is for OS
> d is for sql excutables
> e is for data, then my questions are:
> 1. in sql 2k, even specify program files being installed on d, there are
> some files still being installed on c:\program files\mssql
Yep. Most are system databases which are in simple mode anyway. The only
things you might want to move are the tempdb devices. Once SQL is up and
running you can change the default log and data file locations.
> 2. if all data go to e (both mdf, ldf i assume), is that considered a good
> performance (I/O) and fault tolerence (if the d drive gose bad, what
happen
> to trans log?) strategy?
Again, the system databases with the exception of tempdb are now volume and
simple recovery only. They are also fairly small and can be backed up
daily.
> at my previous company, the set up is
> C is for os and sql excutables
> D is for logs
> and F is for data and backup files.
> c and d are one partitioned mirror drive. (for fault tolerance)
> F drive is raid 5.
Ooh, lousy performance AND all the eggs in one basket. Worst practices run
rampant.
> can someone tell me if the "standard 3 drive setup" is better than this
set
> up? and why?
>
Three drives are for fault tolerance and performance. All transactions are
held up until the log entry is physically committed to disk. Also, log
files are sequential writes and database files tend to be random writes.
Disk head movement optimization algorithms can sometimes cause log writes to
be delayed if data is on the same partition.
The biggest reason to separate them is recovery. As long as you have a
decent backup rotation and FULL recovery for the user databases, a three
drive system will always allow you to recover all transactions up to the
last moment before a hardware failure if it is a single partition failure.
[vbcol=seagreen]
> Steve
>
> "Ed" <anonymous@.discussions.microsoft.com> wrote in message
> news:D3CAE674-8BFF-4CB0-A920-17C91D4A2093@.microsoft.com...
drive,
> the application(SQL Server in this case) on the D drive and the data on
the
> E drive, in your case the .mdf files would be on E and your .ldf files
would
> be on another drive( F for example).
>
>
|||Steve
Most sites (and most people) have different views on the perfect disk set up.
The way we do it.
'C' Operating system (NT4 or Windows 2000/2003)
'D' Install SQL Server, System databases, User databases primary filegroups containing system tables only
'E' User databases user tables
'F' User databases transaction logs
'G' Backups
'H' Application Data
D, E and F are usually RAID 1 + 0 all other drives are mirrored.
We find this gives us good performance and good resilience.
Regards
John
Friday, March 9, 2012
Log File
We are running SQL 2000 Enterprise Edition. How do I setup the log file to
go to a different partition? Thanks.
Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>
|||Hi,
Login to SQL server using Query Analyzer:-
sp_detach_db <dbname>
-- this will detach the database from sql server, so move the LDF file to
new location using windows explorer. After this execute
sp_atatch_db <dbname>,'MDF_file_wth_path','LDF_file_with_NEW_pa th'
Thanks
Hari
MCDBA
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>
|||Thank you. I'll give it a try.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23C0$0vIZEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> Login to SQL server using Query Analyzer:-
> sp_detach_db <dbname>
> -- this will detach the database from sql server, so move the LDF file to
> new location using windows explorer. After this execute
> sp_atatch_db <dbname>,'MDF_file_wth_path','LDF_file_with_NEW_pa th'
>
> Thanks
> Hari
> MCDBA
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> to
>
|||Thank you. I'll give it a try.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGWEeCIZEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> to
>
go to a different partition? Thanks.
Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>
|||Hi,
Login to SQL server using Query Analyzer:-
sp_detach_db <dbname>
-- this will detach the database from sql server, so move the LDF file to
new location using windows explorer. After this execute
sp_atatch_db <dbname>,'MDF_file_wth_path','LDF_file_with_NEW_pa th'
Thanks
Hari
MCDBA
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>
|||Thank you. I'll give it a try.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23C0$0vIZEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> Login to SQL server using Query Analyzer:-
> sp_detach_db <dbname>
> -- this will detach the database from sql server, so move the LDF file to
> new location using windows explorer. After this execute
> sp_atatch_db <dbname>,'MDF_file_wth_path','LDF_file_with_NEW_pa th'
>
> Thanks
> Hari
> MCDBA
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> to
>
|||Thank you. I'll give it a try.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGWEeCIZEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> to
>
Log File
We are running SQL 2000 Enterprise Edition. How do I setup the log file to
go to a different partition? Thanks.Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>|||Hi,
Login to SQL server using Query Analyzer:-
sp_detach_db <dbname>
-- this will detach the database from sql server, so move the LDF file to
new location using windows explorer. After this execute
sp_atatch_db <dbname>,'MDF_file_wth_path','LDF_file_with_NEW_path'
Thanks
Hari
MCDBA
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>|||Thank you. I'll give it a try.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23C0$0vIZEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> Login to SQL server using Query Analyzer:-
> sp_detach_db <dbname>
> -- this will detach the database from sql server, so move the LDF file to
> new location using windows explorer. After this execute
> sp_atatch_db <dbname>,'MDF_file_wth_path','LDF_file_with_NEW_path'
>
> Thanks
> Hari
> MCDBA
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > We are running SQL 2000 Enterprise Edition. How do I setup the log file
> to
> > go to a different partition? Thanks.
> >
> >
>|||Thank you. I'll give it a try.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGWEeCIZEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > We are running SQL 2000 Enterprise Edition. How do I setup the log file
> to
> > go to a different partition? Thanks.
> >
> >
>
go to a different partition? Thanks.Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>|||Hi,
Login to SQL server using Query Analyzer:-
sp_detach_db <dbname>
-- this will detach the database from sql server, so move the LDF file to
new location using windows explorer. After this execute
sp_atatch_db <dbname>,'MDF_file_wth_path','LDF_file_with_NEW_path'
Thanks
Hari
MCDBA
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>|||Thank you. I'll give it a try.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23C0$0vIZEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> Login to SQL server using Query Analyzer:-
> sp_detach_db <dbname>
> -- this will detach the database from sql server, so move the LDF file to
> new location using windows explorer. After this execute
> sp_atatch_db <dbname>,'MDF_file_wth_path','LDF_file_with_NEW_path'
>
> Thanks
> Hari
> MCDBA
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > We are running SQL 2000 Enterprise Edition. How do I setup the log file
> to
> > go to a different partition? Thanks.
> >
> >
>|||Thank you. I'll give it a try.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGWEeCIZEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > We are running SQL 2000 Enterprise Edition. How do I setup the log file
> to
> > go to a different partition? Thanks.
> >
> >
>
Log File
We are running SQL 2000 Enterprise Edition. How do I setup the log file to
go to a different partition? Thanks.Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>|||Hi,
Login to SQL server using Query Analyzer:-
sp_detach_db <dbname>
-- this will detach the database from sql server, so move the LDF file to
new location using windows explorer. After this execute
sp_atatch_db <dbname> ,'MDF_file_wth_path','LDF_file_with_NEW_
path'
Thanks
Hari
MCDBA
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>|||Thank you. I'll give it a try.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23C0$0vIZEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> Login to SQL server using Query Analyzer:-
> sp_detach_db <dbname>
> -- this will detach the database from sql server, so move the LDF file to
> new location using windows explorer. After this execute
> sp_atatch_db <dbname> ,'MDF_file_wth_path','LDF_file_with_NEW_
path'
>
> Thanks
> Hari
> MCDBA
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> to
>|||Thank you. I'll give it a try.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGWEeCIZEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> to
>
go to a different partition? Thanks.Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
Andrew J. Kelly SQL MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>|||Hi,
Login to SQL server using Query Analyzer:-
sp_detach_db <dbname>
-- this will detach the database from sql server, so move the LDF file to
new location using windows explorer. After this execute
sp_atatch_db <dbname> ,'MDF_file_wth_path','LDF_file_with_NEW_
path'
Thanks
Hari
MCDBA
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> We are running SQL 2000 Enterprise Edition. How do I setup the log file
to
> go to a different partition? Thanks.
>|||Thank you. I'll give it a try.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23C0$0vIZEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> Login to SQL server using Query Analyzer:-
> sp_detach_db <dbname>
> -- this will detach the database from sql server, so move the LDF file to
> new location using windows explorer. After this execute
> sp_atatch_db <dbname> ,'MDF_file_wth_path','LDF_file_with_NEW_
path'
>
> Thanks
> Hari
> MCDBA
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> to
>|||Thank you. I'll give it a try.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGWEeCIZEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Take a look at sp_attach_db and sp_detach_db in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ea$DRjGZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> to
>
Subscribe to:
Posts (Atom)