Showing posts with label space. Show all posts
Showing posts with label space. Show all posts

Monday, March 12, 2012

Last allocated extent

Hello,
Is it possible to know the last allocated extent in a datafile? I want to
know this information to know(estimate) how much space will be free when i
shrink one datafile.
Another thing that would like to know is if its possible to shrink one
datafile that initially was created with 10GB and now i want to reduce the
size of this datafile to 3GB.
Thanks and best regardsHi
From BOL:
"The target size for data and log files as calculated by DBCC SHRINKDATABASE
can never be smaller than the minimum size of a file. The minimum size of a
file is the size specified when the file was originally created"
If you created the DB with 10GB, you can's shink it smaller than 10GB.
You are not guaranteed that data is written at the beginning of the data
file and then follows on from that. There might be data extents allocated al
l
over the file.
Regards
Mike
"CC&JM" wrote:

> Hello,
> Is it possible to know the last allocated extent in a datafile? I want to
> know this information to know(estimate) how much space will be free when i
> shrink one datafile.
> Another thing that would like to know is if its possible to shrink one
> datafile that initially was created with 10GB and now i want to reduce the
> size of this datafile to 3GB.
> Thanks and best regards|||Thanks Mike
Best regards
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> From BOL:
> "The target size for data and log files as calculated by DBCC SHRINKDATABA
SE
> can never be smaller than the minimum size of a file. The minimum size of
a
> file is the size specified when the file was originally created"
> If you created the DB with 10GB, you can's shink it smaller than 10GB.
> You are not guaranteed that data is written at the beginning of the data
> file and then follows on from that. There might be data extents allocated
all
> over the file.
> Regards
> Mike
> "CC&JM" wrote:
>|||Also, there's no supported way to find the last allocated extent in a file.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:12334118-BD82-4316-8A82-B62F3AB6A9E7@.microsoft.com...[vbcol=seagreen]
> Thanks Mike
> Best regards
> "Mike Epprecht (SQL MVP)" wrote:
>
SHRINKDATABASE[vbcol=seagreen]
of a[vbcol=seagreen]
allocated all[vbcol=seagreen]
to[vbcol=seagreen]
when i[vbcol=seagreen]
the[vbcol=seagreen]

Last allocated extent

Hello,
Is it possible to know the last allocated extent in a datafile? I want to
know this information to know(estimate) how much space will be free when i
shrink one datafile.
Another thing that would like to know is if its possible to shrink one
datafile that initially was created with 10GB and now i want to reduce the
size of this datafile to 3GB.
Thanks and best regards
Hi
From BOL:
"The target size for data and log files as calculated by DBCC SHRINKDATABASE
can never be smaller than the minimum size of a file. The minimum size of a
file is the size specified when the file was originally created"
If you created the DB with 10GB, you can's shink it smaller than 10GB.
You are not guaranteed that data is written at the beginning of the data
file and then follows on from that. There might be data extents allocated all
over the file.
Regards
Mike
"CC&JM" wrote:

> Hello,
> Is it possible to know the last allocated extent in a datafile? I want to
> know this information to know(estimate) how much space will be free when i
> shrink one datafile.
> Another thing that would like to know is if its possible to shrink one
> datafile that initially was created with 10GB and now i want to reduce the
> size of this datafile to 3GB.
> Thanks and best regards
|||Thanks Mike
Best regards
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> From BOL:
> "The target size for data and log files as calculated by DBCC SHRINKDATABASE
> can never be smaller than the minimum size of a file. The minimum size of a
> file is the size specified when the file was originally created"
> If you created the DB with 10GB, you can's shink it smaller than 10GB.
> You are not guaranteed that data is written at the beginning of the data
> file and then follows on from that. There might be data extents allocated all
> over the file.
> Regards
> Mike
> "CC&JM" wrote:
|||Also, there's no supported way to find the last allocated extent in a file.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:12334118-BD82-4316-8A82-B62F3AB6A9E7@.microsoft.com...[vbcol=seagreen]
> Thanks Mike
> Best regards
> "Mike Epprecht (SQL MVP)" wrote:
SHRINKDATABASE[vbcol=seagreen]
of a[vbcol=seagreen]
allocated all[vbcol=seagreen]
to[vbcol=seagreen]
when i[vbcol=seagreen]
the[vbcol=seagreen]

