Diferrential Backup's

MS 2000 Server MSSQL 7. For an experiment I set up a differential backup
with Enterprise Manage on a training database. I cannot figure out how to
reconfigure the backup; turn it off, rename it, set new time, etc. Is
there anyway to at least turn this differential backup off? ThanksHi
That backup should be run from a scheduled job that appears in the
Management/SQL Server Agent/Jobs branch in Enterprise Manager, you can then
right click and disable or delete the given job.
John
"JD Henderson" <jdhend@.hotmail.com> wrote in message
news:MPG.1a173dac5a7c2c1a989680@.msnews.microsoft.com...
> MS 2000 Server MSSQL 7. For an experiment I set up a differential backup
> with Enterprise Manage on a training database. I cannot figure out how to
> reconfigure the backup; turn it off, rename it, set new time, etc. Is
> there anyway to at least turn this differential backup off? Thanks|||Thanks John
In article <bol5ef$4j6$1@.sparta.btinternet.com>,
jbellnewsposts@.hotmail.com says...
> Hi
> That backup should be run from a scheduled job that appears in the
> Management/SQL Server Agent/Jobs branch in Enterprise Manager, you can then
> right click and disable or delete the given job.
> John
> "JD Henderson" <jdhend@.hotmail.com> wrote in message
> news:MPG.1a173dac5a7c2c1a989680@.msnews.microsoft.com...
> > MS 2000 Server MSSQL 7. For an experiment I set up a differential backup
> > with Enterprise Manage on a training database. I cannot figure out how to
> > reconfigure the backup; turn it off, rename it, set new time, etc. Is
> > there anyway to at least turn this differential backup off? Thanks
>
>

Diagram Editor doesn't work in SQLServer 2000

