Backup to Network Drive: logs

Showing posts with label logs. Show all posts

Tuesday, March 20, 2012

BAK, TRN files

Hey all,
I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
them if you are running server backup IE Using Arcserve.
We seem to be constantly running out of space on your drives so I have been
so far moving the BAK and TRN files to different disks but as you can imagine
this cant continue for too long.
We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
this seems huge to me, as the drive is only 29 Gb.
A friend mentioned that you shouldn't limit the size of the TRN logs as your
only shooting yourself inthe foot should the db go down, but at this Im going
to have to start getting new disks to hold the increasing amounts.
Anyone any ideas of what I can do, is it ok to delete these files or limit
the growth all together, say to a Gb a DB ?
ThanksYes, you have to delete old backup files. Determine a strategy for how long to keep them, and with
this, consider hoe you store these files on tape.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:03BB4744-B688-4190-9220-17993AEB37BB@.microsoft.com...
> Hey all,
> I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
> them if you are running server backup IE Using Arcserve.
> We seem to be constantly running out of space on your drives so I have been
> so far moving the BAK and TRN files to different disks but as you can imagine
> this cant continue for too long.
> We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
> this seems huge to me, as the drive is only 29 Gb.
> A friend mentioned that you shouldn't limit the size of the TRN logs as your
> only shooting yourself inthe foot should the db go down, but at this Im going
> to have to start getting new disks to hold the increasing amounts.
> Anyone any ideas of what I can do, is it ok to delete these files or limit
> the growth all together, say to a Gb a DB ?
> Thanks

BAK, TRN files

Hey all,
I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
them if you are running server backup IE Using Arcserve.
We seem to be constantly running out of space on your drives so I have been
so far moving the BAK and TRN files to different disks but as you can imagin
e
this cant continue for too long.
We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
this seems huge to me, as the drive is only 29 Gb.
A friend mentioned that you shouldn't limit the size of the TRN logs as your
only shooting yourself inthe foot should the db go down, but at this Im goin
g
to have to start getting new disks to hold the increasing amounts.
Anyone any ideas of what I can do, is it ok to delete these files or limit
the growth all together, say to a Gb a DB ?
ThanksYes, you have to delete old backup files. Determine a strategy for how long
to keep them, and with
this, consider hoe you store these files on tape.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:03BB4744-B688-4190-9220-17993AEB37BB@.microsoft.com...
> Hey all,
> I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
> them if you are running server backup IE Using Arcserve.
> We seem to be constantly running out of space on your drives so I have bee
n
> so far moving the BAK and TRN files to different disks but as you can imag
ine
> this cant continue for too long.
> We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10g
bs
> this seems huge to me, as the drive is only 29 Gb.
> A friend mentioned that you shouldn't limit the size of the TRN logs as yo
ur
> only shooting yourself inthe foot should the db go down, but at this Im go
ing
> to have to start getting new disks to hold the increasing amounts.
> Anyone any ideas of what I can do, is it ok to delete these files or limit
> the growth all together, say to a Gb a DB ?
> Thanks

Monday, March 19, 2012

bad page ID in MSDB DB

Hi All,
Greeting,

Sql Server 7
OS: Win NT

In the sql server logs i see the below error alerts

I/O error (bad page ID) detected during read of BUF pointer = 0x11e09e80, page ptr = 0x446b4000, pageid = (0x1:0x2c78), dbid = 4, status = 0x801, file = F:\MSSQL7\DATA\msdbdata.mdf..

Error: 823, Severity: 24, State: 1

Please help me in this.

Thanks in Advance
AdilHere's one place to look: http://support.microsoft.com/kb/q281809/

Bad Page error

