Database corrupted: reading

Showing posts with label reading. Show all posts

Thursday, March 22, 2012

Database backup strategy questions....

quote:

> I have been reading about the SQL Server transaction log and I'm still
> confused about a lot of things. First of all, what is this file
> actually used for? In the past when backing up my database, I only
> backup and restore the actual database and skip the log file and this
> has worked just fine in getting all my data back. Why do I have to
> backup the log file and when would I ever use it?
> I am having a problem now becuase my log file is over 4 GB for a 50 MB
> database and I'm not sure why. My application is a POS system where
> hundreds of transactions are entered daily and backups are done each
> night of the database but not the log. I can restore the database just
> fine without the log file, what is the advantage of backing up the log
> file also? I can't backup that much data so I'll need to make that
> much smaller if I did back it up. Based on my needs what is the best
> backup strategy and what would I lose by not backing up the log?
> Thanks.

sql

Database backup strategy questions....

I have been reading about the SQL Server transaction log and I'm still
confused about a lot of things. First of all, what is this file
actually used for? In the past when backing up my database, I only
backup and restore the actual database and skip the log file and this
has worked just fine in getting all my data back. Why do I have to
backup the log file and when would I ever use it?
I am having a problem now becuase my log file is over 4 GB for a 50 MB
database and I'm not sure why. My application is a POS system where
hundreds of transactions are entered daily and backups are done each
night of the database but not the log. I can restore the database just
fine without the log file, what is the advantage of backing up the log
file also? I can't backup that much data so I'll need to make that
much smaller if I did back it up. Based on my needs what is the best
backup strategy and what would I lose by not backing up the log?
Thanks.Ray,
When you restore the database file from a Full backup it actually restores
the log file along with it. The Log file holds the transactional
information to ensure consistency within the db and can not be used without
it. The log backups allow you to potentially restore to a particular point
in time under thright conditions. If all you care about is being able to
restore to the last Full backup (potentially loosing all transactions since
that last backup) you should place the database in SIMPLE recovery mode and
it will automatically truncate the log and not grow. See here for more
details:
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly SQL MVP
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0401131950.120fda92@.posting.google.com...
> I have been reading about the SQL Server transaction log and I'm still
> confused about a lot of things. First of all, what is this file
> actually used for? In the past when backing up my database, I only
> backup and restore the actual database and skip the log file and this
> has worked just fine in getting all my data back. Why do I have to
> backup the log file and when would I ever use it?
> I am having a problem now becuase my log file is over 4 GB for a 50 MB
> database and I'm not sure why. My application is a POS system where
> hundreds of transactions are entered daily and backups are done each
> night of the database but not the log. I can restore the database just
> fine without the log file, what is the advantage of backing up the log
> file also? I can't backup that much data so I'll need to make that
> much smaller if I did back it up. Based on my needs what is the best
> backup strategy and what would I lose by not backing up the log?
> Thanks.

Sunday, March 11, 2012

Database and log file size

