Showing posts with label tocreate. Show all posts
Showing posts with label tocreate. Show all posts

Tuesday, March 27, 2012

Database Snapshot (SQL Server 2005)

Hi,
--
Sorry for posting my question in this group. Isn't Micsrosoft going to
create new forums for SQL2K5?
--
BOL states that the snapshot file(sparse file) is small when it is created,
and gradually grows. But I tried on my databases (even big ones) and its
size is the same as original data files. For example on AdventureWorks, the
sparse file I created took 223mb which is even bigger than the db itself!
Any help would be greatly appreciated.
LeilaRight-click the file in explorer, properties, check "size on disk".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...[
color=darkred]
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to cre
ate new forums for
> SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is created
, and gradually grows.
> But I tried on my databases (even big ones) and its size is the same as or
iginal data files. For
> example on AdventureWorks, the sparse file I created took 223mb which is e
ven bigger than the db
> itself!
> Any help would be greatly appreciated.
> Leila
>[/color]|||That is the way a sparse file works. It appears as large as it can be but
in reality it is only a few bytes to begin with and will grow as it gets
populated. Right click on the file in Explorer and choose properties. You
will see both sizes.
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
I hope not. It's just SQL Server.

> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
>
Sparse files have a logical size and a smaller physical size. You are just
seeing the logical size of the file.
http://msdn.microsoft.com/en-us/library/ms175823.aspx
Look at the available space on your drive before and after creating the
snapshot. You will find that although the file is reported as being 223mb,
the available space on your drive has hardly diminished at all.
David|||And just to add, within SQL you can use fn_virtualfilestats to get the
actual size on disk of a snapshot e.g.
select db_name(DbId) as [Database],
sum(cast(((BytesOnDisk/1024.0)/1024.0) as numeric(25,2))) as [SizeOnDisk_MB]
from fn_virtualfilestats(-1,-1)
group by db_name(DbId)
You should see your snapshot database is a lot smaller than the database
it's based on (initially at least!)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>

Database Snapshot (SQL Server 2005)

Hi,
Sorry for posting my question in this group. Isn't Micsrosoft going to
create new forums for SQL2K5?
BOL states that the snapshot file(sparse file) is small when it is created,
and gradually grows. But I tried on my databases (even big ones) and its
size is the same as original data files. For example on AdventureWorks, the
sparse file I created took 223mb which is even bigger than the db itself!
Any help would be greatly appreciated.
Leila
Right-click the file in explorer, properties, check "size on disk".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to create new forums for
> SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is created, and gradually grows.
> But I tried on my databases (even big ones) and its size is the same as original data files. For
> example on AdventureWorks, the sparse file I created took 223mb which is even bigger than the db
> itself!
> Any help would be greatly appreciated.
> Leila
>
|||That is the way a sparse file works. It appears as large as it can be but
in reality it is only a few bytes to begin with and will grow as it gets
populated. Right click on the file in Explorer and choose properties. You
will see both sizes.
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
I hope not. It's just SQL Server.

> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
>
Sparse files have a logical size and a smaller physical size. You are just
seeing the logical size of the file.
http://msdn.microsoft.com/en-us/library/ms175823.aspx
Look at the available space on your drive before and after creating the
snapshot. You will find that although the file is reported as being 223mb,
the available space on your drive has hardly diminished at all.
David
|||And just to add, within SQL you can use fn_virtualfilestats to get the
actual size on disk of a snapshot e.g.
select db_name(DbId) as [Database],
sum(cast(((BytesOnDisk/1024.0)/1024.0) as numeric(25,2))) as [SizeOnDisk_MB]
from fn_virtualfilestats(-1,-1)
group by db_name(DbId)
You should see your snapshot database is a lot smaller than the database
it's based on (initially at least!)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>

Database Snapshot (SQL Server 2005)

Hi,
Sorry for posting my question in this group. Isn't Micsrosoft going to
create new forums for SQL2K5?
BOL states that the snapshot file(sparse file) is small when it is created,
and gradually grows. But I tried on my databases (even big ones) and its
size is the same as original data files. For example on AdventureWorks, the
sparse file I created took 223mb which is even bigger than the db itself!
Any help would be greatly appreciated.
Leila
Right-click the file in explorer, properties, check "size on disk".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to create new forums for
> SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is created, and gradually grows.
> But I tried on my databases (even big ones) and its size is the same as original data files. For
> example on AdventureWorks, the sparse file I created took 223mb which is even bigger than the db
> itself!
> Any help would be greatly appreciated.
> Leila
>
|||That is the way a sparse file works. It appears as large as it can be but
in reality it is only a few bytes to begin with and will grow as it gets
populated. Right click on the file in Explorer and choose properties. You
will see both sizes.
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>
|||And just to add, within SQL you can use fn_virtualfilestats to get the
actual size on disk of a snapshot e.g.
select db_name(DbId) as [Database],
sum(cast(((BytesOnDisk/1024.0)/1024.0) as numeric(25,2))) as [SizeOnDisk_MB]
from fn_virtualfilestats(-1,-1)
group by db_name(DbId)
You should see your snapshot database is a lot smaller than the database
it's based on (initially at least!)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>

