Hi,
I have one SQL server where my databases where kept on the D drive, and
the transaction logs where on a physically seperate C drive. The D
drive became corrupted and I could not start SQL Server because the
master database was corrupt. So, I had to use the RebuildDM.exe to
rebuild the system tables. I have the log files sitting on the C
drive, but I never had a chance to back them up to the most recent
point today because I could not get into SQL Server until I rebuild it.
Now what I would like to do is just restore the databases and then use
the logs that are sitting on C, but the only way it seems that I can do
this is by backing up the transaction logs first, which I did not have
a chance to do. Is there anyway I can take a backup of my DB and tell
it to use the transaction logs that already exists so that it can apply
any missing transactions?
I forgot to mention, this box is windows server 2003 with sql server
2000 SP4
|||Doh, after hours of searching I found the answer right after posting
this. For anyone who has this problem, look here
http://support.microsoft.com/default...;EN-US;Q253817
Showing posts with label kept. Show all posts
Showing posts with label kept. Show all posts
Wednesday, March 7, 2012
Database restore after system Rebuild
Hi,
I have one SQL server where my databases where kept on the D drive, and
the transaction logs where on a physically seperate C drive. The D
drive became corrupted and I could not start SQL Server because the
master database was corrupt. So, I had to use the RebuildDM.exe to
rebuild the system tables. I have the log files sitting on the C
drive, but I never had a chance to back them up to the most recent
point today because I could not get into SQL Server until I rebuild it.
Now what I would like to do is just restore the databases and then use
the logs that are sitting on C, but the only way it seems that I can do
this is by backing up the transaction logs first, which I did not have
a chance to do. Is there anyway I can take a backup of my DB and tell
it to use the transaction logs that already exists so that it can apply
any missing transactions?I forgot to mention, this box is windows server 2003 with sql server
2000 SP4|||Doh, after hours of searching I found the answer right after posting
this. For anyone who has this problem, look here
http://support.microsoft.com/defaul...b;EN-US;Q253817
I have one SQL server where my databases where kept on the D drive, and
the transaction logs where on a physically seperate C drive. The D
drive became corrupted and I could not start SQL Server because the
master database was corrupt. So, I had to use the RebuildDM.exe to
rebuild the system tables. I have the log files sitting on the C
drive, but I never had a chance to back them up to the most recent
point today because I could not get into SQL Server until I rebuild it.
Now what I would like to do is just restore the databases and then use
the logs that are sitting on C, but the only way it seems that I can do
this is by backing up the transaction logs first, which I did not have
a chance to do. Is there anyway I can take a backup of my DB and tell
it to use the transaction logs that already exists so that it can apply
any missing transactions?I forgot to mention, this box is windows server 2003 with sql server
2000 SP4|||Doh, after hours of searching I found the answer right after posting
this. For anyone who has this problem, look here
http://support.microsoft.com/defaul...b;EN-US;Q253817
Database restore after system Rebuild
Hi,
I have one SQL server where my databases where kept on the D drive, and
the transaction logs where on a physically seperate C drive. The D
drive became corrupted and I could not start SQL Server because the
master database was corrupt. So, I had to use the RebuildDM.exe to
rebuild the system tables. I have the log files sitting on the C
drive, but I never had a chance to back them up to the most recent
point today because I could not get into SQL Server until I rebuild it.
Now what I would like to do is just restore the databases and then use
the logs that are sitting on C, but the only way it seems that I can do
this is by backing up the transaction logs first, which I did not have
a chance to do. Is there anyway I can take a backup of my DB and tell
it to use the transaction logs that already exists so that it can apply
any missing transactions?I forgot to mention, this box is windows server 2003 with sql server
2000 SP4|||Doh, after hours of searching I found the answer right after posting
this. For anyone who has this problem, look here
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q253817
I have one SQL server where my databases where kept on the D drive, and
the transaction logs where on a physically seperate C drive. The D
drive became corrupted and I could not start SQL Server because the
master database was corrupt. So, I had to use the RebuildDM.exe to
rebuild the system tables. I have the log files sitting on the C
drive, but I never had a chance to back them up to the most recent
point today because I could not get into SQL Server until I rebuild it.
Now what I would like to do is just restore the databases and then use
the logs that are sitting on C, but the only way it seems that I can do
this is by backing up the transaction logs first, which I did not have
a chance to do. Is there anyway I can take a backup of my DB and tell
it to use the transaction logs that already exists so that it can apply
any missing transactions?I forgot to mention, this box is windows server 2003 with sql server
2000 SP4|||Doh, after hours of searching I found the answer right after posting
this. For anyone who has this problem, look here
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q253817
Saturday, February 25, 2012
database refusing to update - timeout
hi...i have a table which has about 100 cols and about 13000 rows...today
it has had problems updating...i kept getting timeout...i run queries from
ASP scripts...it is not any larger than other tables that work fine...it
does however have several cols which are text datatype. if i was viewing
the table in EM the updates would fail...when i closed the table view it
usually updated...is there an explanation for this behaviour...
also
the whole db is about 250MB on backup but when restored it is about 700
including the transaction log...i thought the transaction log might be too
big...it has been set to grow as big as it likes...is there a way to get the
db to update everything into the db files thus reducing the log to
nothing?...this probably sounds ignorant to the experts out there, i am a
developer who is also becoming a reluctant dba...
thanks for any advice offered
gdpThe update needs to take an exclusive lock which it won't
get if there are other locks including shared locks - see
books online topics Understanding Locking in SQL Server and
Lock compatability.
A backup file and the data files are two different
structures so there will be size differences. In terms of
shrinking the log file size to almost nothing, the log will
then need to grow to log subsequent transactions.
Consequently, you would be wasting resources by shrinking,
then having the log grow, then shrinking. It's best to leave
the log at the size it needs to grow to between backups so
that you aren't wasting resources. If the log file sizes is
excessive and you need to shrink it down a bit, you can use
dbcc shrinkfile. You can find more information on this in
the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
You can keep the log at a reasonable size by performing
regular log backups. The backups will truncate the log. But
truncating a log file does not reduce it's physical size.
Truncation marks the parts of the log no longer needed for
recovery as inactive, allowing these parts of the log to be
reused for new transactions. To physically reduce the size
of the log, you need to use dbcc shrinkfile. But as I
already said, shrinking, growing, shrinking, growing isn't a
good use of SQL Server resources.
-Sue
On Mon, 3 Nov 2003 21:07:37 -0000, "gdp"
<gp014i0613@.blueyonder.co.uk> wrote:
>hi...i have a table which has about 100 cols and about 13000 rows...today
>it has had problems updating...i kept getting timeout...i run queries from
>ASP scripts...it is not any larger than other tables that work fine...it
>does however have several cols which are text datatype. if i was viewing
>the table in EM the updates would fail...when i closed the table view it
>usually updated...is there an explanation for this behaviour...
>also
>the whole db is about 250MB on backup but when restored it is about 700
>including the transaction log...i thought the transaction log might be too
>big...it has been set to grow as big as it likes...is there a way to get the
>db to update everything into the db files thus reducing the log to
>nothing?...this probably sounds ignorant to the experts out there, i am a
>developer who is also becoming a reluctant dba...
>thanks for any advice offered
>gdp
>
it has had problems updating...i kept getting timeout...i run queries from
ASP scripts...it is not any larger than other tables that work fine...it
does however have several cols which are text datatype. if i was viewing
the table in EM the updates would fail...when i closed the table view it
usually updated...is there an explanation for this behaviour...
also
the whole db is about 250MB on backup but when restored it is about 700
including the transaction log...i thought the transaction log might be too
big...it has been set to grow as big as it likes...is there a way to get the
db to update everything into the db files thus reducing the log to
nothing?...this probably sounds ignorant to the experts out there, i am a
developer who is also becoming a reluctant dba...
thanks for any advice offered
gdpThe update needs to take an exclusive lock which it won't
get if there are other locks including shared locks - see
books online topics Understanding Locking in SQL Server and
Lock compatability.
A backup file and the data files are two different
structures so there will be size differences. In terms of
shrinking the log file size to almost nothing, the log will
then need to grow to log subsequent transactions.
Consequently, you would be wasting resources by shrinking,
then having the log grow, then shrinking. It's best to leave
the log at the size it needs to grow to between backups so
that you aren't wasting resources. If the log file sizes is
excessive and you need to shrink it down a bit, you can use
dbcc shrinkfile. You can find more information on this in
the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
You can keep the log at a reasonable size by performing
regular log backups. The backups will truncate the log. But
truncating a log file does not reduce it's physical size.
Truncation marks the parts of the log no longer needed for
recovery as inactive, allowing these parts of the log to be
reused for new transactions. To physically reduce the size
of the log, you need to use dbcc shrinkfile. But as I
already said, shrinking, growing, shrinking, growing isn't a
good use of SQL Server resources.
-Sue
On Mon, 3 Nov 2003 21:07:37 -0000, "gdp"
<gp014i0613@.blueyonder.co.uk> wrote:
>hi...i have a table which has about 100 cols and about 13000 rows...today
>it has had problems updating...i kept getting timeout...i run queries from
>ASP scripts...it is not any larger than other tables that work fine...it
>does however have several cols which are text datatype. if i was viewing
>the table in EM the updates would fail...when i closed the table view it
>usually updated...is there an explanation for this behaviour...
>also
>the whole db is about 250MB on backup but when restored it is about 700
>including the transaction log...i thought the transaction log might be too
>big...it has been set to grow as big as it likes...is there a way to get the
>db to update everything into the db files thus reducing the log to
>nothing?...this probably sounds ignorant to the experts out there, i am a
>developer who is also becoming a reluctant dba...
>thanks for any advice offered
>gdp
>
Subscribe to:
Posts (Atom)