I've been reading SQL Server High Availability and went thru some recent
webcasts by Kimberly Tripp and from what I've found out, I think we need to
do some adjustments to our database. It is SQL Server 2000 back end for an
ecommerce site. When it was set up, we just went with default options, but
now we need to do some changing. When I did dbcc loginfo, it came back with
275 vlfs. The database size after about a year and a half is 3.9 GB, it says
space available 1.4 GB.
Space Allocated is
Data File 2.4 gb
Transaction log 1.4 GB
It is running in full recovery mode. Transaction logs are backed up every 2
hours. Database is backed up once a day.
I'm afraid that some of these properties are causing slowdowns and deadlocks.
What would be a reasonable size to set the db and transaction log to at this
growth rate? And grow it by what % or MB? I don't really know what to even
start with and I don't want to do something that will make performance worse.
Thanks for any help.
--
JudyJudy,
These settings basically look fine and are probably not associated with
slowdowns and especially not deadlocks. You should always have plenty of
free space in the data and log files. It sounds like the log file is fine
but I can't tell from that how much free space you have in the datafile.
Right click on the database node in EM and choose "view - Task Pad" to see
the breakdown for each file.The growth should be a fixed size in MB. The
size should be small enough so that it can grow in about 10 seconds or less
but not too small that it would grow over and over.
--
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy|||The database shows 2438.75MB allocated with 2372.06MB used.
The transaction log shows .1441.3 MB allocated with 104.9 used after a log
backup an hour and 1/2 previously.
The growth is fixed at 100MB for both database and log files.
--
Judy
"Andrew J. Kelly" wrote:
> Judy,
> These settings basically look fine and are probably not associated with
> slowdowns and especially not deadlocks. You should always have plenty of
> free space in the data and log files. It sounds like the log file is fine
> but I can't tell from that how much free space you have in the datafile.
> Right click on the database node in EM and choose "view - Task Pad" to see
> the breakdown for each file.The growth should be a fixed size in MB. The
> size should be small enough so that it can grow in about 10 seconds or less
> but not too small that it would grow over and over.
> --
> Andrew J. Kelly SQL MVP
>
> "Judy" <Judy@.discussions.microsoft.com> wrote in message
> news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> > I've been reading SQL Server High Availability and went thru some recent
> > webcasts by Kimberly Tripp and from what I've found out, I think we need
> > to
> > do some adjustments to our database. It is SQL Server 2000 back end for an
> > ecommerce site. When it was set up, we just went with default options, but
> > now we need to do some changing. When I did dbcc loginfo, it came back
> > with
> > 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> > says
> > space available 1.4 GB.
> > Space Allocated is
> > Data File 2.4 gb
> > Transaction log 1.4 GB
> >
> > It is running in full recovery mode. Transaction logs are backed up every
> > 2
> > hours. Database is backed up once a day.
> >
> > I'm afraid that some of these properties are causing slowdowns and
> > deadlocks.
> >
> >
> > What would be a reasonable size to set the db and transaction log to at
> > this
> > growth rate? And grow it by what % or MB? I don't really know what to even
> > start with and I don't want to do something that will make performance
> > worse.
> >
> > Thanks for any help.
> > --
> > Judy
>
>|||You will want more free space in the data file. The log seems fine.
--
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:0EF8EC72-769A-4304-8107-36ECB56B465B@.microsoft.com...
> The database shows 2438.75MB allocated with 2372.06MB used.
> The transaction log shows .1441.3 MB allocated with 104.9 used after a log
> backup an hour and 1/2 previously.
> The growth is fixed at 100MB for both database and log files.
> --
> Judy
>
> "Andrew J. Kelly" wrote:
>> Judy,
>> These settings basically look fine and are probably not associated with
>> slowdowns and especially not deadlocks. You should always have plenty of
>> free space in the data and log files. It sounds like the log file is fine
>> but I can't tell from that how much free space you have in the datafile.
>> Right click on the database node in EM and choose "view - Task Pad" to
>> see
>> the breakdown for each file.The growth should be a fixed size in MB. The
>> size should be small enough so that it can grow in about 10 seconds or
>> less
>> but not too small that it would grow over and over.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Judy" <Judy@.discussions.microsoft.com> wrote in message
>> news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
>> > I've been reading SQL Server High Availability and went thru some
>> > recent
>> > webcasts by Kimberly Tripp and from what I've found out, I think we
>> > need
>> > to
>> > do some adjustments to our database. It is SQL Server 2000 back end for
>> > an
>> > ecommerce site. When it was set up, we just went with default options,
>> > but
>> > now we need to do some changing. When I did dbcc loginfo, it came back
>> > with
>> > 275 vlfs. The database size after about a year and a half is 3.9 GB, it
>> > says
>> > space available 1.4 GB.
>> > Space Allocated is
>> > Data File 2.4 gb
>> > Transaction log 1.4 GB
>> >
>> > It is running in full recovery mode. Transaction logs are backed up
>> > every
>> > 2
>> > hours. Database is backed up once a day.
>> >
>> > I'm afraid that some of these properties are causing slowdowns and
>> > deadlocks.
>> >
>> >
>> > What would be a reasonable size to set the db and transaction log to at
>> > this
>> > growth rate? And grow it by what % or MB? I don't really know what to
>> > even
>> > start with and I don't want to do something that will make performance
>> > worse.
>> >
>> > Thanks for any help.
>> > --
>> > Judy
>>|||The idea log size is the smallest that you can make it without it having to
grow before you back it up. If the transaction log really reaches 1.4GB used
before a backup, then that is your ideal size - well...maybe a little bit of
room to spare...like perhaps 2GB.
So to follow Kimberly's suggestion, backup the transcaction log, shrink it,
and then set it's size back to 1.4GB. The idea is that having to grow the
log is what you want to avoid.
As for the database file grow amounts, i personally like the 20% grow
amount. Every time the database grows, 20% allocates a lot of space (A 2MB
file growing by 20% is a lot less space added compared to a 2GB file growing
by 20%)
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy

