Resizing an existing DB

I created a DB with a preallocated size of 20GB and I need to shink
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting.google.com...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.|||Hi Darren,
Unfortunatly you can't. The smallest size you can shrink
your database to is the size the database was initally set
to when you created it, in this case 20gb.
Although there are lots of ways of doing it, personally I
would save the data, drop the database, recreate it with 1
gb, then copy the database back into it.
J
>--Original Message--
>I created a DB with a preallocated size of 20GB and I
need to shink
>this down to a smaller size. If I go into DB properties
and enter a
>new value it says that the size must be greater than the
current size.
>Is there any way to do this? Also when I try and restore
the DB is
>wants 20GB of disk space even though it's only using
about 1GB of
>data. Any help will be muchly appreciated.
>Darren.
>.
>|||Julie,
DBCC SHRINKFILE should be able to shrink the size below the initial size...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> Hi Darren,
> Unfortunatly you can't. The smallest size you can shrink
> your database to is the size the database was initally set
> to when you created it, in this case 20gb.
> Although there are lots of ways of doing it, personally I
> would save the data, drop the database, recreate it with 1
> gb, then copy the database back into it.
> J
>
>
> >--Original Message--
> >I created a DB with a preallocated size of 20GB and I
> need to shink
> >this down to a smaller size. If I go into DB properties
> and enter a
> >new value it says that the size must be greater than the
> current size.
> >Is there any way to do this? Also when I try and restore
> the DB is
> >wants 20GB of disk space even though it's only using
> about 1GB of
> >data. Any help will be muchly appreciated.
> >
> >Darren.
> >.
> >|||Ok, now hanging my head in shame.
Moving swiftly along...
>--Original Message--
>Julie,
>DBCC SHRINKFILE should be able to shrink the size below
the initial size...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
>> Hi Darren,
>> Unfortunatly you can't. The smallest size you can shrink
>> your database to is the size the database was initally
set
>> to when you created it, in this case 20gb.
>> Although there are lots of ways of doing it,
personally I
>> would save the data, drop the database, recreate it
with 1
>> gb, then copy the database back into it.
>> J
>>
>>
>> >--Original Message--
>> >I created a DB with a preallocated size of 20GB and I
>> need to shink
>> >this down to a smaller size. If I go into DB properties
>> and enter a
>> >new value it says that the size must be greater than
the
>> current size.
>> >Is there any way to do this? Also when I try and
restore
>> the DB is
>> >wants 20GB of disk space even though it's only using
>> about 1GB of
>> >data. Any help will be muchly appreciated.
>> >
>> >Darren.
>> >.
>> >
>
>.
>|||LOL...
As if we didn't do this all of us, from time to time. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b38e01c4373e$eb86b330$a301280a@.phx.gbl...
> Ok, now hanging my head in shame.
> Moving swiftly along...
>
> >--Original Message--
> >Julie,
> >
> >DBCC SHRINKFILE should be able to shrink the size below
> the initial size...
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> >> Hi Darren,
> >>
> >> Unfortunatly you can't. The smallest size you can shrink
> >> your database to is the size the database was initally
> set
> >> to when you created it, in this case 20gb.
> >>
> >> Although there are lots of ways of doing it,
> personally I
> >> would save the data, drop the database, recreate it
> with 1
> >> gb, then copy the database back into it.
> >>
> >> J
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I created a DB with a preallocated size of 20GB and I
> >> need to shink
> >> >this down to a smaller size. If I go into DB properties
> >> and enter a
> >> >new value it says that the size must be greater than
> the
> >> current size.
> >> >Is there any way to do this? Also when I try and
> restore
> >> the DB is
> >> >wants 20GB of disk space even though it's only using
> >> about 1GB of
> >> >data. Any help will be muchly appreciated.
> >> >
> >> >Darren.
> >> >.
> >> >
> >
> >
> >.
> >

Resizing an existing DB

I created a DB with a preallocated size of 20GB and I need to shink
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.
Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting.google.c om...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.

Resizing an existing DB

Hi Darren,
Unfortunatly you can't. The smallest size you can shrink
your database to is the size the database was initally set
to when you created it, in this case 20gb.
Although there are lots of ways of doing it, personally I
would save the data, drop the database, recreate it with 1
gb, then copy the database back into it.
J

