I recently moved a database and its transaction log file to another drive due
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts?
Robert
Hi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set on
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get an
> error that it can't.
> Any thoughts?
> Robert
|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
[vbcol=seagreen]
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
|||Hi
Check out
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
[vbcol=seagreen]
> In the move of this large db/log to another volume, the sql service account
> that starts mssqlserver didn't have specific rights to the new location. I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit the
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
Showing posts with label drive. Show all posts
Showing posts with label drive. Show all posts
Thursday, March 29, 2012
database move
I recently moved a database and its transaction log file to another drive du
e
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set o
n
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive
due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get
an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
[vbcol=seagreen]
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set
on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
>|||Hi
Check out
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
[vbcol=seagreen]
> In the move of this large db/log to another volume, the sql service accoun
t
> that starts mssqlserver didn't have specific rights to the new location.
I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit t
he
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
>sql
e
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set o
n
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive
due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get
an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
[vbcol=seagreen]
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set
on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
>|||Hi
Check out
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
[vbcol=seagreen]
> In the move of this large db/log to another volume, the sql service accoun
t
> that starts mssqlserver didn't have specific rights to the new location.
I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit t
he
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
>sql
database move
I recently moved a database and its transaction log file to another drive due
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set on
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
> > I recently moved a database and its transaction log file to another drive due
> > to space issues. I followed the kb article 224071 on how to move a user
> > database and it appears that it worked correctly.
> >
> > However, now the database comes up as "read-only" and if I view the
> > properties of the db and try to remove the "read-only" designation, I get an
> > error that it can't.
> >
> > Any thoughts'
> >
> > Robert|||Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
> In the move of this large db/log to another volume, the sql service account
> that starts mssqlserver didn't have specific rights to the new location. I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit the
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
> > Hi Robert
> >
> > Check the SQL Server error log to see if there is any information on the
> > problem. You may also want to check that the readonly attribute is not set on
> > the mdf and ldf files.
> >
> > John
> >
> > "Robert Gandrud" wrote:
> >
> > > I recently moved a database and its transaction log file to another drive due
> > > to space issues. I followed the kb article 224071 on how to move a user
> > > database and it appears that it worked correctly.
> > >
> > > However, now the database comes up as "read-only" and if I view the
> > > properties of the db and try to remove the "read-only" designation, I get an
> > > error that it can't.
> > >
> > > Any thoughts'
> > >
> > > Robertsql
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set on
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
> > I recently moved a database and its transaction log file to another drive due
> > to space issues. I followed the kb article 224071 on how to move a user
> > database and it appears that it worked correctly.
> >
> > However, now the database comes up as "read-only" and if I view the
> > properties of the db and try to remove the "read-only" designation, I get an
> > error that it can't.
> >
> > Any thoughts'
> >
> > Robert|||Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
> In the move of this large db/log to another volume, the sql service account
> that starts mssqlserver didn't have specific rights to the new location. I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit the
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
> > Hi Robert
> >
> > Check the SQL Server error log to see if there is any information on the
> > problem. You may also want to check that the readonly attribute is not set on
> > the mdf and ldf files.
> >
> > John
> >
> > "Robert Gandrud" wrote:
> >
> > > I recently moved a database and its transaction log file to another drive due
> > > to space issues. I followed the kb article 224071 on how to move a user
> > > database and it appears that it worked correctly.
> > >
> > > However, now the database comes up as "read-only" and if I view the
> > > properties of the db and try to remove the "read-only" designation, I get an
> > > error that it can't.
> > >
> > > Any thoughts'
> > >
> > > Robertsql
Monday, March 19, 2012
Database marked as suspect please HELP!
Hi,
I restored my Windows XP system and discovered that I cannot access a
MSDE DB that was saved in D: drive (I restored my primary partition in
C). How can I restore it?
Thanks a lot.did you try reattaching it ?|||On 6 Jan 2005 10:25:09 -0800, "getinked" <glenn@.imsocial.com> wrote:
>did you try reattaching it ?
How can I do that? I have the MDF and LDF files but I've never done a
backup. I'm using the trial version of Teratrax Database Manager and
DbaMGR2K.
Thanks a lot for your help.|||On 6 Jan 2005 10:25:09 -0800, "getinked" <glenn@.imsocial.com> wrote:
>did you try reattaching it ?
I noticed the attach command in Teratrax but don't know how to use it.
It tells me
sp_attach_db 'database_name', 'file_name_and_path_relative_to_server'
Is the database_name the filename? What's the relative path? I've my
DB saved in D:\database while the server is in C:\InetPub I think.
Thanks.|||yea the relative path would be where you are storing the data...thus
being on your D drive.|||On 6 Jan 2005 13:23:38 -0800, "getinked" <glenn@.imsocial.com> wrote:
>yea the relative path would be where you are storing the data...thus
>being on your D drive.
Could you be more specific, what string should I enter? Thanks.|||kurotsuke (kurotsuke@.yahoo.it) writes:
> On 6 Jan 2005 13:23:38 -0800, "getinked" <glenn@.imsocial.com> wrote:
>>yea the relative path would be where you are storing the data...thus
>>being on your D drive.
> Could you be more specific, what string should I enter? Thanks.
The exact path to where the database file is located. (The Send To Cliboard
as Name from the Win95 Powertoys comes in extremely handy here.) For
instance D:\Program Files\Microsoft SQL Server\MSSQL\Data\yourdatabase.mdf.
Note that you have two files to enter: the .MDF and the .LDF.
However, since your database is marked as suspect, it still attached
to SQL Server in some sense. You may have to drop it first. Then again,
you could attach the database under a new name.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Friday, February 24, 2012
Database mail attachments filling up the system drive!
Hi all,
Has anyone run into this issue before?
My company uses database mail extensively for alerting and the like, and most emails sent have attachments. We've put in place procedures for removing emails older than a month from the MSDB database, however we've now had a client server completely run out of the disk space due to attachments being stored in the C:\Documents and Settings\User\Local Settings\Temp folder!?!
When I check the sysmail_delete_mailitems_sp sproc, it only removes entries in the MSDB tables, and doesn't remove these temporary files in the temp directory.
Does anyone know why SQL Server stores them in the temp directory of the service account user, as well as how we can remove them (apart from manually deleting the files), as we need to have an automated process for cleaning up emails, and at the moment SQL Server 2005 doesn't handle this very well.
I've checked this on our test server as well, and we're getting the same there, and this server is running the latest SP2 + hot fixes.
Thanks,
Reece.
It happens because when you attach a file to an email, the email system makes a copy of the file to attach -so that if the file is in use, or even deleted, the email attachment will send without error.
However, then temp folder is not being cleared -normally that happens when the user logs out. Since the mail account belongs to a service account, it is not logging out.
You may wish to create a SQL Agent job (or a Windows Scheduler task) to run daily and delete files older than one day.
|||Thanks for the info. We're in the process of testing a job to remove the files, but it would be definitely nice if SQL Server cleaned up these files itself when it does the DB Mail cleanup on the database.
Cheers,
Reece.
Subscribe to:
Posts (Atom)