Database Unit Testing: explain

Showing posts with label explain. Show all posts

Tuesday, March 27, 2012

Database Snapshots, Mirroring, and Log Shipping

Can someone explain to me how/why you can create a database snapshot
against a mirrored database, but not a log shipped database?Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegroups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>

Database Snapshots, Mirroring, and Log Shipping

Can someone explain to me how/why you can create a database snapshot
against a mirrored database, but not a log shipped database?Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegroups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>

Database Snapshots, Mirroring, and Log Shipping

Can someone explain to me how/why you can create a database snapshot
against a mirrored database, but not a log shipped database?
Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegro ups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>

Database Snapshot Disk IO considerations and Best Practices

I understand the concept of database snapshots, however, I'm unsure how to tackle this from disk utilization point of view.

First off I'll explain how I want to use database snapshots, then the configurations at the disk level I was considering.

Currently in 2005 we can also find Snapshot Isolation whcih helps with concurrency but it will use the tempdb heavily. Since I have enough things hammering the tempdb I was considering using database snapshots to help with the reporting functionality.

Since a database snapshot creates sparse files there will not be a lot of space being consumed (I'll have a question on this one later). Since creating a database snapshot is pretty light weight I was considering creating a job that will snapshot the database every minute (don't freak out yet let me explain). Then I will have another job to drop snapshots that do not have an active transaction in them. I've created a function that will return the latest available snapshot and using some dynamic SQL I can use that in my reporting code.

I've created all the code for this to happen, but now comes the fun part... where do I put the database snapshots?

Currently my databases have seperated array for Log files, seperate array for the clustered indexes, and a seperate array for non-clustered indexes (as well as partitioned tables, BLOB, etc...). So all together my IO is split up across about 32 spindles. I have 3 spindles in a RAID 5 available. Do I created the database snapshot in the same location as the files (because creating a db snapshot is just like creating a real database). Or to I lump them up on the small array, data and logs and all?

Also, if my database is let say 75GB and I want to snapshot it to a 36GB partition I know that the sparse files will be small and it show actual space on disk to be small but logically it will show at 75GB will SQL or the OS have an issue with that?

Thanks

Rich

If there is a possibility to create another drive then use that to store the backup/snapshot files. I'm not sure if you are using EMC then it will be easy to store them inspite of any issue. Why you want to compress the snapshot in this case.|||I'm not looking to compress anything. I'm not sure if I'm being clear but I'm talking about SQL 2005 new ability to create database snapshots, which automatically creates sparse files. We are using an EMC CX3-20 SAN if that helps.

Thursday, March 22, 2012

Database Size