>--Original Message--
>I created a DB with a preallocated size of 20GB and I
need to shink
>this down to a smaller size. If I go into DB properties
and enter a
>new value it says that the size must be greater than the
current size.
>Is there any way to do this? Also when I try and restore
the DB is
>wants 20GB of disk space even though it's only using
about 1GB of
>data. Any help will be muchly appreciated.
>Darren.
>.
>
Julie,
DBCC SHRINKFILE should be able to shrink the size below the initial size...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b30801c43735$c4a30970$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Darren,
> Unfortunatly you can't. The smallest size you can shrink
> your database to is the size the database was initally set
> to when you created it, in this case 20gb.
> Although there are lots of ways of doing it, personally I
> would save the data, drop the database, recreate it with 1
> gb, then copy the database back into it.
> J
>
>
> need to shink
> and enter a
> current size.
> the DB is
> about 1GB of
|||Ok, now hanging my head in shame.
Moving swiftly along...

>--Original Message--
>Julie,
>DBCC SHRINKFILE should be able to shrink the size below
the initial size...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message news:b30801c43735$c4a30970$a301280a@.phx.gbl...[vbcol=seagreen]
set[vbcol=seagreen]
personally I[vbcol=seagreen]
with 1[vbcol=seagreen]
the[vbcol=seagreen]
restore
>
>.
>
|||LOL...
As if we didn't do this all of us, from time to time. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b38e01c4373e$eb86b330$a301280a@.phx.gbl...[vbcol=seagreen]
> Ok, now hanging my head in shame.
> Moving swiftly along...
>
> the initial size...
> message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> set
> personally I
> with 1
> the
> restore

Resizing an existing DB

Hi Darren,
Unfortunatly you can't. The smallest size you can shrink
your database to is the size the database was initally set
to when you created it, in this case 20gb.
Although there are lots of ways of doing it, personally I
would save the data, drop the database, recreate it with 1
gb, then copy the database back into it.
J

>--Original Message--
>I created a DB with a preallocated size of 20GB and I
need to shink
>this down to a smaller size. If I go into DB properties
and enter a
>new value it says that the size must be greater than the
current size.
>Is there any way to do this? Also when I try and restore
the DB is
>wants 20GB of disk space even though it's only using
about 1GB of
>data. Any help will be muchly appreciated.
>Darren.
>.
>Julie,
DBCC SHRINKFILE should be able to shrink the size below the initial size...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b30801c43735$c4a30970$a3
01280a@.phx.gbl...[vbcol=seagreen]
> Hi Darren,
> Unfortunatly you can't. The smallest size you can shrink
> your database to is the size the database was initally set
> to when you created it, in this case 20gb.
> Although there are lots of ways of doing it, personally I
> would save the data, drop the database, recreate it with 1
> gb, then copy the database back into it.
> J
>
>
> need to shink
> and enter a
> current size.
> the DB is
> about 1GB of|||Ok, now hanging my head in shame.
Moving swiftly along...

>--Original Message--
>Julie,
>DBCC SHRINKFILE should be able to shrink the size below
the initial size...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
set[vbcol=seagreen]
personally I[vbcol=seagreen]
with 1[vbcol=seagreen]
the[vbcol=seagreen]
restore[vbcol=seagreen]
>
>.
>|||LOL...
As if we didn't do this all of us, from time to time. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b38e01c4373e$eb86b330$a3
01280a@.phx.gbl...[vbcol=seagreen]
> Ok, now hanging my head in shame.
> Moving swiftly along...
>
> the initial size...
> message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> set
> personally I
> with 1
> the
> restoresql

Resizing an existing DB

I created a DB with a preallocated size of 20GB and I need to shink
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in
Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting
.google.com...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.

Reset date to first of month

I receive an existing date variable, which I copy to my @.today variable. I
need to reset the day of the month to be the first of the month, leaving all
other aspects of @.today alone.
Any suggestions?
-- simulate the date I get, which I have no control over
declare @.date datetime
set @.date = '02/08/2008'
--assign to my date variable, which I do control
declare @.today datetime
SET @.today = @.date
--the following doesn't work, but it shows what I want to do
SET MONTH(@.today) = 1
SELECT @.today
Thanks
--
RandySET @.today = DATEADD(month,DATEDIFF(month,0,@.date),0)
Steve Kass
Drew University
randy1200 wrote:

>I receive an existing date variable, which I copy to my @.today variable. I
>need to reset the day of the month to be the first of the month, leaving al
l
>other aspects of @.today alone.
>Any suggestions?
>-- simulate the date I get, which I have no control over
>declare @.date datetime
>set @.date = '02/08/2008'
>--assign to my date variable, which I do control
>declare @.today datetime
>SET @.today = @.date
>--the following doesn't work, but it shows what I want to do
>SET MONTH(@.today) = 1
>SELECT @.today
>Thanks
>|||You can try using:
-- simulate the date I get, which I have no control over
declare @.date datetime
set @.date = '02/08/2008'
--assign to my date variable, which I do control
declare @.today datetime
SET @.today = @.date
--If I understood correctly
set @.today = @.today - (DAY(@.today)-1)
SELECT @.today
Let me know if it helps..
"randy1200" wrote:

> I receive an existing date variable, which I copy to my @.today variable. I
> need to reset the day of the month to be the first of the month, leaving a
ll
> other aspects of @.today alone.
> Any suggestions?
> -- simulate the date I get, which I have no control over
> declare @.date datetime
> set @.date = '02/08/2008'
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --the following doesn't work, but it shows what I want to do
> SET MONTH(@.today) = 1
> SELECT @.today
> Thanks
> --
> Randy|||That did it. Many thanks.
--
Randy
"Edgardo Valdez, MCSD, MCDBA" wrote:
> You can try using:
> -- simulate the date I get, which I have no control over
> declare @.date datetime
> set @.date = '02/08/2008'
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --If I understood correctly
> set @.today = @.today - (DAY(@.today)-1)
> SELECT @.today
> Let me know if it helps..
> "randy1200" wrote:
>|||That did it. Many thanks.
--
Randy
"Steve Kass" wrote:

> SET @.today = DATEADD(month,DATEDIFF(month,0,@.date),0)
> Steve Kass
> Drew University
> randy1200 wrote:
>
>|||This one will work for the first date as well:
set @.today = @.today - (case when DAY(@.today) = 1 then 0 else DAY(@.today)-1
end)
"randy1200" wrote:
> That did it. Many thanks.
> --
> Randy
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Very . Many thanks again!
--
Randy
"Edgardo Valdez, MCSD, MCDBA" wrote:
> This one will work for the first date as well:
> set @.today = @.today - (case when DAY(@.today) = 1 then 0 else DAY(@.today)-1
> end)
> "randy1200" wrote:
>|||You are very welcome!
"randy1200" wrote:
> Very . Many thanks again!
> --
> Randy
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Hi Randy,
Try:
select DATEADD(mm, DATEDIFF(mm,0,@.today), 0)
"DATEDIFF(mm,0,getdate())" calculates the number of months between the curre
nt
date and the date "1900-01-01 00:00:00.000".
Remember date and time variables are stored as the number of milliseconds
since "1900-01-01 00:00:00.000"; this is why you can specify the first datet
ime
expression of the DATEDIFF function as "0."
Now the last function call, DATEADD, adds the number of months between the
current date and '1900-01-01".
By adding the number of months between our pre-determined date '1900-01-01'
and the current date, you are able to arrive at the first day of the current
month.
In addition, the time portion of the calculated date will be "00:00:00.000."

> I receive an existing date variable, which I copy to my @.today
> variable. I
> need to reset the day of the month to be the first of the month,
> leaving all
> other aspects of @.today alone.
> Any suggestions?
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --the following doesn't work, but it shows what I want to do
> SET MONTH(@.today) = 1
> SELECT @.today
> Thanks
>sql

Reserved Error importing Access 2003 Report into VS2005 Report Module, How do I troublesho

