Hi all,
I have a database I maintain that has grown quite large. The part of it
I am interested in, is one paticular table that logs the history of
certain high volume "events". I have events back to 2002, and the
database is almost 3 Gigs. I rarely need to query more than 6 months
worth. Here is what I would like to do.
I'd like to split the database, and create an archive database. I want
to move everything before 2006 to this new database. Now, I know I can
do a complete backup, and then delete the stuff I don't need, but I
wanted to know if there is a way to import just the dates I need from
the current database. Of course, there is a timedate field to key off
of.
I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
2003 Server.
Thanks in advance.Hi there
There are several ways to do this. You can try to use DTS to move the data
over. Or you can use query analyzer. If you can use Query Analyzer, you can
actually use a query something like:
SELECT *
INTO [ArchiveDBName].dbo.[TableName]
FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
And to delete the records from the original table:
DELETE FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
However, it was not clear from you post if you have access to Query Analyzer
.
Lucas
"mattdaddym@.gmail.com" wrote:
> Hi all,
> I have a database I maintain that has grown quite large. The part of it
> I am interested in, is one paticular table that logs the history of
> certain high volume "events". I have events back to 2002, and the
> database is almost 3 Gigs. I rarely need to query more than 6 months
> worth. Here is what I would like to do.
> I'd like to split the database, and create an archive database. I want
> to move everything before 2006 to this new database. Now, I know I can
> do a complete backup, and then delete the stuff I don't need, but I
> wanted to know if there is a way to import just the dates I need from
> the current database. Of course, there is a timedate field to key off
> of.
> I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> 2003 Server.
> Thanks in advance.
>|||I do have access to query analyzer. I was hoping for a way to do it in
the GUI since it creats the tables automatically when you do an import.
I don't know for sure, but I will assume if I use your command that I
will need to set the table up first. Not a big deal, though. I guess
I'm just being lazy. Thank you!
Lucas Kartawidjaja wrote:[vbcol=seagreen]
> Hi there
> There are several ways to do this. You can try to use DTS to move the data
> over. Or you can use query analyzer. If you can use Query Analyzer, you ca
n
> actually use a query something like:
> SELECT *
> INTO [ArchiveDBName].dbo.[TableName]
> FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> And to delete the records from the original table:
> DELETE FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> However, it was not clear from you post if you have access to Query Analyz
er.
> Lucas
>
> "mattdaddym@.gmail.com" wrote:
>|||Actually with the SELECT INTO statement the beauty of it is that you don't
need to create the table first. SELECT INTO will create it for you the table
structure will be the same as the original table.
Lucas
"mattdaddym@.gmail.com" wrote:
> I do have access to query analyzer. I was hoping for a way to do it in
> the GUI since it creats the tables automatically when you do an import.
> I don't know for sure, but I will assume if I use your command that I
> will need to set the table up first. Not a big deal, though. I guess
> I'm just being lazy. Thank you!
> Lucas Kartawidjaja wrote:
>|||That is great. Thank you!
Lucas Kartawidjaja wrote:[vbcol=seagreen]
> Actually with the SELECT INTO statement the beauty of it is that you don't
> need to create the table first. SELECT INTO will create it for you the tab
le
> structure will be the same as the original table.
> Lucas
> "mattdaddym@.gmail.com" wrote:
>
Showing posts with label grown. Show all posts
Showing posts with label grown. Show all posts
Thursday, March 29, 2012
database import question
Hi all,
I have a database I maintain that has grown quite large. The part of it
I am interested in, is one paticular table that logs the history of
certain high volume "events". I have events back to 2002, and the
database is almost 3 Gigs. I rarely need to query more than 6 months
worth. Here is what I would like to do.
I'd like to split the database, and create an archive database. I want
to move everything before 2006 to this new database. Now, I know I can
do a complete backup, and then delete the stuff I don't need, but I
wanted to know if there is a way to import just the dates I need from
the current database. Of course, there is a timedate field to key off
of.
I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
2003 Server.
Thanks in advance.Hi there
There are several ways to do this. You can try to use DTS to move the data
over. Or you can use query analyzer. If you can use Query Analyzer, you can
actually use a query something like:
SELECT *
INTO [ArchiveDBName].dbo.[TableName]
FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
And to delete the records from the original table:
DELETE FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
However, it was not clear from you post if you have access to Query Analyzer.
Lucas
"mattdaddym@.gmail.com" wrote:
> Hi all,
> I have a database I maintain that has grown quite large. The part of it
> I am interested in, is one paticular table that logs the history of
> certain high volume "events". I have events back to 2002, and the
> database is almost 3 Gigs. I rarely need to query more than 6 months
> worth. Here is what I would like to do.
> I'd like to split the database, and create an archive database. I want
> to move everything before 2006 to this new database. Now, I know I can
> do a complete backup, and then delete the stuff I don't need, but I
> wanted to know if there is a way to import just the dates I need from
> the current database. Of course, there is a timedate field to key off
> of.
> I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> 2003 Server.
> Thanks in advance.
>|||I do have access to query analyzer. I was hoping for a way to do it in
the GUI since it creats the tables automatically when you do an import.
I don't know for sure, but I will assume if I use your command that I
will need to set the table up first. Not a big deal, though. I guess
I'm just being lazy. Thank you!
Lucas Kartawidjaja wrote:
> Hi there
> There are several ways to do this. You can try to use DTS to move the data
> over. Or you can use query analyzer. If you can use Query Analyzer, you can
> actually use a query something like:
> SELECT *
> INTO [ArchiveDBName].dbo.[TableName]
> FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> And to delete the records from the original table:
> DELETE FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> However, it was not clear from you post if you have access to Query Analyzer.
> Lucas
>
> "mattdaddym@.gmail.com" wrote:
> > Hi all,
> >
> > I have a database I maintain that has grown quite large. The part of it
> > I am interested in, is one paticular table that logs the history of
> > certain high volume "events". I have events back to 2002, and the
> > database is almost 3 Gigs. I rarely need to query more than 6 months
> > worth. Here is what I would like to do.
> >
> > I'd like to split the database, and create an archive database. I want
> > to move everything before 2006 to this new database. Now, I know I can
> > do a complete backup, and then delete the stuff I don't need, but I
> > wanted to know if there is a way to import just the dates I need from
> > the current database. Of course, there is a timedate field to key off
> > of.
> >
> > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > 2003 Server.
> >
> > Thanks in advance.
> >
> >|||Actually with the SELECT INTO statement the beauty of it is that you don't
need to create the table first. SELECT INTO will create it for you the table
structure will be the same as the original table.
Lucas
"mattdaddym@.gmail.com" wrote:
> I do have access to query analyzer. I was hoping for a way to do it in
> the GUI since it creats the tables automatically when you do an import.
> I don't know for sure, but I will assume if I use your command that I
> will need to set the table up first. Not a big deal, though. I guess
> I'm just being lazy. Thank you!
> Lucas Kartawidjaja wrote:
> > Hi there
> >
> > There are several ways to do this. You can try to use DTS to move the data
> > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > actually use a query something like:
> >
> > SELECT *
> > INTO [ArchiveDBName].dbo.[TableName]
> > FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > And to delete the records from the original table:
> >
> > DELETE FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > However, it was not clear from you post if you have access to Query Analyzer.
> >
> > Lucas
> >
> >
> > "mattdaddym@.gmail.com" wrote:
> >
> > > Hi all,
> > >
> > > I have a database I maintain that has grown quite large. The part of it
> > > I am interested in, is one paticular table that logs the history of
> > > certain high volume "events". I have events back to 2002, and the
> > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > worth. Here is what I would like to do.
> > >
> > > I'd like to split the database, and create an archive database. I want
> > > to move everything before 2006 to this new database. Now, I know I can
> > > do a complete backup, and then delete the stuff I don't need, but I
> > > wanted to know if there is a way to import just the dates I need from
> > > the current database. Of course, there is a timedate field to key off
> > > of.
> > >
> > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > 2003 Server.
> > >
> > > Thanks in advance.
> > >
> > >
>|||That is great. Thank you!
Lucas Kartawidjaja wrote:
> Actually with the SELECT INTO statement the beauty of it is that you don't
> need to create the table first. SELECT INTO will create it for you the table
> structure will be the same as the original table.
> Lucas
> "mattdaddym@.gmail.com" wrote:
> > I do have access to query analyzer. I was hoping for a way to do it in
> > the GUI since it creats the tables automatically when you do an import.
> > I don't know for sure, but I will assume if I use your command that I
> > will need to set the table up first. Not a big deal, though. I guess
> > I'm just being lazy. Thank you!
> > Lucas Kartawidjaja wrote:
> > > Hi there
> > >
> > > There are several ways to do this. You can try to use DTS to move the data
> > > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > > actually use a query something like:
> > >
> > > SELECT *
> > > INTO [ArchiveDBName].dbo.[TableName]
> > > FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > And to delete the records from the original table:
> > >
> > > DELETE FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > However, it was not clear from you post if you have access to Query Analyzer.
> > >
> > > Lucas
> > >
> > >
> > > "mattdaddym@.gmail.com" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have a database I maintain that has grown quite large. The part of it
> > > > I am interested in, is one paticular table that logs the history of
> > > > certain high volume "events". I have events back to 2002, and the
> > > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > > worth. Here is what I would like to do.
> > > >
> > > > I'd like to split the database, and create an archive database. I want
> > > > to move everything before 2006 to this new database. Now, I know I can
> > > > do a complete backup, and then delete the stuff I don't need, but I
> > > > wanted to know if there is a way to import just the dates I need from
> > > > the current database. Of course, there is a timedate field to key off
> > > > of.
> > > >
> > > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > > 2003 Server.
> > > >
> > > > Thanks in advance.
> > > >
> > > >
> >
> >
I have a database I maintain that has grown quite large. The part of it
I am interested in, is one paticular table that logs the history of
certain high volume "events". I have events back to 2002, and the
database is almost 3 Gigs. I rarely need to query more than 6 months
worth. Here is what I would like to do.
I'd like to split the database, and create an archive database. I want
to move everything before 2006 to this new database. Now, I know I can
do a complete backup, and then delete the stuff I don't need, but I
wanted to know if there is a way to import just the dates I need from
the current database. Of course, there is a timedate field to key off
of.
I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
2003 Server.
Thanks in advance.Hi there
There are several ways to do this. You can try to use DTS to move the data
over. Or you can use query analyzer. If you can use Query Analyzer, you can
actually use a query something like:
SELECT *
INTO [ArchiveDBName].dbo.[TableName]
FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
And to delete the records from the original table:
DELETE FROM dbo.[OriginalDBName]
WHERE [DateStamp] < '1/1/2006'
However, it was not clear from you post if you have access to Query Analyzer.
Lucas
"mattdaddym@.gmail.com" wrote:
> Hi all,
> I have a database I maintain that has grown quite large. The part of it
> I am interested in, is one paticular table that logs the history of
> certain high volume "events". I have events back to 2002, and the
> database is almost 3 Gigs. I rarely need to query more than 6 months
> worth. Here is what I would like to do.
> I'd like to split the database, and create an archive database. I want
> to move everything before 2006 to this new database. Now, I know I can
> do a complete backup, and then delete the stuff I don't need, but I
> wanted to know if there is a way to import just the dates I need from
> the current database. Of course, there is a timedate field to key off
> of.
> I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> 2003 Server.
> Thanks in advance.
>|||I do have access to query analyzer. I was hoping for a way to do it in
the GUI since it creats the tables automatically when you do an import.
I don't know for sure, but I will assume if I use your command that I
will need to set the table up first. Not a big deal, though. I guess
I'm just being lazy. Thank you!
Lucas Kartawidjaja wrote:
> Hi there
> There are several ways to do this. You can try to use DTS to move the data
> over. Or you can use query analyzer. If you can use Query Analyzer, you can
> actually use a query something like:
> SELECT *
> INTO [ArchiveDBName].dbo.[TableName]
> FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> And to delete the records from the original table:
> DELETE FROM dbo.[OriginalDBName]
> WHERE [DateStamp] < '1/1/2006'
> However, it was not clear from you post if you have access to Query Analyzer.
> Lucas
>
> "mattdaddym@.gmail.com" wrote:
> > Hi all,
> >
> > I have a database I maintain that has grown quite large. The part of it
> > I am interested in, is one paticular table that logs the history of
> > certain high volume "events". I have events back to 2002, and the
> > database is almost 3 Gigs. I rarely need to query more than 6 months
> > worth. Here is what I would like to do.
> >
> > I'd like to split the database, and create an archive database. I want
> > to move everything before 2006 to this new database. Now, I know I can
> > do a complete backup, and then delete the stuff I don't need, but I
> > wanted to know if there is a way to import just the dates I need from
> > the current database. Of course, there is a timedate field to key off
> > of.
> >
> > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > 2003 Server.
> >
> > Thanks in advance.
> >
> >|||Actually with the SELECT INTO statement the beauty of it is that you don't
need to create the table first. SELECT INTO will create it for you the table
structure will be the same as the original table.
Lucas
"mattdaddym@.gmail.com" wrote:
> I do have access to query analyzer. I was hoping for a way to do it in
> the GUI since it creats the tables automatically when you do an import.
> I don't know for sure, but I will assume if I use your command that I
> will need to set the table up first. Not a big deal, though. I guess
> I'm just being lazy. Thank you!
> Lucas Kartawidjaja wrote:
> > Hi there
> >
> > There are several ways to do this. You can try to use DTS to move the data
> > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > actually use a query something like:
> >
> > SELECT *
> > INTO [ArchiveDBName].dbo.[TableName]
> > FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > And to delete the records from the original table:
> >
> > DELETE FROM dbo.[OriginalDBName]
> > WHERE [DateStamp] < '1/1/2006'
> >
> > However, it was not clear from you post if you have access to Query Analyzer.
> >
> > Lucas
> >
> >
> > "mattdaddym@.gmail.com" wrote:
> >
> > > Hi all,
> > >
> > > I have a database I maintain that has grown quite large. The part of it
> > > I am interested in, is one paticular table that logs the history of
> > > certain high volume "events". I have events back to 2002, and the
> > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > worth. Here is what I would like to do.
> > >
> > > I'd like to split the database, and create an archive database. I want
> > > to move everything before 2006 to this new database. Now, I know I can
> > > do a complete backup, and then delete the stuff I don't need, but I
> > > wanted to know if there is a way to import just the dates I need from
> > > the current database. Of course, there is a timedate field to key off
> > > of.
> > >
> > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > 2003 Server.
> > >
> > > Thanks in advance.
> > >
> > >
>|||That is great. Thank you!
Lucas Kartawidjaja wrote:
> Actually with the SELECT INTO statement the beauty of it is that you don't
> need to create the table first. SELECT INTO will create it for you the table
> structure will be the same as the original table.
> Lucas
> "mattdaddym@.gmail.com" wrote:
> > I do have access to query analyzer. I was hoping for a way to do it in
> > the GUI since it creats the tables automatically when you do an import.
> > I don't know for sure, but I will assume if I use your command that I
> > will need to set the table up first. Not a big deal, though. I guess
> > I'm just being lazy. Thank you!
> > Lucas Kartawidjaja wrote:
> > > Hi there
> > >
> > > There are several ways to do this. You can try to use DTS to move the data
> > > over. Or you can use query analyzer. If you can use Query Analyzer, you can
> > > actually use a query something like:
> > >
> > > SELECT *
> > > INTO [ArchiveDBName].dbo.[TableName]
> > > FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > And to delete the records from the original table:
> > >
> > > DELETE FROM dbo.[OriginalDBName]
> > > WHERE [DateStamp] < '1/1/2006'
> > >
> > > However, it was not clear from you post if you have access to Query Analyzer.
> > >
> > > Lucas
> > >
> > >
> > > "mattdaddym@.gmail.com" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have a database I maintain that has grown quite large. The part of it
> > > > I am interested in, is one paticular table that logs the history of
> > > > certain high volume "events". I have events back to 2002, and the
> > > > database is almost 3 Gigs. I rarely need to query more than 6 months
> > > > worth. Here is what I would like to do.
> > > >
> > > > I'd like to split the database, and create an archive database. I want
> > > > to move everything before 2006 to this new database. Now, I know I can
> > > > do a complete backup, and then delete the stuff I don't need, but I
> > > > wanted to know if there is a way to import just the dates I need from
> > > > the current database. Of course, there is a timedate field to key off
> > > > of.
> > > >
> > > > I am using the GUI to do this in Enterprise Manager SQL 2000, Windows
> > > > 2003 Server.
> > > >
> > > > Thanks in advance.
> > > >
> > > >
> >
> >
Tuesday, March 27, 2012
Database has grown too big (5 GB)
Hello
My database should have a normal size on about 500-1000 mb, but it now has
the size of 5 gb. I am talking about the MDF file, not the log file.
I think it is because of a wrong setup of the maintenance plan. Now I have
altered the optimization and checked "remove unused space from database
files", so it is set to shrink database when it grows beyond 50 Mb.
So do you think that next time the maintenance plan runs the Optimization,
the database size will be set to normal ?
I use MSSQL 2000, service pack 3, MDAC 2.8
\AndersIt depends on how much space is being used. Run a sp_spaceused inside the
database name. The reserved number (which equals data + log+ unused) is
roughly how much space to expect from shrinking, which can also be
accomplished by running dbcc shrinkfile (<data name>, 0) -- see SQL Server
Books online for details (which can be downloaded for free from Microsoft's
SQL site -- http://www.microsoft.com/sql)
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>|||If after shrinking the DB, you see the same problem, then try the following:
Do you have clustered index on your tables? If not, add and see if you see
the change.
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>
My database should have a normal size on about 500-1000 mb, but it now has
the size of 5 gb. I am talking about the MDF file, not the log file.
I think it is because of a wrong setup of the maintenance plan. Now I have
altered the optimization and checked "remove unused space from database
files", so it is set to shrink database when it grows beyond 50 Mb.
So do you think that next time the maintenance plan runs the Optimization,
the database size will be set to normal ?
I use MSSQL 2000, service pack 3, MDAC 2.8
\AndersIt depends on how much space is being used. Run a sp_spaceused inside the
database name. The reserved number (which equals data + log+ unused) is
roughly how much space to expect from shrinking, which can also be
accomplished by running dbcc shrinkfile (<data name>, 0) -- see SQL Server
Books online for details (which can be downloaded for free from Microsoft's
SQL site -- http://www.microsoft.com/sql)
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>|||If after shrinking the DB, you see the same problem, then try the following:
Do you have clustered index on your tables? If not, add and see if you see
the change.
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>
Database has grown too big (5 GB)
Hello
My database should have a normal size on about 500-1000 mb, but it now has
the size of 5 gb. I am talking about the MDF file, not the log file.
I think it is because of a wrong setup of the maintenance plan. Now I have
altered the optimization and checked "remove unused space from database
files", so it is set to shrink database when it grows beyond 50 Mb.
So do you think that next time the maintenance plan runs the Optimization,
the database size will be set to normal ?
I use MSSQL 2000, service pack 3, MDAC 2.8
\AndersIt depends on how much space is being used. Run a sp_spaceused inside the
database name. The reserved number (which equals data + log+ unused) is
roughly how much space to expect from shrinking, which can also be
accomplished by running dbcc shrinkfile (<data name>, 0) -- see SQL Server
Books online for details (which can be downloaded for free from Microsoft's
SQL site -- http://www.microsoft.com/sql)
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>|||If after shrinking the DB, you see the same problem, then try the following:
Do you have clustered index on your tables? If not, add and see if you see
the change.
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>
My database should have a normal size on about 500-1000 mb, but it now has
the size of 5 gb. I am talking about the MDF file, not the log file.
I think it is because of a wrong setup of the maintenance plan. Now I have
altered the optimization and checked "remove unused space from database
files", so it is set to shrink database when it grows beyond 50 Mb.
So do you think that next time the maintenance plan runs the Optimization,
the database size will be set to normal ?
I use MSSQL 2000, service pack 3, MDAC 2.8
\AndersIt depends on how much space is being used. Run a sp_spaceused inside the
database name. The reserved number (which equals data + log+ unused) is
roughly how much space to expect from shrinking, which can also be
accomplished by running dbcc shrinkfile (<data name>, 0) -- see SQL Server
Books online for details (which can be downloaded for free from Microsoft's
SQL site -- http://www.microsoft.com/sql)
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>|||If after shrinking the DB, you see the same problem, then try the following:
Do you have clustered index on your tables? If not, add and see if you see
the change.
"anders" <a.vindal@.techotel.dk> wrote in message
news:%23hSDU9I$DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Hello
> My database should have a normal size on about 500-1000 mb, but it now has
> the size of 5 gb. I am talking about the MDF file, not the log file.
> I think it is because of a wrong setup of the maintenance plan. Now I have
> altered the optimization and checked "remove unused space from database
> files", so it is set to shrink database when it grows beyond 50 Mb.
> So do you think that next time the maintenance plan runs the Optimization,
> the database size will be set to normal ?
> I use MSSQL 2000, service pack 3, MDAC 2.8
>
>
> \Anders
>
Subscribe to:
Posts (Atom)