Last allocated extent

Hello,
Is it possible to know the last allocated extent in a datafile? I want to
know this information to know(estimate) how much space will be free when i
shrink one datafile.
Another thing that would like to know is if its possible to shrink one
datafile that initially was created with 10GB and now i want to reduce the
size of this datafile to 3GB.
Thanks and best regardsHi
From BOL:
"The target size for data and log files as calculated by DBCC SHRINKDATABASE
can never be smaller than the minimum size of a file. The minimum size of a
file is the size specified when the file was originally created"
If you created the DB with 10GB, you can's shink it smaller than 10GB.
You are not guaranteed that data is written at the beginning of the data
file and then follows on from that. There might be data extents allocated all
over the file.
Regards
Mike
"CC&JM" wrote:
> Hello,
> Is it possible to know the last allocated extent in a datafile? I want to
> know this information to know(estimate) how much space will be free when i
> shrink one datafile.
> Another thing that would like to know is if its possible to shrink one
> datafile that initially was created with 10GB and now i want to reduce the
> size of this datafile to 3GB.
> Thanks and best regards|||Thanks Mike
Best regards
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> From BOL:
> "The target size for data and log files as calculated by DBCC SHRINKDATABASE
> can never be smaller than the minimum size of a file. The minimum size of a
> file is the size specified when the file was originally created"
> If you created the DB with 10GB, you can's shink it smaller than 10GB.
> You are not guaranteed that data is written at the beginning of the data
> file and then follows on from that. There might be data extents allocated all
> over the file.
> Regards
> Mike
> "CC&JM" wrote:
> > Hello,
> >
> > Is it possible to know the last allocated extent in a datafile? I want to
> > know this information to know(estimate) how much space will be free when i
> > shrink one datafile.
> >
> > Another thing that would like to know is if its possible to shrink one
> > datafile that initially was created with 10GB and now i want to reduce the
> > size of this datafile to 3GB.
> >
> > Thanks and best regards|||Also, there's no supported way to find the last allocated extent in a file.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:12334118-BD82-4316-8A82-B62F3AB6A9E7@.microsoft.com...
> Thanks Mike
> Best regards
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > From BOL:
> > "The target size for data and log files as calculated by DBCC
SHRINKDATABASE
> > can never be smaller than the minimum size of a file. The minimum size
of a
> > file is the size specified when the file was originally created"
> >
> > If you created the DB with 10GB, you can's shink it smaller than 10GB.
> >
> > You are not guaranteed that data is written at the beginning of the data
> > file and then follows on from that. There might be data extents
allocated all
> > over the file.
> >
> > Regards
> > Mike
> >
> > "CC&JM" wrote:
> >
> > > Hello,
> > >
> > > Is it possible to know the last allocated extent in a datafile? I want
to
> > > know this information to know(estimate) how much space will be free
when i
> > > shrink one datafile.
> > >
> > > Another thing that would like to know is if its possible to shrink one
> > > datafile that initially was created with 10GB and now i want to reduce
the
> > > size of this datafile to 3GB.
> > >
> > > Thanks and best regards

Friday, March 9, 2012

Largest SQL Server 2000 Enterprise Ed. Databases

I like to obtain information on several of the largest
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
MikeHi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evalua.../wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx
.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techin...scalability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx
.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike

Largest SQL Server 2000 Enterprise Ed. Databases

I like to obtain information on several of the largest
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
Mike
Hi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evaluat...wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techinf...calability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike

Largest SQL Server 2000 Enterprise Ed. Databases

I like to obtain information on several of the largest
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
MikeHi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evaluation/compare/wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike

Friday, February 24, 2012

Large Delete & tran log