Hi,
I'm trying to Import an existing Access 2003 report into into an existing
VS2005 Report Project, I am getting the following error. Is there a way to
troubleshoot what is causing this error?
Importing reports from 'C:\db2.mdb'...
Error : An error occurred while the report Test Report Form was being
imported: Reserved ErrorWe ran across a few reports with this issue. I reviewed my notes but didn't
come up with anything.
I found this in a group:
2 From: Albert Yen [MSFT] - view profile
Date: Tues, Jul 26 2005 3:51 pm
Email: "Albert Yen [MSFT]" <albe...@.online.microsoft.com>
Groups: microsoft.public.sqlserver.reportingsvcs
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
The first error could be coming from Access. Could you try the following?
Open your database in Microsoft Access. Create a new Module and paste the
following code into it:
Sub Export()
Application.ExportXML ObjectType:=acExportReport, _
DataSource:="DailySales ", _
ImageTarget:=".", _
PresentationTarget:="DailySales .xsl", _
OtherFlags:=acPersistReportML
End Sub
Set the cursor at the beginning of the Sub and click the Run button. If
ExportXML succeeds, it should create a file called "DailySales_report.xml"
in your "My Documents" folder. If you can send the file to me, I'll look at
it. If ExportXML fails, then it's an Access problem. You could try the
Access newsgroups for help.
Albert Yen
SQL Server Reporting Services
Steve MunLeeuw
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:u8Pq0zJ7GHA.4708@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I'm trying to Import an existing Access 2003 report into into an existing
> VS2005 Report Project, I am getting the following error. Is there a way
> to troubleshoot what is causing this error?
> Importing reports from 'C:\db2.mdb'...
> Error : An error occurred while the report Test Report Form was being
> imported: Reserved Error
>

Reseed Identity of Existing

Hey all,
Is there an easy way to reseed an identity column while retaining all of
the data within each row?
This doesn't really make much of a difference since it doesn't affect
anything 'operationally'; just curious more than anything else.
I read several posts here and checked DBCC_CHECKIDENT but wasn't sure about
the impact to existing.
Thanks.
Message posted via http://www.droptable.com
DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)
As long as you don't reset it to a value lower than the maximum existing
identity value, you are ok.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:c25abe6eaa404a5b8ef9e7aff85038f2@.droptable.co m...
> Hey all,
> Is there an easy way to reseed an identity column while retaining all of
> the data within each row?
> This doesn't really make much of a difference since it doesn't affect
> anything 'operationally'; just curious more than anything else.
> I read several posts here and checked DBCC_CHECKIDENT but wasn't sure
> about
> the impact to existing.
> Thanks.
> --
> Message posted via http://www.droptable.com
|||Thanks, Mike.
I was actually wondering about the ability to 'include' existing rows. In
other words, to also automatically 're-number' existing rows beginning with
the RESEED value used in executing DCCC_CHECKIDENT.
Message posted via http://www.droptable.com

Reseed Identity of Existing

Hey all,
Is there an easy way to reseed an identity column while retaining all of
the data within each row?
This doesn't really make much of a difference since it doesn't affect
anything 'operationally'; just curious more than anything else.
I read several posts here and checked DBCC_CHECKIDENT but wasn't sure about
the impact to existing.
Thanks.
--
Message posted via http://www.sqlmonster.comDBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)
As long as you don't reset it to a value lower than the maximum existing
identity value, you are ok.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:c25abe6eaa404a5b8ef9e7aff85038f2@.SQLMonster.com...
> Hey all,
> Is there an easy way to reseed an identity column while retaining all of
> the data within each row?
> This doesn't really make much of a difference since it doesn't affect
> anything 'operationally'; just curious more than anything else.
> I read several posts here and checked DBCC_CHECKIDENT but wasn't sure
> about
> the impact to existing.
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Thanks, Mike.
I was actually wondering about the ability to 'include' existing rows. In
other words, to also automatically 're-number' existing rows beginning with
the RESEED value used in executing DCCC_CHECKIDENT.
--
Message posted via http://www.sqlmonster.com

Reseed Identity of Existing

Hey all,
Is there an easy way to reseed an identity column while retaining all of
the data within each row?
This doesn't really make much of a difference since it doesn't affect
anything 'operationally'; just curious more than anything else.
I read several posts here and checked DBCC_CHECKIDENT but wasn't sure about
the impact to existing.
Thanks.
Message posted via http://www.droptable.comDBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)
As long as you don't reset it to a value lower than the maximum existing
identity value, you are ok.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:c25abe6eaa404a5b8ef9e7aff85038f2@.SQ
droptable.com...
> Hey all,
> Is there an easy way to reseed an identity column while retaining all of
> the data within each row?
> This doesn't really make much of a difference since it doesn't affect
> anything 'operationally'; just curious more than anything else.
> I read several posts here and checked DBCC_CHECKIDENT but wasn't sure
> about
> the impact to existing.
> Thanks.
> --
> Message posted via http://www.droptable.com|||Thanks, Mike.
I was actually wondering about the ability to 'include' existing rows. In
other words, to also automatically 're-number' existing rows beginning with
the RESEED value used in executing DCCC_CHECKIDENT.
Message posted via http://www.droptable.com