I am getting an error in my DTS logs about a bad page. Here is the exact
message.
Step Error Description:I/O error (bad page ID) detected during read at
offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
We are getting this error on 2 different servers. The process has been
running like a champ for years and now we are getting this message. All that
is running when it errors out is an update statement that is joining 2
tables. I have read some about tempdb running into these issues and it
suggests that there may be hardware issues. That is not the case as we have
looked into that and we have many other processes that run on these servers.
I have also read that service pack 4 needs to be installed. Well we moved
our process to another server with exactly the same configuration and it ran
fine on there. Had anybody else ran into this? Any suggestions? All help
is appreciated.
Thankshave you run DBCC CHECKDB to see if there are any errors?
--
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> I am getting an error in my DTS logs about a bad page. Here is the exact
> message.
> Step Error Description:I/O error (bad page ID) detected during read at
> offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> We are getting this error on 2 different servers. The process has been
> running like a champ for years and now we are getting this message. All
that
> is running when it errors out is an update statement that is joining 2
> tables. I have read some about tempdb running into these issues and it
> suggests that there may be hardware issues. That is not the case as we
have
> looked into that and we have many other processes that run on these
servers.
> I have also read that service pack 4 needs to be installed. Well we moved
> our process to another server with exactly the same configuration and it
ran
> fine on there. Had anybody else ran into this? Any suggestions? All
help
> is appreciated.
> Thanks|||Yes, we ran that and no errors were returned. We also ran it with Allow data
loss and no errors were returned. Like I mentioned below, this is happening
on 2 servers. It is the same process, but 1 is the dev server and 1 is prod.
"Jack Vamvas" wrote:
> have you run DBCC CHECKDB to see if there are any errors?
> --
> Jack Vamvas
> __________________________________________________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> > I am getting an error in my DTS logs about a bad page. Here is the exact
> > message.
> >
> > Step Error Description:I/O error (bad page ID) detected during read at
> > offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> >
> > We are getting this error on 2 different servers. The process has been
> > running like a champ for years and now we are getting this message. All
> that
> > is running when it errors out is an update statement that is joining 2
> > tables. I have read some about tempdb running into these issues and it
> > suggests that there may be hardware issues. That is not the case as we
> have
> > looked into that and we have many other processes that run on these
> servers.
> > I have also read that service pack 4 needs to be installed. Well we moved
> > our process to another server with exactly the same configuration and it
> ran
> > fine on there. Had anybody else ran into this? Any suggestions? All
> help
> > is appreciated.
> >
> > Thanks
>
>|||Hi Andy,
This is usually caused by the hardware, but if it is happening on two
different hardware systems, it seems like a logical problem in the database.
You probably restored a backup of the database from server to another.
Here is the logical meaning of this error:
http://support.microsoft.com/default.aspx?scid=kb;en-us;828339
HTH
DeeJay Puar
MCDBA
(bad page ID): This message means that the pageID on the page header is not
the expected page that was read from the disk. For example, if SQL Server
2000 provides a file offset for database file 1 that is for logical page 100,
the pageID on the page header for that 8 KB page should be 1:100. If not, the
bad page ID is included in the logical I/O check failure message.
You can read more about it here:
"Andy" wrote:
> Yes, we ran that and no errors were returned. We also ran it with Allow data
> loss and no errors were returned. Like I mentioned below, this is happening
> on 2 servers. It is the same process, but 1 is the dev server and 1 is prod.
>
> "Jack Vamvas" wrote:
> > have you run DBCC CHECKDB to see if there are any errors?
> >
> > --
> > Jack Vamvas
> > __________________________________________________________________
> > Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> > SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> > New article by Jack Vamvas - SQL and Markov Chains -
> > www.ciquery.com/articles/art_04.asp
> > "Andy" <Andy@.discussions.microsoft.com> wrote in message
> > news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> > > I am getting an error in my DTS logs about a bad page. Here is the exact
> > > message.
> > >
> > > Step Error Description:I/O error (bad page ID) detected during read at
> > > offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> > >
> > > We are getting this error on 2 different servers. The process has been
> > > running like a champ for years and now we are getting this message. All
> > that
> > > is running when it errors out is an update statement that is joining 2
> > > tables. I have read some about tempdb running into these issues and it
> > > suggests that there may be hardware issues. That is not the case as we
> > have
> > > looked into that and we have many other processes that run on these
> > servers.
> > > I have also read that service pack 4 needs to be installed. Well we moved
> > > our process to another server with exactly the same configuration and it
> > ran
> > > fine on there. Had anybody else ran into this? Any suggestions? All
> > help
> > > is appreciated.
> > >
> > > Thanks
> >
> >
> >|||I looked into that as well, as I thought I did take a backup. The 2nd server
that it is happening on I created brand new databases before I kicked off the
process and we received the same error, at the same point in the process.
"DeeJay Puar" wrote:
> Hi Andy,
> This is usually caused by the hardware, but if it is happening on two
> different hardware systems, it seems like a logical problem in the database.
> You probably restored a backup of the database from server to another.
> Here is the logical meaning of this error:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;828339
> HTH
> DeeJay Puar
> MCDBA
> (bad page ID): This message means that the pageID on the page header is not
> the expected page that was read from the disk. For example, if SQL Server
> 2000 provides a file offset for database file 1 that is for logical page 100,
> the pageID on the page header for that 8 KB page should be 1:100. If not, the
> bad page ID is included in the logical I/O check failure message.
> You can read more about it here:
>
> "Andy" wrote:
> > Yes, we ran that and no errors were returned. We also ran it with Allow data
> > loss and no errors were returned. Like I mentioned below, this is happening
> > on 2 servers. It is the same process, but 1 is the dev server and 1 is prod.
> >
> >
> > "Jack Vamvas" wrote:
> >
> > > have you run DBCC CHECKDB to see if there are any errors?
> > >
> > > --
> > > Jack Vamvas
> > > __________________________________________________________________
> > > Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> > > SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> > > New article by Jack Vamvas - SQL and Markov Chains -
> > > www.ciquery.com/articles/art_04.asp
> > > "Andy" <Andy@.discussions.microsoft.com> wrote in message
> > > news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> > > > I am getting an error in my DTS logs about a bad page. Here is the exact
> > > > message.
> > > >
> > > > Step Error Description:I/O error (bad page ID) detected during read at
> > > > offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> > > >
> > > > We are getting this error on 2 different servers. The process has been
> > > > running like a champ for years and now we are getting this message. All
> > > that
> > > > is running when it errors out is an update statement that is joining 2
> > > > tables. I have read some about tempdb running into these issues and it
> > > > suggests that there may be hardware issues. That is not the case as we
> > > have
> > > > looked into that and we have many other processes that run on these
> > > servers.
> > > > I have also read that service pack 4 needs to be installed. Well we moved
> > > > our process to another server with exactly the same configuration and it
> > > ran
> > > > fine on there. Had anybody else ran into this? Any suggestions? All
> > > help
> > > > is appreciated.
> > > >
> > > > Thanks
> > >
> > >
> > >|||No too sure as to what is happening. I can not really duplicate it here.
On the server, did you take a backup from the old server and restore the
database on the new server? Or did you just create a shell and then ran your
dts package to load the data? Have you looked at the source tables in the DTS
package?
Have you looked into torn-page?
"Andy" wrote:
> I looked into that as well, as I thought I did take a backup. The 2nd server
> that it is happening on I created brand new databases before I kicked off the
> process and we received the same error, at the same point in the process.
> "DeeJay Puar" wrote:
> > Hi Andy,
> >
> > This is usually caused by the hardware, but if it is happening on two
> > different hardware systems, it seems like a logical problem in the database.
> > You probably restored a backup of the database from server to another.
> >
> > Here is the logical meaning of this error:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;828339
> >
> > HTH
> >
> > DeeJay Puar
> > MCDBA
> >
> > (bad page ID): This message means that the pageID on the page header is not
> > the expected page that was read from the disk. For example, if SQL Server
> > 2000 provides a file offset for database file 1 that is for logical page 100,
> > the pageID on the page header for that 8 KB page should be 1:100. If not, the
> > bad page ID is included in the logical I/O check failure message.
> >
> > You can read more about it here:
> >
> >
> >
> > "Andy" wrote:
> >
> > > Yes, we ran that and no errors were returned. We also ran it with Allow data
> > > loss and no errors were returned. Like I mentioned below, this is happening
> > > on 2 servers. It is the same process, but 1 is the dev server and 1 is prod.
> > >
> > >
> > > "Jack Vamvas" wrote:
> > >
> > > > have you run DBCC CHECKDB to see if there are any errors?
> > > >
> > > > --
> > > > Jack Vamvas
> > > > __________________________________________________________________
> > > > Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> > > > SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> > > > New article by Jack Vamvas - SQL and Markov Chains -
> > > > www.ciquery.com/articles/art_04.asp
> > > > "Andy" <Andy@.discussions.microsoft.com> wrote in message
> > > > news:AA513080-F72F-4386-93CA-93CEF0673F76@.microsoft.com...
> > > > > I am getting an error in my DTS logs about a bad page. Here is the exact
> > > > > message.
> > > > >
> > > > > Step Error Description:I/O error (bad page ID) detected during read at
> > > > > offset 0x0000022a040000 in file 'E:\SQLData\ALS_Stage_Data.MDF'.
> > > > >
> > > > > We are getting this error on 2 different servers. The process has been
> > > > > running like a champ for years and now we are getting this message. All
> > > > that
> > > > > is running when it errors out is an update statement that is joining 2
> > > > > tables. I have read some about tempdb running into these issues and it
> > > > > suggests that there may be hardware issues. That is not the case as we
> > > > have
> > > > > looked into that and we have many other processes that run on these
> > > > servers.
> > > > > I have also read that service pack 4 needs to be installed. Well we moved
> > > > > our process to another server with exactly the same configuration and it
> > > > ran
> > > > > fine on there. Had anybody else ran into this? Any suggestions? All
> > > > help
> > > > > is appreciated.
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >

