Greetings All
I'm new to SQL. I'm using Standard edition (SP3)
I have recently had to sort out the import of a Unisys dbs into our SQL
server. This dbs needs to be imported a minimum of 3 times per week. It won'
t
overwrite an existing dbs so the only way I can think to do this is by
deleting the existing dbs before the scheduled import. I know Unisys can
provide a componant that allows synchronisation but as with everything that
costs mucho money.
Is there a was to delete the existing dbs automatically so that the dbs will
be imported through Data Transformation Services, local packages?
Or am I, due to my lack of knowledge going about this the wrong way.
Andy
--
Ah! thats what pushing the button does.!!!I don't know what a dbs is. Is it a table? And entire database of
many tables?
It is possible for DTS to delete the rows in a table before importing
new data. In the DTS wizard click on the Transform column to find the
option, to the right of the targe table you select. Save the package.
Roy
On Tue, 25 Jul 2006 08:18:01 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Greetings All
>I'm new to SQL. I'm using Standard edition (SP3)
>I have recently had to sort out the import of a Unisys dbs into our SQL
>server. This dbs needs to be imported a minimum of 3 times per week. It won
't
>overwrite an existing dbs so the only way I can think to do this is by
>deleting the existing dbs before the scheduled import. I know Unisys can
>provide a componant that allows synchronisation but as with everything that
>costs mucho money.
>Is there a was to delete the existing dbs automatically so that the dbs wil
l
>be imported through Data Transformation Services, local packages?
>Or am I, due to my lack of knowledge going about this the wrong way.
>Andy|||Thanks Roy
dbs = database (Sorry for confusing you)
The problem is we have at least 150 tables.
A coleague found the "Drop" command .. do you know if this could be used?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> I don't know what a dbs is. Is it a table? And entire database of
> many tables?
> It is possible for DTS to delete the rows in a table before importing
> new data. In the DTS wizard click on the Transform column to find the
> option, to the right of the targe table you select. Save the package.
> Roy
>
> On Tue, 25 Jul 2006 08:18:01 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>|||Are you figuring on...
Ddropping and re-creating the database?
Dropping and recreating the tables?
Truncating the tables and reloading the data?
What tool are you using for the load? Something from Unisys? Have
you already gone through the process the first time, and just trying
to make it run three times a week? Or is that first load still ahead
of you?
In general, DROP will let you drop a database or a table. TRUNCATE or
DELETE will let you remove the data from a table.
I have always found that the table definitions generated by automatic
load tools leave something - usually a great deal - to be desired.
Having all the character columns end up as NVARCHAR(4000), and all the
numbers end up as FLOAT, is not my idea of good data type choices. So
what I generally do is load the first time using whatever tool builds
the tables, then script the tables and fix the definiions. Those new
table definiions replace the first versions of tables, and I then
rework the load process to load into existing tables rather than
creating them.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 06:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Thanks Roy
>dbs = database (Sorry for confusing you)
>The problem is we have at least 150 tables.
>A coleague found the "Drop" command .. do you know if this could be used?
>Andy|||Well we have successfully done a copy already and found that if we wanted to
keep the database uptodate we needed to copy 3 times per week (for our
purposes) If the database exists it fails to copy. So we had looked at
deleting manually but then someone suggested that if we could incorporate
this in the import then that would solve it all in one go.
Basically I'm just trying to achive a copy of the Unisys database to the SQL
server using DTS on a schedual.
As for the table definitions I have a colleague who may have the skills
required. If we achive this can the import into existing tables be done via
DTS?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> Are you figuring on...
> Ddropping and re-creating the database?
> Dropping and recreating the tables?
> Truncating the tables and reloading the data?
> What tool are you using for the load? Something from Unisys? Have
> you already gone through the process the first time, and just trying
> to make it run three times a week? Or is that first load still ahead
> of you?
> In general, DROP will let you drop a database or a table. TRUNCATE or
> DELETE will let you remove the data from a table.
> I have always found that the table definitions generated by automatic
> load tools leave something - usually a great deal - to be desired.
> Having all the character columns end up as NVARCHAR(4000), and all the
> numbers end up as FLOAT, is not my idea of good data type choices. So
> what I generally do is load the first time using whatever tool builds
> the tables, then script the tables and fix the definiions. Those new
> table definiions replace the first versions of tables, and I then
> rework the load process to load into existing tables rather than
> creating them.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 06:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>|||If you have already loaded the data onece you are well along.
Yes, import to existing tables can be done using DTS. If you start
with the import wizard, there is a spot where you can choose an
existing table (the Destination column), and next to that (the
Transform column) specify that the existing data in the table is to be
deleted before the fresh data is loaded. I generally use the wizard
to get me started, telling it to save the package, then edit the
package as required.
Once choice you have to make is whether to make one huge DTS package,
an individual package for each table, or something in between.
Combining all the tables into one package is a bit less work to set
up, perhaps, and simpler to schedule, but has major shortocomings when
you need to run just one bit. Also, the tools for editing packages,
with the graphical representation of the objects, isn't the greatest
for working with large numbers of objects. So I would be inclined
toward an individual package for each table. A compromise would be to
use individual packages for large or problematic tables, and then
group the rest of the tables into logical sets, a package for each.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 07:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Well we have successfully done a copy already and found that if we wanted t
o
>keep the database uptodate we needed to copy 3 times per week (for our
>purposes) If the database exists it fails to copy. So we had looked at
>deleting manually but then someone suggested that if we could incorporate
>this in the import then that would solve it all in one go.
>Basically I'm just trying to achive a copy of the Unisys database to the SQ
L
>server using DTS on a schedual.
>As for the table definitions I have a colleague who may have the skills
>required. If we achive this can the import into existing tables be done via
>DTS?
>Andy|||Thanks very much for this Roy. I apriciate you taking the time to expliain
it. I think I'll follow your guidence here and although more work initialy,
do seperate packages. I'll see how it goes
Thank you once again
Andy
"Roy Harvey" wrote:
> If you have already loaded the data onece you are well along.
> Yes, import to existing tables can be done using DTS. If you start
> with the import wizard, there is a spot where you can choose an
> existing table (the Destination column), and next to that (the
> Transform column) specify that the existing data in the table is to be
> deleted before the fresh data is loaded. I generally use the wizard
> to get me started, telling it to save the package, then edit the
> package as required.
> Once choice you have to make is whether to make one huge DTS package,
> an individual package for each table, or something in between.
> Combining all the tables into one package is a bit less work to set
> up, perhaps, and simpler to schedule, but has major shortocomings when
> you need to run just one bit. Also, the tools for editing packages,
> with the graphical representation of the objects, isn't the greatest
> for working with large numbers of objects. So I would be inclined
> toward an individual package for each table. A compromise would be to
> use individual packages for large or problematic tables, and then
> group the rest of the tables into logical sets, a package for each.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 07:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
>
>
Showing posts with label dbs. Show all posts
Showing posts with label dbs. Show all posts
Sunday, March 11, 2012
Automatic deletion to allow import
Greetings All
I'm new to SQL. I'm using Standard edition (SP3)
I have recently had to sort out the import of a Unisys dbs into our SQL
server. This dbs needs to be imported a minimum of 3 times per week. It won't
overwrite an existing dbs so the only way I can think to do this is by
deleting the existing dbs before the scheduled import. I know Unisys can
provide a componant that allows synchronisation but as with everything that
costs mucho money.
Is there a was to delete the existing dbs automatically so that the dbs will
be imported through Data Transformation Services, local packages?
Or am I, due to my lack of knowledge going about this the wrong way.
Andy
--
Ah! thats what pushing the button does.!!!I don't know what a dbs is. Is it a table? And entire database of
many tables?
It is possible for DTS to delete the rows in a table before importing
new data. In the DTS wizard click on the Transform column to find the
option, to the right of the targe table you select. Save the package.
Roy
On Tue, 25 Jul 2006 08:18:01 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Greetings All
>I'm new to SQL. I'm using Standard edition (SP3)
>I have recently had to sort out the import of a Unisys dbs into our SQL
>server. This dbs needs to be imported a minimum of 3 times per week. It won't
>overwrite an existing dbs so the only way I can think to do this is by
>deleting the existing dbs before the scheduled import. I know Unisys can
>provide a componant that allows synchronisation but as with everything that
>costs mucho money.
>Is there a was to delete the existing dbs automatically so that the dbs will
>be imported through Data Transformation Services, local packages?
>Or am I, due to my lack of knowledge going about this the wrong way.
>Andy|||Thanks Roy
dbs = database (Sorry for confusing you)
The problem is we have at least 150 tables.
A coleague found the "Drop" command .. do you know if this could be used?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> I don't know what a dbs is. Is it a table? And entire database of
> many tables?
> It is possible for DTS to delete the rows in a table before importing
> new data. In the DTS wizard click on the Transform column to find the
> option, to the right of the targe table you select. Save the package.
> Roy
>
> On Tue, 25 Jul 2006 08:18:01 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Greetings All
> >
> >I'm new to SQL. I'm using Standard edition (SP3)
> >I have recently had to sort out the import of a Unisys dbs into our SQL
> >server. This dbs needs to be imported a minimum of 3 times per week. It won't
> >overwrite an existing dbs so the only way I can think to do this is by
> >deleting the existing dbs before the scheduled import. I know Unisys can
> >provide a componant that allows synchronisation but as with everything that
> >costs mucho money.
> >
> >Is there a was to delete the existing dbs automatically so that the dbs will
> >be imported through Data Transformation Services, local packages?
> >
> >Or am I, due to my lack of knowledge going about this the wrong way.
> >
> >Andy
>|||Are you figuring on...
Ddropping and re-creating the database?
Dropping and recreating the tables?
Truncating the tables and reloading the data?
What tool are you using for the load? Something from Unisys? Have
you already gone through the process the first time, and just trying
to make it run three times a week? Or is that first load still ahead
of you?
In general, DROP will let you drop a database or a table. TRUNCATE or
DELETE will let you remove the data from a table.
I have always found that the table definitions generated by automatic
load tools leave something - usually a great deal - to be desired.
Having all the character columns end up as NVARCHAR(4000), and all the
numbers end up as FLOAT, is not my idea of good data type choices. So
what I generally do is load the first time using whatever tool builds
the tables, then script the tables and fix the definiions. Those new
table definiions replace the first versions of tables, and I then
rework the load process to load into existing tables rather than
creating them.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 06:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Thanks Roy
>dbs = database (Sorry for confusing you)
>The problem is we have at least 150 tables.
>A coleague found the "Drop" command .. do you know if this could be used?
>Andy|||Well we have successfully done a copy already and found that if we wanted to
keep the database uptodate we needed to copy 3 times per week (for our
purposes) If the database exists it fails to copy. So we had looked at
deleting manually but then someone suggested that if we could incorporate
this in the import then that would solve it all in one go.
Basically I'm just trying to achive a copy of the Unisys database to the SQL
server using DTS on a schedual.
As for the table definitions I have a colleague who may have the skills
required. If we achive this can the import into existing tables be done via
DTS?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> Are you figuring on...
> Ddropping and re-creating the database?
> Dropping and recreating the tables?
> Truncating the tables and reloading the data?
> What tool are you using for the load? Something from Unisys? Have
> you already gone through the process the first time, and just trying
> to make it run three times a week? Or is that first load still ahead
> of you?
> In general, DROP will let you drop a database or a table. TRUNCATE or
> DELETE will let you remove the data from a table.
> I have always found that the table definitions generated by automatic
> load tools leave something - usually a great deal - to be desired.
> Having all the character columns end up as NVARCHAR(4000), and all the
> numbers end up as FLOAT, is not my idea of good data type choices. So
> what I generally do is load the first time using whatever tool builds
> the tables, then script the tables and fix the definiions. Those new
> table definiions replace the first versions of tables, and I then
> rework the load process to load into existing tables rather than
> creating them.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 06:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Thanks Roy
> >
> >dbs = database (Sorry for confusing you)
> >
> >The problem is we have at least 150 tables.
> >A coleague found the "Drop" command .. do you know if this could be used?
> >
> >Andy
>|||If you have already loaded the data onece you are well along.
Yes, import to existing tables can be done using DTS. If you start
with the import wizard, there is a spot where you can choose an
existing table (the Destination column), and next to that (the
Transform column) specify that the existing data in the table is to be
deleted before the fresh data is loaded. I generally use the wizard
to get me started, telling it to save the package, then edit the
package as required.
Once choice you have to make is whether to make one huge DTS package,
an individual package for each table, or something in between.
Combining all the tables into one package is a bit less work to set
up, perhaps, and simpler to schedule, but has major shortocomings when
you need to run just one bit. Also, the tools for editing packages,
with the graphical representation of the objects, isn't the greatest
for working with large numbers of objects. So I would be inclined
toward an individual package for each table. A compromise would be to
use individual packages for large or problematic tables, and then
group the rest of the tables into logical sets, a package for each.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 07:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Well we have successfully done a copy already and found that if we wanted to
>keep the database uptodate we needed to copy 3 times per week (for our
>purposes) If the database exists it fails to copy. So we had looked at
>deleting manually but then someone suggested that if we could incorporate
>this in the import then that would solve it all in one go.
>Basically I'm just trying to achive a copy of the Unisys database to the SQL
>server using DTS on a schedual.
>As for the table definitions I have a colleague who may have the skills
>required. If we achive this can the import into existing tables be done via
>DTS?
>Andy|||Thanks very much for this Roy. I apriciate you taking the time to expliain
it. I think I'll follow your guidence here and although more work initialy,
do seperate packages. I'll see how it goes
Thank you once again
Andy
"Roy Harvey" wrote:
> If you have already loaded the data onece you are well along.
> Yes, import to existing tables can be done using DTS. If you start
> with the import wizard, there is a spot where you can choose an
> existing table (the Destination column), and next to that (the
> Transform column) specify that the existing data in the table is to be
> deleted before the fresh data is loaded. I generally use the wizard
> to get me started, telling it to save the package, then edit the
> package as required.
> Once choice you have to make is whether to make one huge DTS package,
> an individual package for each table, or something in between.
> Combining all the tables into one package is a bit less work to set
> up, perhaps, and simpler to schedule, but has major shortocomings when
> you need to run just one bit. Also, the tools for editing packages,
> with the graphical representation of the objects, isn't the greatest
> for working with large numbers of objects. So I would be inclined
> toward an individual package for each table. A compromise would be to
> use individual packages for large or problematic tables, and then
> group the rest of the tables into logical sets, a package for each.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 07:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Well we have successfully done a copy already and found that if we wanted to
> >keep the database uptodate we needed to copy 3 times per week (for our
> >purposes) If the database exists it fails to copy. So we had looked at
> >deleting manually but then someone suggested that if we could incorporate
> >this in the import then that would solve it all in one go.
> >
> >Basically I'm just trying to achive a copy of the Unisys database to the SQL
> >server using DTS on a schedual.
> >
> >As for the table definitions I have a colleague who may have the skills
> >required. If we achive this can the import into existing tables be done via
> >DTS?
> >
> >Andy
>
I'm new to SQL. I'm using Standard edition (SP3)
I have recently had to sort out the import of a Unisys dbs into our SQL
server. This dbs needs to be imported a minimum of 3 times per week. It won't
overwrite an existing dbs so the only way I can think to do this is by
deleting the existing dbs before the scheduled import. I know Unisys can
provide a componant that allows synchronisation but as with everything that
costs mucho money.
Is there a was to delete the existing dbs automatically so that the dbs will
be imported through Data Transformation Services, local packages?
Or am I, due to my lack of knowledge going about this the wrong way.
Andy
--
Ah! thats what pushing the button does.!!!I don't know what a dbs is. Is it a table? And entire database of
many tables?
It is possible for DTS to delete the rows in a table before importing
new data. In the DTS wizard click on the Transform column to find the
option, to the right of the targe table you select. Save the package.
Roy
On Tue, 25 Jul 2006 08:18:01 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Greetings All
>I'm new to SQL. I'm using Standard edition (SP3)
>I have recently had to sort out the import of a Unisys dbs into our SQL
>server. This dbs needs to be imported a minimum of 3 times per week. It won't
>overwrite an existing dbs so the only way I can think to do this is by
>deleting the existing dbs before the scheduled import. I know Unisys can
>provide a componant that allows synchronisation but as with everything that
>costs mucho money.
>Is there a was to delete the existing dbs automatically so that the dbs will
>be imported through Data Transformation Services, local packages?
>Or am I, due to my lack of knowledge going about this the wrong way.
>Andy|||Thanks Roy
dbs = database (Sorry for confusing you)
The problem is we have at least 150 tables.
A coleague found the "Drop" command .. do you know if this could be used?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> I don't know what a dbs is. Is it a table? And entire database of
> many tables?
> It is possible for DTS to delete the rows in a table before importing
> new data. In the DTS wizard click on the Transform column to find the
> option, to the right of the targe table you select. Save the package.
> Roy
>
> On Tue, 25 Jul 2006 08:18:01 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Greetings All
> >
> >I'm new to SQL. I'm using Standard edition (SP3)
> >I have recently had to sort out the import of a Unisys dbs into our SQL
> >server. This dbs needs to be imported a minimum of 3 times per week. It won't
> >overwrite an existing dbs so the only way I can think to do this is by
> >deleting the existing dbs before the scheduled import. I know Unisys can
> >provide a componant that allows synchronisation but as with everything that
> >costs mucho money.
> >
> >Is there a was to delete the existing dbs automatically so that the dbs will
> >be imported through Data Transformation Services, local packages?
> >
> >Or am I, due to my lack of knowledge going about this the wrong way.
> >
> >Andy
>|||Are you figuring on...
Ddropping and re-creating the database?
Dropping and recreating the tables?
Truncating the tables and reloading the data?
What tool are you using for the load? Something from Unisys? Have
you already gone through the process the first time, and just trying
to make it run three times a week? Or is that first load still ahead
of you?
In general, DROP will let you drop a database or a table. TRUNCATE or
DELETE will let you remove the data from a table.
I have always found that the table definitions generated by automatic
load tools leave something - usually a great deal - to be desired.
Having all the character columns end up as NVARCHAR(4000), and all the
numbers end up as FLOAT, is not my idea of good data type choices. So
what I generally do is load the first time using whatever tool builds
the tables, then script the tables and fix the definiions. Those new
table definiions replace the first versions of tables, and I then
rework the load process to load into existing tables rather than
creating them.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 06:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Thanks Roy
>dbs = database (Sorry for confusing you)
>The problem is we have at least 150 tables.
>A coleague found the "Drop" command .. do you know if this could be used?
>Andy|||Well we have successfully done a copy already and found that if we wanted to
keep the database uptodate we needed to copy 3 times per week (for our
purposes) If the database exists it fails to copy. So we had looked at
deleting manually but then someone suggested that if we could incorporate
this in the import then that would solve it all in one go.
Basically I'm just trying to achive a copy of the Unisys database to the SQL
server using DTS on a schedual.
As for the table definitions I have a colleague who may have the skills
required. If we achive this can the import into existing tables be done via
DTS?
Andy
--
Ah! thats what pushing the button does.!!!
"Roy Harvey" wrote:
> Are you figuring on...
> Ddropping and re-creating the database?
> Dropping and recreating the tables?
> Truncating the tables and reloading the data?
> What tool are you using for the load? Something from Unisys? Have
> you already gone through the process the first time, and just trying
> to make it run three times a week? Or is that first load still ahead
> of you?
> In general, DROP will let you drop a database or a table. TRUNCATE or
> DELETE will let you remove the data from a table.
> I have always found that the table definitions generated by automatic
> load tools leave something - usually a great deal - to be desired.
> Having all the character columns end up as NVARCHAR(4000), and all the
> numbers end up as FLOAT, is not my idea of good data type choices. So
> what I generally do is load the first time using whatever tool builds
> the tables, then script the tables and fix the definiions. Those new
> table definiions replace the first versions of tables, and I then
> rework the load process to load into existing tables rather than
> creating them.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 06:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Thanks Roy
> >
> >dbs = database (Sorry for confusing you)
> >
> >The problem is we have at least 150 tables.
> >A coleague found the "Drop" command .. do you know if this could be used?
> >
> >Andy
>|||If you have already loaded the data onece you are well along.
Yes, import to existing tables can be done using DTS. If you start
with the import wizard, there is a spot where you can choose an
existing table (the Destination column), and next to that (the
Transform column) specify that the existing data in the table is to be
deleted before the fresh data is loaded. I generally use the wizard
to get me started, telling it to save the package, then edit the
package as required.
Once choice you have to make is whether to make one huge DTS package,
an individual package for each table, or something in between.
Combining all the tables into one package is a bit less work to set
up, perhaps, and simpler to schedule, but has major shortocomings when
you need to run just one bit. Also, the tools for editing packages,
with the graphical representation of the objects, isn't the greatest
for working with large numbers of objects. So I would be inclined
toward an individual package for each table. A compromise would be to
use individual packages for large or problematic tables, and then
group the rest of the tables into logical sets, a package for each.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Jul 2006 07:28:02 -0700, sage
<sage@.discussions.microsoft.com> wrote:
>Well we have successfully done a copy already and found that if we wanted to
>keep the database uptodate we needed to copy 3 times per week (for our
>purposes) If the database exists it fails to copy. So we had looked at
>deleting manually but then someone suggested that if we could incorporate
>this in the import then that would solve it all in one go.
>Basically I'm just trying to achive a copy of the Unisys database to the SQL
>server using DTS on a schedual.
>As for the table definitions I have a colleague who may have the skills
>required. If we achive this can the import into existing tables be done via
>DTS?
>Andy|||Thanks very much for this Roy. I apriciate you taking the time to expliain
it. I think I'll follow your guidence here and although more work initialy,
do seperate packages. I'll see how it goes
Thank you once again
Andy
"Roy Harvey" wrote:
> If you have already loaded the data onece you are well along.
> Yes, import to existing tables can be done using DTS. If you start
> with the import wizard, there is a spot where you can choose an
> existing table (the Destination column), and next to that (the
> Transform column) specify that the existing data in the table is to be
> deleted before the fresh data is loaded. I generally use the wizard
> to get me started, telling it to save the package, then edit the
> package as required.
> Once choice you have to make is whether to make one huge DTS package,
> an individual package for each table, or something in between.
> Combining all the tables into one package is a bit less work to set
> up, perhaps, and simpler to schedule, but has major shortocomings when
> you need to run just one bit. Also, the tools for editing packages,
> with the graphical representation of the objects, isn't the greatest
> for working with large numbers of objects. So I would be inclined
> toward an individual package for each table. A compromise would be to
> use individual packages for large or problematic tables, and then
> group the rest of the tables into logical sets, a package for each.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 26 Jul 2006 07:28:02 -0700, sage
> <sage@.discussions.microsoft.com> wrote:
> >Well we have successfully done a copy already and found that if we wanted to
> >keep the database uptodate we needed to copy 3 times per week (for our
> >purposes) If the database exists it fails to copy. So we had looked at
> >deleting manually but then someone suggested that if we could incorporate
> >this in the import then that would solve it all in one go.
> >
> >Basically I'm just trying to achive a copy of the Unisys database to the SQL
> >server using DTS on a schedual.
> >
> >As for the table definitions I have a colleague who may have the skills
> >required. If we achive this can the import into existing tables be done via
> >DTS?
> >
> >Andy
>
Saturday, February 25, 2012
Autogrowth values keep changing
Hi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...
John
|||Not a helpful reply, it can and it does.
I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.
This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.
This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.
|||Yes this is quite serious. It stops our ETL-process because of the drive being full.
Nothing in our tracelog yet...
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
|||Thanks!
BR John
|||HowardRichards wrote:
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
I was not aware of this bug. Thanks for the correction.|||
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!
Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.
|||Yes - we've been hit by this as well.
We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.
The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!
So you need to change it using the alter database statement:
alter database dbname modify file (name = filename, filegrowth = 10%)
Roll on SP2...
|||We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.
When attempting to change the value, the following error appears:
Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)
from the SQL Server 2005 SP2 fix list:
Can anyone advise if running this statement will correct this issue permanently?
alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)
Thanks!
|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.Autogrowth values keep changing
Hi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...
John
|||Not a helpful reply, it can and it does.
I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.
This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.
This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.
|||Yes this is quite serious. It stops our ETL-process because of the drive being full.
Nothing in our tracelog yet...
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
|||Thanks!
BR John
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
I was not aware of this bug. Thanks for the correction.|||
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!
Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.
|||Yes - we've been hit by this as well.
We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.
The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!
So you need to change it using the alter database statement:
alter database dbname modify file (name = filename, filegrowth = 10%)
Roll on SP2...
|||We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.
When attempting to change the value, the following error appears:
Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)
from the SQL Server 2005 SP2 fix list:
Can anyone advise if running this statement will correct this issue permanently?
alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)
Thanks!
|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.Autogrowth values keep changing
Hi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...
John
|||Not a helpful reply, it can and it does.
I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.
This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.
This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.
|||Yes this is quite serious. It stops our ETL-process because of the drive being full.
Nothing in our tracelog yet...
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
|||Thanks!
BR John
|||HowardRichards wrote:
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
I was not aware of this bug. Thanks for the correction.|||
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!
Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.
|||Yes - we've been hit by this as well.
We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.
The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!
So you need to change it using the alter database statement:
alter database dbname modify file (name = filename, filegrowth = 10%)
Roll on SP2...
|||We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.
When attempting to change the value, the following error appears:
Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)
from the SQL Server 2005 SP2 fix list:
Can anyone advise if running this statement will correct this issue permanently?
alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)
Thanks!
|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.Autogrowth values keep changing
Hi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...
John
|||Not a helpful reply, it can and it does.
I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.
This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.
This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.
|||Yes this is quite serious. It stops our ETL-process because of the drive being full.
Nothing in our tracelog yet...
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
|||Thanks!
BR John
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
I was not aware of this bug. Thanks for the correction.|||
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!
Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.
|||Yes - we've been hit by this as well.
We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.
The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!
So you need to change it using the alter database statement:
alter database dbname modify file (name = filename, filegrowth = 10%)
Roll on SP2...
|||We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.
When attempting to change the value, the following error appears:
Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)
from the SQL Server 2005 SP2 fix list:
Can anyone advise if running this statement will correct this issue permanently?
alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)
Thanks!
|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.Autogrowth values keep changing
Hi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
The value does not change on its own. Someone/process must be changing this. Perhaps, you should turn on audit to see who's responsible.|||Just did it. Created a trace with the SQL Profiler to catch the SQL Batch-events. Now we just have to wait it out...
John
|||Not a helpful reply, it can and it does.
I have now experienced the same bug myself. SQL 2005 on x64, has been running okay for months. Last month the database jumped from 3Gb to something like 60Gb, I found the autogrowth setting had switched from xxMB to 2048%. I thought I must have done this by mistake, so I shrank the DB and reset the autogrowth values.
This morning I log in, database is now 204GB and the autogrowth is set to 32768% !!! It stopped at 204Gb as there was no diskspace left. I've reset the autogrow again also set "maxsize" to 10GB.
This is definitely a bug and quite a serious one - it could quite easily take out an operational server by eating up disk space.
|||Yes this is quite serious. It stops our ETL-process because of the drive being full.
Nothing in our tracelog yet...
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
|||Thanks!
BR John
|||It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
b) it only manifests after the SQL server has been restarted
c) It has not been fixed in SP1 so here's hoping for SP2.
You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/
Workarounds:
set Max size of the file to prevent the file from growing too large
turn off AutoGrow
use % based autogrow
I was not aware of this bug. Thanks for the correction.|||
It is a known bug, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
a) it only seems to affect databases upgraded from SQL 2000
Regarding above comment ... I ran into that exact bug with a fresh install of SQL Server 2005 and SP1 on a completely new server. And it shut down my new 1 TB server!!!
Here's hoping SP2 does address it, as I have no interest in having my production servers needing that much coddling.
|||Yes - we've been hit by this as well.
We had it set to autogrow by 250MB, until after a reboot where it changed to 32000%. Unfortunately we didn't spot this until we filled up our log file disk.
The annoying thing was that SQL Server Management Studio doesn't let you change back a value that has erroneously gone to 32000%, because it complains that the value exceeds the maximum allowed!!
So you need to change it using the alter database statement:
alter database dbname modify file (name = filename, filegrowth = 10%)
Roll on SP2...
|||We just upgraded to SP2 for that reason (the fix is supposedly in there), but we're still unable to correct the value.
When attempting to change the value, the following error appears:
Value of ‘32768’ is not valid for “value’. “Value” should be between ‘Minimum’ and “Maximum’ Parameter name Value (System. Windows.Forms)
from the SQL Server 2005 SP2 fix list:
Can anyone advise if running this statement will correct this issue permanently?
alter database MyTestDB
Modify File
(name=mytestdb, filegrowth = 500 mb)
Thanks!
|||Executing an alter database statement does not appear to prevent the behavior from resurfacing.Sunday, February 19, 2012
AutoClose - How much resources are saved
We have a server with a number of relatively infrequently used DBs, one or
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE
> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?
|||Here you go:
http://msdn.microsoft.com/library/de...ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE
> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?
|||Here you go:
http://msdn.microsoft.com/library/de...ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE
|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
AutoClose - How much resources are saved
We have a server with a number of relatively infrequently used DBs, one or
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/d...br />
1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all th
e
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/d...br />
1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all th
e
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never real
ly
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
AutoClose - How much resources are saved
We have a server with a number of relatively infrequently used DBs, one or
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
--
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
two are more archival.
If I set them to Autclose, any idea as to resources released ?
I know the users will suffer when they do access them, but some never really
are used.
--
KlK, MCSE> I know the users will suffer when they do access them, but some never
really
> are used.
Then wouldn't it make more sense to detach them?|||Here you go:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_1o4z.asp
Sincerely,
Anthony Thomas
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||note that every time you access EM and open the Database tree/folder, all the
databases that were closed will re-open and it also gets logged... I would
follow Aaron advice.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||Well unfortunately, while I say they are really never used, the users would
not agree and want their data. So I guess we leave as is.
"KevinK" wrote:
> We have a server with a number of relatively infrequently used DBs, one or
> two are more archival.
> If I set them to Autclose, any idea as to resources released ?
> I know the users will suffer when they do access them, but some never really
> are used.
> --
> KlK, MCSE|||> Well unfortunately, while I say they are really never used, the users
would
> not agree and want their data. So I guess we leave as is.
So what is the big concern about moving them or having them autoclose or not
then?
If having them on the same server as other databases is such a big concern,
why not detach them from that server and attach them to a different one?
Monday, February 13, 2012
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||thanks
appreciate it.
rik
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||
Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||
ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||
thanks
appreciate it.
rik
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||thanks
appreciate it.
rik
Auto restore .bak from Production to same server''s TEST db with other Dbs & .baks in same
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
rik
|||Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.
create table #tb(i int identity primary key, name nvarchar(80) null)
declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'
insert #tb(name)
exec xp_cmdshell @.sql
select * from #tb
|||ok, this was what I was afraid of: having to always write sql code.
oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.
|||If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver
Be sure to include a business case if you want it to have any merit.
|||thanks
appreciate it.
rik
Friday, February 10, 2012
Auto grow enabled but DB wont grow
I've been through the forum and read a number of threads on people's DBs not growing and the answer usually is they don't have auotmatically grow data file. Unfortunately I have this on, but when I look at the properties of the database it reports the space available is 0.00 MB? Up until about two weeks ago I was showing appx 48% space utilization. When I ran an SP to show growth, it tells me that it was expanded by 20% yesterday, but SQL Server is still telling me the space available is zero.
The log file is also set for auto growth. The DB is 14.5 GB in size and the drives still have around 92 GB of space.
Has anyone experienced this before? Any ideas? Does anyone know of an SPs that can give me detailed info on internal data file size compared to stated size (i.e. wasted space in data file)? Is SQL Server doing something funny in the way it is seeing the database or data files individually? Any help is appreciated.Hi,
Have you tried DBCC SHRINKFILE? It can move the pages to beginning of the data file and freeing unused space.
Regards,
Leila|||Are you getting any messages in the errorlog? Are you loosing any data? My suspicion is that the database is functioning according to the parameters you've set, and that it is perfectly content.
The first thing I'd suggest that you check is the MS-SQL errorlog (either via Enterprise Mangler or by simply printing out the file). If there are no error messages there, I'd be really surprised if you've lost any data at all.
The next thing I'd check would be the NT Event log (either via "Manage my computer" or the Control Panel | Administrative Tools | Event Viewer). Look for the red icons for errors and the yellow ones for warnings... Other messages are just status information.
If those come up clean (no serious error messages), you haven't lost any data yet. At that point, you might want to think about performance issues which might exist, but integrity issues have to come first.
-PatP|||I had not run a DBCC SHRINKFILE yet as I was unsure about the validity of the DB and didn't want to cause more headaches than I have. My initial concern was not necessarily that it was not using its existing space effectively as much as it was why the data files weren't grwing when they should. I checked the logs at the time, and again just now; sorry I hadn't put that in the thread. Everything looks good as far as the NT event logs and the SQL logs, no errors and everything looks to be in order.
I will run the SHRINKFILE tonight and see how it goes. Pat, what kind of performance issues do you think could be involved in this?
Thanks to both of you, I'll keep you posted as to what I find.|||sp_spaceused will give a more detailed info on space utilization. And I don't see anything that can be posted in Windows event log that is not recorded by SQL Server error log that can indicate "a data loss." Pat is probably day-dreaming again ;)|||That sp_spaceused comes in pretty handy, thanks. I ended up running
DBCC UPDATEUSAGE and that resolved the problem. Still not sure why this occured, but I'm hoping this resolves it.
Thanks again for all of your help, it was very good to bounce this off others.|||SQL Server has historically been very bad about keeping size data in the system tables up to date. The DBCC UPDATEUSAGE(0) command checks these numbers against what is actually used, and makes corrections. Even so, I doubt it is worth setting up a job to run UPDATEUSAGE on a regular basis, so long aas you are not getting errors and such.
The log file is also set for auto growth. The DB is 14.5 GB in size and the drives still have around 92 GB of space.
Has anyone experienced this before? Any ideas? Does anyone know of an SPs that can give me detailed info on internal data file size compared to stated size (i.e. wasted space in data file)? Is SQL Server doing something funny in the way it is seeing the database or data files individually? Any help is appreciated.Hi,
Have you tried DBCC SHRINKFILE? It can move the pages to beginning of the data file and freeing unused space.
Regards,
Leila|||Are you getting any messages in the errorlog? Are you loosing any data? My suspicion is that the database is functioning according to the parameters you've set, and that it is perfectly content.
The first thing I'd suggest that you check is the MS-SQL errorlog (either via Enterprise Mangler or by simply printing out the file). If there are no error messages there, I'd be really surprised if you've lost any data at all.
The next thing I'd check would be the NT Event log (either via "Manage my computer" or the Control Panel | Administrative Tools | Event Viewer). Look for the red icons for errors and the yellow ones for warnings... Other messages are just status information.
If those come up clean (no serious error messages), you haven't lost any data yet. At that point, you might want to think about performance issues which might exist, but integrity issues have to come first.
-PatP|||I had not run a DBCC SHRINKFILE yet as I was unsure about the validity of the DB and didn't want to cause more headaches than I have. My initial concern was not necessarily that it was not using its existing space effectively as much as it was why the data files weren't grwing when they should. I checked the logs at the time, and again just now; sorry I hadn't put that in the thread. Everything looks good as far as the NT event logs and the SQL logs, no errors and everything looks to be in order.
I will run the SHRINKFILE tonight and see how it goes. Pat, what kind of performance issues do you think could be involved in this?
Thanks to both of you, I'll keep you posted as to what I find.|||sp_spaceused will give a more detailed info on space utilization. And I don't see anything that can be posted in Windows event log that is not recorded by SQL Server error log that can indicate "a data loss." Pat is probably day-dreaming again ;)|||That sp_spaceused comes in pretty handy, thanks. I ended up running
DBCC UPDATEUSAGE and that resolved the problem. Still not sure why this occured, but I'm hoping this resolves it.
Thanks again for all of your help, it was very good to bounce this off others.|||SQL Server has historically been very bad about keeping size data in the system tables up to date. The DBCC UPDATEUSAGE(0) command checks these numbers against what is actually used, and makes corrections. Even so, I doubt it is worth setting up a job to run UPDATEUSAGE on a regular basis, so long aas you are not getting errors and such.
Subscribe to:
Posts (Atom)