I am building a database and I can not explain the size
it is growing to. I made the size of the database file
100GB. I added 28,000 records with a length of 100
bytes. I added 70,000,000 records with a lenght of 65
bytes. I ran out of space in the database.
I assumed 3 or 4 times the record sizes for overhead when
planning my DB. But I still ran out of room. I have the
backup mode set to simple so the Log file doesn't grow.
Any thoughts would be appreciated.Please post DDL, including indexes. Also, how are you inserting the data?
"DB" <daveblair-nospam-@.adelphia.net> wrote in message
news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
> I am building a database and I can not explain the size
> it is growing to. I made the size of the database file
> 100GB. I added 28,000 records with a length of 100
> bytes. I added 70,000,000 records with a lenght of 65
> bytes. I ran out of space in the database.
> I assumed 3 or 4 times the record sizes for overhead when
> planning my DB. But I still ran out of room. I have the
> backup mode set to simple so the Log file doesn't grow.
> Any thoughts would be appreciated.|||DB,
Hmmm, you should only need a database of around 4-5Gb for that amount of
data. How are you inserting the data?
The log file WILL grow if you are inserting data in large batches. It
needs to grow the log file so that it can roll back the batch if it
fails for some reason (like hardware failure).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DB wrote:
> I am building a database and I can not explain the size
> it is growing to. I made the size of the database file
> 100GB. I added 28,000 records with a length of 100
> bytes. I added 70,000,000 records with a lenght of 65
> bytes. I ran out of space in the database.
> I assumed 3 or 4 times the record sizes for overhead when
> planning my DB. But I still ran out of room. I have the
> backup mode set to simple so the Log file doesn't grow.
> Any thoughts would be appreciated.|||Here is the DDL for the tables. There are no indexes at
this time. The data is inserted by a VB program using an
ADO connection to the database. The general code is:
tblAlias.Open "tblAlias", cn3, adOpenDynamic,
adLockOptimistic
**Start loop for each record to add
tblAlias.AddNew
tblAlias!BTBID = rsBusiness!BTBID
tblAlias!State = rsPrs!State
tblAlias!BusID = rsPrs!BusID
tblAlias!SiteID = rsPrs!SiteID
tblAlias!Company = Trim(rsPrs!Company)
tblAlias!StreetName = Trim(rsBusiness!StreetName)
tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
tblAlias!Zip5 = rsBusiness!Zip5
tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) & Left
(rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) & Left
(rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
& Mid(rsBusiness!Company, 3, 2)
tblAlias!MatchAddress3 = ""
tblAlias.Update
**End loop
DDL for table definitions.
CREATE TABLE [dbo].[tblAlias] (
[BTBID] [int] NOT NULL ,
[BusID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Company] [char] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip3] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip5] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress1] [char] (6) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress2] [char] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MatchAddress3] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblWord] (
[BTBID] [int] NOT NULL ,
[BusID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[State] [char] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Company] [char] (35) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip3] [char] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip5] [char] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Word] [char] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
>--Original Message--
>Please post DDL, including indexes. Also, how are you
inserting the data?
>
>"DB" <daveblair-nospam-@.adelphia.net> wrote in message
>news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
>> I am building a database and I can not explain the size
>> it is growing to. I made the size of the database file
>> 100GB. I added 28,000 records with a length of 100
>> bytes. I added 70,000,000 records with a lenght of 65
>> bytes. I ran out of space in the database.
>> I assumed 3 or 4 times the record sizes for overhead
when
>> planning my DB. But I still ran out of room. I have
the
>> backup mode set to simple so the Log file doesn't grow.
>> Any thoughts would be appreciated.
>
>.
>|||Given that your tables have no indexes and your inserts are being done on a
row-by-row basis, I have no clue how it's possible that 70,000,000 rows of
data grew to over 100gb. Are you certain that no other processes are using
the database in question? Did all 70,000,000 rows get inserted, or did the
process stop before it reached completion?
<anonymous@.discussions.microsoft.com> wrote in message
news:210e101c45a1c$ffefdb70$a101280a@.phx.gbl...
> Here is the DDL for the tables. There are no indexes at
> this time. The data is inserted by a VB program using an
> ADO connection to the database. The general code is:
> tblAlias.Open "tblAlias", cn3, adOpenDynamic,
> adLockOptimistic
> **Start loop for each record to add
> tblAlias.AddNew
> tblAlias!BTBID = rsBusiness!BTBID
> tblAlias!State = rsPrs!State
> tblAlias!BusID = rsPrs!BusID
> tblAlias!SiteID = rsPrs!SiteID
> tblAlias!Company = Trim(rsPrs!Company)
> tblAlias!StreetName = Trim(rsBusiness!StreetName)
> tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
> tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
> tblAlias!Zip5 = rsBusiness!Zip5
> tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) & Left
> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
> tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) & Left
> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company, 1)
> & Mid(rsBusiness!Company, 3, 2)
> tblAlias!MatchAddress3 = ""
> tblAlias.Update
> **End loop
>
> DDL for table definitions.
> CREATE TABLE [dbo].[tblAlias] (
> [BTBID] [int] NOT NULL ,
> [BusID] [int] NOT NULL ,
> [SiteID] [int] NOT NULL ,
> [State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Company] [char] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip3] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip5] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress1] [char] (6) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress2] [char] (9) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MatchAddress3] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblWord] (
> [BTBID] [int] NOT NULL ,
> [BusID] [int] NOT NULL ,
> [SiteID] [int] NOT NULL ,
> [State] [char] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Company] [char] (35) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip3] [char] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Zip5] [char] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Word] [char] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> >--Original Message--
> >Please post DDL, including indexes. Also, how are you
> inserting the data?
> >
> >
> >"DB" <daveblair-nospam-@.adelphia.net> wrote in message
> >news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
> >> I am building a database and I can not explain the size
> >> it is growing to. I made the size of the database file
> >> 100GB. I added 28,000 records with a length of 100
> >> bytes. I added 70,000,000 records with a lenght of 65
> >> bytes. I ran out of space in the database.
> >>
> >> I assumed 3 or 4 times the record sizes for overhead
> when
> >> planning my DB. But I still ran out of room. I have
> the
> >> backup mode set to simple so the Log file doesn't grow.
> >>
> >> Any thoughts would be appreciated.
> >
> >
> >.
> >|||Actually, that's my problem. This is a new stand alone
box. There are no users other than myself. I have
roughly 280,000,000 total records to add to the
database. It halted at the 70,000,000 or so because the
database was full.
I'm completely stumped. While I assume there is
significant storage "overhead" associated with database
records, even 2 or 3 times the size should be less than
20GB.
I have a work around - shrink the DB when it maxes out.
Get all that space back, then resume adding records. A
pain but I'll get there.
Thanks for your efforts.
>--Original Message--
>Given that your tables have no indexes and your inserts
are being done on a
>row-by-row basis, I have no clue how it's possible that
70,000,000 rows of
>data grew to over 100gb. Are you certain that no other
processes are using
>the database in question? Did all 70,000,000 rows get
inserted, or did the
>process stop before it reached completion?
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:210e101c45a1c$ffefdb70$a101280a@.phx.gbl...
>> Here is the DDL for the tables. There are no indexes
at
>> this time. The data is inserted by a VB program using
an
>> ADO connection to the database. The general code is:
>> tblAlias.Open "tblAlias", cn3, adOpenDynamic,
>> adLockOptimistic
>> **Start loop for each record to add
>> tblAlias.AddNew
>> tblAlias!BTBID = rsBusiness!BTBID
>> tblAlias!State = rsPrs!State
>> tblAlias!BusID = rsPrs!BusID
>> tblAlias!SiteID = rsPrs!SiteID
>> tblAlias!Company = Trim(rsPrs!Company)
>> tblAlias!StreetName = Trim(rsBusiness!StreetName)
>> tblAlias!StreetNumber = Trim(rsBusiness!StreetNumber)
>> tblAlias!Zip3 = Left(rsBusiness!Zip5, 3)
>> tblAlias!Zip5 = rsBusiness!Zip5
>> tblAlias!MatchAddress1 = Left(rsBusiness!Zip5, 3) &
Left
>> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company,
1)
>> tblAlias!MatchAddress2 = Left(rsBusiness!Zip5, 3) &
Left
>> (rsBusiness!StreetName, 2) & Left(rsBusiness!Company,
1)
>> & Mid(rsBusiness!Company, 3, 2)
>> tblAlias!MatchAddress3 = ""
>> tblAlias.Update
>> **End loop
>>
>> DDL for table definitions.
>> CREATE TABLE [dbo].[tblAlias] (
>> [BTBID] [int] NOT NULL ,
>> [BusID] [int] NOT NULL ,
>> [SiteID] [int] NOT NULL ,
>> [State] [char] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS
>> NOT NULL ,
>> [Company] [char] (35) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Zip3] [char] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Zip5] [char] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MatchAddress1] [char] (6) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MatchAddress2] [char] (9) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MatchAddress3] [char] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL
>> ) ON [PRIMARY]
>> GO
>>
>> CREATE TABLE [dbo].[tblWord] (
>> [BTBID] [int] NOT NULL ,
>> [BusID] [int] NOT NULL ,
>> [SiteID] [int] NOT NULL ,
>> [State] [char] (2) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Company] [char] (35) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Zip3] [char] (3) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Zip5] [char] (5) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [Word] [char] (15) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL
>> ) ON [PRIMARY]
>> GO
>>
>> >--Original Message--
>> >Please post DDL, including indexes. Also, how are you
>> inserting the data?
>> >
>> >
>> >"DB" <daveblair-nospam-@.adelphia.net> wrote in message
>> >news:209a901c45a06$a1f1f9b0$a601280a@.phx.gbl...
>> >> I am building a database and I can not explain the
size
>> >> it is growing to. I made the size of the database
file
>> >> 100GB. I added 28,000 records with a length of 100
>> >> bytes. I added 70,000,000 records with a lenght of
65
>> >> bytes. I ran out of space in the database.
>> >>
>> >> I assumed 3 or 4 times the record sizes for overhead
>> when
>> >> planning my DB. But I still ran out of room. I
have
>> the
>> >> backup mode set to simple so the Log file doesn't
grow.
>> >>
>> >> Any thoughts would be appreciated.
>> >
>> >
>> >.
>> >
>
>.
>