Database and log file size

Saturday, February 25, 2012

Data warehousing design

Hi

We are starting with designing a datawarehouse for my company. I have done some reading on the concepts and steps involved, but what I am seriously lacking is some examples. I'd like to read through some real examples of data warehouses that worked including the full design diagrams.
Can anyone direct me to some good sites for this?

Thanks,
TeaaZAIf you are building a true data warehouse, then get the books written by Bill Inmon. If you are building a small data mart, then you might consider Kimball's books.

Friday, February 24, 2012

data types

I'm reading Sams TY SQL in 10 minutes and on the examples he uses CHAR for
practically everything, id's, addresses etc.
I upsized a small dbase from access and when I looked at a table, the
datatypes were a lot different. I noticed all ID fields were int and
nvarchar was used on most other fields. In the reserved words glossary, I
didn't see nvarchar. I guess it's missing a few types from the list.
I did a search for dbase schemas on google and found the Duwamish Book
example on MSDN. That dbase also uses nvarchar and int as the upsized access
dbase did.
Just curious why I should use nvarchar instead of char etc.
ThanksThe n in nvarchar indicates that it's for unicode. If you don't plan to use
unicode, you don't need to use nvarchar, and you can use varchar instead.
nvarchar is documented in the SQL Server Books Online.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"James" <no@.email.com> wrote in message
news:%23e1oE4TiDHA.1456@.TK2MSFTNGP11.phx.gbl...
> I'm reading Sams TY SQL in 10 minutes and on the examples he uses CHAR for
> practically everything, id's, addresses etc.
> I upsized a small dbase from access and when I looked at a table, the
> datatypes were a lot different. I noticed all ID fields were int and
> nvarchar was used on most other fields. In the reserved words glossary, I
> didn't see nvarchar. I guess it's missing a few types from the list.
> I did a search for dbase schemas on google and found the Duwamish Book
> example on MSDN. That dbase also uses nvarchar and int as the upsized
access
> dbase did.
> Just curious why I should use nvarchar instead of char etc.
> Thanks
>|||> I upsized a small dbase from access and when I looked at a table, the
> datatypes were a lot different. I noticed all ID fields were int and
> nvarchar was used on most other fields.
You shouldn't be using varchar unless you know you'll need to support
foreign alphabets / unicode characters.
For a description of the (n)(var)char/(n)text datatypes, see
http://www.aspfaq.com/2354
For an exhaustive discussion of differences between Access and SQL Server,
see http://www.aspfaq.com/2214
For some other issues with upsizing, see http://www.aspfaq.com/2182
> In the reserved words glossary,
Of a SAMS book? You should be using Books Online for this kind of thing,
IMHO. Also, see http://www.aspfaq.com/2080|||Thanks

Thursday, March 22, 2012

Database backup strategy questions....

Database backup strategy questions....

Sunday, March 11, 2012

Database and log file size

Database and log file size

Database and log file size

Saturday, February 25, 2012

Data warehousing design

Friday, February 24, 2012

data types

Database corrupted

Blog Archive

About Me