I have a table that has 110 million records, I will be deleting over 60 million records, but I dont have enough space to hold all the deletes on the trans log file. Is there a way to delete 1 million record then free the trans action then run another delete in one script.Set up simple model for your database and fire your deletes: 1000000 or less records - transaction log will be freed after every delete.

Monday, February 20, 2012

Large insert, how to switch off transactions?

Hello, I've a database with 8 million rows on my development pc and I've got
about 8 GB free disk space.
I have 3000 duplicate records within the table.
I ran a 'distinct' query and put them into a temp table and I am pulling
them back in again but after 2 hours I have ran out of transaction space. I
still have my records in the p# temp table but can't do an sp_rename on it
(probably because it is temporary).
Any way to run an insert like this but not write a transaction log out?
Thanks, Kevin Munro.
My sql is :
select distinct propertyid,pointid,containerid,value
into #p from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #pDo not do it using SELECT ... INTO ..., it consumes a lot of resources. Try:
select propertyid, pointid, containerid, value
into #p from propertieslink where 0 = 1
insert into #p
select distinct propertyid, pointid, containerid, value
from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
-- or
select propertyid,pointid,containerid,value
into #p from propertieslink where 0 = 1
insert into #p
select propertyid, pointid, containerid, value
from propertieslink
group by
propertyid, pointid, containerid, value
having
count(*) > 1
delete
a
from
propertieslink as a
inner join
#p as b
on a.propertyid = b.propertyid
and a.pointid = b.pointid
and a.containerid = b.containerid
and a.value = b.value
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
AMB
"Kevin Munro" wrote:

> Hello, I've a database with 8 million rows on my development pc and I've g
ot
> about 8 GB free disk space.
> I have 3000 duplicate records within the table.
> I ran a 'distinct' query and put them into a temp table and I am pulling
> them back in again but after 2 hours I have ran out of transaction space.
I
> still have my records in the p# temp table but can't do an sp_rename on it
> (probably because it is temporary).
> Any way to run an insert like this but not write a transaction log out?
> Thanks, Kevin Munro.
> My sql is :
> select distinct propertyid,pointid,containerid,value
> into #p from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
>
>|||If you are just trying to get rid of the 3000 duplicates, there are easier
ways to do it.
A clever query with HAVING COUNT(*) would do the trick.
Regards
Mike
"Alejandro Mesa" wrote:
> Do not do it using SELECT ... INTO ..., it consumes a lot of resources. Tr
y:
> select propertyid, pointid, containerid, value
> into #p from propertieslink where 0 = 1
> insert into #p
> select distinct propertyid, pointid, containerid, value
> from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> -- or
> select propertyid,pointid,containerid,value
> into #p from propertieslink where 0 = 1
> insert into #p
> select propertyid, pointid, containerid, value
> from propertieslink
> group by
> propertyid, pointid, containerid, value
> having
> count(*) > 1
> delete
> a
> from
> propertieslink as a
> inner join
> #p as b
> on a.propertyid = b.propertyid
> and a.pointid = b.pointid
> and a.containerid = b.containerid
> and a.value = b.value
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> AMB
>
> "Kevin Munro" wrote:
>|||It was included as a possible solution (second one). Your comment is welcome
anyway.
Thanks,
Alejandro Mesa
"Mike Epprecht (SQL MVP)" wrote:
> If you are just trying to get rid of the 3000 duplicates, there are easier
> ways to do it.
> A clever query with HAVING COUNT(*) would do the trick.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>|||Thanks for these suggestions, the one I thought of would have been ok in a
smaller table and I'll do the HAVING COUNT query.
I got around it by dropping the indexes and it only took 5 minutes. I am
now applying the indexes one at at time and this is somewhat
transactionalising it I think.
Kevin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:590BB8B7-640D-4530-948A-D868A5B42477@.microsoft.com...
> It was included as a possible solution (second one). Your comment is
> welcome
> anyway.
> Thanks,
> Alejandro Mesa
>
> "Mike Epprecht (SQL MVP)" wrote:
>