Hi,
All of a sudden the other day my diagram editor stopped working. I really rely on and can't figure out what happened. Everything else works fine. Since the problem started, I installed SP3 and it didn't help. I'm using a 60 day eval version of SQLS
erver which may have something to do with it (I have one licensed copy that I will install on another machine and am ordering another copy at the moment). Anyways, any ideas?
tdk
tdk,
Did you install VB6 SP6? If so you overwrote the working version of
mdt2df.dll. You will need to restore this dll and register it from before
the update. If you didn't you can try installing an older version of
mdt2df.dll.
-Sam Matzen
"tdk" <tdk@.discussions.microsoft.com> wrote in message
news:9340199E-172E-436A-84CA-275B77CDD642@.microsoft.com...
> Hi,
> All of a sudden the other day my diagram editor stopped working. I
really rely on and can't figure out what happened. Everything else works
fine. Since the problem started, I installed SP3 and it didn't help. I'm
using a 60 day eval version of SQLServer which may have something to do with
it (I have one licensed copy that I will install on another machine and am
ordering another copy at the moment). Anyways, any ideas?
> tdk
|||I have a similar problem that this causing me all kinds annoyance, in my
case diagrams created in Enterprise Manager are only viewable in EM and
diagrams created though Access (through Access Data Projects) are only
viewable in Access. If I try to view a diagram in one tool that was created
in the other the viewing program crashes. I do have VB6 installed so I
wonder if this is related...any links, additional documentation on this
problem? How would you even know which version of the dll is the proper one
to be compatible with everything?
|||Jon,
All I know is that when I installed VB6 Service Pack 6 it overrode the .dll
and my Enterprise Manager diagrams no longer worked.
I don't think there is any way to view Access diagrams in Enterprise
Manager.
-Sam Matzen
"Jon" <jonremovemewest@.msn.com> wrote in message
news:uL$xg6lcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> I have a similar problem that this causing me all kinds annoyance, in my
> case diagrams created in Enterprise Manager are only viewable in EM and
> diagrams created though Access (through Access Data Projects) are only
> viewable in Access. If I try to view a diagram in one tool that was
created
> in the other the viewing program crashes. I do have VB6 installed so I
> wonder if this is related...any links, additional documentation on this
> problem? How would you even know which version of the dll is the proper
one
> to be compatible with everything?
>
|||Ah ha. I found an older version where I unpacked the SQLServer eval version onto my drive (\dir mdt2df.dll /s
Thanks a bunch for the help Samuel.
Tyson
"Samuel L Matzen" wrote:

> tdk,
> Did you install VB6 SP6? If so you overwrote the working version of
> mdt2df.dll. You will need to restore this dll and register it from before
> the update. If you didn't you can try installing an older version of
> mdt2df.dll.
> -Sam Matzen
>
> "tdk" <tdk@.discussions.microsoft.com> wrote in message
> news:9340199E-172E-436A-84CA-275B77CDD642@.microsoft.com...
> really rely on and can't figure out what happened. Everything else works
> fine. Since the problem started, I installed SP3 and it didn't help. I'm
> using a 60 day eval version of SQLServer which may have something to do with
> it (I have one licensed copy that I will install on another machine and am
> ordering another copy at the moment). Anyways, any ideas?
>
>
|||Thanks Samuel, I definitely do have VB SP6 on my machine. I am fairly certain it was installed later than SQLServer. The version of MDT2DF.DLL that I have is 2.0.0.9586, which from what I can gather about version updates on the msdn site is a fairly ne
w version. The question is, how do I get an old version of just that dll? Once I have it, I know how to register it (asusming it's a COM dll, regsvr32).
Tyson
"Samuel L Matzen" wrote:

> tdk,
> Did you install VB6 SP6? If so you overwrote the working version of
> mdt2df.dll. You will need to restore this dll and register it from before
> the update. If you didn't you can try installing an older version of
> mdt2df.dll.
> -Sam Matzen
>
> "tdk" <tdk@.discussions.microsoft.com> wrote in message
> news:9340199E-172E-436A-84CA-275B77CDD642@.microsoft.com...
> really rely on and can't figure out what happened. Everything else works
> fine. Since the problem started, I installed SP3 and it didn't help. I'm
> using a 60 day eval version of SQLServer which may have something to do with
> it (I have one licensed copy that I will install on another machine and am
> ordering another copy at the moment). Anyways, any ideas?
>
>

Diagram Editor doesn't work in SQLServer 2000

Hi,
All of a sudden the other day my diagram editor stopped working. I really rely on and can't figure out what happened. Everything else works fine. Since the problem started, I installed SP3 and it didn't help. I'm using a 60 day eval version of SQLServer which may have something to do with it (I have one licensed copy that I will install on another machine and am ordering another copy at the moment). Anyways, any ideas?
tdktdk,
Did you install VB6 SP6? If so you overwrote the working version of
mdt2df.dll. You will need to restore this dll and register it from before
the update. If you didn't you can try installing an older version of
mdt2df.dll.
-Sam Matzen
"tdk" <tdk@.discussions.microsoft.com> wrote in message
news:9340199E-172E-436A-84CA-275B77CDD642@.microsoft.com...
> Hi,
> All of a sudden the other day my diagram editor stopped working. I
really rely on and can't figure out what happened. Everything else works
fine. Since the problem started, I installed SP3 and it didn't help. I'm
using a 60 day eval version of SQLServer which may have something to do with
it (I have one licensed copy that I will install on another machine and am
ordering another copy at the moment). Anyways, any ideas?
> tdk|||I have a similar problem that this causing me all kinds annoyance, in my
case diagrams created in Enterprise Manager are only viewable in EM and
diagrams created though Access (through Access Data Projects) are only
viewable in Access. If I try to view a diagram in one tool that was created
in the other the viewing program crashes. I do have VB6 installed so I
wonder if this is related...any links, additional documentation on this
problem? How would you even know which version of the dll is the proper one
to be compatible with everything?|||Jon,
All I know is that when I installed VB6 Service Pack 6 it overrode the .dll
and my Enterprise Manager diagrams no longer worked.
I don't think there is any way to view Access diagrams in Enterprise
Manager.
-Sam Matzen
"Jon" <jonremovemewest@.msn.com> wrote in message
news:uL$xg6lcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> I have a similar problem that this causing me all kinds annoyance, in my
> case diagrams created in Enterprise Manager are only viewable in EM and
> diagrams created though Access (through Access Data Projects) are only
> viewable in Access. If I try to view a diagram in one tool that was
created
> in the other the viewing program crashes. I do have VB6 installed so I
> wonder if this is related...any links, additional documentation on this
> problem? How would you even know which version of the dll is the proper
one
> to be compatible with everything?
>

Diagram Editor doesn't work in SQLServer 2000

Hi,
All of a sudden the other day my diagram editor stopped working. I really r
ely on and can't figure out what happened. Everything else works fine. Sin
ce the problem started, I installed SP3 and it didn't help. I'm using a 60
day eval version of SQLS
erver which may have something to do with it (I have one licensed copy that
I will install on another machine and am ordering another copy at the moment
). Anyways, any ideas?
tdktdk,
Did you install VB6 SP6? If so you overwrote the working version of
mdt2df.dll. You will need to restore this dll and register it from before
the update. If you didn't you can try installing an older version of
mdt2df.dll.
-Sam Matzen
"tdk" <tdk@.discussions.microsoft.com> wrote in message
news:9340199E-172E-436A-84CA-275B77CDD642@.microsoft.com...
> Hi,
> All of a sudden the other day my diagram editor stopped working. I
really rely on and can't figure out what happened. Everything else works
fine. Since the problem started, I installed SP3 and it didn't help. I'm
using a 60 day eval version of SQLServer which may have something to do with
it (I have one licensed copy that I will install on another machine and am
ordering another copy at the moment). Anyways, any ideas?
> tdk|||I have a similar problem that this causing me all kinds annoyance, in my
case diagrams created in Enterprise Manager are only viewable in EM and
diagrams created though Access (through Access Data Projects) are only
viewable in Access. If I try to view a diagram in one tool that was created
in the other the viewing program crashes. I do have VB6 installed so I
wonder if this is related...any links, additional documentation on this
problem? How would you even know which version of the dll is the proper one
to be compatible with everything?|||Jon,
All I know is that when I installed VB6 Service Pack 6 it overrode the .dll
and my Enterprise Manager diagrams no longer worked.
I don't think there is any way to view Access diagrams in Enterprise
Manager.
-Sam Matzen
"Jon" <jonremovemewest@.msn.com> wrote in message
news:uL$xg6lcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> I have a similar problem that this causing me all kinds annoyance, in my
> case diagrams created in Enterprise Manager are only viewable in EM and
> diagrams created though Access (through Access Data Projects) are only
> viewable in Access. If I try to view a diagram in one tool that was
created
> in the other the viewing program crashes. I do have VB6 installed so I
> wonder if this is related...any links, additional documentation on this
> problem? How would you even know which version of the dll is the proper
one
> to be compatible with everything?
>|||Ah ha. I found an older version where I unpacked the SQLServer eval version
onto my drive (\dir mdt2df.dll /s
Thanks a bunch for the help Samuel.
Tyson
"Samuel L Matzen" wrote:

> tdk,
> Did you install VB6 SP6? If so you overwrote the working version of
> mdt2df.dll. You will need to restore this dll and register it from before
> the update. If you didn't you can try installing an older version of
> mdt2df.dll.
> -Sam Matzen
>
> "tdk" <tdk@.discussions.microsoft.com> wrote in message
> news:9340199E-172E-436A-84CA-275B77CDD642@.microsoft.com...
> really rely on and can't figure out what happened. Everything else works
> fine. Since the problem started, I installed SP3 and it didn't help. I'm
> using a 60 day eval version of SQLServer which may have something to do wi
th
> it (I have one licensed copy that I will install on another machine and am
> ordering another copy at the moment). Anyways, any ideas?
>
>|||Thanks Samuel, I definitely do have VB SP6 on my machine. I am fairly cert
ain it was installed later than SQLServer. The version of MDT2DF.DLL that I
have is 2.0.0.9586, which from what I can gather about version updates on t
he msdn site is a fairly ne
w version. The question is, how do I get an old version of just that dll?
Once I have it, I know how to register it (asusming it's a COM dll, regsvr32
).
Tyson
"Samuel L Matzen" wrote:

> tdk,
> Did you install VB6 SP6? If so you overwrote the working version of
> mdt2df.dll. You will need to restore this dll and register it from before
> the update. If you didn't you can try installing an older version of
> mdt2df.dll.
> -Sam Matzen
>
> "tdk" <tdk@.discussions.microsoft.com> wrote in message
> news:9340199E-172E-436A-84CA-275B77CDD642@.microsoft.com...
> really rely on and can't figure out what happened. Everything else works
> fine. Since the problem started, I installed SP3 and it didn't help. I'm
> using a 60 day eval version of SQLServer which may have something to do wi
th
> it (I have one licensed copy that I will install on another machine and am
> ordering another copy at the moment). Anyways, any ideas?
>
>

Development Environment Needs?

Trying to figure out what development enviroment we need in order to
do the following:

- develop a non-native SQL server stored procedure;
- call a web service or java program from the stored procedure;
- return static values;
- call the stored procedure from a view.

How do I get a hold of the right tools and what do I need to put the
pieces together?

Obviously, I've not used SQL server and I'm looking for the basic
starting point.

Thanks!CG (chelseagraylin@.hotmail.com) writes:
> Trying to figure out what development enviroment we need in order to
> do the following:
> - develop a non-native SQL server stored procedure;
> - call a web service or java program from the stored procedure;
> - return static values;
> - call the stored procedure from a view.

You cannot call stored procedures from views. You can call extended
stored procedures from used-defined functions though, and these you
call from views. (Or use table-valued functions which are basically
parameterized views, and which can be multi-statement.)

Typically you develop extended stored procedures in C++. If you want to
talk .Net you would need a COM interop.

Since you appear to be forward-looking, you might find interest in
the upcoming version of SQL Server, where you can program CLR directly
in SQL Server. In SQL 2005 you can develop the function directly in
CLR. To call a web service there would still be a few things to go
through, but it would certainly be easier.

Beta 2 of SQL Server is expected soon, and this will be a public beta.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||So if I want to do this with the current version (yes, looks like this
will be much easier in the future!), I need a SQL Server environment
set up (hopefully just the desktop version) and then I need an
environment to be able to write a C++ program that is accessible by
SQL Server?

Sounds so easy...|||CG (chelseagraylin@.hotmail.com) writes:
> So if I want to do this with the current version (yes, looks like this
> will be much easier in the future!), I need a SQL Server environment
> set up (hopefully just the desktop version) and then I need an
> environment to be able to write a C++ program that is accessible by
> SQL Server?

For SQL Server I would recommend using Developer Edition, which is at
49 USD only. Developer Edition comes with graphic tools, and having
Query Analyzer to submit queries is invaluable.

However, once you go in production, you are better of with MSDE, since
Developer Edition is not licensed for production. (And for some strange
reason, the graphic tools can be used against MSDE according to the
license.)

You will also need a couple of include files and link libraries. They
come with Devloper Edition.

For the C++ environment I am not really the guy to ask, but Visual
Studio is of course a safe bet. If you go for GNU C++ to use freeware,
you will probably need the Platform SDK, which I have no idea how it
is available outside VS.

> Sounds so easy...

Getting the environment is indeed the easy part. The actual development
is likely to be tougher. Writing extended stored procedures is not for
the faint of heart. Keep in mind that they execute in-process, so an
access violation in your XP can crash the entire SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> (And for some strange reason, the graphic tools can be used against MSDE
> according to the license.)

An important word disappeared here, so I take it again:

> (And for some strange reason, the graphic tools can *not* be used against
> MSDE according to the license.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

To add to Erland's post...

Don't forget source code control, such as Visual Source Safe or PVCS... If
you go the whole Microsoft suite then a MSDN subscription would be an
excellent investment.

John

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns951B2905B452Yazorman@.127.0.0.1...
> CG (chelseagraylin@.hotmail.com) writes:
> > So if I want to do this with the current version (yes, looks like this
> > will be much easier in the future!), I need a SQL Server environment
> > set up (hopefully just the desktop version) and then I need an
> > environment to be able to write a C++ program that is accessible by
> > SQL Server?
> For SQL Server I would recommend using Developer Edition, which is at
> 49 USD only. Developer Edition comes with graphic tools, and having
> Query Analyzer to submit queries is invaluable.
> However, once you go in production, you are better of with MSDE, since
> Developer Edition is not licensed for production. (And for some strange
> reason, the graphic tools can be used against MSDE according to the
> license.)
> You will also need a couple of include files and link libraries. They
> come with Devloper Edition.
> For the C++ environment I am not really the guy to ask, but Visual
> Studio is of course a safe bet. If you go for GNU C++ to use freeware,
> you will probably need the Platform SDK, which I have no idea how it
> is available outside VS.
> > Sounds so easy...
> Getting the environment is indeed the easy part. The actual development
> is likely to be tougher. Writing extended stored procedures is not for
> the faint of heart. Keep in mind that they execute in-process, so an
> access violation in your XP can crash the entire SQL Server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Developing the clever backup strategy

Hi there...
I've been reading around in the BOL to figure out what backup strategy is th
e
"best" available. The database I want to backup, is ~4.5 after a shrink, but
it
of course grows after a while. Same with the log.
The database runs in "full recovery" mode currently, and it is being used du
ring
a normal workday, by around 15 people. What backup setup is the recommended
in
this situation?
I've tried setting it up to backup the database every 4 hours, and the log e
very
30 minutes. This generates a /lot/ of *.trn files, is there anyway to
"consolidate" those after a day? Secondly, when I do a database backup every
24
hours, does that mean that I can then afterward safely truncate the log (I l
ike
keeping things neat ? What do I do with the *.bak and *.trn when they are
over
two days old?
I doubt, therefore I might be.I'd suggest a good, thorough read through of books on line to make sure you
understand things like truncating the logs, full recovery more, simple
recovery mode, etc.
Truncating the logs should probably never be done which is why I suggest thi
s.
burt_king@.yahoo.com
"Kim Noer" wrote:

> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
the
> "best" available. The database I want to backup, is ~4.5 after a shrink, b
ut it
> of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
during
> a normal workday, by around 15 people. What backup setup is the recommende
d in
> this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
every
> 30 minutes. This generates a /lot/ of *.trn files, is there anyway to
> "consolidate" those after a day? Secondly, when I do a database backup eve
ry 24
> hours, does that mean that I can then afterward safely truncate the log (I
like
> keeping things neat ? What do I do with the *.bak and *.trn when they ar
e over
> two days old?
> --
> I doubt, therefore I might be.
>|||I definitely agree. Also, be restrictive using shrink, for more info, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"burt_king" <burt_king@.yahoo.com> wrote in message
news:193B7308-3D54-426E-AF7A-B9983D509983@.microsoft.com...[vbcol=seagreen]
> I'd suggest a good, thorough read through of books on line to make sure yo
u
> understand things like truncating the logs, full recovery more, simple
> recovery mode, etc.
> Truncating the logs should probably never be done which is why I suggest t
his.
>
> --
> burt_king@.yahoo.com
>
> "Kim Noer" wrote:
>|||Usually in a smaller environment like you describe I reccomend a daily full
backup and then differential backups throughout the day. With this scenario
you can put the database in simple recovery mode and not have to worry about
log file growth. I have seen too many environments where they left the
databases in Full Recovery (default) and never back up the logs. A couple
weeks go by and the database is refusing transactions because the disk is
full.
I truncate and shrink the logs every night to make sure it gets done. One
big Insert or something else can throw your log files out of whack. With
this you should make sure your minimum log file is big enough that it does
not have to grow signifigantly during a normal work day.
When you say your DB is 4.5G (I assume G) after the shrink, how big is it
before the shrink? If your database files are shrinking signifigantly
(>20%) every night you need to look at your clustered indexes and fill
factors to make sure there is enough room that the database is not having to
auto grow throughout the day. Also, if you have not done reindexed in a
while, you should look at doing that - see DBCC Reindex (note that this
should be done when the database is not being accessed).
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%231ouK%2327FHA.2816@.tk2msftngp13.phx.gbl...
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
> the "best" available. The database I want to backup, is ~4.5 after a
> shrink, but it of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
> during a normal workday, by around 15 people. What backup setup is the
> recommended in this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
> every 30 minutes. This generates a /lot/ of *.trn files, is there anyway
> to "consolidate" those after a day? Secondly, when I do a database backup
> every 24 hours, does that mean that I can then afterward safely truncate
> the log (I like keeping things neat ? What do I do with the *.bak and
> *.trn when they are over two days old?
> --
> I doubt, therefore I might be.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:uHbwqP37FHA.1140@.tk2msftngp13.phx.gbl
> I definitely agree. Also, be restrictive using shrink, for more info,
> see http://www.karaszi.com/SQLServer/info_dont_shrink.asp
I've read your excellent guide, and I think I actually more now than before
.
Anyway. Currently the database runs in "full recovery" mode. Then I did a
complete backup of the database, and then I tried a dbcc loginfo('database')
Now, according to your guide, status 2 means the VLF is in use. The result i
s
this -
2 253952 8192 65770 0 64 0
2 253952 262144 65769 0 64 0
2 253952 516096 65768 0 128 0
2 278528 770048 65771 0 64 0
2 262144 1048576 65772 0 128 40875000000042200043
...
And this continues all the way to row 308 (your guide tells me I should set
the
allocated size way above the current) -
2 27000832 2188771328 65773 2 64 65760000004588300008
2 27000832 2215772160 65766 0 64 65760000004588300008
As you might be able to see, row 308 have the status 2. Doesn't this mean th
at
this logfile will never go below 2.06GB unless I truncate it manually? Secon
dly,
what about the VLF's below, since they are unused will this cause SQL Server
to
reuse the VLF, or will it proceed from CreateLSN 65760000004588300008?
I doubt, therefore I might be.|||"Kim Noer" <kn@.nospam.dk> wrote in message
news:OZD%23gFE8FHA.1140@.tk2msftngp13.phx.gbl

> Secondly, what about the VLF's below, since they are
> unused will this cause SQL Server to reuse the VLF, or will it
> proceed from CreateLSN 65760000004588300008?
Nevermind, I decided to use your guide as test, that is, create a table, the
n
insert a lot of records, which showed me that SQL Server reuses unused VLF,
even
when they are "before" used VLF's.
I doubt, therefore I might be.

Developing the clever backup strategy

Hi there...
I've been reading around in the BOL to figure out what backup strategy is the
"best" available. The database I want to backup, is ~4.5 after a shrink, but it
of course grows after a while. Same with the log.
The database runs in "full recovery" mode currently, and it is being used during
a normal workday, by around 15 people. What backup setup is the recommended in
this situation?
I've tried setting it up to backup the database every 4 hours, and the log every
30 minutes. This generates a /lot/ of *.trn files, is there anyway to
"consolidate" those after a day? Secondly, when I do a database backup every 24
hours, does that mean that I can then afterward safely truncate the log (I like
keeping things neat ? What do I do with the *.bak and *.trn when they are over
two days old?
I doubt, therefore I might be.
I'd suggest a good, thorough read through of books on line to make sure you
understand things like truncating the logs, full recovery more, simple
recovery mode, etc.
Truncating the logs should probably never be done which is why I suggest this.
burt_king@.yahoo.com
"Kim Noer" wrote:

> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is the
> "best" available. The database I want to backup, is ~4.5 after a shrink, but it
> of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used during
> a normal workday, by around 15 people. What backup setup is the recommended in
> this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log every
> 30 minutes. This generates a /lot/ of *.trn files, is there anyway to
> "consolidate" those after a day? Secondly, when I do a database backup every 24
> hours, does that mean that I can then afterward safely truncate the log (I like
> keeping things neat ? What do I do with the *.bak and *.trn when they are over
> two days old?
> --
> I doubt, therefore I might be.
>
|||I definitely agree. Also, be restrictive using shrink, for more info, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"burt_king" <burt_king@.yahoo.com> wrote in message
news:193B7308-3D54-426E-AF7A-B9983D509983@.microsoft.com...[vbcol=seagreen]
> I'd suggest a good, thorough read through of books on line to make sure you
> understand things like truncating the logs, full recovery more, simple
> recovery mode, etc.
> Truncating the logs should probably never be done which is why I suggest this.
>
> --
> burt_king@.yahoo.com
>
> "Kim Noer" wrote:
|||Usually in a smaller environment like you describe I reccomend a daily full
backup and then differential backups throughout the day. With this scenario
you can put the database in simple recovery mode and not have to worry about
log file growth. I have seen too many environments where they left the
databases in Full Recovery (default) and never back up the logs. A couple
weeks go by and the database is refusing transactions because the disk is
full.
I truncate and shrink the logs every night to make sure it gets done. One
big Insert or something else can throw your log files out of whack. With
this you should make sure your minimum log file is big enough that it does
not have to grow signifigantly during a normal work day.
When you say your DB is 4.5G (I assume G) after the shrink, how big is it
before the shrink? If your database files are shrinking signifigantly
(>20%) every night you need to look at your clustered indexes and fill
factors to make sure there is enough room that the database is not having to
auto grow throughout the day. Also, if you have not done reindexed in a
while, you should look at doing that - see DBCC Reindex (note that this
should be done when the database is not being accessed).
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%231ouK%2327FHA.2816@.tk2msftngp13.phx.gbl...
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
> the "best" available. The database I want to backup, is ~4.5 after a
> shrink, but it of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
> during a normal workday, by around 15 people. What backup setup is the
> recommended in this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
> every 30 minutes. This generates a /lot/ of *.trn files, is there anyway
> to "consolidate" those after a day? Secondly, when I do a database backup
> every 24 hours, does that mean that I can then afterward safely truncate
> the log (I like keeping things neat ? What do I do with the *.bak and
> *.trn when they are over two days old?
> --
> I doubt, therefore I might be.
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:uHbwqP37FHA.1140@.tk2msftngp13.phx.gbl
> I definitely agree. Also, be restrictive using shrink, for more info,
> see http://www.karaszi.com/SQLServer/info_dont_shrink.asp
I've read your excellent guide, and I think I actually more now than before .
Anyway. Currently the database runs in "full recovery" mode. Then I did a
complete backup of the database, and then I tried a dbcc loginfo('database')
Now, according to your guide, status 2 means the VLF is in use. The result is
this -
2 253952 8192 65770 0 64 0
2 253952 262144 65769 0 64 0
2 253952 516096 65768 0 128 0
2 278528 770048 65771 0 64 0
2 262144 1048576 65772 0 128 40875000000042200043
...
And this continues all the way to row 308 (your guide tells me I should set the
allocated size way above the current) -
2 27000832 2188771328 65773 2 64 65760000004588300008
2 27000832 2215772160 65766 0 64 65760000004588300008
As you might be able to see, row 308 have the status 2. Doesn't this mean that
this logfile will never go below 2.06GB unless I truncate it manually? Secondly,
what about the VLF's below, since they are unused will this cause SQL Server to
reuse the VLF, or will it proceed from CreateLSN 65760000004588300008?
I doubt, therefore I might be.
|||"Kim Noer" <kn@.nospam.dk> wrote in message
news:OZD%23gFE8FHA.1140@.tk2msftngp13.phx.gbl

> Secondly, what about the VLF's below, since they are
> unused will this cause SQL Server to reuse the VLF, or will it
> proceed from CreateLSN 65760000004588300008?
Nevermind, I decided to use your guide as test, that is, create a table, then
insert a lot of records, which showed me that SQL Server reuses unused VLF, even
when they are "before" used VLF's.
I doubt, therefore I might be.

Developing the clever backup strategy

Hi there...
I've been reading around in the BOL to figure out what backup strategy is the
"best" available. The database I want to backup, is ~4.5 after a shrink, but it
of course grows after a while. Same with the log.
The database runs in "full recovery" mode currently, and it is being used during
a normal workday, by around 15 people. What backup setup is the recommended in
this situation?
I've tried setting it up to backup the database every 4 hours, and the log every
30 minutes. This generates a /lot/ of *.trn files, is there anyway to
"consolidate" those after a day? Secondly, when I do a database backup every 24
hours, does that mean that I can then afterward safely truncate the log (I like
keeping things neat :)? What do I do with the *.bak and *.trn when they are over
two days old?
--
I doubt, therefore I might be.I'd suggest a good, thorough read through of books on line to make sure you
understand things like truncating the logs, full recovery more, simple
recovery mode, etc.
Truncating the logs should probably never be done which is why I suggest this.
burt_king@.yahoo.com
"Kim Noer" wrote:
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is the
> "best" available. The database I want to backup, is ~4.5 after a shrink, but it
> of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used during
> a normal workday, by around 15 people. What backup setup is the recommended in
> this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log every
> 30 minutes. This generates a /lot/ of *.trn files, is there anyway to
> "consolidate" those after a day? Secondly, when I do a database backup every 24
> hours, does that mean that I can then afterward safely truncate the log (I like
> keeping things neat :)? What do I do with the *.bak and *.trn when they are over
> two days old?
> --
> I doubt, therefore I might be.
>|||I definitely agree. Also, be restrictive using shrink, for more info, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"burt_king" <burt_king@.yahoo.com> wrote in message
news:193B7308-3D54-426E-AF7A-B9983D509983@.microsoft.com...
> I'd suggest a good, thorough read through of books on line to make sure you
> understand things like truncating the logs, full recovery more, simple
> recovery mode, etc.
> Truncating the logs should probably never be done which is why I suggest this.
>
> --
> burt_king@.yahoo.com
>
> "Kim Noer" wrote:
>> Hi there...
>> I've been reading around in the BOL to figure out what backup strategy is the
>> "best" available. The database I want to backup, is ~4.5 after a shrink, but it
>> of course grows after a while. Same with the log.
>> The database runs in "full recovery" mode currently, and it is being used during
>> a normal workday, by around 15 people. What backup setup is the recommended in
>> this situation?
>>
>> I've tried setting it up to backup the database every 4 hours, and the log every
>> 30 minutes. This generates a /lot/ of *.trn files, is there anyway to
>> "consolidate" those after a day? Secondly, when I do a database backup every 24
>> hours, does that mean that I can then afterward safely truncate the log (I like
>> keeping things neat :)? What do I do with the *.bak and *.trn when they are over
>> two days old?
>> --
>> I doubt, therefore I might be.
>>|||Usually in a smaller environment like you describe I reccomend a daily full
backup and then differential backups throughout the day. With this scenario
you can put the database in simple recovery mode and not have to worry about
log file growth. I have seen too many environments where they left the
databases in Full Recovery (default) and never back up the logs. A couple
weeks go by and the database is refusing transactions because the disk is
full.
I truncate and shrink the logs every night to make sure it gets done. One
big Insert or something else can throw your log files out of whack. With
this you should make sure your minimum log file is big enough that it does
not have to grow signifigantly during a normal work day.
When you say your DB is 4.5G (I assume G) after the shrink, how big is it
before the shrink? If your database files are shrinking signifigantly
(>20%) every night you need to look at your clustered indexes and fill
factors to make sure there is enough room that the database is not having to
auto grow throughout the day. Also, if you have not done reindexed in a
while, you should look at doing that - see DBCC Reindex (note that this
should be done when the database is not being accessed).
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%231ouK%2327FHA.2816@.tk2msftngp13.phx.gbl...
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
> the "best" available. The database I want to backup, is ~4.5 after a
> shrink, but it of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
> during a normal workday, by around 15 people. What backup setup is the
> recommended in this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
> every 30 minutes. This generates a /lot/ of *.trn files, is there anyway
> to "consolidate" those after a day? Secondly, when I do a database backup
> every 24 hours, does that mean that I can then afterward safely truncate
> the log (I like keeping things neat :)? What do I do with the *.bak and
> *.trn when they are over two days old?
> --
> I doubt, therefore I might be.|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:uHbwqP37FHA.1140@.tk2msftngp13.phx.gbl
> I definitely agree. Also, be restrictive using shrink, for more info,
> see http://www.karaszi.com/SQLServer/info_dont_shrink.asp
I've read your excellent guide, and I think I actually more now than before :).
Anyway. Currently the database runs in "full recovery" mode. Then I did a
complete backup of the database, and then I tried a dbcc loginfo('database')
Now, according to your guide, status 2 means the VLF is in use. The result is
this -
2 253952 8192 65770 0 64 0
2 253952 262144 65769 0 64 0
2 253952 516096 65768 0 128 0
2 278528 770048 65771 0 64 0
2 262144 1048576 65772 0 128 40875000000042200043
...
And this continues all the way to row 308 (your guide tells me I should set the
allocated size way above the current) -
2 27000832 2188771328 65773 2 64 65760000004588300008
2 27000832 2215772160 65766 0 64 65760000004588300008
As you might be able to see, row 308 have the status 2. Doesn't this mean that
this logfile will never go below 2.06GB unless I truncate it manually? Secondly,
what about the VLF's below, since they are unused will this cause SQL Server to
reuse the VLF, or will it proceed from CreateLSN 65760000004588300008?
--
I doubt, therefore I might be.|||"Kim Noer" <kn@.nospam.dk> wrote in message
news:OZD%23gFE8FHA.1140@.tk2msftngp13.phx.gbl
> Secondly, what about the VLF's below, since they are
> unused will this cause SQL Server to reuse the VLF, or will it
> proceed from CreateLSN 65760000004588300008?
Nevermind, I decided to use your guide as test, that is, create a table, then
insert a lot of records, which showed me that SQL Server reuses unused VLF, even
when they are "before" used VLF's.
--
I doubt, therefore I might be.

Determining table for a particular File_id:Page_No

Hello,
I have a deadlock message and I can not figure out the resource that the
SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
1204):
PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
the page number but my question is:
How do I determine the table that this page belongs to?
Thanks in advance,
Tom
You can use DBCC PAGE. Google and you will find how to use it. It will return object id in page
header. Use the function OBJECT_NAME() to convert from id to name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:OKdyoeFqFHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>
|||try...
dbcc traceon (3604)
dbcc page(11,3,4791032)
read page header, find m_objId
Aleksandar Grbic
MCDBA, Senior Database Administrator
"TJT" wrote:

> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>
>

Determining table for a particular File_id:Page_No

Hello,
I have a deadlock message and I can not figure out the resource that the
SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
1204):
PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
the page number but my question is:
How do I determine the table that this page belongs to?
Thanks in advance,
TomYou can use DBCC PAGE. Google and you will find how to use it. It will return object id in page
header. Use the function OBJECT_NAME() to convert from id to name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:OKdyoeFqFHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>|||try...
dbcc traceon (3604)
dbcc page(11,3,4791032)
read page header, find m_objId
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"TJT" wrote:
> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>
>

Determining table for a particular File_id:Page_No

Hello,
I have a deadlock message and I can not figure out the resource that the
SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
1204):
PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
the page number but my question is:
How do I determine the table that this page belongs to?
Thanks in advance,
TomYou can use DBCC PAGE. Google and you will find how to use it. It will retur
n object id in page
header. Use the function OBJECT_NAME() to convert from id to name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:OKdyoeFqFHA.1096@.TK2MSFTNGP11.phx.gbl...seagreen">
> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>|||try...
dbcc traceon (3604)
dbcc page(11,3,4791032)
read page header, find m_objId
Aleksandar Grbic
MCDBA, Senior Database Administrator
"TJT" wrote:

> Hello,
> I have a deadlock message and I can not figure out the resource that the
> SPID is waiting on. I see a message in the ErrorLog (running Trace Flag
> 1204):
> PAG: 11:3:4791032 CleanCnt:1 Mode: IX Flags: 0x2
> I know that the 11 is the database ID and 3 is the file_ID and 4791032 is
> the page number but my question is:
> How do I determine the table that this page belongs to?
> Thanks in advance,
> Tom
>
>