Bad Page error

Bad Logs

Let me correct that. The data part of the database is also corrupt. Is there
a way to delete the corrupted part of the data base and try a partial
recovery?
--
Chris DavoliAs Greg stated you are best to call MS PSS and work directly with someone
there.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
> --
> Chris Davoli
>|||what is microsoft PSS? Is there a phone or email or something?
--
Chris Davoli
"Andrew J. Kelly" wrote:
> As Greg stated you are best to call MS PSS and work directly with someone
> there.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> > Let me correct that. The data part of the database is also corrupt. Is
> > there
> > a way to delete the corrupted part of the data base and try a partial
> > recovery?
> > --
> > Chris Davoli
> >
>|||On Fri, 29 Feb 2008 18:14:00 -0800, Chris Davoli
<ChrisDavoli@.discussions.microsoft.com> wrote:
>what is microsoft PSS? Is there a phone or email or something?
Microsoft support. You start with a phone call, pay them some money
with your charge card, and go on from there. Unless of course you
already have a support contract.
Roy Harvey
Beacon Falls, CT|||"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
I would still HIGHLY recommend calling Microsoft.
However, if it's the data portion that's corrupt and not the log, the
recovery scenario may be something like this.
Back up "the tail of the log" as its called (there's a special command for
this but if you've already stopped SQL Server, I don't think you'll be able
to do this.)
Now, if you truly have a good backup from several months ago and haven't
truncated the log since then or in any other way broken the log chain, you
MIGHT be able to restore the database backup from then and then restore the
log and apply that.
HOWEVER, trying this on your own... I give about a chance of 1 in a 1000 of
working. With Microsoft helping, I think you could get this down to about 1
in a 100. In other words, not very likely. Sorry.
> --
> Chris Davoli
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||You can also try out ApexSQL Log. It may be able to recover some of the
data. Others are right though - corrupt database is deffinitely a time to
contact Microsoft support!
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
> --
> Chris Davoli
>|||http://support.microsoft.com/default.aspx?scid=fh%3BEN-US%3Bofferprophone
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:A2EC39C0-4AE9-4E86-91F4-3080E5A0D9DE@.microsoft.com...
> what is microsoft PSS? Is there a phone or email or something?
> --
> Chris Davoli
>
> "Andrew J. Kelly" wrote:
>> As Greg stated you are best to call MS PSS and work directly with someone
>> there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
>> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> > Let me correct that. The data part of the database is also corrupt. Is
>> > there
>> > a way to delete the corrupted part of the data base and try a partial
>> > recovery?
>> > --
>> > Chris Davoli
>> >
>>|||Just to add a few tiny bits to Greg's recommendations:
> I would still HIGHLY recommend calling Microsoft.
Just to start with "I agree".
> Back up "the tail of the log" as its called (there's a special command for this but if you've
> already stopped SQL Server, I don't think you'll be able to do this.)
To backup the log of a damaged database one might have to add the NO_TRUNCATE option of the backup
command, like
BACKUP LOG dbname TO DISK = 'C:\db.trn' WITH NO_TRUNCATE
This is doable even if SQL Server has been stopped (assuming one start SQL Server again, of course
:-) ). We can even copy the ldf file(s) for a database to some other machine, create a (dummy)
database there, stop that SQL Server, delete its database files, slide in the log file(s) for this
damaged database, start that SQL Server and now do the log backup using NO_TRUNCATE. It is all about
getting the log records from the ldf file to a transaction log file.
Of course, a pre-requisite for all this is an unbroken chain of log backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eNaOrq6eIHA.4696@.TK2MSFTNGP05.phx.gbl...
> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> Let me correct that. The data part of the database is also corrupt. Is there
>> a way to delete the corrupted part of the data base and try a partial
>> recovery?
> I would still HIGHLY recommend calling Microsoft.
> However, if it's the data portion that's corrupt and not the log, the recovery scenario may be
> something like this.
> Back up "the tail of the log" as its called (there's a special command for this but if you've
> already stopped SQL Server, I don't think you'll be able to do this.)
> Now, if you truly have a good backup from several months ago and haven't truncated the log since
> then or in any other way broken the log chain, you MIGHT be able to restore the database backup
> from then and then restore the log and apply that.
> HOWEVER, trying this on your own... I give about a chance of 1 in a 1000 of working. With
> Microsoft helping, I think you could get this down to about 1 in a 100. In other words, not very
> likely. Sorry.
>
>> --
>> Chris Davoli
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F5D8A180-8F94-4654-836B-7CBE021CD5E0@.microsoft.com...
> To backup the log of a damaged database one might have to add the
> NO_TRUNCATE option of the backup command, like
> BACKUP LOG dbname TO DISK = 'C:\db.trn' WITH NO_TRUNCATE
> This is doable even if SQL Server has been stopped (assuming one start SQL
> Server again, of course :-) ). We can even copy the ldf file(s) for a
> database to some other machine, create a (dummy) database there, stop that
> SQL Server, delete its database files, slide in the log file(s) for this
> damaged database, start that SQL Server and now do the log backup using
> NO_TRUNCATE. It is all about getting the log records from the ldf file to
> a transaction log file.
Ah, I wasn't sure that would work. (and I assume you mean delete the log
files, not both database files?)
> Of course, a pre-requisite for all this is an unbroken chain of log
> backups...
A mighty big one. ;-)
This is the sort of thing I might try on a lark if I had spare time, but for
production data, I'd definitely be calling Microsoft. Anything that Tibor
and I might say may or may not work and I can't speak for Tibor (though I'm
sure he'd agree) I'd hate to have you attempt to follow any advice I might
give in this case that makes things worse when Microsoft is very likely to
have a better idea.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:eNaOrq6eIHA.4696@.TK2MSFTNGP05.phx.gbl...
>> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
>> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> Let me correct that. The data part of the database is also corrupt. Is
>> there
>> a way to delete the corrupted part of the data base and try a partial
>> recovery?
>> I would still HIGHLY recommend calling Microsoft.
>> However, if it's the data portion that's corrupt and not the log, the
>> recovery scenario may be something like this.
>> Back up "the tail of the log" as its called (there's a special command
>> for this but if you've already stopped SQL Server, I don't think you'll
>> be able to do this.)
>> Now, if you truly have a good backup from several months ago and haven't
>> truncated the log since then or in any other way broken the log chain,
>> you MIGHT be able to restore the database backup from then and then
>> restore the log and apply that.
>> HOWEVER, trying this on your own... I give about a chance of 1 in a 1000
>> of working. With Microsoft helping, I think you could get this down to
>> about 1 in a 100. In other words, not very likely. Sorry.
>>
>> --
>> Chris Davoli
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>>
>|||>> This is doable even if SQL Server has been stopped (assuming one start SQL Server again, of
>> course :-) ). We can even copy the ldf file(s) for a database to some other machine, create a
>> (dummy) database there, stop that SQL Server, delete its database files, slide in the log file(s)
>> for this damaged database, start that SQL Server and now do the log backup using NO_TRUNCATE. It
>> is all about getting the log records from the ldf file to a transaction log file.
> Ah, I wasn't sure that would work. (and I assume you mean delete the log files, not both database
> files?)
Yep, it work, and I've done that in productions. And I did indeed mean delete all database files.
Say you have a SQL Server installation that is toast, all you have is the ldf file for your critical
database. What you want to do is essentially to turn this ldf file into a log backup file. For this
you need to "get it into" a working SQL Server so you can issue a BACKUP LOG command.
So on on some working SQL Server, you create a database. The sole purpose of this is to get an entry
in "sysdatabases". The database files are not of interest for us. This is why we stop that SQL
Server and delete the database files. And now we copy the lof file from the crasched server (in the
right path, and file name, of course - it need to be the same as the log file for the "dummy"
database we created). So when we not start that SQL Server it will look like any SQL Server for
which the data files for the database are lost - but the log files are there. This is why we can
BACKUP LOG ... WITH NO_TRUNCATE against that database. :-)
> This is the sort of thing I might try on a lark if I had spare time, but for production data, I'd
> definitely be calling Microsoft. Anything that Tibor and I might say may or may not work and I
> can't speak for Tibor (though I'm sure he'd agree) I'd hate to have you attempt to follow any
> advice I might give in this case that makes things worse when Microsoft is very likely to have a
> better idea.
I absolutely agree.
If you don't know, by heart, what measures to take "Oh, that happenened - I know I can do this, I've
done it plenty of times before.", then call MS Support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eJdwguBfIHA.4376@.TK2MSFTNGP05.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:F5D8A180-8F94-4654-836B-7CBE021CD5E0@.microsoft.com...
>> To backup the log of a damaged database one might have to add the NO_TRUNCATE option of the
>> backup command, like
>> BACKUP LOG dbname TO DISK = 'C:\db.trn' WITH NO_TRUNCATE
>> This is doable even if SQL Server has been stopped (assuming one start SQL Server again, of
>> course :-) ). We can even copy the ldf file(s) for a database to some other machine, create a
>> (dummy) database there, stop that SQL Server, delete its database files, slide in the log file(s)
>> for this damaged database, start that SQL Server and now do the log backup using NO_TRUNCATE. It
>> is all about getting the log records from the ldf file to a transaction log file.
> Ah, I wasn't sure that would work. (and I assume you mean delete the log files, not both database
> files?)
>
>> Of course, a pre-requisite for all this is an unbroken chain of log backups...
> A mighty big one. ;-)
>
> This is the sort of thing I might try on a lark if I had spare time, but for production data, I'd
> definitely be calling Microsoft. Anything that Tibor and I might say may or may not work and I
> can't speak for Tibor (though I'm sure he'd agree) I'd hate to have you attempt to follow any
> advice I might give in this case that makes things worse when Microsoft is very likely to have a
> better idea.
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
>> news:eNaOrq6eIHA.4696@.TK2MSFTNGP05.phx.gbl...
>> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
>> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> Let me correct that. The data part of the database is also corrupt. Is there
>> a way to delete the corrupted part of the data base and try a partial
>> recovery?
>> I would still HIGHLY recommend calling Microsoft.
>> However, if it's the data portion that's corrupt and not the log, the recovery scenario may be
>> something like this.
>> Back up "the tail of the log" as its called (there's a special command for this but if you've
>> already stopped SQL Server, I don't think you'll be able to do this.)
>> Now, if you truly have a good backup from several months ago and haven't truncated the log since
>> then or in any other way broken the log chain, you MIGHT be able to restore the database backup
>> from then and then restore the log and apply that.
>> HOWEVER, trying this on your own... I give about a chance of 1 in a 1000 of working. With
>> Microsoft helping, I think you could get this down to about 1 in a 100. In other words, not
>> very likely. Sorry.
>>
>> --
>> Chris Davoli
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>

