First time poster...
Alright this is what I got...I am running MSSQL server 2000.
I have already restored a copy of the production db to our test sql server.
What I need to do is run some update scripts (from 3rd party vendor, going to newer version of their product). The database is about 60Gb and I filled up the transaction log (causing later updates to fail) while running one of their scripts that executed 194 routines of:
alter table Table_Name add New_Column int
GO
EXEC sp_bindefault mg_zero_dflt, [Table_Name.New_Column]
GO
update Table_Name set New_Column=1
GO
Will "sp_dboption 'dbname' 'trunc. log on chkpt' 'TRUE' " automatically truncate the transaction log when approaching the transaction log file limit while running these update scripts. Then when the updates are complete go ahead and "sp_dboption 'dbname' 'trunc. log on chkpt' 'FALSE' "
I'm not worried about logging the transactions during this process, I just don't want to have the transaction log fill up while running these scripts causing it to error out.
Or is there a way to make those changes using bcp?In the database properties window, go to the options tab and set your database recovery model to "simple". This should keep the log from filling up.
When you are finished, set the recovery model back to "full" and run an immediate backup.|||Thanks for the help. I remember seeing something about that and got sidetracked with the whole 'trunc. log on chkpt' idea. I'm in the process of finishing the scripts on the test server and I'll update with results. Thanks again!|||I finished running the scripts late yesterday without any hitches. That was exactly what I was looking for!
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
Friday, March 16, 2012
"The process could not connect to Distributor"
Hi, i have the following problem:
i have a publisher and a subscriber in the same sql server 2005 machine. All
the process (the Log Reader Agent and the Snapshot Agent) works fine. Even
the snapshot files are being created in the UNC path (\\Desa5el1\Paso), but
when attempt to run synchronization, the Replication Monitor show me an error
in the Subscription. When i see the details of the error, i see the following:
Error messages:
The process could not read file
'\\Desa5el1\Paso\unc\DESA5EL1$CASCABEL_MC_AREASQL_ PUB_MC_AREASQL_PRUEBA\20070719170351\prueba_2.pre'
due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024)
Get help: http://help/MSSQL_REPL20024
The account under which the agent runs is db_owner (is an sql account, not
an Windows account) and member of the Publication Access List. I think maybe
the problem it is in this:
"Have read permissions on the snapshot share."
Could someone explain me this point? i need to configure some thing extra?
some permissions to the unc path?
I really appreciate any help.
Thanks in advance
Locate your snapshot share. Do that by issue the following on your
distributor
sp_helpdistributor
The name of your snapshot share should be in the directory column.
Also on your publisher in your publication database do this
sp_helppublication 'pubs' -- where pubs is your publication name
Note the value for alt_snapshot_folder column. If this value is null your
snapshot share is the value for the directory column returned by
sp_helpdistributor. If it is not null the snapshot share is in the
alt_snapshot_folder column.
Using file explorer navigate to this folder and right click on it, select
sharing and security and then make sure that the account which the sql
server agent account runs under has read and list files and folders rights
on this folder. The share permissions are by default always read.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:9E69C2AD-9AA6-4B12-8B1A-B118123E9F3D@.microsoft.com...
> Hi, i have the following problem:
> i have a publisher and a subscriber in the same sql server 2005 machine.
> All
> the process (the Log Reader Agent and the Snapshot Agent) works fine. Even
> the snapshot files are being created in the UNC path (\\Desa5el1\Paso),
> but
> when attempt to run synchronization, the Replication Monitor show me an
> error
> in the Subscription. When i see the details of the error, i see the
> following:
> Error messages:
> The process could not read file
> '\\Desa5el1\Paso\unc\DESA5EL1$CASCABEL_MC_AREASQL_ PUB_MC_AREASQL_PRUEBA\20070719170351\prueba_2.pre'
> due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024)
> Get help: http://help/MSSQL_REPL20024
> The account under which the agent runs is db_owner (is an sql account, not
> an Windows account) and member of the Publication Access List. I think
> maybe
> the problem it is in this:
> "Have read permissions on the snapshot share."
> Could someone explain me this point? i need to configure some thing extra?
> some permissions to the unc path?
> I really appreciate any help.
> --
> Thanks in advance
>
|||Thanks for your help, Hilary.
I executed both sp_helpdistributor and sp_helppublication, and the snapshot
share that appears is correct (\\Desa5el1\Paso).
How can i set sharing and security rights for the account under which the
agent runs? The account exists only in sql server. I need to "register" this
account in somewhere out of sql server? How can i do that?
Thanks again.
"Hilary Cotter" wrote:
> Locate your snapshot share. Do that by issue the following on your
> distributor
> sp_helpdistributor
> The name of your snapshot share should be in the directory column.
> Also on your publisher in your publication database do this
> sp_helppublication 'pubs' -- where pubs is your publication name
> Note the value for alt_snapshot_folder column. If this value is null your
> snapshot share is the value for the directory column returned by
> sp_helpdistributor. If it is not null the snapshot share is in the
> alt_snapshot_folder column.
> Using file explorer navigate to this folder and right click on it, select
> sharing and security and then make sure that the account which the sql
> server agent account runs under has read and list files and folders rights
> on this folder. The share permissions are by default always read.
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "byteman" <byteman@.discussions.microsoft.com> wrote in message
> news:9E69C2AD-9AA6-4B12-8B1A-B118123E9F3D@.microsoft.com...
>
>
i have a publisher and a subscriber in the same sql server 2005 machine. All
the process (the Log Reader Agent and the Snapshot Agent) works fine. Even
the snapshot files are being created in the UNC path (\\Desa5el1\Paso), but
when attempt to run synchronization, the Replication Monitor show me an error
in the Subscription. When i see the details of the error, i see the following:
Error messages:
The process could not read file
'\\Desa5el1\Paso\unc\DESA5EL1$CASCABEL_MC_AREASQL_ PUB_MC_AREASQL_PRUEBA\20070719170351\prueba_2.pre'
due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024)
Get help: http://help/MSSQL_REPL20024
The account under which the agent runs is db_owner (is an sql account, not
an Windows account) and member of the Publication Access List. I think maybe
the problem it is in this:
"Have read permissions on the snapshot share."
Could someone explain me this point? i need to configure some thing extra?
some permissions to the unc path?
I really appreciate any help.
Thanks in advance
Locate your snapshot share. Do that by issue the following on your
distributor
sp_helpdistributor
The name of your snapshot share should be in the directory column.
Also on your publisher in your publication database do this
sp_helppublication 'pubs' -- where pubs is your publication name
Note the value for alt_snapshot_folder column. If this value is null your
snapshot share is the value for the directory column returned by
sp_helpdistributor. If it is not null the snapshot share is in the
alt_snapshot_folder column.
Using file explorer navigate to this folder and right click on it, select
sharing and security and then make sure that the account which the sql
server agent account runs under has read and list files and folders rights
on this folder. The share permissions are by default always read.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:9E69C2AD-9AA6-4B12-8B1A-B118123E9F3D@.microsoft.com...
> Hi, i have the following problem:
> i have a publisher and a subscriber in the same sql server 2005 machine.
> All
> the process (the Log Reader Agent and the Snapshot Agent) works fine. Even
> the snapshot files are being created in the UNC path (\\Desa5el1\Paso),
> but
> when attempt to run synchronization, the Replication Monitor show me an
> error
> in the Subscription. When i see the details of the error, i see the
> following:
> Error messages:
> The process could not read file
> '\\Desa5el1\Paso\unc\DESA5EL1$CASCABEL_MC_AREASQL_ PUB_MC_AREASQL_PRUEBA\20070719170351\prueba_2.pre'
> due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL20024)
> Get help: http://help/MSSQL_REPL20024
> The account under which the agent runs is db_owner (is an sql account, not
> an Windows account) and member of the Publication Access List. I think
> maybe
> the problem it is in this:
> "Have read permissions on the snapshot share."
> Could someone explain me this point? i need to configure some thing extra?
> some permissions to the unc path?
> I really appreciate any help.
> --
> Thanks in advance
>
|||Thanks for your help, Hilary.
I executed both sp_helpdistributor and sp_helppublication, and the snapshot
share that appears is correct (\\Desa5el1\Paso).
How can i set sharing and security rights for the account under which the
agent runs? The account exists only in sql server. I need to "register" this
account in somewhere out of sql server? How can i do that?
Thanks again.
"Hilary Cotter" wrote:
> Locate your snapshot share. Do that by issue the following on your
> distributor
> sp_helpdistributor
> The name of your snapshot share should be in the directory column.
> Also on your publisher in your publication database do this
> sp_helppublication 'pubs' -- where pubs is your publication name
> Note the value for alt_snapshot_folder column. If this value is null your
> snapshot share is the value for the directory column returned by
> sp_helpdistributor. If it is not null the snapshot share is in the
> alt_snapshot_folder column.
> Using file explorer navigate to this folder and right click on it, select
> sharing and security and then make sure that the account which the sql
> server agent account runs under has read and list files and folders rights
> on this folder. The share permissions are by default always read.
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "byteman" <byteman@.discussions.microsoft.com> wrote in message
> news:9E69C2AD-9AA6-4B12-8B1A-B118123E9F3D@.microsoft.com...
>
>
Thursday, March 8, 2012
"query notification" problem
I am using query notification and have the following problem:
I tegister once, then change the DB table manually to test. a msg is printed
to the log proving the call back function was called. I can repeat this
several time.
BUT, I then go away for 10 min', and come back. Now, when I change the data
in the DB nothing happens!
any clue how to fix and undertand this?
thanksUPDATE:
I used trace to see what is going on in the database. So I see that I get
"subscription fired" followed by "subscription registered" everytime I do
changes to the database.
After I stop doing any changes to the database for 5 minuts, and re-change
the data there, I see the database fire the "subscription fired" event, but
this time there is no "subscription registered" event. I also do not get the
log printout that I have in my web ASP.NET application, that is supposed to
write a line in the callback function.
So the hear is the deal:
The callback function gets the event fired for the first few times, so I am
doing the registration right. but, somehting happens to the ASP.NET
aplication, or the DB connection or something, that causes the callback
function not to be called after a certain time. anyone has an idea of what
am I up against? why is this happening?
thanks|||Any idea what is this error I get?
The query notification dialog on conversation handle
'{2FD61BF6-44B8-DA11-8B4E-00123F74CFF6}.' closed due to the following error:
'<?xml version="1.0"?><Error
xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Co
de><Description>Cannot
find the remote service
'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4'
because it does not exist.</Description></Error>'.
?|||It means that service named
'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4' does not
exist in the database where the notifications should be delivered to. Seems
like you are adding the broker instance at the end of the service name. The
service name you pass to the SqlNotificationRequest.Options must match the
service name you created in the database.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"csmba" <csmba@.nowhere.com> wrote in message
news:u4e2l3fTGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Any idea what is this error I get?
> The query notification dialog on conversation handle
> '{2FD61BF6-44B8-DA11-8B4E-00123F74CFF6}.' closed due to the following
> error: '<?xml version="1.0"?><Error
> xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</
Code><Description>Cannot
> find the remote service
> 'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4&apo
s;
> because it does not exist.</Description></Error>'.
>
> ?
>
I tegister once, then change the DB table manually to test. a msg is printed
to the log proving the call back function was called. I can repeat this
several time.
BUT, I then go away for 10 min', and come back. Now, when I change the data
in the DB nothing happens!
any clue how to fix and undertand this?
thanksUPDATE:
I used trace to see what is going on in the database. So I see that I get
"subscription fired" followed by "subscription registered" everytime I do
changes to the database.
After I stop doing any changes to the database for 5 minuts, and re-change
the data there, I see the database fire the "subscription fired" event, but
this time there is no "subscription registered" event. I also do not get the
log printout that I have in my web ASP.NET application, that is supposed to
write a line in the callback function.
So the hear is the deal:
The callback function gets the event fired for the first few times, so I am
doing the registration right. but, somehting happens to the ASP.NET
aplication, or the DB connection or something, that causes the callback
function not to be called after a certain time. anyone has an idea of what
am I up against? why is this happening?
thanks|||Any idea what is this error I get?
The query notification dialog on conversation handle
'{2FD61BF6-44B8-DA11-8B4E-00123F74CFF6}.' closed due to the following error:
'<?xml version="1.0"?><Error
xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Co
de><Description>Cannot
find the remote service
'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4'
because it does not exist.</Description></Error>'.
?|||It means that service named
'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4' does not
exist in the database where the notifications should be delivered to. Seems
like you are adding the broker instance at the end of the service name. The
service name you pass to the SqlNotificationRequest.Options must match the
service name you created in the database.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"csmba" <csmba@.nowhere.com> wrote in message
news:u4e2l3fTGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Any idea what is this error I get?
> The query notification dialog on conversation handle
> '{2FD61BF6-44B8-DA11-8B4E-00123F74CFF6}.' closed due to the following
> error: '<?xml version="1.0"?><Error
> xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</
Code><Description>Cannot
> find the remote service
> 'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4&apo
s;
> because it does not exist.</Description></Error>'.
>
> ?
>
Saturday, February 25, 2012
"non-trusted connection" what does it mean?
What does it mean when a users is connected but it is a "non-trusted connection". This is showing up in the log file after my users connect. How does this effect the system/user? We have been experiencing speed issues and I was wondering if this might be a possible source of the problem.
Thanks in advance.A "trusted connection" is made using Windoze authentication. A "non-trusted connection" is made using SQL authentication. That is is the only difference that I know of between the two kinds of connections.
-PatP
Thanks in advance.A "trusted connection" is made using Windoze authentication. A "non-trusted connection" is made using SQL authentication. That is is the only difference that I know of between the two kinds of connections.
-PatP
"model" system database transaction log
why would the transaction log of the model database have increased? this is a
template database what could be writing to it?
Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
template database what could be writing to it?
Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
"model" system database transaction log
why would the transaction log of the model database have increased? this is
a
template database what could be writing to it?Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
a
template database what could be writing to it?Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
"model" system database transaction log
why would the transaction log of the model database have increased? this is a
template database what could be writing to it?Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
--
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
template database what could be writing to it?Obviously someone or some app has connected to the wrong db. Maybe someone
meant to use Tempdb and incorrectly chose Model instead.
--
Andrew J. Kelly SQL MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:9B15D142-5FE8-4822-AAD2-57DDCE393B34@.microsoft.com...
> why would the transaction log of the model database have increased? this
> is a
> template database what could be writing to it?
>
Sunday, February 19, 2012
"Insufficient memory available" on SQL 2000
Hi All,
We have SQL 2000 ent and Windows 2003 , in a few day , restart many
time the error message in event log is "Error: 17803, Severity: 20, State:
14
Insufficient memory available."
i try open SQL profiler and use performance monitor , but i don't know how
to fix ? in profiler display error log , but i don't know is server problem
or application problem , how to check the problem ? help
SQL 2000 Ent ,enable AWE
Windows 2003 ent enable PAE
RAM : 16 GB
In Profiler error :
" 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address found."
"2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
Free=949769 Procedures=7
Inram=0 Dirty=236616 Kept=0
I/O=0, Latched=194, Other=107327"
"2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
Target=1415936 Hashed=344137
InternalReservation=360 ExternalReservation=0 Min Free=128 Visible= 191272"
"2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
TotalPages=7 InUsePages=4"
"2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
Reserved=3168
OS Committed=3112
OS In Use=3108
Query Plan=96903 Optimizer=1
General=24144
Utilities=160 Connection=3834 "
"2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
Reserved=3168
OS Committed=3112
OS In Use=3108
Query Plan=96903 Optimizer=1
General=24144
Utilities=160 Connection=3834"
"2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0 Waiting=0
Maximum=52143 Available=52143"
"Error: 701, Severity: 17, State: 132"
"2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address found."
"2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
Free=949770 Procedures=7
Inram=0 Dirty=236616 Kept=0
I/O=0, Latched=194, Other=107327"
"2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
Target=1415936 Hashed=344137
InternalReservation=360 ExternalReservation=0 Min Free=128 Visible= 191272"
"2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
TotalPages=7 InUsePages=4"
......
how to fix this , i need restart the server mant time on everyday , HELP !!!Check out the following stuff from System Monitor:
Memory: Available MBytes
Memory: Page Faults\sec
MSSQL$<instance_name>: Buffer Manager: Buffer cache hit ratio
MSSQL$<instance_name>: Buffer Manager: Page life expectancy
MSSQL$<instance_name>: General Statistics: User Connections
Physical Disk: % Disk Time
Physical Disk: Disk Read Bytes\sec
Physical Disk: Disk Write Bytes\sec
Physical Disk: Avg. Disk Queue Length
Processor: % Processor Time
System: Processor Queue Length
Did you restarted your Windows Server after setting up PAE and same for SQL
Server's AWE setting. You need to restart your SQL Server service to take
effect this setting.
Please let me know the values of the counters above. (Don't analyze only for
2-3 mins. Give them at least 1 hour to work and do it in peak-hours)
Ekrem Önsoy
"pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
news:e3Te6Yh%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> We have SQL 2000 ent and Windows 2003 , in a few day , restart many
> time the error message in event log is "Error: 17803, Severity: 20,
> State:
> 14
> Insufficient memory available."
> i try open SQL profiler and use performance monitor , but i don't know how
> to fix ? in profiler display error log , but i don't know is server
> problem
> or application problem , how to check the problem ? help
> SQL 2000 Ent ,enable AWE
> Windows 2003 ent enable PAE
> RAM : 16 GB
> In Profiler error :
> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address
> found."
> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
> Free=949769 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
> OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834 "
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
> OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834"
> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0
> Waiting=0
> Maximum=52143 Available=52143"
> "Error: 701, Severity: 17, State: 132"
> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address
> found."
> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
> Free=949770 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> ......
> how to fix this , i need restart the server mant time on everyday , HELP
> !!!
>
>
>
>|||pcnetnet (pcnetnet@.yahoo.com.hk) writes:
> We have SQL 2000 ent and Windows 2003 , in a few day , restart
> many time the error message in event log is "Error: 17803, Severity:
> 20, State: 14 Insufficient memory available." i try open SQL profiler
> and use performance monitor , but i don't know how to fix ? in profiler
> display error log , but i don't know is server problem or application
> problem , how to check the problem ? help
That looks really bad. I was about to suggest that you should open a
case with Microsoft, but as I searched in Books Online for error
17803, I found something interesting: it's listed in a section for
for error codes listed by Open Data Services. So maybe this is due
to a memory leak in an extended stored procedure?
Do you if there are any extended stored procedures installed on your
system (beside those that ship with SQL Server)? Would it be possible
to keep them from running for a while to see if the problem goes away.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi ekrem,
i have restart the server to take the effect , but the same case, i have
the momitor file , how to post to this ?
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:4F41E891-DC4A-4D2C-89C3-52441BDB998A@.microsoft.com...
> Check out the following stuff from System Monitor:
> Memory: Available MBytes
> Memory: Page Faults\sec
> MSSQL$<instance_name>: Buffer Manager: Buffer cache hit ratio
> MSSQL$<instance_name>: Buffer Manager: Page life expectancy
> MSSQL$<instance_name>: General Statistics: User Connections
> Physical Disk: % Disk Time
> Physical Disk: Disk Read Bytes\sec
> Physical Disk: Disk Write Bytes\sec
> Physical Disk: Avg. Disk Queue Length
> Processor: % Processor Time
> System: Processor Queue Length
> Did you restarted your Windows Server after setting up PAE and same for
> SQL Server's AWE setting. You need to restart your SQL Server service to
> take effect this setting.
> Please let me know the values of the counters above. (Don't analyze only
> for 2-3 mins. Give them at least 1 hour to work and do it in peak-hours)
>
> --
> Ekrem Önsoy
>
> "pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
> news:e3Te6Yh%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> We have SQL 2000 ent and Windows 2003 , in a few day , restart
>> many
>> time the error message in event log is "Error: 17803, Severity: 20,
>> State:
>> 14
>> Insufficient memory available."
>> i try open SQL profiler and use performance monitor , but i don't know
>> how
>> to fix ? in profiler display error log , but i don't know is server
>> problem
>> or application problem , how to check the problem ? help
>> SQL 2000 Ent ,enable AWE
>> Windows 2003 ent enable PAE
>> RAM : 16 GB
>> In Profiler error :
>> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
>> Free=949769 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834 "
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834"
>> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0
>> Waiting=0
>> Maximum=52143 Available=52143"
>> "Error: 701, Severity: 17, State: 132"
>> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
>> Free=949770 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> ......
>> how to fix this , i need restart the server mant time on everyday , HELP
>> !!!
>>
>>
>>
>>
>|||Attach file to your message and send it.
I hope it's not a big issue as Erland mentioned.
--
Ekrem Önsoy
"Pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
news:%232SKRom%23HHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi ekrem,
> i have restart the server to take the effect , but the same case, i
> have the momitor file , how to post to this ?
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:4F41E891-DC4A-4D2C-89C3-52441BDB998A@.microsoft.com...
>> Check out the following stuff from System Monitor:
>> Memory: Available MBytes
>> Memory: Page Faults\sec
>> MSSQL$<instance_name>: Buffer Manager: Buffer cache hit ratio
>> MSSQL$<instance_name>: Buffer Manager: Page life expectancy
>> MSSQL$<instance_name>: General Statistics: User Connections
>> Physical Disk: % Disk Time
>> Physical Disk: Disk Read Bytes\sec
>> Physical Disk: Disk Write Bytes\sec
>> Physical Disk: Avg. Disk Queue Length
>> Processor: % Processor Time
>> System: Processor Queue Length
>> Did you restarted your Windows Server after setting up PAE and same for
>> SQL Server's AWE setting. You need to restart your SQL Server service to
>> take effect this setting.
>> Please let me know the values of the counters above. (Don't analyze only
>> for 2-3 mins. Give them at least 1 hour to work and do it in peak-hours)
>>
>> --
>> Ekrem Önsoy
>>
>> "pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
>> news:e3Te6Yh%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> We have SQL 2000 ent and Windows 2003 , in a few day , restart
>> many
>> time the error message in event log is "Error: 17803, Severity: 20,
>> State:
>> 14
>> Insufficient memory available."
>> i try open SQL profiler and use performance monitor , but i don't know
>> how
>> to fix ? in profiler display error log , but i don't know is server
>> problem
>> or application problem , how to check the problem ? help
>> SQL 2000 Ent ,enable AWE
>> Windows 2003 ent enable PAE
>> RAM : 16 GB
>> In Profiler error :
>> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
>> Free=949769 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834 "
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834"
>> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0
>> Waiting=0
>> Maximum=52143 Available=52143"
>> "Error: 701, Severity: 17, State: 132"
>> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
>> Free=949770 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> ......
>> how to fix this , i need restart the server mant time on everyday , HELP
>> !!!
>>
>>
>>
>>
>|||Did you ever get a fix to your problem? We are also running SQL 2000 sp4
with the awe hot fix. We are on windows 2003 sp2. We've had this happen
several times on a production server. We tried killing some spids that we
thought might be causing the problem - but that didn't help. We had to
reboot the server.
We have a case open with microsoft - but so far they are telling us its
beyond SQL Server's scope. SQL Server is using all the memory except for a
little we have set aside (1 gb out of 4 gb) for the OS, etc.
Let me know what you found.
"pcnetnet" wrote:
> Hi All,
> We have SQL 2000 ent and Windows 2003 , in a few day , restart many
> time the error message in event log is "Error: 17803, Severity: 20, State:
> 14
> Insufficient memory available."
> i try open SQL profiler and use performance monitor , but i don't know how
> to fix ? in profiler display error log , but i don't know is server problem
> or application problem , how to check the problem ? help
> SQL 2000 Ent ,enable AWE
> Windows 2003 ent enable PAE
> RAM : 16 GB
> In Profiler error :
> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address found."
> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
> Free=949769 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834 "
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834"
> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0 Waiting=0
> Maximum=52143 Available=52143"
> "Error: 701, Severity: 17, State: 132"
> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address found."
> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
> Free=949770 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> .......
> how to fix this , i need restart the server mant time on everyday , HELP !!!
>
>
>
>
>
We have SQL 2000 ent and Windows 2003 , in a few day , restart many
time the error message in event log is "Error: 17803, Severity: 20, State:
14
Insufficient memory available."
i try open SQL profiler and use performance monitor , but i don't know how
to fix ? in profiler display error log , but i don't know is server problem
or application problem , how to check the problem ? help
SQL 2000 Ent ,enable AWE
Windows 2003 ent enable PAE
RAM : 16 GB
In Profiler error :
" 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address found."
"2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
Free=949769 Procedures=7
Inram=0 Dirty=236616 Kept=0
I/O=0, Latched=194, Other=107327"
"2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
Target=1415936 Hashed=344137
InternalReservation=360 ExternalReservation=0 Min Free=128 Visible= 191272"
"2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
TotalPages=7 InUsePages=4"
"2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
Reserved=3168
OS Committed=3112
OS In Use=3108
Query Plan=96903 Optimizer=1
General=24144
Utilities=160 Connection=3834 "
"2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
Reserved=3168
OS Committed=3112
OS In Use=3108
Query Plan=96903 Optimizer=1
General=24144
Utilities=160 Connection=3834"
"2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0 Waiting=0
Maximum=52143 Available=52143"
"Error: 701, Severity: 17, State: 132"
"2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address found."
"2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
Free=949770 Procedures=7
Inram=0 Dirty=236616 Kept=0
I/O=0, Latched=194, Other=107327"
"2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
Target=1415936 Hashed=344137
InternalReservation=360 ExternalReservation=0 Min Free=128 Visible= 191272"
"2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
TotalPages=7 InUsePages=4"
......
how to fix this , i need restart the server mant time on everyday , HELP !!!Check out the following stuff from System Monitor:
Memory: Available MBytes
Memory: Page Faults\sec
MSSQL$<instance_name>: Buffer Manager: Buffer cache hit ratio
MSSQL$<instance_name>: Buffer Manager: Page life expectancy
MSSQL$<instance_name>: General Statistics: User Connections
Physical Disk: % Disk Time
Physical Disk: Disk Read Bytes\sec
Physical Disk: Disk Write Bytes\sec
Physical Disk: Avg. Disk Queue Length
Processor: % Processor Time
System: Processor Queue Length
Did you restarted your Windows Server after setting up PAE and same for SQL
Server's AWE setting. You need to restart your SQL Server service to take
effect this setting.
Please let me know the values of the counters above. (Don't analyze only for
2-3 mins. Give them at least 1 hour to work and do it in peak-hours)
Ekrem Önsoy
"pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
news:e3Te6Yh%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> We have SQL 2000 ent and Windows 2003 , in a few day , restart many
> time the error message in event log is "Error: 17803, Severity: 20,
> State:
> 14
> Insufficient memory available."
> i try open SQL profiler and use performance monitor , but i don't know how
> to fix ? in profiler display error log , but i don't know is server
> problem
> or application problem , how to check the problem ? help
> SQL 2000 Ent ,enable AWE
> Windows 2003 ent enable PAE
> RAM : 16 GB
> In Profiler error :
> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address
> found."
> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
> Free=949769 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
> OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834 "
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
> OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834"
> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0
> Waiting=0
> Maximum=52143 Available=52143"
> "Error: 701, Severity: 17, State: 132"
> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address
> found."
> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
> Free=949770 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> ......
> how to fix this , i need restart the server mant time on everyday , HELP
> !!!
>
>
>
>|||pcnetnet (pcnetnet@.yahoo.com.hk) writes:
> We have SQL 2000 ent and Windows 2003 , in a few day , restart
> many time the error message in event log is "Error: 17803, Severity:
> 20, State: 14 Insufficient memory available." i try open SQL profiler
> and use performance monitor , but i don't know how to fix ? in profiler
> display error log , but i don't know is server problem or application
> problem , how to check the problem ? help
That looks really bad. I was about to suggest that you should open a
case with Microsoft, but as I searched in Books Online for error
17803, I found something interesting: it's listed in a section for
for error codes listed by Open Data Services. So maybe this is due
to a memory leak in an extended stored procedure?
Do you if there are any extended stored procedures installed on your
system (beside those that ship with SQL Server)? Would it be possible
to keep them from running for a while to see if the problem goes away.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi ekrem,
i have restart the server to take the effect , but the same case, i have
the momitor file , how to post to this ?
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:4F41E891-DC4A-4D2C-89C3-52441BDB998A@.microsoft.com...
> Check out the following stuff from System Monitor:
> Memory: Available MBytes
> Memory: Page Faults\sec
> MSSQL$<instance_name>: Buffer Manager: Buffer cache hit ratio
> MSSQL$<instance_name>: Buffer Manager: Page life expectancy
> MSSQL$<instance_name>: General Statistics: User Connections
> Physical Disk: % Disk Time
> Physical Disk: Disk Read Bytes\sec
> Physical Disk: Disk Write Bytes\sec
> Physical Disk: Avg. Disk Queue Length
> Processor: % Processor Time
> System: Processor Queue Length
> Did you restarted your Windows Server after setting up PAE and same for
> SQL Server's AWE setting. You need to restart your SQL Server service to
> take effect this setting.
> Please let me know the values of the counters above. (Don't analyze only
> for 2-3 mins. Give them at least 1 hour to work and do it in peak-hours)
>
> --
> Ekrem Önsoy
>
> "pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
> news:e3Te6Yh%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> We have SQL 2000 ent and Windows 2003 , in a few day , restart
>> many
>> time the error message in event log is "Error: 17803, Severity: 20,
>> State:
>> 14
>> Insufficient memory available."
>> i try open SQL profiler and use performance monitor , but i don't know
>> how
>> to fix ? in profiler display error log , but i don't know is server
>> problem
>> or application problem , how to check the problem ? help
>> SQL 2000 Ent ,enable AWE
>> Windows 2003 ent enable PAE
>> RAM : 16 GB
>> In Profiler error :
>> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
>> Free=949769 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834 "
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834"
>> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0
>> Waiting=0
>> Maximum=52143 Available=52143"
>> "Error: 701, Severity: 17, State: 132"
>> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
>> Free=949770 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> ......
>> how to fix this , i need restart the server mant time on everyday , HELP
>> !!!
>>
>>
>>
>>
>|||Attach file to your message and send it.
I hope it's not a big issue as Erland mentioned.
--
Ekrem Önsoy
"Pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
news:%232SKRom%23HHA.748@.TK2MSFTNGP04.phx.gbl...
> Hi ekrem,
> i have restart the server to take the effect , but the same case, i
> have the momitor file , how to post to this ?
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:4F41E891-DC4A-4D2C-89C3-52441BDB998A@.microsoft.com...
>> Check out the following stuff from System Monitor:
>> Memory: Available MBytes
>> Memory: Page Faults\sec
>> MSSQL$<instance_name>: Buffer Manager: Buffer cache hit ratio
>> MSSQL$<instance_name>: Buffer Manager: Page life expectancy
>> MSSQL$<instance_name>: General Statistics: User Connections
>> Physical Disk: % Disk Time
>> Physical Disk: Disk Read Bytes\sec
>> Physical Disk: Disk Write Bytes\sec
>> Physical Disk: Avg. Disk Queue Length
>> Processor: % Processor Time
>> System: Processor Queue Length
>> Did you restarted your Windows Server after setting up PAE and same for
>> SQL Server's AWE setting. You need to restart your SQL Server service to
>> take effect this setting.
>> Please let me know the values of the counters above. (Don't analyze only
>> for 2-3 mins. Give them at least 1 hour to work and do it in peak-hours)
>>
>> --
>> Ekrem Önsoy
>>
>> "pcnetnet" <pcnetnet@.yahoo.com.hk> wrote in message
>> news:e3Te6Yh%23HHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> We have SQL 2000 ent and Windows 2003 , in a few day , restart
>> many
>> time the error message in event log is "Error: 17803, Severity: 20,
>> State:
>> 14
>> Insufficient memory available."
>> i try open SQL profiler and use performance monitor , but i don't know
>> how
>> to fix ? in profiler display error log , but i don't know is server
>> problem
>> or application problem , how to check the problem ? help
>> SQL 2000 Ent ,enable AWE
>> Windows 2003 ent enable PAE
>> RAM : 16 GB
>> In Profiler error :
>> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
>> Free=949769 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834 "
>> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029
>> OS
>> Reserved=3168
>> OS Committed=3112
>> OS In Use=3108
>> Query Plan=96903 Optimizer=1
>> General=24144
>> Utilities=160 Connection=3834"
>> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0
>> Waiting=0
>> Maximum=52143 Available=52143"
>> "Error: 701, Severity: 17, State: 132"
>> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address
>> found."
>> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
>> Free=949770 Procedures=7
>> Inram=0 Dirty=236616 Kept=0
>> I/O=0, Latched=194, Other=107327"
>> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
>> Target=1415936 Hashed=344137
>> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=>> 191272"
>> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
>> TotalPages=7 InUsePages=4"
>> ......
>> how to fix this , i need restart the server mant time on everyday , HELP
>> !!!
>>
>>
>>
>>
>|||Did you ever get a fix to your problem? We are also running SQL 2000 sp4
with the awe hot fix. We are on windows 2003 sp2. We've had this happen
several times on a production server. We tried killing some spids that we
thought might be causing the problem - but that didn't help. We had to
reboot the server.
We have a case open with microsoft - but so far they are telling us its
beyond SQL Server's scope. SQL Server is using all the memory except for a
little we have set aside (1 gb out of 4 gb) for the OS, etc.
Let me know what you found.
"pcnetnet" wrote:
> Hi All,
> We have SQL 2000 ent and Windows 2003 , in a few day , restart many
> time the error message in event log is "Error: 17803, Severity: 20, State:
> 14
> Insufficient memory available."
> i try open SQL profiler and use performance monitor , but i don't know how
> to fix ? in profiler display error log , but i don't know is server problem
> or application problem , how to check the problem ? help
> SQL 2000 Ent ,enable AWE
> Windows 2003 ent enable PAE
> RAM : 16 GB
> In Profiler error :
> " 2007-09-18 04:54:49.51 spid56 BPool::Map: no remappable address found."
> "2007-09-18 04:54:49.54 spid56 Buffer Distribution: Stolen=122023
> Free=949769 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.54 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.54 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834 "
> "2007-09-18 04:54:49.54 spid56 Dynamic Memory Manager: Stolen=122029 OS
> Reserved=3168
> OS Committed=3112
> OS In Use=3108
> Query Plan=96903 Optimizer=1
> General=24144
> Utilities=160 Connection=3834"
> "2007-09-18 04:54:49.54 spid56 Query Memory Manager: Grants=0 Waiting=0
> Maximum=52143 Available=52143"
> "Error: 701, Severity: 17, State: 132"
> "2007-09-18 04:54:49.56 spid56 BPool::Map: no remappable address found."
> "2007-09-18 04:54:49.59 spid56 Buffer Distribution: Stolen=122022
> Free=949770 Procedures=7
> Inram=0 Dirty=236616 Kept=0
> I/O=0, Latched=194, Other=107327"
> "2007-09-18 04:54:49.59 spid56 Buffer Counts: Commited=1415936
> Target=1415936 Hashed=344137
> InternalReservation=360 ExternalReservation=0 Min Free=128 Visible=> 191272"
> "2007-09-18 04:54:49.59 spid56 Procedure Cache: TotalProcs=6
> TotalPages=7 InUsePages=4"
> .......
> how to fix this , i need restart the server mant time on everyday , HELP !!!
>
>
>
>
>
Thursday, February 9, 2012
"backup log {Database_Name} with no_log" issue
I issue the "backup log {Database_Name} with no_log" and
also "Dump transaction {Datebase_Name} with no_log". The
LDF file still got the same size. Any idea?It's usually best to leave the size of the .ldf file as is after backing up
a log because this saves sql server from having to go through the effort of
manually growing it whilst the database is in operation. This is because
having to grow the log file during operation slows down the performance of
SQL Server. This is why most production environments leave the .ldf file as
it is & simply truncate the "logical" log records from the file. There are
also other factors that come into play when considering recovery times as
well.
So, backing up a log & shrinking a .ldf file aren't two things you should
expect to happen automatically.
If you really want to shrink the .ldf file for some reason, there is a good
article on the topic in SQL Server Books Online here:
http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1uzr.asp
HTH
Regards,
Greg Linwood
SQL Server MVP
"KL" <anonymous@.discussions.microsoft.com> wrote in message
news:034801c3c744$e685fc70$a101280a@.phx.gbl...
> I issue the "backup log {Database_Name} with no_log" and
> also "Dump transaction {Datebase_Name} with no_log". The
> LDF file still got the same size. Any idea?
>|||Just to add to Greg's comments you should note that backingup a log file
will not shrink it. That will only happen with a DBCC SHRINKDATABASE or
SHRINKFILE command. It may not be able to actually shrink until the backup
is completed but that alone does not shrink the log file.
--
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23LGr8z2xDHA.1616@.TK2MSFTNGP11.phx.gbl...
> It's usually best to leave the size of the .ldf file as is after backing
up
> a log because this saves sql server from having to go through the effort
of
> manually growing it whilst the database is in operation. This is because
> having to grow the log file during operation slows down the performance of
> SQL Server. This is why most production environments leave the .ldf file
as
> it is & simply truncate the "logical" log records from the file. There are
> also other factors that come into play when considering recovery times as
> well.
> So, backing up a log & shrinking a .ldf file aren't two things you should
> expect to happen automatically.
> If you really want to shrink the .ldf file for some reason, there is a
good
> article on the topic in SQL Server Books Online here:
> http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1uzr.asp
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "KL" <anonymous@.discussions.microsoft.com> wrote in message
> news:034801c3c744$e685fc70$a101280a@.phx.gbl...
> > I issue the "backup log {Database_Name} with no_log" and
> > also "Dump transaction {Datebase_Name} with no_log". The
> > LDF file still got the same size. Any idea?
> >
>
also "Dump transaction {Datebase_Name} with no_log". The
LDF file still got the same size. Any idea?It's usually best to leave the size of the .ldf file as is after backing up
a log because this saves sql server from having to go through the effort of
manually growing it whilst the database is in operation. This is because
having to grow the log file during operation slows down the performance of
SQL Server. This is why most production environments leave the .ldf file as
it is & simply truncate the "logical" log records from the file. There are
also other factors that come into play when considering recovery times as
well.
So, backing up a log & shrinking a .ldf file aren't two things you should
expect to happen automatically.
If you really want to shrink the .ldf file for some reason, there is a good
article on the topic in SQL Server Books Online here:
http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1uzr.asp
HTH
Regards,
Greg Linwood
SQL Server MVP
"KL" <anonymous@.discussions.microsoft.com> wrote in message
news:034801c3c744$e685fc70$a101280a@.phx.gbl...
> I issue the "backup log {Database_Name} with no_log" and
> also "Dump transaction {Datebase_Name} with no_log". The
> LDF file still got the same size. Any idea?
>|||Just to add to Greg's comments you should note that backingup a log file
will not shrink it. That will only happen with a DBCC SHRINKDATABASE or
SHRINKFILE command. It may not be able to actually shrink until the backup
is completed but that alone does not shrink the log file.
--
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23LGr8z2xDHA.1616@.TK2MSFTNGP11.phx.gbl...
> It's usually best to leave the size of the .ldf file as is after backing
up
> a log because this saves sql server from having to go through the effort
of
> manually growing it whilst the database is in operation. This is because
> having to grow the log file during operation slows down the performance of
> SQL Server. This is why most production environments leave the .ldf file
as
> it is & simply truncate the "logical" log records from the file. There are
> also other factors that come into play when considering recovery times as
> well.
> So, backing up a log & shrinking a .ldf file aren't two things you should
> expect to happen automatically.
> If you really want to shrink the .ldf file for some reason, there is a
good
> article on the topic in SQL Server Books Online here:
> http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1uzr.asp
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "KL" <anonymous@.discussions.microsoft.com> wrote in message
> news:034801c3c744$e685fc70$a101280a@.phx.gbl...
> > I issue the "backup log {Database_Name} with no_log" and
> > also "Dump transaction {Datebase_Name} with no_log". The
> > LDF file still got the same size. Any idea?
> >
>
Friday, January 27, 2012
"Application Log is Full"
Can anyone tell me how to empty the Application log ,as I keep getting Application is Full message on the SQL box.
Many thanks
ZI believe this would be one of the three event logs that the OS maintains (application, security, system).
You should run event viewer and either increase space for the log, or allow it to rollover and drop oldest entries (or both). Many times the applucation log will fill up when you have sql server (or other apps) throwing numerous errors over a period of time.
Many thanks
ZI believe this would be one of the three event logs that the OS maintains (application, security, system).
You should run event viewer and either increase space for the log, or allow it to rollover and drop oldest entries (or both). Many times the applucation log will fill up when you have sql server (or other apps) throwing numerous errors over a period of time.
'-' in database name
Hi All,
We have a couple development database that contain a dash in them, something
like 'DB-TEST'.
I need to truncate the log and I would like to issue the following command:
backup log 'DB-TEST' with truncate_only
but sql-server doesn't like it
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
database names.
This fails both interactively and in a job.
Any help in escaping the - would be greatly appreciated.
TIA,
TimThere are rules for naming of things (aka 'identifiers') if you want to be
able to just type the name as it (without quoting the name in any way). This
is called 'standard identifiers'. By using a hyphen (dash, minus) you do not
follow those rules anymore. I discourage naming objects in this way.
The BACKUP command expects an identifier for the database name, and if you
don't follow the rules for standard identifiers, you have to quote the
database name, You can either use double-quotes or square brackets (non-ANSI
SQL). But not single quotes.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Tim" <tfleury@.pacbell.net> wrote in message
news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> We have a couple development database that contain a dash in them,
something
> like 'DB-TEST'.
> I need to truncate the log and I would like to issue the following
command:
> backup log 'DB-TEST' with truncate_only
> but sql-server doesn't like it
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
> database names.
> This fails both interactively and in a job.
> Any help in escaping the - would be greatly appreciated.
> TIA,
> Tim
>|||I inherited these database and I have tried to get them renamed but I have
encountered too much resistance.
But, I tried the [ ] brackets and they work like a champ.
Thanks a lot for the help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usYV8HO7DHA.2712@.tk2msftngp13.phx.gbl...
> There are rules for naming of things (aka 'identifiers') if you want to be
> able to just type the name as it (without quoting the name in any way).
This
> is called 'standard identifiers'. By using a hyphen (dash, minus) you do
not
> follow those rules anymore. I discourage naming objects in this way.
> The BACKUP command expects an identifier for the database name, and if you
> don't follow the rules for standard identifiers, you have to quote the
> database name, You can either use double-quotes or square brackets
(non-ANSI
> SQL). But not single quotes.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Tim" <tfleury@.pacbell.net> wrote in message
> news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> something
> command:
>
We have a couple development database that contain a dash in them, something
like 'DB-TEST'.
I need to truncate the log and I would like to issue the following command:
backup log 'DB-TEST' with truncate_only
but sql-server doesn't like it
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
database names.
This fails both interactively and in a job.
Any help in escaping the - would be greatly appreciated.
TIA,
TimThere are rules for naming of things (aka 'identifiers') if you want to be
able to just type the name as it (without quoting the name in any way). This
is called 'standard identifiers'. By using a hyphen (dash, minus) you do not
follow those rules anymore. I discourage naming objects in this way.
The BACKUP command expects an identifier for the database name, and if you
don't follow the rules for standard identifiers, you have to quote the
database name, You can either use double-quotes or square brackets (non-ANSI
SQL). But not single quotes.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Tim" <tfleury@.pacbell.net> wrote in message
news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> We have a couple development database that contain a dash in them,
something
> like 'DB-TEST'.
> I need to truncate the log and I would like to issue the following
command:
> backup log 'DB-TEST' with truncate_only
> but sql-server doesn't like it
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
> database names.
> This fails both interactively and in a job.
> Any help in escaping the - would be greatly appreciated.
> TIA,
> Tim
>|||I inherited these database and I have tried to get them renamed but I have
encountered too much resistance.
But, I tried the [ ] brackets and they work like a champ.
Thanks a lot for the help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usYV8HO7DHA.2712@.tk2msftngp13.phx.gbl...
> There are rules for naming of things (aka 'identifiers') if you want to be
> able to just type the name as it (without quoting the name in any way).
This
> is called 'standard identifiers'. By using a hyphen (dash, minus) you do
not
> follow those rules anymore. I discourage naming objects in this way.
> The BACKUP command expects an identifier for the database name, and if you
> don't follow the rules for standard identifiers, you have to quote the
> database name, You can either use double-quotes or square brackets
(non-ANSI
> SQL). But not single quotes.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Tim" <tfleury@.pacbell.net> wrote in message
> news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> something
> command:
>
'-' in database name
Hi All,
We have a couple development database that contain a dash in them, something
like 'DB-TEST'.
I need to truncate the log and I would like to issue the following command:
backup log 'DB-TEST' with truncate_only
but sql-server doesn't like it
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
database names.
This fails both interactively and in a job.
Any help in escaping the - would be greatly appreciated.
TIA,
TimThere are rules for naming of things (aka 'identifiers') if you want to be
able to just type the name as it (without quoting the name in any way). This
is called 'standard identifiers'. By using a hyphen (dash, minus) you do not
follow those rules anymore. I discourage naming objects in this way.
The BACKUP command expects an identifier for the database name, and if you
don't follow the rules for standard identifiers, you have to quote the
database name, You can either use double-quotes or square brackets (non-ANSI
SQL). But not single quotes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tim" <tfleury@.pacbell.net> wrote in message
news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> We have a couple development database that contain a dash in them,
something
> like 'DB-TEST'.
> I need to truncate the log and I would like to issue the following
command:
> backup log 'DB-TEST' with truncate_only
> but sql-server doesn't like it
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
> database names.
> This fails both interactively and in a job.
> Any help in escaping the - would be greatly appreciated.
> TIA,
> Tim
>|||I inherited these database and I have tried to get them renamed but I have
encountered too much resistance.
But, I tried the [ ] brackets and they work like a champ.
Thanks a lot for the help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usYV8HO7DHA.2712@.tk2msftngp13.phx.gbl...
> There are rules for naming of things (aka 'identifiers') if you want to be
> able to just type the name as it (without quoting the name in any way).
This
> is called 'standard identifiers'. By using a hyphen (dash, minus) you do
not
> follow those rules anymore. I discourage naming objects in this way.
> The BACKUP command expects an identifier for the database name, and if you
> don't follow the rules for standard identifiers, you have to quote the
> database name, You can either use double-quotes or square brackets
(non-ANSI
> SQL). But not single quotes.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Tim" <tfleury@.pacbell.net> wrote in message
> news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> > Hi All,
> >
> > We have a couple development database that contain a dash in them,
> something
> > like 'DB-TEST'.
> >
> > I need to truncate the log and I would like to issue the following
> command:
> >
> > backup log 'DB-TEST' with truncate_only
> >
> > but sql-server doesn't like it
> >
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
> > database names.
> >
> > This fails both interactively and in a job.
> >
> > Any help in escaping the - would be greatly appreciated.
> >
> > TIA,
> > Tim
> >
> >
>
We have a couple development database that contain a dash in them, something
like 'DB-TEST'.
I need to truncate the log and I would like to issue the following command:
backup log 'DB-TEST' with truncate_only
but sql-server doesn't like it
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
database names.
This fails both interactively and in a job.
Any help in escaping the - would be greatly appreciated.
TIA,
TimThere are rules for naming of things (aka 'identifiers') if you want to be
able to just type the name as it (without quoting the name in any way). This
is called 'standard identifiers'. By using a hyphen (dash, minus) you do not
follow those rules anymore. I discourage naming objects in this way.
The BACKUP command expects an identifier for the database name, and if you
don't follow the rules for standard identifiers, you have to quote the
database name, You can either use double-quotes or square brackets (non-ANSI
SQL). But not single quotes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tim" <tfleury@.pacbell.net> wrote in message
news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> We have a couple development database that contain a dash in them,
something
> like 'DB-TEST'.
> I need to truncate the log and I would like to issue the following
command:
> backup log 'DB-TEST' with truncate_only
> but sql-server doesn't like it
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
> database names.
> This fails both interactively and in a job.
> Any help in escaping the - would be greatly appreciated.
> TIA,
> Tim
>|||I inherited these database and I have tried to get them renamed but I have
encountered too much resistance.
But, I tried the [ ] brackets and they work like a champ.
Thanks a lot for the help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usYV8HO7DHA.2712@.tk2msftngp13.phx.gbl...
> There are rules for naming of things (aka 'identifiers') if you want to be
> able to just type the name as it (without quoting the name in any way).
This
> is called 'standard identifiers'. By using a hyphen (dash, minus) you do
not
> follow those rules anymore. I discourage naming objects in this way.
> The BACKUP command expects an identifier for the database name, and if you
> don't follow the rules for standard identifiers, you have to quote the
> database name, You can either use double-quotes or square brackets
(non-ANSI
> SQL). But not single quotes.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Tim" <tfleury@.pacbell.net> wrote in message
> news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> > Hi All,
> >
> > We have a couple development database that contain a dash in them,
> something
> > like 'DB-TEST'.
> >
> > I need to truncate the log and I would like to issue the following
> command:
> >
> > backup log 'DB-TEST' with truncate_only
> >
> > but sql-server doesn't like it
> >
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
> > database names.
> >
> > This fails both interactively and in a job.
> >
> > Any help in escaping the - would be greatly appreciated.
> >
> > TIA,
> > Tim
> >
> >
>
Subscribe to:
Posts (Atom)