Wednesday, March 21, 2012

Database Size

Friday, February 24, 2012

Database Recovering Question

Hi
I sometimes see '(Recovering)' written next to one of the sql server
databases. Can anybody please explain on why does that happen and also if
there is any problem with my databases?
TIA
VaibhavViabhav,
If you see these during startup it means that the tranaction log is still
being applied to the database in question.
When the (Recovering) goes away, the recovery is complete. If it is taking
significant time it suggests that the SQL Server was not shutdown cleanly
and that quite a bit of log activity must be processed.
If (Recovering) does not go away, then you have a problem. Otherwise, all
is fine.
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Vaibhav" <consultvaibhav@.yahoo.com> wrote in message
news:Ogv$GdEsDHA.2380@.TK2MSFTNGP09.phx.gbl...
> Hi
> I sometimes see '(Recovering)' written next to one of the sql server
> databases. Can anybody please explain on why does that happen and also if
> there is any problem with my databases?
> TIA
> Vaibhav
>

Tuesday, March 27, 2012

Database Snapshots, Mirroring, and Log Shipping

Database Snapshots, Mirroring, and Log Shipping

Database Snapshots, Mirroring, and Log Shipping

Database Snapshot Disk IO considerations and Best Practices

Thursday, March 22, 2012

Database Size

Database Size

Wednesday, March 21, 2012

Database Size

Friday, February 24, 2012

Database Recovering Question

Database Unit Testing

Blog Archive

About Me