Bad Logs

Let me correct that. The data part of the database is also corrupt. Is there
a way to delete the corrupted part of the data base and try a partial
recovery?
Chris Davoli
As Greg stated you are best to call MS PSS and work directly with someone
there.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
> --
> Chris Davoli
>
|||what is microsoft PSS? Is there a phone or email or something?
Chris Davoli
"Andrew J. Kelly" wrote:

> As Greg stated you are best to call MS PSS and work directly with someone
> there.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>
|||On Fri, 29 Feb 2008 18:14:00 -0800, Chris Davoli
<ChrisDavoli@.discussions.microsoft.com> wrote:

>what is microsoft PSS? Is there a phone or email or something?
Microsoft support. You start with a phone call, pay them some money
with your charge card, and go on from there. Unless of course you
already have a support contract.
Roy Harvey
Beacon Falls, CT
|||"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
I would still HIGHLY recommend calling Microsoft.
However, if it's the data portion that's corrupt and not the log, the
recovery scenario may be something like this.
Back up "the tail of the log" as its called (there's a special command for
this but if you've already stopped SQL Server, I don't think you'll be able
to do this.)
Now, if you truly have a good backup from several months ago and haven't
truncated the log since then or in any other way broken the log chain, you
MIGHT be able to restore the database backup from then and then restore the
log and apply that.
HOWEVER, trying this on your own... I give about a chance of 1 in a 1000 of
working. With Microsoft helping, I think you could get this down to about 1
in a 100. In other words, not very likely. Sorry.

