MSDE

I looking for some guidance here. I have database which has exceeded the 2 gig limit. I deleted some old tables in an attempt to acquire some space. This worked out. However when I look at the size of my *.mdf it still appears in excess of the 2 gigs . I know this isn't true since I deleted 500 megs of stuff. How do i reclaim the space (get the file size to report correctly) or do I need to even worry about it.

AndyYou might try first backing up the database and then running the "Shrink" command. In Enterprise Manager Select the database, right click and select "Shrink..." from the list of commands. If you don't have Enterprise Manager you can try this command through osql or equivalent.

DBCC SHRINKDATABASE ([YourDatabaseName], 0)

|||Thanks for your reply and help. I was just about to attempt it when I realized from reading another article on the subject than MSDE dynamically recalculates and adjusts files and indices. Turns out all I had to do was be patient and it took care of itself. This apparently is different functionality from the full blown version of SQL2000. Anyhow, thanks
Andy

msdb's sysjobs tables

Please list the cases when the contents of sysjobhistory table would be
deleted.
thanks
--
Cathy BOne way
from SQL server agent--> Properties-->Job System click on Clear Log
http://sqlservercode.blogspot.com/|||Yes,
But I am looking as to why there's no history on a particular job.
Any other ways?
--
Cathy B
"SQL" wrote:
> One way
> from SQL server agent--> Properties-->Job System click on Clear Log
> http://sqlservercode.blogspot.com/
>|||yes
right click on a job-->view job history-->clear all
http://sqlservercode.blogspot.com/|||Thank you.
I am not looking to clear the table.
I am looking for job history that I can't find.
The job was scheduled twice a week to run for a year.
There appears to be no history on it.
And I don't believe anybody here would go and clear the log.
Although anything is possible.
--
Cathy B
"SQL" wrote:
> yes
> right click on a job-->view job history-->clear all
> http://sqlservercode.blogspot.com/
>|||Is your maximum job history log size (rows) set to 1000 and you have
reached that number a year ago?|||when somebody does "delete from sysjobhistory"
Cathy Boehm wrote:
> Please list the cases when the contents of sysjobhistory table would be
> deleted.
> thanks
> --
> Cathy B

msdb restore?

A terrible event has occurred. Our SQL Server 2000 database was attacked an
d
the master, msdb, etc was deleted. We used a utility to undelete those file
s
and have saved them. We had to reinstall Windows and SQL Server, but am
unable to replace the msdb. We have no backup so that isn't an option. Is
there a way to restore our DTS packages from the original msdb? Thanks.Did you try attaching it with a different name?
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> A terrible event has occurred. Our SQL Server 2000 database was attacked
and
> the master, msdb, etc was deleted. We used a utility to undelete those
files
> and have saved them. We had to reinstall Windows and SQL Server, but am
> unable to replace the msdb. We have no backup so that isn't an option.
Is
> there a way to restore our DTS packages from the original msdb? Thanks.|||I tried but I get the following error:
I/O error 38(Reached the end of the file.) detected during read at offset
0000000000000000 in file 'C:\Mymsdbdata.mdf'.
Connection Broken
"Aaron [SQL Server MVP]" wrote:

> Did you try attaching it with a different name?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> and
> files
> Is
>
>|||> I tried but I get the following error:
What *exactly* did you try? I see that you renamed the MDF file, I meant to
attach as a different database name.
If the file is corrupt, and you may be S.O.L. (this is what backups are
for).
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...[vbcol=seagreen]
> I/O error 38(Reached the end of the file.) detected during read at offset
> 0000000000000000 in file 'C:\Mymsdbdata.mdf'.
> Connection Broken
> "Aaron [SQL Server MVP]" wrote:
>
attacked[vbcol=seagreen]
those[vbcol=seagreen]
am[vbcol=seagreen]
option.[vbcol=seagreen]
Thanks.[vbcol=seagreen]|||I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
ran the following in Query Analyser:
exec sp_attach_db @.dbname =
N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filename2=N'C:\Mymsdblog.ldf'
"Aaron [SQL Server MVP]" wrote:

> What *exactly* did you try? I see that you renamed the MDF file, I meant
to
> attach as a different database name.
> If the file is corrupt, and you may be S.O.L. (this is what backups are
> for).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...
> attacked
> those
> am
> option.
> Thanks.
>
>|||The data file is 0k, but the log file has data in it. I'm hoping that since
msdb was never backed up that it can be rebuilt using the log file. Can thi
s
be done?
"Phill" wrote:
[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filename2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
>|||I think you may have to open a case with PSS, or cut your losses. In the
future: backup is a very important word.
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filename2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
>
meant to[vbcol=seagreen]
offset[vbcol=seagreen]
but[vbcol=seagreen]|||Thanks Aaron. I knida knew that answer but didn't want to hear it.
"Aaron [SQL Server MVP]" wrote:

> I think you may have to open a case with PSS, or cut your losses. In the
> future: backup is a very important word.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...
> meant to
> offset
> but
>
>

msdb restore?

A terrible event has occurred. Our SQL Server 2000 database was attacked and
the master, msdb, etc was deleted. We used a utility to undelete those files
and have saved them. We had to reinstall Windows and SQL Server, but am
unable to replace the msdb. We have no backup so that isn't an option. Is
there a way to restore our DTS packages from the original msdb? Thanks.
Did you try attaching it with a different name?
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> A terrible event has occurred. Our SQL Server 2000 database was attacked
and
> the master, msdb, etc was deleted. We used a utility to undelete those
files
> and have saved them. We had to reinstall Windows and SQL Server, but am
> unable to replace the msdb. We have no backup so that isn't an option.
Is
> there a way to restore our DTS packages from the original msdb? Thanks.
|||I tried but I get the following error:
I/O error 38(Reached the end of the file.) detected during read at offset
0000000000000000 in file 'C:\Mymsdbdata.mdf'.
Connection Broken
"Aaron [SQL Server MVP]" wrote:

> Did you try attaching it with a different name?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> and
> files
> Is
>
>
|||> I tried but I get the following error:
What *exactly* did you try? I see that you renamed the MDF file, I meant to
attach as a different database name.
If the file is corrupt, and you may be S.O.L. (this is what backups are
for).
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...[vbcol=seagreen]
> I/O error 38(Reached the end of the file.) detected during read at offset
> 0000000000000000 in file 'C:\Mymsdbdata.mdf'.
> Connection Broken
> "Aaron [SQL Server MVP]" wrote:
attacked[vbcol=seagreen]
those[vbcol=seagreen]
am[vbcol=seagreen]
option.[vbcol=seagreen]
Thanks.[vbcol=seagreen]
|||I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
ran the following in Query Analyser:
exec sp_attach_db @.dbname =
N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filenam e2=N'C:\Mymsdblog.ldf'
"Aaron [SQL Server MVP]" wrote:

> What *exactly* did you try? I see that you renamed the MDF file, I meant to
> attach as a different database name.
> If the file is corrupt, and you may be S.O.L. (this is what backups are
> for).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...
> attacked
> those
> am
> option.
> Thanks.
>
>
|||The data file is 0k, but the log file has data in it. I'm hoping that since
msdb was never backed up that it can be rebuilt using the log file. Can this
be done?
"Phill" wrote:
[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filenam e2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
|||I think you may have to open a case with PSS, or cut your losses. In the
future: backup is a very important word.
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filenam e2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
meant to[vbcol=seagreen]
offset[vbcol=seagreen]
but[vbcol=seagreen]
|||Thanks Aaron. I knida knew that answer but didn't want to hear it.
"Aaron [SQL Server MVP]" wrote:

> I think you may have to open a case with PSS, or cut your losses. In the
> future: backup is a very important word.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...
> meant to
> offset
> but
>
>