Database Snapshot (SQL Server 2005)

Hi,
--
Sorry for posting my question in this group. Isn't Micsrosoft going to
create new forums for SQL2K5?
--
BOL states that the snapshot file(sparse file) is small when it is created,
and gradually grows. But I tried on my databases (even big ones) and its
size is the same as original data files. For example on AdventureWorks, the
sparse file I created took 223mb which is even bigger than the db itself!
Any help would be greatly appreciated.
LeilaRight-click the file in explorer, properties, check "size on disk".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...[v
bcol=seagreen]
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to cre
ate new forums for
> SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is created
, and gradually grows.
> But I tried on my databases (even big ones) and its size is the same as or
iginal data files. For
> example on AdventureWorks, the sparse file I created took 223mb which is e
ven bigger than the db
> itself!
> Any help would be greatly appreciated.
> Leila
>[/vbcol]|||That is the way a sparse file works. It appears as large as it can be but
in reality it is only a few bytes to begin with and will grow as it gets
populated. Right click on the file in Explorer and choose properties. You
will see both sizes.
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
I hope not. It's just SQL Server.

> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
>
Sparse files have a logical size and a smaller physical size. You are just
seeing the logical size of the file.
http://msdn.microsoft.com/en-us/library/ms175823.aspx
Look at the available space on your drive before and after creating the
snapshot. You will find that although the file is reported as being 223mb,
the available space on your drive has hardly diminished at all.
David|||And just to add, within SQL you can use fn_virtualfilestats to get the
actual size on disk of a snapshot e.g.
select db_name(DbId) as [Database],
sum(cast(((BytesOnDisk/1024.0)/1024.0) as numeric(25,2))) as [SizeOnDisk
_MB]
from fn_virtualfilestats(-1,-1)
group by db_name(DbId)
You should see your snapshot database is a lot smaller than the database
it's based on (initially at least!)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:usZ5Tdr5FHA.2576@.TK2MSFTNGP10.phx.gbl...
> Hi,
> --
> Sorry for posting my question in this group. Isn't Micsrosoft going to
> create new forums for SQL2K5?
> --
> BOL states that the snapshot file(sparse file) is small when it is
> created, and gradually grows. But I tried on my databases (even big ones)
> and its size is the same as original data files. For example on
> AdventureWorks, the sparse file I created took 223mb which is even bigger
> than the db itself!
> Any help would be greatly appreciated.
> Leila
>

Thursday, March 22, 2012

database size and design questions

I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techin...scalability.asp
GAJ|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegroups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.

> Any input is greatly appreciated.
> Thanks
> Roger
>

database size and design questions

I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.
1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?
2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.
Any input is greatly appreciated.
Thanks
Roger
1st of all, your application folks dont know what they'r talking about.
You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)
If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.
Multiple databases on the same server is not a bad option at all.
further, you should look at this site and learn about large Databases and
maintenance, etc:
Cheers
Greg Jackson
PDX, Oregon
|||man I was so aggravated, I forgot to paste my link
http://www.microsoft.com/sql/techinf...calability.asp
GAJ
|||Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Thanks for link Greg...i appreciate it
|||> cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.
Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.
Let the developers write the code. This is why you have database people on
staff. :-)
|||AMEN My Brother...!
Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.
but I'm not going to even gonna go down that road.
I like Aarons solution much better anyway.
GAJ
|||"sql rookie" <anytasks@.gmail.com> wrote in message
news:1112294686.285175.58040@.o13g2000cwo.googlegro ups.com...
> 2. If I create this huge database, how can i do maintainance on it ? Is
> there a way, I can backup quickly. My guestimate for backing up a 250
> GB database is around 1-2 hrs, which is not feasible.
Since no on addressed this:
Backup time should not generally be the criteria here. Recovery time should
be.
As you can do online backups, you can do backups w/o downtime.
Moreover, you can do other things to help with recovery.
Look at filegroup backups... i.e. backup only parts of the DB and recover
parts as required. (BTW, SQL 2005 Enterprise handles this in a BEAUTIFUL
manner...)
Also look at perhaps a weekly full backup and then daily differentials with
transaction log backups as required.

> Any input is greatly appreciated.
> Thanks
> Roger
>