> --
> Chris Davoli
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||You can also try out ApexSQL Log. It may be able to recover some of the
data. Others are right though - corrupt database is deffinitely a time to
contact Microsoft support!
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
> --
> Chris Davoli
>
|||http://support.microsoft.com/default.aspx?scid=fh%3BEN-US%3Bofferprophone
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:A2EC39C0-4AE9-4E86-91F4-3080E5A0D9DE@.microsoft.com...[vbcol=seagreen]
> what is microsoft PSS? Is there a phone or email or something?
> --
> Chris Davoli
>
> "Andrew J. Kelly" wrote:
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F5D8A180-8F94-4654-836B-7CBE021CD5E0@.microsoft.com...
> To backup the log of a damaged database one might have to add the
> NO_TRUNCATE option of the backup command, like
> BACKUP LOG dbname TO DISK = 'C:\db.trn' WITH NO_TRUNCATE
> This is doable even if SQL Server has been stopped (assuming one start SQL
> Server again, of course :-) ). We can even copy the ldf file(s) for a
> database to some other machine, create a (dummy) database there, stop that
> SQL Server, delete its database files, slide in the log file(s) for this
> damaged database, start that SQL Server and now do the log backup using
> NO_TRUNCATE. It is all about getting the log records from the ldf file to
> a transaction log file.
Ah, I wasn't sure that would work. (and I assume you mean delete the log
files, not both database files?)

> Of course, a pre-requisite for all this is an unbroken chain of log
> backups...
A mighty big one. ;-)
This is the sort of thing I might try on a lark if I had spare time, but for
production data, I'd definitely be calling Microsoft. Anything that Tibor
and I might say may or may not work and I can't speak for Tibor (though I'm
sure he'd agree) I'd hate to have you attempt to follow any advice I might
give in this case that makes things worse when Microsoft is very likely to
have a better idea.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:eNaOrq6eIHA.4696@.TK2MSFTNGP05.phx.gbl...
>

Thursday, March 8, 2012

Backups of Logs - Growing way, way out of control & Need Assistanc

