Wednesday, March 28, 2012
Latest Build
What is the latest build of SS2K5 and where may I inquire as to a link to
where I may download it.
Currently running SP2 build 3042
--
Thanks ...
J. KuschThe latest build I know of is 3175, a cumulative package including several
hotfixes. You can get the hotfix through PSS if you open a case and can
demonstrate that your environment has symptoms that at least one of the
hotfixes address.
http://support.microsoft.com/kb/936305
The latest *public* build I know is 3152. Unless you are directly affected
by one of the problems fixed as of 3175, this is the one I recommend
obtaining... since publicly available hotfixes are generally much "safer"
and tested than those that can only be obtained through PSS to fix specific
problems.
http://support.microsoft.com/kb/933097
--
Aaron Bertrand
SQL Server MVP
"Jay Kusch" <JayKusch@.discussions.microsoft.com> wrote in message
news:78B0C5D4-FE38-4AFA-A3BC-9194EA79E1C6@.microsoft.com...
> Morning~
> What is the latest build of SS2K5 and where may I inquire as to a link to
> where I may download it.
> Currently running SP2 build 3042
> --
> Thanks ...
> J. Kusch|||I requested Build 3175 thru /kb/936305 but they told me the hotfix for
platform x86 is unavailable? I apply SP2 on active node of SQL Server 2005
Std active/passive, one instance. an error popup says "A recently applied
update, KB921896, failed to install" but installation completed and when
select @.@.version, it says "9.00.3042.00" which is SP2. Database Engine is
the only thing installed, all is working fine except when I start configuring
DBMail and received "activation failure". After searching online, MSDN forum
says Build 3175 will fix this along with Maintenence Plan that's also broke
by applying SP2. Can someone please tell me how can I obtain Build 3175?
Thank you in advance for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> The latest build I know of is 3175, a cumulative package including several
> hotfixes. You can get the hotfix through PSS if you open a case and can
> demonstrate that your environment has symptoms that at least one of the
> hotfixes address.
> http://support.microsoft.com/kb/936305
> The latest *public* build I know is 3152. Unless you are directly affected
> by one of the problems fixed as of 3175, this is the one I recommend
> obtaining... since publicly available hotfixes are generally much "safer"
> and tested than those that can only be obtained through PSS to fix specific
> problems.
> http://support.microsoft.com/kb/933097
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Jay Kusch" <JayKusch@.discussions.microsoft.com> wrote in message
> news:78B0C5D4-FE38-4AFA-A3BC-9194EA79E1C6@.microsoft.com...
> > Morning~
> >
> > What is the latest build of SS2K5 and where may I inquire as to a link to
> > where I may download it.
> >
> > Currently running SP2 build 3042
> > --
> > Thanks ...
> >
> > J. Kusch
>
>|||Maybe this link helps:
http://sqlserverbuilds.blogspot.com/
--
Ekrem Ã?nsoy
"Li" <Li@.discussions.microsoft.com> wrote in message
news:07EE0796-C500-41B6-9D0F-6907285376FF@.microsoft.com...
>I requested Build 3175 thru /kb/936305 but they told me the hotfix for
> platform x86 is unavailable? I apply SP2 on active node of SQL Server
> 2005
> Std active/passive, one instance. an error popup says "A recently applied
> update, KB921896, failed to install" but installation completed and when
> select @.@.version, it says "9.00.3042.00" which is SP2. Database Engine is
> the only thing installed, all is working fine except when I start
> configuring
> DBMail and received "activation failure". After searching online, MSDN
> forum
> says Build 3175 will fix this along with Maintenence Plan that's also
> broke
> by applying SP2. Can someone please tell me how can I obtain Build 3175?
> Thank you in advance for your help.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> The latest build I know of is 3175, a cumulative package including
>> several
>> hotfixes. You can get the hotfix through PSS if you open a case and can
>> demonstrate that your environment has symptoms that at least one of the
>> hotfixes address.
>> http://support.microsoft.com/kb/936305
>> The latest *public* build I know is 3152. Unless you are directly
>> affected
>> by one of the problems fixed as of 3175, this is the one I recommend
>> obtaining... since publicly available hotfixes are generally much "safer"
>> and tested than those that can only be obtained through PSS to fix
>> specific
>> problems.
>> http://support.microsoft.com/kb/933097
>> --
>> Aaron Bertrand
>> SQL Server MVP
>>
>>
>> "Jay Kusch" <JayKusch@.discussions.microsoft.com> wrote in message
>> news:78B0C5D4-FE38-4AFA-A3BC-9194EA79E1C6@.microsoft.com...
>> > Morning~
>> >
>> > What is the latest build of SS2K5 and where may I inquire as to a link
>> > to
>> > where I may download it.
>> >
>> > Currently running SP2 build 3042
>> > --
>> > Thanks ...
>> >
>> > J. Kusch
>>sql
Latest Build
What is the latest build of SS2K5 and where may I inquire as to a link to
where I may download it.
Currently running SP2 build 3042
--
Thanks ...
J. KuschThe latest build I know of is 3175, a cumulative package including several
hotfixes. You can get the hotfix through PSS if you open a case and can
demonstrate that your environment has symptoms that at least one of the
hotfixes address.
http://support.microsoft.com/kb/936305
The latest *public* build I know is 3152. Unless you are directly affected
by one of the problems fixed as of 3175, this is the one I recommend
obtaining... since publicly available hotfixes are generally much "safer"
and tested than those that can only be obtained through PSS to fix specific
problems.
http://support.microsoft.com/kb/933097
Aaron Bertrand
SQL Server MVP
"Jay Kusch" <JayKusch@.discussions.microsoft.com> wrote in message
news:78B0C5D4-FE38-4AFA-A3BC-9194EA79E1C6@.microsoft.com...
> Morning~
> What is the latest build of SS2K5 and where may I inquire as to a link to
> where I may download it.
> Currently running SP2 build 3042
> --
> Thanks ...
> J. Kusch
Latest Build
What is the latest build of SS2K5 and where may I inquire as to a link to
where I may download it.
Currently running SP2 build 3042
Thanks ...
J. Kusch
The latest build I know of is 3175, a cumulative package including several
hotfixes. You can get the hotfix through PSS if you open a case and can
demonstrate that your environment has symptoms that at least one of the
hotfixes address.
http://support.microsoft.com/kb/936305
The latest *public* build I know is 3152. Unless you are directly affected
by one of the problems fixed as of 3175, this is the one I recommend
obtaining... since publicly available hotfixes are generally much "safer"
and tested than those that can only be obtained through PSS to fix specific
problems.
http://support.microsoft.com/kb/933097
Aaron Bertrand
SQL Server MVP
"Jay Kusch" <JayKusch@.discussions.microsoft.com> wrote in message
news:78B0C5D4-FE38-4AFA-A3BC-9194EA79E1C6@.microsoft.com...
> Morning~
> What is the latest build of SS2K5 and where may I inquire as to a link to
> where I may download it.
> Currently running SP2 build 3042
> --
> Thanks ...
> J. Kusch
Friday, March 23, 2012
Last try: Polling the network for SQL servers, including named instances
Hi group,
I am trying to get a list of SQL Server installations running on the
network. I tried to use sqlcmd -L (or osql -L). However, when I do this
from my box the named instances do not show up, whereas when it is run from
a server box those instances do get listed.
What is required to make the command return the full list? Is there other
(simple) way to do it?
Thanks.
QuentinThe -L option will not give you the 'full list' in most cases. The most
reliable way I found is to enumerate the servers in a network/domain, and for
each server run a multiplicity of tests to see whether there are SQL instance
running or installed (e.g. checking for the SQL services. interrogating the
registry for a list of installed SQL instances). This approach can be time
consuming in a large network, and the network security may not like it.
Ideally, you should have some type of inentory in place and updated so that
you don't have to do this.
Exactly how you want to do this and whether you can simplify the work to get
it done depend on a number of factors: (1) how reliable you want this list to
be, and (2) the specifics of your environment. For instance, if you know that
you can count on your SQL Browser running on the SQL boxes, you could just
enumerate your servers, and run SQLPing.exe against each to get a list.
Linchi
"Quentin Ran" wrote:
> Hopefully I do not end up empty-handed.
> Hi group,
> I am trying to get a list of SQL Server installations running on the
> network. I tried to use sqlcmd -L (or osql -L). However, when I do this
> from my box the named instances do not show up, whereas when it is run from
> a server box those instances do get listed.
> What is required to make the command return the full list? Is there other
> (simple) way to do it?
> Thanks.
> Quentin
>
>
>|||Thanks Linchi.
independent of what I want to use it for, the fact that sqlcmd -L returns
different results when run from differnet servers makes it interesting, if
not bizzare ...
Quentin
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:0C43A8EF-CF62-45F4-B060-CC72396CDEE4@.microsoft.com...
> The -L option will not give you the 'full list' in most cases. The most
> reliable way I found is to enumerate the servers in a network/domain, and
> for
> each server run a multiplicity of tests to see whether there are SQL
> instance
> running or installed (e.g. checking for the SQL services. interrogating
> the
> registry for a list of installed SQL instances). This approach can be time
> consuming in a large network, and the network security may not like it.
> Ideally, you should have some type of inentory in place and updated so
> that
> you don't have to do this.
> Exactly how you want to do this and whether you can simplify the work to
> get
> it done depend on a number of factors: (1) how reliable you want this list
> to
> be, and (2) the specifics of your environment. For instance, if you know
> that
> you can count on your SQL Browser running on the SQL boxes, you could just
> enumerate your servers, and run SQLPing.exe against each to get a list.
> Linchi
> "Quentin Ran" wrote:
>> Hopefully I do not end up empty-handed.
>> Hi group,
>> I am trying to get a list of SQL Server installations running on the
>> network. I tried to use sqlcmd -L (or osql -L). However, when I do this
>> from my box the named instances do not show up, whereas when it is run
>> from
>> a server box those instances do get listed.
>> What is required to make the command return the full list? Is there
>> other
>> (simple) way to do it?
>> Thanks.
>> Quentin
>>
>>
Friday, March 9, 2012
Largest tables
Is there an easier way to determine which tables in a db are the largest other than running sp_spaceused for each table? Interested mainly in physical size.
ThanksRun DBCC UPDATEUSAGE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_24rp.asp) to bring your statistics up to date, then you can use:SELECT so.name, 8 * Sum(si.reserved)
FROM dbo.sysobjects AS so
JOIN dbo.sysindexes AS si
ON (si.id = so.id)
ORDER BY 2 desc, 1for a good estimate.
-PatP
Large transaction log backups
running a full backup on Sundays, followed by transaction log backups each
day of the week. As far as I understand it, each transaction log should
then be backing up transactions made since the last backup (whether that's a
full or transaction).
What I'm actually seeing though is that the first transaction log backup
after each full one is far larger than I'm expecting (in comparison to the
size of the database anyway).
For example, one of those is producing backup files of these sizes:
06/05/2007 02:12 857,955,840 ADM_db_200705060200.BAK
07/05/2007 00:12 1,313,012,224 ADM_tlog_200705070000.TRN
08/05/2007 00:03 611,840 ADM_tlog_200705080001.TRN
09/05/2007 00:03 5,133,824 ADM_tlog_200705090001.TRN
10/05/2007 00:02 6,510,080 ADM_tlog_200705100000.TRN
11/05/2007 00:03 10,966,528 ADM_tlog_200705110001.TRN
12/05/2007 00:02 10,376,704 ADM_tlog_200705120000.TRN
13/05/2007 02:20 860,951,040 ADM_db_200705130201.BAK
14/05/2007 00:06 428,621,312 ADM_tlog_200705140002.TRN
15/05/2007 00:02 9,046,528 ADM_tlog_200705150000.TRN
16/05/2007 00:02 5,048,832 ADM_tlog_200705160000.TRN
17/05/2007 00:03 13,699,584 ADM_tlog_200705170000.TRN
18/05/2007 00:04 92,212,736 ADM_tlog_200705180000.TRN
19/05/2007 00:03 32,824,832 ADM_tlog_200705190002.TRN
20/05/2007 02:06 864,737,792 ADM_db_200705200202.BAK
21/05/2007 00:05 393,709,056 ADM_tlog_200705210002.TRN
22/05/2007 00:04 17,836,544 ADM_tlog_200705220003.TRN
I'm think I'm misunderstanding the process somewhere. Could anyone clarify?"Rob Oldfield" <blah@.blah.com> wrote in message
news:%23mRHK8EnHHA.3520@.TK2MSFTNGP04.phx.gbl...
>I have a maintenance plans set up for a few of my databases where I am
<Snip>
Please ignore. I've found lots of answers by just Googling. Apologies... I
should have done that first.|||In a Full or Bulk logged recovery model you should plan at regular interval a
clearing of the transaction log, because the full backup in this model
does'nt automatically truncate the log.
The Backup LOG backs up the current consistency of the transaction log
starting from the last successful backup log.
Gilberto Zampatti
"Rob Oldfield" wrote:
> I have a maintenance plans set up for a few of my databases where I am
> running a full backup on Sundays, followed by transaction log backups each
> day of the week. As far as I understand it, each transaction log should
> then be backing up transactions made since the last backup (whether that's a
> full or transaction).
> What I'm actually seeing though is that the first transaction log backup
> after each full one is far larger than I'm expecting (in comparison to the
> size of the database anyway).
> For example, one of those is producing backup files of these sizes:
> 06/05/2007 02:12 857,955,840 ADM_db_200705060200.BAK
> 07/05/2007 00:12 1,313,012,224 ADM_tlog_200705070000.TRN
> 08/05/2007 00:03 611,840 ADM_tlog_200705080001.TRN
> 09/05/2007 00:03 5,133,824 ADM_tlog_200705090001.TRN
> 10/05/2007 00:02 6,510,080 ADM_tlog_200705100000.TRN
> 11/05/2007 00:03 10,966,528 ADM_tlog_200705110001.TRN
> 12/05/2007 00:02 10,376,704 ADM_tlog_200705120000.TRN
> 13/05/2007 02:20 860,951,040 ADM_db_200705130201.BAK
> 14/05/2007 00:06 428,621,312 ADM_tlog_200705140002.TRN
> 15/05/2007 00:02 9,046,528 ADM_tlog_200705150000.TRN
> 16/05/2007 00:02 5,048,832 ADM_tlog_200705160000.TRN
> 17/05/2007 00:03 13,699,584 ADM_tlog_200705170000.TRN
> 18/05/2007 00:04 92,212,736 ADM_tlog_200705180000.TRN
> 19/05/2007 00:03 32,824,832 ADM_tlog_200705190002.TRN
> 20/05/2007 02:06 864,737,792 ADM_db_200705200202.BAK
> 21/05/2007 00:05 393,709,056 ADM_tlog_200705210002.TRN
> 22/05/2007 00:04 17,836,544 ADM_tlog_200705220003.TRN
> I'm think I'm misunderstanding the process somewhere. Could anyone clarify?
>
>
Large transaction log backups
I'm currently running sql 2k with sp3 on win2k with sp4.
I have a maintenance job set up to back up the transaction
logs every four hours every day. I also have it checked
to retain the files for only one day. Afer some intense
nightly jobs, the morning tran log backup can be over 4GB
in size. The problem is not backing up the log to file,
it's the clean-up that's supposed to happen afterwards.
After the tran backup is complete, sql server is supposed
to delete the older tran log backup file from the previous
day, but it always fails to delete the older tran log
backup when it's over 4GB in size. It marks the job as
failed, but the backup finishes so it's not that big a
deal. Is this a known bug or is there something else I'm
missing?
I know I could back up the tran logs more often to reduce
size, but I'm doing log shipping using a custom script and
I'd rather not have 20+ logs to script and ship.
LeonLeon,
First, you might be able to get the size of the log backup down. If the size
is caused by reindexing, perhaps DBCC INDEXDEFRAG will help? Of perhaps
bulk-logged recovery mode? Things you can play with...
As for backup files not being deleted. Perhaps below might help? It is my
"canned" answered for that topic:
Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:1320001c3f6f9$e945c820$a601280a@.phx.gbl...
> Hi everyone,
> I'm currently running sql 2k with sp3 on win2k with sp4.
> I have a maintenance job set up to back up the transaction
> logs every four hours every day. I also have it checked
> to retain the files for only one day. Afer some intense
> nightly jobs, the morning tran log backup can be over 4GB
> in size. The problem is not backing up the log to file,
> it's the clean-up that's supposed to happen afterwards.
> After the tran backup is complete, sql server is supposed
> to delete the older tran log backup file from the previous
> day, but it always fails to delete the older tran log
> backup when it's over 4GB in size. It marks the job as
> failed, but the backup finishes so it's not that big a
> deal. Is this a known bug or is there something else I'm
> missing?
> I know I could back up the tran logs more often to reduce
> size, but I'm doing log shipping using a custom script and
> I'd rather not have 20+ logs to script and ship.
> Leon|||Tibor,
Thanks a bunch for the article and the suggestions from
Bill.
Leon
>--Original Message--
>Leon,
>First, you might be able to get the size of the log
backup down. If the size
>is caused by reindexing, perhaps DBCC INDEXDEFRAG will
help? Of perhaps
>bulk-logged recovery mode? Things you can play with...
>As for backup files not being deleted. Perhaps below
might help? It is my
>"canned" answered for that topic:
>Below KB might help:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;303292&Product=sql2k
>
>Also, check out below great troubleshooting suggestions
from Bill H at MS:
>
>-- Log files don't delete --
>This is likely to be either a permissions problem or a
sharing violation
>problem. The maintenance plan is run as a job, and jobs
are run by the
>SQLServerAgent service.
>Permissions:
>1. Determine the startup account for the SQLServerAgent
service
>(Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup). This
>account is the security context for jobs, and thus the
maintenance plan.
>2. If SQLServerAgent is started using LocalSystem (as
opposed to a domain
>account) then skip step 3.
>3. On that box, log onto NT as that account. Using
Explorer, attempt to
>delete an expired backup. If that succeeds then go to
Sharing Violation
>section.
>4. Log onto NT with an account that is an administrator
and use Explorer to
>look at the Properties|Security of the folder (where the
backups reside)
>and ensure the SQLServerAgent startup account has Full
Control. If the
>SQLServerAgent startup account is LocalSystem, then the
account to consider
>is SYSTEM.
>5. In NT, if an account is a member of an NT group, and
if that group has
>Access is Denied, then that account will have Access is
Denied, even if
>that account is also a member of the Administrators
group. Thus you may
>need to check group permissions (if the Startup Account
is a member of a
>group).
>6. Keep in mind that permissions (by default) are
inherited from a parent
>folder. Thus, if the backups are stored in C:\bak, and if
someone had
>denied permission to the SQLServerAgent startup account
for C:\, then
>C:\bak will inherit access is denied.
>Sharing violation:
>This is likely to be rooted in a timing issue, with the
most likely cause
>being another scheduled process (such as NT Backup or
Anti-Virus software)
>having the backup file open at the time when the
SQLServerAgent (i.e., the
>maintenance plan job) tried to delete it.
>1. Download filemon and handle from www.sysinternals.com.
>2. I am not sure whether filemon can be scheduled, or you
might be able to
>use NT scheduling services to start filemon just before
the maintenance
>plan job is started, but the filemon log can become very
large, so it would
>be best to start it some short time before the
maintenance plan starts.
>3. Inspect the filemon log for another process that has
that backup file
>open (if your lucky enough to have started filemon before
this other
>process grabs the backup folder), and inspect the log for
the results when
>the SQLServerAgent agent attempts to open that same file.
>4. Schedule the job or that other process to do their
work at different
>times.
>5. You can use the handle utility if you are around at
the time when the
>job is scheduled to run.
>If the backup files are going to a \\share or a mapped
drive (as opposed to
>local drive), then you will need to modify the above
(with respect to where
>the tests and utilities are run).
>Finally, inspection of the maintenance plan's history
report might be
>useful.
>Thanks,
>Bill Hollinshead
>Microsoft, SQL Server
>
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Leon" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1320001c3f6f9$e945c820$a601280a@.phx.gbl...
>> Hi everyone,
>> I'm currently running sql 2k with sp3 on win2k with sp4.
>> I have a maintenance job set up to back up the
transaction
>> logs every four hours every day. I also have it checked
>> to retain the files for only one day. Afer some intense
>> nightly jobs, the morning tran log backup can be over
4GB
>> in size. The problem is not backing up the log to file,
>> it's the clean-up that's supposed to happen afterwards.
>> After the tran backup is complete, sql server is
supposed
>> to delete the older tran log backup file from the
previous
>> day, but it always fails to delete the older tran log
>> backup when it's over 4GB in size. It marks the job as
>> failed, but the backup finishes so it's not that big a
>> deal. Is this a known bug or is there something else
I'm
>> missing?
>> I know I could back up the tran logs more often to
reduce
>> size, but I'm doing log shipping using a custom script
and
>> I'd rather not have 20+ logs to script and ship.
>> Leon
>
>.
>
Wednesday, March 7, 2012
Large transaction log backups
I'm currently running sql 2k with sp3 on win2k with sp4.
I have a maintenance job set up to back up the transaction
logs every four hours every day. I also have it checked
to retain the files for only one day. Afer some intense
nightly jobs, the morning tran log backup can be over 4GB
in size. The problem is not backing up the log to file,
it's the clean-up that's supposed to happen afterwards.
After the tran backup is complete, sql server is supposed
to delete the older tran log backup file from the previous
day, but it always fails to delete the older tran log
backup when it's over 4GB in size. It marks the job as
failed, but the backup finishes so it's not that big a
deal. Is this a known bug or is there something else I'm
missing?
I know I could back up the tran logs more often to reduce
size, but I'm doing log shipping using a custom script and
I'd rather not have 20+ logs to script and ship.
LeonLeon,
First, you might be able to get the size of the log backup down. If the size
is caused by reindexing, perhaps DBCC INDEXDEFRAG will help? Of perhaps
bulk-logged recovery mode? Things you can play with...
As for backup files not being deleted. Perhaps below might help? It is my
"canned" answered for that topic:
Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:1320001c3f6f9$e945c820$a601280a@.phx
.gbl...
> Hi everyone,
> I'm currently running sql 2k with sp3 on win2k with sp4.
> I have a maintenance job set up to back up the transaction
> logs every four hours every day. I also have it checked
> to retain the files for only one day. Afer some intense
> nightly jobs, the morning tran log backup can be over 4GB
> in size. The problem is not backing up the log to file,
> it's the clean-up that's supposed to happen afterwards.
> After the tran backup is complete, sql server is supposed
> to delete the older tran log backup file from the previous
> day, but it always fails to delete the older tran log
> backup when it's over 4GB in size. It marks the job as
> failed, but the backup finishes so it's not that big a
> deal. Is this a known bug or is there something else I'm
> missing?
> I know I could back up the tran logs more often to reduce
> size, but I'm doing log shipping using a custom script and
> I'd rather not have 20+ logs to script and ship.
> Leon|||Tibor,
Thanks a bunch for the article and the suggestions from
Bill.
Leon
>--Original Message--
>Leon,
>First, you might be able to get the size of the log
backup down. If the size
>is caused by reindexing, perhaps DBCC INDEXDEFRAG will
help? Of perhaps
>bulk-logged recovery mode? Things you can play with...
>As for backup files not being deleted. Perhaps below
might help? It is my
>"canned" answered for that topic:
>Below KB might help:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;303292&Product=sql2k
>
>Also, check out below great troubleshooting suggestions
from Bill H at MS:
>
>-- Log files don't delete --
>This is likely to be either a permissions problem or a
sharing violation
>problem. The maintenance plan is run as a job, and jobs
are run by the
>SQLServerAgent service.
>Permissions:
>1. Determine the startup account for the SQLServerAgent
service
>(Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup). This
>account is the security context for jobs, and thus the
maintenance plan.
>2. If SQLServerAgent is started using LocalSystem (as
opposed to a domain
>account) then skip step 3.
>3. On that box, log onto NT as that account. Using
Explorer, attempt to
>delete an expired backup. If that succeeds then go to
Sharing Violation
>section.
>4. Log onto NT with an account that is an administrator
and use Explorer to
>look at the Properties|Security of the folder (where the
backups reside)
>and ensure the SQLServerAgent startup account has Full
Control. If the
>SQLServerAgent startup account is LocalSystem, then the
account to consider
>is SYSTEM.
>5. In NT, if an account is a member of an NT group, and
if that group has
>Access is Denied, then that account will have Access is
Denied, even if
>that account is also a member of the Administrators
group. Thus you may
>need to check group permissions (if the Startup Account
is a member of a
>group).
>6. Keep in mind that permissions (by default) are
inherited from a parent
>folder. Thus, if the backups are stored in C:\bak, and if
someone had
>denied permission to the SQLServerAgent startup account
for C:\, then
>C:\bak will inherit access is denied.
>Sharing violation:
>This is likely to be rooted in a timing issue, with the
most likely cause
>being another scheduled process (such as NT Backup or
Anti-Virus software)
>having the backup file open at the time when the
SQLServerAgent (i.e., the
>maintenance plan job) tried to delete it.
>1. Download filemon and handle from www.sysinternals.com.
>2. I am not sure whether filemon can be scheduled, or you
might be able to
>use NT scheduling services to start filemon just before
the maintenance
>plan job is started, but the filemon log can become very
large, so it would
>be best to start it some short time before the
maintenance plan starts.
>3. Inspect the filemon log for another process that has
that backup file
>open (if your lucky enough to have started filemon before
this other
>process grabs the backup folder), and inspect the log for
the results when
>the SQLServerAgent agent attempts to open that same file.
>4. Schedule the job or that other process to do their
work at different
>times.
>5. You can use the handle utility if you are around at
the time when the
>job is scheduled to run.
>If the backup files are going to a \\share or a mapped
drive (as opposed to
>local drive), then you will need to modify the above
(with respect to where
>the tests and utilities are run).
>Finally, inspection of the maintenance plan's history
report might be
>useful.
>Thanks,
>Bill Hollinshead
>Microsoft, SQL Server
>
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Leon" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1320001c3f6f9$e945c820$a601280a@.phx
.gbl...
transaction
4GB
supposed
previous
I'm
reduce
and
>
>.
>
Large Transacation Log even with Simple Recovery
I am running into a problem where some of our tables are
creating the log files to grow even with simple
recovery. Everything I read says that simple recovery
will prevent this. We especially notice this when we
delete the rows in a large table from the query analyzer
however if we issue the same command within a DTS package
the logs doesn't grow.
Any suggestions? Thanks!!hi terry,
using simple recovery model does not mean, DML operation is not logged. when
you issue a delete statement againtst the table it is logged into
transaction log, All that simple recovery mode does is, it truncates the log
each time SQL Server performs a checkpoint.
if you want to perform a large delete do it in chunks of batches, following
example will delete the rows from table in batch of 1000 per batch. Im not
sure what does DTS do behind the scene. probably, it is doing the same
thing. you can run a profiler trace and check whats happening.
ex:
set rowcount 1000
while 1=1
begin
delete from customers
if @.@.rowcount=0
break
end
set rowcount 0
Refer to following urls for more information.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Thank you for this information. It is very helpful!
>--Original Message--
>hi terry,
>using simple recovery model does not mean, DML operation
is not logged. when
>you issue a delete statement againtst the table it is
logged into
>transaction log, All that simple recovery mode does is,
it truncates the log
>each time SQL Server performs a checkpoint.
>if you want to perform a large delete do it in chunks of
batches, following
>example will delete the rows from table in batch of 1000
per batch. Im not
>sure what does DTS do behind the scene. probably, it is
doing the same
>thing. you can run a profiler trace and check whats
happening.
>ex:
>set rowcount 1000
>while 1=1
>begin
>delete from customers
>if @.@.rowcount=0
>break
>end
>set rowcount 0
>Refer to following urls for more information.
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Transaction Log
>http://www.support.microsoft.com/?id=317375 Log File
Grows too big
>http://www.support.microsoft.com/?id=110139 Log file
filling up
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
Shrink File
>http://www.support.microsoft.com/?id=315512
Considerations for Autogrow
>and AutoShrink
>http://www.support.microsoft.com/?id=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
>.
>
Large Transacation Log even with Simple Recovery
I am running into a problem where some of our tables are
creating the log files to grow even with simple
recovery. Everything I read says that simple recovery
will prevent this. We especially notice this when we
delete the rows in a large table from the query analyzer
however if we issue the same command within a DTS package
the logs doesn't grow.
Any suggestions? Thanks!!
hi terry,
using simple recovery model does not mean, DML operation is not logged. when
you issue a delete statement againtst the table it is logged into
transaction log, All that simple recovery mode does is, it truncates the log
each time SQL Server performs a checkpoint.
if you want to perform a large delete do it in chunks of batches, following
example will delete the rows from table in batch of 1000 per batch. Im not
sure what does DTS do behind the scene. probably, it is doing the same
thing. you can run a profiler trace and check whats happening.
ex:
set rowcount 1000
while 1=1
begin
delete from customers
if @.@.rowcount=0
break
end
set rowcount 0
Refer to following urls for more information.
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Transaction Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Thank you for this information. It is very helpful!
>--Original Message--
>hi terry,
>using simple recovery model does not mean, DML operation
is not logged. when
>you issue a delete statement againtst the table it is
logged into
>transaction log, All that simple recovery mode does is,
it truncates the log
>each time SQL Server performs a checkpoint.
>if you want to perform a large delete do it in chunks of
batches, following
>example will delete the rows from table in batch of 1000
per batch. Im not
>sure what does DTS do behind the scene. probably, it is
doing the same
>thing. you can run a profiler trace and check whats
happening.
>ex:
>set rowcount 1000
>while 1=1
>begin
>delete from customers
>if @.@.rowcount=0
>break
>end
>set rowcount 0
>Refer to following urls for more information.
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Transaction Log
>http://www.support.microsoft.com/?id=317375 Log File
Grows too big
>http://www.support.microsoft.com/?id=110139 Log file
filling up
>http://www.mssqlserver.com/faq/logs-shrinklog.asp
Shrink File
>http://www.support.microsoft.com/?id=315512
Considerations for Autogrow
>and AutoShrink
>http://www.support.microsoft.com/?id=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>
>--
>Vishal Parkar
>vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
>.
>
Large TempDB
I have many reports running from a SQL Server DB, from Crystal Enterprise
reporting system. However I have noticed some performance degradation, and
I noticed that my TempDB had ballooned in size. Is there a way to reset
this? I tried Shrink, but this did not reduce the size of the DB?
Kind Regards
Ricky
(SQL2K-SP3A/WIN2K-Client/WIN2K-Server/Crystal Enterprise 9.0)http://www.aspfaq.com/show.asp?id=2446
David Portas
SQL Server MVP
--
"rikesh patel" <trikky@.rukp28.freeserve.co.uk> wrote in message
news:ubgD8RnxFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have many reports running from a SQL Server DB, from Crystal Enterprise
> reporting system. However I have noticed some performance degradation,
> and
> I noticed that my TempDB had ballooned in size. Is there a way to reset
> this? I tried Shrink, but this did not reduce the size of the DB?
> Kind Regards
> Ricky
> (SQL2K-SP3A/WIN2K-Client/WIN2K-Server/Crystal Enterprise 9.0)
>
Large Table Maintenance with BLOB data
database. I have a 300gb or so table on a 500gb server
and am considering how to keep the table within my space
constraints.
My requirements/issues are:
1. win2k server running sqlserver2000
2. each row contains the IMAGE data type
3. The database resides on a SCSI RAID disk drive array.
4. I don't have enough room on anything to back up the
database or make a copy.
5. Uptime is of the essence as this db is used heavily
during business hours.
Can someone provide recommendations on what would be the
best way to keep this database manageable?
I want to drop any rows older than a certain date to get
rid of old images first.
Thanks,
TobinYou got big troubles.
Your first problem is to make management aware of the serious availability
problems you are facing. If this is a business critical system, they MUST
free up some resources ($$$) to handle backups and maintenance tasks OR deal
with the outage times. Document the backup and recovery strategy, warts and
all, and present it to your boss. Same for maintenance issues. Start
reporting database space ona weekly basis with projections of when it gets
90% full and is effectively used up. Be sure and note that certain
maintenance tasks may no longer be possible (DBCC DBREINDEX) given the tight
space constraints. You don't have to fix all the problems now, but you do
have to make your company's management aware of them so you don't get
blasted when this system blows up. Be sure and have a plan ready to fix the
shortcomings in case management gets scared and demands something be done
NOW.
As for the short term problem, I take older data and DTS it out to another
database. I use views to link it back to the original database. Once the
data is out, you can backup the new DB once and then lock it read-only.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tobin" <tobin@.computac.com> wrote in message
news:f76001c3f181$3f324480$a601280a@.phx.gbl...
> I am quickly running out of room on my sqlserver2000
> database. I have a 300gb or so table on a 500gb server
> and am considering how to keep the table within my space
> constraints.
> My requirements/issues are:
> 1. win2k server running sqlserver2000
> 2. each row contains the IMAGE data type
> 3. The database resides on a SCSI RAID disk drive array.
> 4. I don't have enough room on anything to back up the
> database or make a copy.
> 5. Uptime is of the essence as this db is used heavily
> during business hours.
> Can someone provide recommendations on what would be the
> best way to keep this database manageable?
> I want to drop any rows older than a certain date to get
> rid of old images first.
> Thanks,
> Tobin|||Thanks for the advice. A question on something you said:
"Once the data is out, you can backup the new DB once and
then lock it read-only."
At the current time I can't backup the database. I am a
beginner when it comes to sqlserver. I understand that if
I just drop the old data the pages the data is stored on
might not be contiguous and I'll need to reindex the db.
Is my only solution to dts the data out or delete the rows
and then reindex to keep the database a reasonable size?
>--Original Message--
>You got big troubles.
>Your first problem is to make management aware of the
serious availability
>problems you are facing. If this is a business critical
system, they MUST
>free up some resources ($$$) to handle backups and
maintenance tasks OR deal
>with the outage times. Document the backup and recovery
strategy, warts and
>all, and present it to your boss. Same for maintenance
issues. Start
>reporting database space ona weekly basis with
projections of when it gets
>90% full and is effectively used up. Be sure and note
that certain
>maintenance tasks may no longer be possible (DBCC
DBREINDEX) given the tight
>space constraints. You don't have to fix all the
problems now, but you do
>have to make your company's management aware of them so
you don't get
>blasted when this system blows up. Be sure and have a
plan ready to fix the
>shortcomings in case management gets scared and demands
something be done
>NOW.
>As for the short term problem, I take older data and DTS
it out to another
>database. I use views to link it back to the original
database. Once the
>data is out, you can backup the new DB once and then lock
it read-only.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Tobin" <tobin@.computac.com> wrote in message
>news:f76001c3f181$3f324480$a601280a@.phx.gbl...
array.
>
>.
>|||Reindexing and space are only part of your problem. Not having a backup is
a huge issue. Not having space for basic maintenance is just as bad.
Seriously, get your management involved and aware of the problems. If you
don't know much about SQL, get some books and some training. If they won't
listen, get your resume updated. You will need it when the system blows up.
When you DTS the data, where will you put it. My recommendation is to DTS
to another database and link the information back using a view. After it is
all done, you can set the DTS target database read-only. Use multiple
target databases (perhaps one per Month or Quarter) and you may get this
down to a more managable size.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:fa0a01c3f1a4$2ebf37c0$a501280a@.phx.gbl...
> Thanks for the advice. A question on something you said:
> "Once the data is out, you can backup the new DB once and
> then lock it read-only."
> At the current time I can't backup the database. I am a
> beginner when it comes to sqlserver. I understand that if
> I just drop the old data the pages the data is stored on
> might not be contiguous and I'll need to reindex the db.
> Is my only solution to dts the data out or delete the rows
> and then reindex to keep the database a reasonable size?
>
> serious availability
> system, they MUST
> maintenance tasks OR deal
> strategy, warts and
> issues. Start
> projections of when it gets
> that certain
> DBREINDEX) given the tight
> problems now, but you do
> you don't get
> plan ready to fix the
> something be done
> it out to another
> database. Once the
> it read-only.
> array.
Large Table Maintenance with BLOB data
database. I have a 300gb or so table on a 500gb server
and am considering how to keep the table within my space
constraints.
My requirements/issues are:
1. win2k server running sqlserver2000
2. each row contains the IMAGE data type
3. The database resides on a SCSI RAID disk drive array.
4. I don't have enough room on anything to back up the
database or make a copy.
5. Uptime is of the essence as this db is used heavily
during business hours.
Can someone provide recommendations on what would be the
best way to keep this database manageable?
I want to drop any rows older than a certain date to get
rid of old images first.
Thanks,
TobinYou got big troubles.
Your first problem is to make management aware of the serious availability
problems you are facing. If this is a business critical system, they MUST
free up some resources ($$$) to handle backups and maintenance tasks OR deal
with the outage times. Document the backup and recovery strategy, warts and
all, and present it to your boss. Same for maintenance issues. Start
reporting database space ona weekly basis with projections of when it gets
90% full and is effectively used up. Be sure and note that certain
maintenance tasks may no longer be possible (DBCC DBREINDEX) given the tight
space constraints. You don't have to fix all the problems now, but you do
have to make your company's management aware of them so you don't get
blasted when this system blows up. Be sure and have a plan ready to fix the
shortcomings in case management gets scared and demands something be done
NOW.
As for the short term problem, I take older data and DTS it out to another
database. I use views to link it back to the original database. Once the
data is out, you can backup the new DB once and then lock it read-only.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tobin" <tobin@.computac.com> wrote in message
news:f76001c3f181$3f324480$a601280a@.phx.gbl...
> I am quickly running out of room on my sqlserver2000
> database. I have a 300gb or so table on a 500gb server
> and am considering how to keep the table within my space
> constraints.
> My requirements/issues are:
> 1. win2k server running sqlserver2000
> 2. each row contains the IMAGE data type
> 3. The database resides on a SCSI RAID disk drive array.
> 4. I don't have enough room on anything to back up the
> database or make a copy.
> 5. Uptime is of the essence as this db is used heavily
> during business hours.
> Can someone provide recommendations on what would be the
> best way to keep this database manageable?
> I want to drop any rows older than a certain date to get
> rid of old images first.
> Thanks,
> Tobin|||Thanks for the advice. A question on something you said:
"Once the data is out, you can backup the new DB once and
then lock it read-only."
At the current time I can't backup the database. I am a
beginner when it comes to sqlserver. I understand that if
I just drop the old data the pages the data is stored on
might not be contiguous and I'll need to reindex the db.
Is my only solution to dts the data out or delete the rows
and then reindex to keep the database a reasonable size?
>--Original Message--
>You got big troubles.
>Your first problem is to make management aware of the
serious availability
>problems you are facing. If this is a business critical
system, they MUST
>free up some resources ($$$) to handle backups and
maintenance tasks OR deal
>with the outage times. Document the backup and recovery
strategy, warts and
>all, and present it to your boss. Same for maintenance
issues. Start
>reporting database space ona weekly basis with
projections of when it gets
>90% full and is effectively used up. Be sure and note
that certain
>maintenance tasks may no longer be possible (DBCC
DBREINDEX) given the tight
>space constraints. You don't have to fix all the
problems now, but you do
>have to make your company's management aware of them so
you don't get
>blasted when this system blows up. Be sure and have a
plan ready to fix the
>shortcomings in case management gets scared and demands
something be done
>NOW.
>As for the short term problem, I take older data and DTS
it out to another
>database. I use views to link it back to the original
database. Once the
>data is out, you can backup the new DB once and then lock
it read-only.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Tobin" <tobin@.computac.com> wrote in message
>news:f76001c3f181$3f324480$a601280a@.phx.gbl...
>> I am quickly running out of room on my sqlserver2000
>> database. I have a 300gb or so table on a 500gb server
>> and am considering how to keep the table within my space
>> constraints.
>> My requirements/issues are:
>> 1. win2k server running sqlserver2000
>> 2. each row contains the IMAGE data type
>> 3. The database resides on a SCSI RAID disk drive
array.
>> 4. I don't have enough room on anything to back up the
>> database or make a copy.
>> 5. Uptime is of the essence as this db is used heavily
>> during business hours.
>> Can someone provide recommendations on what would be the
>> best way to keep this database manageable?
>> I want to drop any rows older than a certain date to get
>> rid of old images first.
>> Thanks,
>> Tobin
>
>.
>|||Reindexing and space are only part of your problem. Not having a backup is
a huge issue. Not having space for basic maintenance is just as bad.
Seriously, get your management involved and aware of the problems. If you
don't know much about SQL, get some books and some training. If they won't
listen, get your resume updated. You will need it when the system blows up.
When you DTS the data, where will you put it. My recommendation is to DTS
to another database and link the information back using a view. After it is
all done, you can set the DTS target database read-only. Use multiple
target databases (perhaps one per Month or Quarter) and you may get this
down to a more managable size.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:fa0a01c3f1a4$2ebf37c0$a501280a@.phx.gbl...
> Thanks for the advice. A question on something you said:
> "Once the data is out, you can backup the new DB once and
> then lock it read-only."
> At the current time I can't backup the database. I am a
> beginner when it comes to sqlserver. I understand that if
> I just drop the old data the pages the data is stored on
> might not be contiguous and I'll need to reindex the db.
> Is my only solution to dts the data out or delete the rows
> and then reindex to keep the database a reasonable size?
> >--Original Message--
> >You got big troubles.
> >
> >Your first problem is to make management aware of the
> serious availability
> >problems you are facing. If this is a business critical
> system, they MUST
> >free up some resources ($$$) to handle backups and
> maintenance tasks OR deal
> >with the outage times. Document the backup and recovery
> strategy, warts and
> >all, and present it to your boss. Same for maintenance
> issues. Start
> >reporting database space ona weekly basis with
> projections of when it gets
> >90% full and is effectively used up. Be sure and note
> that certain
> >maintenance tasks may no longer be possible (DBCC
> DBREINDEX) given the tight
> >space constraints. You don't have to fix all the
> problems now, but you do
> >have to make your company's management aware of them so
> you don't get
> >blasted when this system blows up. Be sure and have a
> plan ready to fix the
> >shortcomings in case management gets scared and demands
> something be done
> >NOW.
> >
> >As for the short term problem, I take older data and DTS
> it out to another
> >database. I use views to link it back to the original
> database. Once the
> >data is out, you can backup the new DB once and then lock
> it read-only.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> >"Tobin" <tobin@.computac.com> wrote in message
> >news:f76001c3f181$3f324480$a601280a@.phx.gbl...
> >> I am quickly running out of room on my sqlserver2000
> >> database. I have a 300gb or so table on a 500gb server
> >> and am considering how to keep the table within my space
> >> constraints.
> >>
> >> My requirements/issues are:
> >>
> >> 1. win2k server running sqlserver2000
> >> 2. each row contains the IMAGE data type
> >> 3. The database resides on a SCSI RAID disk drive
> array.
> >> 4. I don't have enough room on anything to back up the
> >> database or make a copy.
> >> 5. Uptime is of the essence as this db is used heavily
> >> during business hours.
> >>
> >> Can someone provide recommendations on what would be the
> >> best way to keep this database manageable?
> >>
> >> I want to drop any rows older than a certain date to get
> >> rid of old images first.
> >>
> >> Thanks,
> >>
> >> Tobin
> >
> >
> >.
> >
Monday, February 20, 2012
Large log file
7mb but now it "suddenly" has grown to 1850mb!
I have been running the SQL profiler the last 24 hours, can that have
anything to do with it ? or why does it grow so large?
Does a large log file have affect on the performance? What happen if I
delete the log?
LasseDon't ever delete the log file. Read these for more info:
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly
SQL Server MVP
"Lasse" <lars@.kpwood.com> wrote in message
news:u38iOlNnDHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi, I am maintaning a database that is 783mb, usually the logfile is
about
> 7mb but now it "suddenly" has grown to 1850mb!
> I have been running the SQL profiler the last 24 hours, can that have
> anything to do with it ? or why does it grow so large?
> Does a large log file have affect on the performance? What happen if I
> delete the log?
> Lasse
>|||Thank you.
Is it so that if there is no backup setup the log file just keep on growing,
I understand there can be other reasons but is that one reason?
Lasse
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> skrev i meddelandet
news:#IR9B6OnDHA.3288@.tk2msftngp13.phx.gbl...
> Don't ever delete the log file. Read these for more info:
>
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> http://www.support.microsoft.com/?id=315512 Considerations for
Autogrow
> and AutoShrink
> http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
>
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Lasse" <lars@.kpwood.com> wrote in message
> news:u38iOlNnDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > Hi, I am maintaning a database that is 783mb, usually the logfile is
> about
> > 7mb but now it "suddenly" has grown to 1850mb!
> > I have been running the SQL profiler the last 24 hours, can that have
> > anything to do with it ? or why does it grow so large?
> > Does a large log file have affect on the performance? What happen if I
> > delete the log?
> > Lasse
> >
> >
>|||Yes, if the database is in full recovery and you never do log backup, then the transaction log will
never be emptied.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lasse" <lars@.kpwood.com> wrote in message news:e9ddibfnDHA.372@.TK2MSFTNGP11.phx.gbl...
> Thank you.
> Is it so that if there is no backup setup the log file just keep on growing,
> I understand there can be other reasons but is that one reason?
> Lasse
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> skrev i meddelandet
> news:#IR9B6OnDHA.3288@.tk2msftngp13.phx.gbl...
> > Don't ever delete the log file. Read these for more info:
> >
> >
> > http://www.support.microsoft.com/?id=317375 Log File Grows too big
> > http://www.support.microsoft.com/?id=110139 Log file filling up
> > http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> > http://www.support.microsoft.com/?id=315512 Considerations for
> Autogrow
> > and AutoShrink
> > http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> > Server 7.0 Tran Log
> > http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> > Server 2000 with DBCC SHRINKFILE
> >
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Lasse" <lars@.kpwood.com> wrote in message
> > news:u38iOlNnDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > > Hi, I am maintaning a database that is 783mb, usually the logfile is
> > about
> > > 7mb but now it "suddenly" has grown to 1850mb!
> > > I have been running the SQL profiler the last 24 hours, can that have
> > > anything to do with it ? or why does it grow so large?
> > > Does a large log file have affect on the performance? What happen if I
> > > delete the log?
> > > Lasse
> > >
> > >
> >
> >
>
large INSERTS effects on TempDB?
lots of joins, into a table.
There appear to be some locking issues around this object. How do I
determine the effect, if any, on tempdb?
Would the Select portion of the Insert create some exclusive locks, or tie
up the tempdb in some way? If so, how do I monitor tempdb size?
Thanks,
Bill Thompson> Would the Select portion of the Insert create some exclusive locks, or tie
> up the tempdb in some way?
Could be that, depending on your query hints. Also, if you are inserting
into a table with a clustered index, there could be a lot of sorting and
data movement going on...
--
http://www.aspfaq.com/
(Reverse address to reply.)
large INSERTS effects on TempDB?
lots of joins, into a table.
There appear to be some locking issues around this object. How do I
determine the effect, if any, on tempdb?
Would the Select portion of the Insert create some exclusive locks, or tie
up the tempdb in some way? If so, how do I monitor tempdb size?
Thanks,
Bill Thompson
> Would the Select portion of the Insert create some exclusive locks, or tie
> up the tempdb in some way?
Could be that, depending on your query hints. Also, if you are inserting
into a table with a clustered index, there could be a lot of sorting and
data movement going on...
http://www.aspfaq.com/
(Reverse address to reply.)
large INSERTS effects on TempDB?
lots of joins, into a table.
There appear to be some locking issues around this object. How do I
determine the effect, if any, on tempdb?
Would the Select portion of the Insert create some exclusive locks, or tie
up the tempdb in some way? If so, how do I monitor tempdb size?
Thanks,
Bill Thompson> Would the Select portion of the Insert create some exclusive locks, or tie
> up the tempdb in some way?
Could be that, depending on your query hints. Also, if you are inserting
into a table with a clustered index, there could be a lot of sorting and
data movement going on...
http://www.aspfaq.com/
(Reverse address to reply.)
large inserts
What is the best way to do a large insert WITHOUT having direct access
to the machine SQL Server is running on? For example, imagine I want
to insert something like 20,000 records. If I were to have access to
the server, I could BULK INSERT into a temp table and then insert into
the destination table. But if I can't create a file on the server to
use for BULK INSERT, what is the next best alternative to doing lots
of 1 record insert statements?
Thanks,
-EminOn Thu, 05 Jul 2007 17:34:42 -0000, Emin <emin.shopper@.gmail.com>
wrote:
Quote:
Originally Posted by
>Dear Experts,
>
>What is the best way to do a large insert WITHOUT having direct access
>to the machine SQL Server is running on? For example, imagine I want
>to insert something like 20,000 records. If I were to have access to
>the server, I could BULK INSERT into a temp table and then insert into
>the destination table. But if I can't create a file on the server to
>use for BULK INSERT, what is the next best alternative to doing lots
>of 1 record insert statements?
>
>Thanks,
>-Emin
The Bulk Copy command line utility BCP.EXE is one alternative, as are
DTS (SQL Server 2000) and SSIS (2005) executing locally.
Roy Harvey
Beacon Falls, CT|||Doesn't the client need something like SQL Management studio installed
to use DTS or SSIS? Ideally, I'd like something where the client can
just connect to the database using odbc or ado and do the insert as
opposed to requiring the client to have special software installed.
Thanks,
-Emin
On Jul 5, 5:28 pm, Roy Harvey <roy_har...@.snet.netwrote:
Quote:
Originally Posted by
>
The Bulk Copy command line utility BCP.EXE is one alternative, as are
DTS (SQL Server 2000) and SSIS (2005) executing locally.
>
Roy Harvey
Beacon Falls, CT
Quote:
Originally Posted by
Dear Experts,
>
What is the best way to do a large insert WITHOUT having direct access
to the machine SQL Server is running on? For example, imagine I want
to insert something like 20,000 records. If I were to have access to
the server, I could BULK INSERT into a temp table and then insert into
the destination table. But if I can't create a file on the server to
use for BULK INSERT, what is the next best alternative to doing lots
of 1 record insert statements?
>
Thanks,
-Emin
You can put your file in a shared folder - BULK INSERT can read it if
it is accessible by the account SQL Server runs under.
Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/|||You don't need the full set of client tools to run DTS packages, just
the dtsrun.exe utility. And for SSIS packages there is the dtexec
command line utility, though I have not used it. And as Erland said,
for BCP you need bcp.exe.
Roy Harvey
Beacon Falls, CT
On Thu, 05 Jul 2007 21:55:53 -0000, Emin <emin.shopper@.gmail.com>
wrote:
Quote:
Originally Posted by
>Doesn't the client need something like SQL Management studio installed
>to use DTS or SSIS? Ideally, I'd like something where the client can
>just connect to the database using odbc or ado and do the insert as
>opposed to requiring the client to have special software installed.
>
>Thanks,
>-Emin
>
>On Jul 5, 5:28 pm, Roy Harvey <roy_har...@.snet.netwrote:
Quote:
Originally Posted by
>>
>The Bulk Copy command line utility BCP.EXE is one alternative, as are
>DTS (SQL Server 2000) and SSIS (2005) executing locally.
>>
>Roy Harvey
>Beacon Falls, CT
>