Hi,
I am getting a message that my transaction log is full. After looking at
the drive where it is located, I only have 8 megs left on that drive which
is pretty bad! I would like to do two things. Dump the log and then move
it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
Thanks in advance,
WillWill Dupleich,
Backup the transaction log and use "alter database ... modify file ..." to
move the log to a new drive.
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
AMB
"Will Dupleich" wrote:
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
>|||Hello,
1. Do the Trasnaction log backup
2. Detach the database [SP_DETACH_DB DBname>
3. Copy/Move the LDF file to new disk/drive
4. Attach the database with new drive letter to LDF file [SP_ATTACH_DB
dbname,'MDF name with path','Ldf name with new path'
FYI, This steps require some downtime...
You can also do it ONLINE by creating a new LDF file in new drive using
ALTER DATABASE command or from SQL Server management studio.
Thanks
Hari
"Will Dupleich" <will@.getdynamic.com> wrote in message
news:Oghm22udHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
Showing posts with label drive. Show all posts
Showing posts with label drive. Show all posts
Monday, March 19, 2012
Dump log and move log to another drive
Hi,
I am getting a message that my transaction log is full. After looking at
the drive where it is located, I only have 8 megs left on that drive which
is pretty bad! I would like to do two things. Dump the log and then move
it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
Thanks in advance,
Will
Will Dupleich,
Backup the transaction log and use "alter database ... modify file ..." to
move the log to a new drive.
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
AMB
"Will Dupleich" wrote:
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
>
|||Hello,
1. Do the Trasnaction log backup
2. Detach the database [SP_DETACH_DB DBname>
3. Copy/Move the LDF file to new disk/drive
4. Attach the database with new drive letter to LDF file [SP_ATTACH_DB
dbname,'MDF name with path','Ldf name with new path'
FYI, This steps require some downtime...
You can also do it ONLINE by creating a new LDF file in new drive using
ALTER DATABASE command or from SQL Server management studio.
Thanks
Hari
"Will Dupleich" <will@.getdynamic.com> wrote in message
news:Oghm22udHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
I am getting a message that my transaction log is full. After looking at
the drive where it is located, I only have 8 megs left on that drive which
is pretty bad! I would like to do two things. Dump the log and then move
it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
Thanks in advance,
Will
Will Dupleich,
Backup the transaction log and use "alter database ... modify file ..." to
move the log to a new drive.
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
AMB
"Will Dupleich" wrote:
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
>
|||Hello,
1. Do the Trasnaction log backup
2. Detach the database [SP_DETACH_DB DBname>
3. Copy/Move the LDF file to new disk/drive
4. Attach the database with new drive letter to LDF file [SP_ATTACH_DB
dbname,'MDF name with path','Ldf name with new path'
FYI, This steps require some downtime...
You can also do it ONLINE by creating a new LDF file in new drive using
ALTER DATABASE command or from SQL Server management studio.
Thanks
Hari
"Will Dupleich" <will@.getdynamic.com> wrote in message
news:Oghm22udHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
Dump log and move log to another drive
Hi,
I am getting a message that my transaction log is full. After looking at
the drive where it is located, I only have 8 megs left on that drive which
is pretty bad! I would like to do two things. Dump the log and then move
it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
Thanks in advance,
WillWill Dupleich,
Backup the transaction log and use "alter database ... modify file ..." to
move the log to a new drive.
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
AMB
"Will Dupleich" wrote:
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
>|||Hello,
1. Do the Trasnaction log backup
2. Detach the database [SP_DETACH_DB DBname>
3. Copy/Move the LDF file to new disk/drive
4. Attach the database with new drive letter to LDF file [SP_ATTACH_DB
dbname,'MDF name with path','Ldf name with new path'
FYI, This steps require some downtime...
You can also do it ONLINE by creating a new LDF file in new drive using
ALTER DATABASE command or from SQL Server management studio.
Thanks
Hari
"Will Dupleich" <will@.getdynamic.com> wrote in message
news:Oghm22udHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
I am getting a message that my transaction log is full. After looking at
the drive where it is located, I only have 8 megs left on that drive which
is pretty bad! I would like to do two things. Dump the log and then move
it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
Thanks in advance,
WillWill Dupleich,
Backup the transaction log and use "alter database ... modify file ..." to
move the log to a new drive.
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
AMB
"Will Dupleich" wrote:
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
>|||Hello,
1. Do the Trasnaction log backup
2. Detach the database [SP_DETACH_DB DBname>
3. Copy/Move the LDF file to new disk/drive
4. Attach the database with new drive letter to LDF file [SP_ATTACH_DB
dbname,'MDF name with path','Ldf name with new path'
FYI, This steps require some downtime...
You can also do it ONLINE by creating a new LDF file in new drive using
ALTER DATABASE command or from SQL Server management studio.
Thanks
Hari
"Will Dupleich" <will@.getdynamic.com> wrote in message
news:Oghm22udHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
Sunday, March 11, 2012
Dual Transaction Log
I have just taken over an SQL Server 7.0 database which has two transaction
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
Nick
Hi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=re...t .com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
Nick
Hi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=re...t .com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>
Dual Transaction Log
I have just taken over an SQL Server 7.0 database which has two transaction
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
NickHi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=remove+log+file+in+sql+7&hl=en&lr=&ie=UTF-8&selm=us%24RwvUb%24GA.223%40cppssbbsa02.microsoft.com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
NickHi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=remove+log+file+in+sql+7&hl=en&lr=&ie=UTF-8&selm=us%24RwvUb%24GA.223%40cppssbbsa02.microsoft.com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>
Sunday, February 19, 2012
dts works but run same dts via sql job failed
hi all,
i posted this somewhere else but i think here is the right group.
i have a dts that shld write to a text file located at a mapped drive. i read somewhere that sql job does not recognise mapped drive, so i use UNC path in form \\128.1.1.1\dtsfile, which dtsfile folder is the shared folder name. i opened the folder permission to everyone.
thats abt the remote server. the sql server i am running is on windows NT, logon using Administrator to local. my sql is SQL 2000. i register the server using sa username. the server agent is start up using System account.
now the problem is when i run the dts interactively/manually, it runs succesfully. but when i run it thru SQL job it says "Access denied". Or full error is like this,
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
i really hope and appreciate if someone out there can help me out. thanks!
ps: pls let me know if more clues needed from my environment settings.The account that executes the scheduled job does not have the same permissions as you. You need the job to be run with an account with the necessary permissions (same as your perhaps) which also has a non-changing password.|||I had a similar problem which was caused by the account used by the sqlagent service. I changed it to a domain account with the appropriate privs, and the problem was resolved. Whey you exec the DTS job via the designer, it is using your credentials vs. the agent's credentials.|||thank u both for yr reply...
what do u mean by change it to domain account? i tried to start up the sql server agent using THIS ACCOUNT, which taking sql id that i created as sysadm and the name and password is the same username/password i log on to the server. the reason to do this to make the job using the same logon id as i run it thru dts designer. but now i am getting this error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 1326 (52E); Provider Error: 1326 (52E) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
i think my problem now, i am not sure when running the dts thru job, from where the job is taking username/password to connect to the remote server location. appreciate further help...thnks much.|||hi again...
i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.
how to check the SQL Server service account?
really need yr expertise here..thanks.|||hi again...
i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.
how to check the SQL Server service account?
really need yr expertise here..thanks.
start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.|||start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.
hi again...
i already changed the sql services to use logon username/password to be the same as sql agent. means both sql agent and sql services are using domain account now, no longer system account. but unfortunately, i am stil getting the same error...
anymore ideas can help me out here? please please...
tqvm.|||hi all again...
maybe i forgot to tell that my remote server i am connecting to is UNIX server (is that made a difference anyway?). well...just to test out, i just gave it a try to point to another remote server running on Windows. i shared the same folder and give sufficient privs while my sql server is stil using same domain account for sql agent as well as MSSQLSERVER services. then i run the job, it run successfully!
1.now i wld like to ask yr expertise here to guide me - how do we share a folder in UNIX to enable another server to connect to it? right now i just give permission read/write/execute to the folder. and it seems giving the above error - logon error/bad username or bad password.
2.how do we give permission to a server running on local? (not on network). the reason i asked is - normally its easy to give permission to some users on same network. is it by IP will work?
hope u all out there can help! thanks...really appreciate yr help.
i posted this somewhere else but i think here is the right group.
i have a dts that shld write to a text file located at a mapped drive. i read somewhere that sql job does not recognise mapped drive, so i use UNC path in form \\128.1.1.1\dtsfile, which dtsfile folder is the shared folder name. i opened the folder permission to everyone.
thats abt the remote server. the sql server i am running is on windows NT, logon using Administrator to local. my sql is SQL 2000. i register the server using sa username. the server agent is start up using System account.
now the problem is when i run the dts interactively/manually, it runs succesfully. but when i run it thru SQL job it says "Access denied". Or full error is like this,
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
i really hope and appreciate if someone out there can help me out. thanks!
ps: pls let me know if more clues needed from my environment settings.The account that executes the scheduled job does not have the same permissions as you. You need the job to be run with an account with the necessary permissions (same as your perhaps) which also has a non-changing password.|||I had a similar problem which was caused by the account used by the sqlagent service. I changed it to a domain account with the appropriate privs, and the problem was resolved. Whey you exec the DTS job via the designer, it is using your credentials vs. the agent's credentials.|||thank u both for yr reply...
what do u mean by change it to domain account? i tried to start up the sql server agent using THIS ACCOUNT, which taking sql id that i created as sysadm and the name and password is the same username/password i log on to the server. the reason to do this to make the job using the same logon id as i run it thru dts designer. but now i am getting this error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 1326 (52E); Provider Error: 1326 (52E) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
i think my problem now, i am not sure when running the dts thru job, from where the job is taking username/password to connect to the remote server location. appreciate further help...thnks much.|||hi again...
i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.
how to check the SQL Server service account?
really need yr expertise here..thanks.|||hi again...
i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.
how to check the SQL Server service account?
really need yr expertise here..thanks.
start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.|||start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.
hi again...
i already changed the sql services to use logon username/password to be the same as sql agent. means both sql agent and sql services are using domain account now, no longer system account. but unfortunately, i am stil getting the same error...
anymore ideas can help me out here? please please...
tqvm.|||hi all again...
maybe i forgot to tell that my remote server i am connecting to is UNIX server (is that made a difference anyway?). well...just to test out, i just gave it a try to point to another remote server running on Windows. i shared the same folder and give sufficient privs while my sql server is stil using same domain account for sql agent as well as MSSQLSERVER services. then i run the job, it run successfully!
1.now i wld like to ask yr expertise here to guide me - how do we share a folder in UNIX to enable another server to connect to it? right now i just give permission read/write/execute to the folder. and it seems giving the above error - logon error/bad username or bad password.
2.how do we give permission to a server running on local? (not on network). the reason i asked is - normally its easy to give permission to some users on same network. is it by IP will work?
hope u all out there can help! thanks...really appreciate yr help.
Subscribe to:
Posts (Atom)