Hello All
I should start by saying, I am not a SQL guru at all!
We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
NetBackup 5.1 MP5 using an online SQL Agent.
I will start with the good news, in that all FULL Backups work fine! I have
discovered that the Transaction logs are NOT included in this FULL Backup.
In NetBackup there is an option to "Backup and Truncate the logs" - so I
attempted to do this, and it claims it worked fine (the Application log also
shows event id 18265 and the description gives an indication "log was backed
up Database ect and ending in "No user action is required".
Great! but here is the problem. The .ldf files are growing WAY out of
control. For example, they can grow to over 40GB in a week !
I know there is a query or a SHRINK command that can be used to help, but I
am being told that NetBackup should be "truncating" the log down. I guess
this means shrinking.
Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
2005 Administrator has to manually shrink the logs or run a query to do this?
Or could something be setup wrong in SQL2005.
Any help is warmly appreciated.
Thank you
SimonConsider a log file a bucket. As modifications are performed, the bucket is filled. It is only
emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
by SQL Server if it becomes full and modifications are performed (autogrow).
So, one reason for large log files is that you never did backup log. You now did it, and that log
backup was probably pretty big and you should now have a lot of empty space in the log file. This
can be a valid situation for shrinking the log file. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> Hello All
> I should start by saying, I am not a SQL guru at all!
> We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> NetBackup 5.1 MP5 using an online SQL Agent.
> I will start with the good news, in that all FULL Backups work fine! I have
> discovered that the Transaction logs are NOT included in this FULL Backup.
> In NetBackup there is an option to "Backup and Truncate the logs" - so I
> attempted to do this, and it claims it worked fine (the Application log also
> shows event id 18265 and the description gives an indication "log was backed
> up Database ect and ending in "No user action is required".
> Great! but here is the problem. The .ldf files are growing WAY out of
> control. For example, they can grow to over 40GB in a week !
> I know there is a query or a SHRINK command that can be used to help, but I
> am being told that NetBackup should be "truncating" the log down. I guess
> this means shrinking.
> Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> 2005 Administrator has to manually shrink the logs or run a query to do this?
> Or could something be setup wrong in SQL2005.
> Any help is warmly appreciated.
> Thank you
> Simon|||Tibor thanks
Is there a way of telling what free space is in the log file. I sort of
understand the "bucket" route now :-)
I am guessing that if the log file size was 2GB in size, it does NOT mean
that SQL would use that - so in other words, the bucket may only contain 1GB
of data. Whats the best way of finding out?
Will check the link out as well. I apprecaite that a 3rd party tool is doing
the backup, but it sounds like I may be on the right track - the only concern
I had is the log file does not shrink. but if the query I showed below is
run, then the file is reduced down in size.
Simon
"Tibor Karaszi" wrote:
> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
> by SQL Server if it becomes full and modifications are performed (autogrow).
> So, one reason for large log files is that you never did backup log. You now did it, and that log
> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> can be a valid situation for shrinking the log file. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> > Hello All
> > I should start by saying, I am not a SQL guru at all!
> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> > NetBackup 5.1 MP5 using an online SQL Agent.
> >
> > I will start with the good news, in that all FULL Backups work fine! I have
> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >
> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> > attempted to do this, and it claims it worked fine (the Application log also
> > shows event id 18265 and the description gives an indication "log was backed
> > up Database ect and ending in "No user action is required".
> >
> > Great! but here is the problem. The .ldf files are growing WAY out of
> > control. For example, they can grow to over 40GB in a week !
> >
> > I know there is a query or a SHRINK command that can be used to help, but I
> > am being told that NetBackup should be "truncating" the log down. I guess
> > this means shrinking.
> >
> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >
> > Or could something be setup wrong in SQL2005.
> >
> > Any help is warmly appreciated.
> > Thank you
> > Simon
>|||Tibor thanks
Is there a way of telling what free space is in the log file. I sort of
understand the "bucket" route now :-)
I am guessing that if the log file size was 2GB in size, it does NOT mean
that SQL would use that - so in other words, the bucket may only contain 1GB
of data. Whats the best way of finding out?
Will check the link out as well. I apprecaite that a 3rd party tool is doing
the backup, but it sounds like I may be on the right track - the only concern
I had is the log file does not shrink. but if the query I showed below is
run, then the file is reduced down in size.
Simon
"Tibor Karaszi" wrote:
> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
> by SQL Server if it becomes full and modifications are performed (autogrow).
> So, one reason for large log files is that you never did backup log. You now did it, and that log
> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> can be a valid situation for shrinking the log file. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> > Hello All
> > I should start by saying, I am not a SQL guru at all!
> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> > NetBackup 5.1 MP5 using an online SQL Agent.
> >
> > I will start with the good news, in that all FULL Backups work fine! I have
> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >
> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> > attempted to do this, and it claims it worked fine (the Application log also
> > shows event id 18265 and the description gives an indication "log was backed
> > up Database ect and ending in "No user action is required".
> >
> > Great! but here is the problem. The .ldf files are growing WAY out of
> > control. For example, they can grow to over 40GB in a week !
> >
> > I know there is a query or a SHRINK command that can be used to help, but I
> > am being told that NetBackup should be "truncating" the log down. I guess
> > this means shrinking.
> >
> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >
> > Or could something be setup wrong in SQL2005.
> >
> > Any help is warmly appreciated.
> > Thank you
> > Simon
>|||> Is there a way of telling what free space is in the log file.
Sure.:
DBCC SQLPERF(LOGSPACE)
> I am guessing that if the log file size was 2GB in size, it does NOT mean
> that SQL would use that - so in other words, the bucket may only contain 1GB
> of data. Whats the best way of finding out?
Correct thinking. Use above command.
So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
shrinking the file size. Here's an elaboration on the "bucket" analogy:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
> Tibor thanks
> Is there a way of telling what free space is in the log file. I sort of
> understand the "bucket" route now :-)
> I am guessing that if the log file size was 2GB in size, it does NOT mean
> that SQL would use that - so in other words, the bucket may only contain 1GB
> of data. Whats the best way of finding out?
> Will check the link out as well. I apprecaite that a 3rd party tool is doing
> the backup, but it sounds like I may be on the right track - the only concern
> I had is the log file does not shrink. but if the query I showed below is
> run, then the file is reduced down in size.
> Simon
> "Tibor Karaszi" wrote:
>> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
>> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
>> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in
>> size
>> by SQL Server if it becomes full and modifications are performed (autogrow).
>> So, one reason for large log files is that you never did backup log. You now did it, and that log
>> backup was probably pretty big and you should now have a lot of empty space in the log file. This
>> can be a valid situation for shrinking the log file. See
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
>> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
>> > Hello All
>> > I should start by saying, I am not a SQL guru at all!
>> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
>> > NetBackup 5.1 MP5 using an online SQL Agent.
>> >
>> > I will start with the good news, in that all FULL Backups work fine! I have
>> > discovered that the Transaction logs are NOT included in this FULL Backup.
>> >
>> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
>> > attempted to do this, and it claims it worked fine (the Application log also
>> > shows event id 18265 and the description gives an indication "log was backed
>> > up Database ect and ending in "No user action is required".
>> >
>> > Great! but here is the problem. The .ldf files are growing WAY out of
>> > control. For example, they can grow to over 40GB in a week !
>> >
>> > I know there is a query or a SHRINK command that can be used to help, but I
>> > am being told that NetBackup should be "truncating" the log down. I guess
>> > this means shrinking.
>> >
>> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
>> > 2005 Administrator has to manually shrink the logs or run a query to do this?
>> >
>> > Or could something be setup wrong in SQL2005.
>> >
>> > Any help is warmly appreciated.
>> > Thank you
>> > Simon
>>|||Thank you! I think I understand a bit better now!
Any recommendations on a 2005 SQL book? Microsoft one perhaps or do you have
another recommendation?
Thanks
"Tibor Karaszi" wrote:
> > Is there a way of telling what free space is in the log file.
> Sure.:
> DBCC SQLPERF(LOGSPACE)
>
> > I am guessing that if the log file size was 2GB in size, it does NOT mean
> > that SQL would use that - so in other words, the bucket may only contain 1GB
> > of data. Whats the best way of finding out?
> Correct thinking. Use above command.
> So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
> shrinking the file size. Here's an elaboration on the "bucket" analogy:
> http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
> > Tibor thanks
> > Is there a way of telling what free space is in the log file. I sort of
> > understand the "bucket" route now :-)
> >
> > I am guessing that if the log file size was 2GB in size, it does NOT mean
> > that SQL would use that - so in other words, the bucket may only contain 1GB
> > of data. Whats the best way of finding out?
> >
> > Will check the link out as well. I apprecaite that a 3rd party tool is doing
> > the backup, but it sounds like I may be on the right track - the only concern
> > I had is the log file does not shrink. but if the query I showed below is
> > run, then the file is reduced down in size.
> >
> > Simon
> >
> > "Tibor Karaszi" wrote:
> >
> >> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> >> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> >> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in
> >> size
> >> by SQL Server if it becomes full and modifications are performed (autogrow).
> >>
> >> So, one reason for large log files is that you never did backup log. You now did it, and that log
> >> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> >> can be a valid situation for shrinking the log file. See
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> >>
> >> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> >> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> >> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> >> > Hello All
> >> > I should start by saying, I am not a SQL guru at all!
> >> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> >> > NetBackup 5.1 MP5 using an online SQL Agent.
> >> >
> >> > I will start with the good news, in that all FULL Backups work fine! I have
> >> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >> >
> >> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> >> > attempted to do this, and it claims it worked fine (the Application log also
> >> > shows event id 18265 and the description gives an indication "log was backed
> >> > up Database ect and ending in "No user action is required".
> >> >
> >> > Great! but here is the problem. The .ldf files are growing WAY out of
> >> > control. For example, they can grow to over 40GB in a week !
> >> >
> >> > I know there is a query or a SHRINK command that can be used to help, but I
> >> > am being told that NetBackup should be "truncating" the log down. I guess
> >> > this means shrinking.
> >> >
> >> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> >> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >> >
> >> > Or could something be setup wrong in SQL2005.
> >> >
> >> > Any help is warmly appreciated.
> >> > Thank you
> >> > Simon
> >>
> >>
>|||There are so many books out there. First think about what area you want (admin, architecture, some
specific component, programming etc), then browse at various book sites. I've always liked the
"Inside SQL Server" series from MS Press.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:26E40685-B062-4B78-BAB5-FCCCCCB5D5E8@.microsoft.com...
> Thank you! I think I understand a bit better now!
> Any recommendations on a 2005 SQL book? Microsoft one perhaps or do you have
> another recommendation?
> Thanks
> "Tibor Karaszi" wrote:
>> > Is there a way of telling what free space is in the log file.
>> Sure.:
>> DBCC SQLPERF(LOGSPACE)
>>
>> > I am guessing that if the log file size was 2GB in size, it does NOT mean
>> > that SQL would use that - so in other words, the bucket may only contain 1GB
>> > of data. Whats the best way of finding out?
>> Correct thinking. Use above command.
>> So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
>> shrinking the file size. Here's an elaboration on the "bucket" analogy:
>> http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
>> > Tibor thanks
>> > Is there a way of telling what free space is in the log file. I sort of
>> > understand the "bucket" route now :-)
>> >
>> > I am guessing that if the log file size was 2GB in size, it does NOT mean
>> > that SQL would use that - so in other words, the bucket may only contain 1GB
>> > of data. Whats the best way of finding out?
>> >
>> > Will check the link out as well. I apprecaite that a 3rd party tool is doing
>> > the backup, but it sounds like I may be on the right track - the only concern
>> > I had is the log file does not shrink. but if the query I showed below is
>> > run, then the file is reduced down in size.
>> >
>> > Simon
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
>> >> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
>> >> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow
>> >> in
>> >> size
>> >> by SQL Server if it becomes full and modifications are performed (autogrow).
>> >>
>> >> So, one reason for large log files is that you never did backup log. You now did it, and that
>> >> log
>> >> backup was probably pretty big and you should now have a lot of empty space in the log file.
>> >> This
>> >> can be a valid situation for shrinking the log file. See
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> >>
>> >> But, since you use some 3:rd party tool to do the backup, we don't know what command (like
>> >> BACKUP
>> >> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that
>> >> app.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> >> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
>> >> > Hello All
>> >> > I should start by saying, I am not a SQL guru at all!
>> >> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
>> >> > NetBackup 5.1 MP5 using an online SQL Agent.
>> >> >
>> >> > I will start with the good news, in that all FULL Backups work fine! I have
>> >> > discovered that the Transaction logs are NOT included in this FULL Backup.
>> >> >
>> >> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
>> >> > attempted to do this, and it claims it worked fine (the Application log also
>> >> > shows event id 18265 and the description gives an indication "log was backed
>> >> > up Database ect and ending in "No user action is required".
>> >> >
>> >> > Great! but here is the problem. The .ldf files are growing WAY out of
>> >> > control. For example, they can grow to over 40GB in a week !
>> >> >
>> >> > I know there is a query or a SHRINK command that can be used to help, but I
>> >> > am being told that NetBackup should be "truncating" the log down. I guess
>> >> > this means shrinking.
>> >> >
>> >> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
>> >> > 2005 Administrator has to manually shrink the logs or run a query to do this?
>> >> >
>> >> > Or could something be setup wrong in SQL2005.
>> >> >
>> >> > Any help is warmly appreciated.
>> >> > Thank you
>> >> > Simon
>> >>
>> >>

Saturday, February 25, 2012

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
..
..
..
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!
I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>
|||One thing you might want to consider is to have separate file for db backup vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then apply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain of log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be stored
> in a seperate file. For example today for our server we would have 26 files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

Backups

How do you backup a SQL database so the logs are commited
to the database file? Will a backup using Veritas Backup
Exec do it for us or do I need to use SQL to backup the
product?
Thanks,
ScottAll inactive (not-currently running) transactions will be included in your
db backup, a db backup does not back up the actual log though. Your log will
still have the same footprint size and used space size.
Ray Higdon MCSE, MCDBA, CCNA
--
"spt" <anonymous@.discussions.microsoft.com> wrote in message
news:bd8b01c40c17$295146b0$a601280a@.phx.gbl...
> How do you backup a SQL database so the logs are commited
> to the database file? Will a backup using Veritas Backup
> Exec do it for us or do I need to use SQL to backup the
> product?
> Thanks,
> Scott|||I would like to get some more information before I can address your
concerns.
Any third party product, will use the SQL APIs for Backup. So the first
thing would be to do a GAP analysis of what SQL Server Backup can provide
and what your specific requirements are. For this, I would recommend that
you go through the BooksOnline Backup section that provides all the
information that you would ever need about SQL Server Backup. I would also
suggest that a Backup Plan is of now use without a Disaster Recovery plan.
For that, plese visit the following links :
http://support.microsoft.com/defaul...kb;en-us;169039
http://support.microsoft.com/defaul...kb;en-us;307775
Once you are done with it, you will get a fair idea of what you can achieve
with SQL Backup and what are the GAP areas for which you might want to use
a third party product.
Please let me know if I can be of any further assistance.
Sanchan [MSFT]
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
.
.
.
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>|||One thing you might want to consider is to have separate file for db backup
vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then ap
ply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain o
f log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...seagreen">
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a singl
e
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be store
d
> in a seperate file. For example today for our server we would have 26 fil
es
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one fil
e
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

Backups

When backing up a database in SQL 2000 I get a failure on
the transaction logs. The database is configured
as 'simple' would this expalin the error? Are the
transaction logs only backed up for 'full' databases.yes full and Bulk.
But can you post error?
Yovan Fernandez
"Sarah Scott" <SarahScott_1@.hotmail.com> wrote in message
news:592101c376dd$858790d0$a401280a@.phx.gbl...
> When backing up a database in SQL 2000 I get a failure on
> the transaction logs. The database is configured
> as 'simple' would this expalin the error? Are the
> transaction logs only backed up for 'full' databases.|||Short answers are: 1. Yes. 2. Question is not meaninful
Please have a look in BOL for the different recovery models. This is an
important decision and you should understand the ramifications of the
various options available to you.
"Sarah Scott" <SarahScott_1@.hotmail.com> wrote in message
news:592101c376dd$858790d0$a401280a@.phx.gbl...
> When backing up a database in SQL 2000 I get a failure on
> the transaction logs. The database is configured
> as 'simple' would this expalin the error? Are the
> transaction logs only backed up for 'full' databases.

Friday, February 24, 2012

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
.
.
.
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>|||One thing you might want to consider is to have separate file for db backup vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then apply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain of log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be stored
> in a seperate file. For example today for our server we would have 26 files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

Tuesday, March 20, 2012

BAK, TRN files

BAK, TRN files

Monday, March 19, 2012

bad page ID in MSDB DB

Bad Page error

Bad Page error

Bad Page error

Bad Logs

Bad Logs

Thursday, March 8, 2012

Backups of Logs - Growing way, way out of control & Need Assistanc

Saturday, February 25, 2012

Backups

Backups

Backups

Backups

Friday, February 24, 2012

Backups

Backup to Network Drive

Blog Archive

About Me