User is getting timeout errors when they try to backup a 2000 database using
a db backup and restore utility I wrote. The problem has just started
occurring w/in the last few days. I'd like to avoid driving to the site,
over 200-miles away, and changing the .ConnectionTimeout and .CommandTimeout
property of the db connection object building another distribution. Is there
a way to accomplish thisi via some "SET" command in Enterprise Mgr.?
TIATimeouts occur on the client side, not on the server, so there is no EM
setting. I hope you enjoy your trip :-)
pe this helps.
Dan Guzman
SQL Server MVP
"PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
> User is getting timeout errors when they try to backup a 2000 database
> using a db backup and restore utility I wrote. The problem has just
> started occurring w/in the last few days. I'd like to avoid driving to the
> site, over 200-miles away, and changing the .ConnectionTimeout and
> .CommandTimeout property of the db connection object building another
> distribution. Is there a way to accomplish thisi via some "SET" command in
> Enterprise Mgr.?
> TIA
>
>|||thanks ;o(
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBzkJGIxFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Timeouts occur on the client side, not on the server, so there is no EM
> setting. I hope you enjoy your trip :-)
>
> pe this helps.
> Dan Guzman
> SQL Server MVP
> "PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
> news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
>> User is getting timeout errors when they try to backup a 2000 database
>> using a db backup and restore utility I wrote. The problem has just
>> started occurring w/in the last few days. I'd like to avoid driving to
>> the site, over 200-miles away, and changing the .ConnectionTimeout and
>> .CommandTimeout property of the db connection object building another
>> distribution. Is there a way to accomplish thisi via some "SET" command
>> in Enterprise Mgr.?
>> TIA
>>
>
Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts
Friday, March 16, 2012
"Timeout expired" errors
User is getting timeout errors when they try to backup a 2000 database using
a db backup and restore utility I wrote. The problem has just started
occurring w/in the last few days. I'd like to avoid driving to the site,
over 200-miles away, and changing the .ConnectionTimeout and .CommandTimeout
property of the db connection object building another distribution. Is there
a way to accomplish thisi via some "SET" command in Enterprise Mgr.?
TIATimeouts occur on the client side, not on the server, so there is no EM
setting. I hope you enjoy your trip :-)
pe this helps.
Dan Guzman
SQL Server MVP
"PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
> User is getting timeout errors when they try to backup a 2000 database
> using a db backup and restore utility I wrote. The problem has just
> started occurring w/in the last few days. I'd like to avoid driving to the
> site, over 200-miles away, and changing the .ConnectionTimeout and
> .CommandTimeout property of the db connection object building another
> distribution. Is there a way to accomplish thisi via some "SET" command in
> Enterprise Mgr.?
> TIA
>
>|||thanks ;o(
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBzkJGIxFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Timeouts occur on the client side, not on the server, so there is no EM
> setting. I hope you enjoy your trip :-)
>
> pe this helps.
> Dan Guzman
> SQL Server MVP
> "PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
> news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
>
a db backup and restore utility I wrote. The problem has just started
occurring w/in the last few days. I'd like to avoid driving to the site,
over 200-miles away, and changing the .ConnectionTimeout and .CommandTimeout
property of the db connection object building another distribution. Is there
a way to accomplish thisi via some "SET" command in Enterprise Mgr.?
TIATimeouts occur on the client side, not on the server, so there is no EM
setting. I hope you enjoy your trip :-)
pe this helps.
Dan Guzman
SQL Server MVP
"PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
> User is getting timeout errors when they try to backup a 2000 database
> using a db backup and restore utility I wrote. The problem has just
> started occurring w/in the last few days. I'd like to avoid driving to the
> site, over 200-miles away, and changing the .ConnectionTimeout and
> .CommandTimeout property of the db connection object building another
> distribution. Is there a way to accomplish thisi via some "SET" command in
> Enterprise Mgr.?
> TIA
>
>|||thanks ;o(
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBzkJGIxFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Timeouts occur on the client side, not on the server, so there is no EM
> setting. I hope you enjoy your trip :-)
>
> pe this helps.
> Dan Guzman
> SQL Server MVP
> "PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
> news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
>
"Timeout expired" errors
User is getting timeout errors when they try to backup a 2000 database using
a db backup and restore utility I wrote. The problem has just started
occurring w/in the last few days. I'd like to avoid driving to the site,
over 200-miles away, and changing the .ConnectionTimeout and .CommandTimeout
property of the db connection object building another distribution. Is there
a way to accomplish thisi via some "SET" command in Enterprise Mgr.?
TIA
Timeouts occur on the client side, not on the server, so there is no EM
setting. I hope you enjoy your trip :-)
pe this helps.
Dan Guzman
SQL Server MVP
"PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
> User is getting timeout errors when they try to backup a 2000 database
> using a db backup and restore utility I wrote. The problem has just
> started occurring w/in the last few days. I'd like to avoid driving to the
> site, over 200-miles away, and changing the .ConnectionTimeout and
> .CommandTimeout property of the db connection object building another
> distribution. Is there a way to accomplish thisi via some "SET" command in
> Enterprise Mgr.?
> TIA
>
>
|||thanks ;o(
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBzkJGIxFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Timeouts occur on the client side, not on the server, so there is no EM
> setting. I hope you enjoy your trip :-)
>
> pe this helps.
> Dan Guzman
> SQL Server MVP
> "PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
> news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
>
a db backup and restore utility I wrote. The problem has just started
occurring w/in the last few days. I'd like to avoid driving to the site,
over 200-miles away, and changing the .ConnectionTimeout and .CommandTimeout
property of the db connection object building another distribution. Is there
a way to accomplish thisi via some "SET" command in Enterprise Mgr.?
TIA
Timeouts occur on the client side, not on the server, so there is no EM
setting. I hope you enjoy your trip :-)
pe this helps.
Dan Guzman
SQL Server MVP
"PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
> User is getting timeout errors when they try to backup a 2000 database
> using a db backup and restore utility I wrote. The problem has just
> started occurring w/in the last few days. I'd like to avoid driving to the
> site, over 200-miles away, and changing the .ConnectionTimeout and
> .CommandTimeout property of the db connection object building another
> distribution. Is there a way to accomplish thisi via some "SET" command in
> Enterprise Mgr.?
> TIA
>
>
|||thanks ;o(
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eBzkJGIxFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Timeouts occur on the client side, not on the server, so there is no EM
> setting. I hope you enjoy your trip :-)
>
> pe this helps.
> Dan Guzman
> SQL Server MVP
> "PKSpence" <patrick@.NOSPAMpkspence.com> wrote in message
> news:u673qFGxFHA.3000@.TK2MSFTNGP12.phx.gbl...
>
Thursday, March 8, 2012
"Remove files older than" doesn't work
Hi,
My company is using MS SQL 7. There is a database
maintenance plan to do the backup. It sets the "Remove
files older than 1 day". It used to work properly.
Last week, I suddenly found that this remove function
didn't work, and the files remain there for a whole week
until the disk full and backup fail. I don't know what
happen. I try to solve it by restart the server (Windows
2000 server), and change the setting from "1 day" to "23
hours", it totally doesn't work. Now I have to manually
delete the old file every day.
Anybody have the solution?
Thanks in advance.
ToddBelow 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
"Todd Chen" <powerful_tech@.yahoo.com> wrote in message
news:039901c39fa0$65479680$a001280a@.phx.gbl...
> Hi,
> My company is using MS SQL 7. There is a database
> maintenance plan to do the backup. It sets the "Remove
> files older than 1 day". It used to work properly.
> Last week, I suddenly found that this remove function
> didn't work, and the files remain there for a whole week
> until the disk full and backup fail. I don't know what
> happen. I try to solve it by restart the server (Windows
> 2000 server), and change the setting from "1 day" to "23
> hours", it totally doesn't work. Now I have to manually
> delete the old file every day.
> Anybody have the solution?
> Thanks in advance.
> Todd|||Wow, reply so fast.
Thanks a lot. Will try.
Todd
>--Original Message--
>Below KB might help:
>http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;303292&Product=
=3Dsql2k
>
>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=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
>
>"Todd Chen" <powerful_tech@.yahoo.com> wrote in message
>news:039901c39fa0$65479680$a001280a@.phx.gbl...
>> Hi,
>> My company is using MS SQL 7. There is a database
>> maintenance plan to do the backup. It sets the "Remove
>> files older than 1 day". It used to work properly.
>> Last week, I suddenly found that this remove function
>> didn't work, and the files remain there for a whole week
>> until the disk full and backup fail. I don't know what
>> happen. I try to solve it by restart the server (Windows
>> 2000 server), and change the setting from "1 day" to "23
>> hours", it totally doesn't work. Now I have to manually
>> delete the old file every day.
>> Anybody have the solution?
>> Thanks in advance.
>> Todd
>
>.
>
My company is using MS SQL 7. There is a database
maintenance plan to do the backup. It sets the "Remove
files older than 1 day". It used to work properly.
Last week, I suddenly found that this remove function
didn't work, and the files remain there for a whole week
until the disk full and backup fail. I don't know what
happen. I try to solve it by restart the server (Windows
2000 server), and change the setting from "1 day" to "23
hours", it totally doesn't work. Now I have to manually
delete the old file every day.
Anybody have the solution?
Thanks in advance.
ToddBelow 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
"Todd Chen" <powerful_tech@.yahoo.com> wrote in message
news:039901c39fa0$65479680$a001280a@.phx.gbl...
> Hi,
> My company is using MS SQL 7. There is a database
> maintenance plan to do the backup. It sets the "Remove
> files older than 1 day". It used to work properly.
> Last week, I suddenly found that this remove function
> didn't work, and the files remain there for a whole week
> until the disk full and backup fail. I don't know what
> happen. I try to solve it by restart the server (Windows
> 2000 server), and change the setting from "1 day" to "23
> hours", it totally doesn't work. Now I have to manually
> delete the old file every day.
> Anybody have the solution?
> Thanks in advance.
> Todd|||Wow, reply so fast.
Thanks a lot. Will try.
Todd
>--Original Message--
>Below KB might help:
>http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;303292&Product=
=3Dsql2k
>
>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=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
>
>"Todd Chen" <powerful_tech@.yahoo.com> wrote in message
>news:039901c39fa0$65479680$a001280a@.phx.gbl...
>> Hi,
>> My company is using MS SQL 7. There is a database
>> maintenance plan to do the backup. It sets the "Remove
>> files older than 1 day". It used to work properly.
>> Last week, I suddenly found that this remove function
>> didn't work, and the files remain there for a whole week
>> until the disk full and backup fail. I don't know what
>> happen. I try to solve it by restart the server (Windows
>> 2000 server), and change the setting from "1 day" to "23
>> hours", it totally doesn't work. Now I have to manually
>> delete the old file every day.
>> Anybody have the solution?
>> Thanks in advance.
>> Todd
>
>.
>
"Remove files older than" does not work
Hi.
The Database Maintenance Plan that is configured to backup
the DB every day as the "Remove files older than" option
set to 1 days.
The old files are no being removed. The backups are
working and placed in the specified folder. The "Backup
file extension:" is "bak". What am I missing?
Thanx.Does
BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or ME
Computers
http://support.microsoft.com/default.aspx?scid=kb;en-us;278667
apply?
--
Jacco Schalkwijk
SQL Server MVP
"Hector" <anonymous@.discussions.microsoft.com> wrote in message
news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
> Hi.
> The Database Maintenance Plan that is configured to backup
> the DB every day as the "Remove files older than" option
> set to 1 days.
> The old files are no being removed. The backups are
> working and placed in the specified folder. The "Backup
> file extension:" is "bak". What am I missing?
> Thanx.|||The server runs Windows 2000 (SP4).
I might be missing part of your response. It's sort of
distored.
Thanx!
>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
>> Hi.
>> The Database Maintenance Plan that is configured to
backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx.
>
>.
>|||I'm sorry. Now I understood your message. I'm reading
the article now.
Thanx again.
>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
>> Hi.
>> The Database Maintenance Plan that is configured to
backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx.
>
>.
>|||I reviewed the article and it doesn't apply. We have SQL
Server 2000 running on a Windows 2000 Server.
>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
>> Hi.
>> The Database Maintenance Plan that is configured to
backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx.
>
>.
>|||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
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hector" <anonymous@.discussions.microsoft.com> wrote in message
news:217f301c45ac5$cfffc700$a501280a@.phx.gbl...
> I reviewed the article and it doesn't apply. We have SQL
> Server 2000 running on a Windows 2000 Server.
>
> >--Original Message--
> >Does
> >BUG: Sqlmaint Does Not Delete Expired Backup Files on
> Windows 95, 98 or ME
> >Computers
> >http://support.microsoft.com/default.aspx?scid=kb;en-
> us;278667
> >apply?
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Hector" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
> >> Hi.
> >>
> >> The Database Maintenance Plan that is configured to
> backup
> >> the DB every day as the "Remove files older than" option
> >> set to 1 days.
> >>
> >> The old files are no being removed. The backups are
> >> working and placed in the specified folder. The "Backup
> >> file extension:" is "bak". What am I missing?
> >>
> >> Thanx.
> >
> >
> >.
> >|||I've recently taken over management of an SQL 2000 server also o
Windows 2000 server (and relatively new to SQL) and have exactly th
same problem.
Read through this thread but as yet no success to resolving th
problem.
Hector - did you get this resolved?
Anyone have any other ideas not suggested here?
Regards,
Glen.
Did you ever get a fix?
Hector wrote:
> *Hi.
> The Database Maintenance Plan that is configured to backup
> the DB every day as the "Remove files older than" option
> set to 1 days.
> The old files are no being removed. The backups are
> working and placed in the specified folder. The "Backup
> file extension:" is "bak". What am I missing?
> Thanx.
-
Glen Sutti
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message273097.htm|||I don't have access to the old thread, but perhaps below might help?
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
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Glen Suttie" <Glen.Suttie.1hdx2u@.mail.webservertalk.com> wrote in message
news:Glen.Suttie.1hdx2u@.mail.webservertalk.com...
> I've recently taken over management of an SQL 2000 server also on
> Windows 2000 server (and relatively new to SQL) and have exactly the
> same problem.
> Read through this thread but as yet no success to resolving the
> problem.
> Hector - did you get this resolved?
> Anyone have any other ideas not suggested here?
> Regards,
> Glen.
>
>
> Did you ever get a fix?
> Hector wrote:
>> *Hi.
>> The Database Maintenance Plan that is configured to backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx. *
>
> --
> Glen Suttie
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message273097.html
>
The Database Maintenance Plan that is configured to backup
the DB every day as the "Remove files older than" option
set to 1 days.
The old files are no being removed. The backups are
working and placed in the specified folder. The "Backup
file extension:" is "bak". What am I missing?
Thanx.Does
BUG: Sqlmaint Does Not Delete Expired Backup Files on Windows 95, 98 or ME
Computers
http://support.microsoft.com/default.aspx?scid=kb;en-us;278667
apply?
--
Jacco Schalkwijk
SQL Server MVP
"Hector" <anonymous@.discussions.microsoft.com> wrote in message
news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
> Hi.
> The Database Maintenance Plan that is configured to backup
> the DB every day as the "Remove files older than" option
> set to 1 days.
> The old files are no being removed. The backups are
> working and placed in the specified folder. The "Backup
> file extension:" is "bak". What am I missing?
> Thanx.|||The server runs Windows 2000 (SP4).
I might be missing part of your response. It's sort of
distored.
Thanx!
>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
>> Hi.
>> The Database Maintenance Plan that is configured to
backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx.
>
>.
>|||I'm sorry. Now I understood your message. I'm reading
the article now.
Thanx again.
>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
>> Hi.
>> The Database Maintenance Plan that is configured to
backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx.
>
>.
>|||I reviewed the article and it doesn't apply. We have SQL
Server 2000 running on a Windows 2000 Server.
>--Original Message--
>Does
>BUG: Sqlmaint Does Not Delete Expired Backup Files on
Windows 95, 98 or ME
>Computers
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;278667
>apply?
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Hector" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
>> Hi.
>> The Database Maintenance Plan that is configured to
backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx.
>
>.
>|||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
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hector" <anonymous@.discussions.microsoft.com> wrote in message
news:217f301c45ac5$cfffc700$a501280a@.phx.gbl...
> I reviewed the article and it doesn't apply. We have SQL
> Server 2000 running on a Windows 2000 Server.
>
> >--Original Message--
> >Does
> >BUG: Sqlmaint Does Not Delete Expired Backup Files on
> Windows 95, 98 or ME
> >Computers
> >http://support.microsoft.com/default.aspx?scid=kb;en-
> us;278667
> >apply?
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Hector" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2176001c45ab8$54616d90$a401280a@.phx.gbl...
> >> Hi.
> >>
> >> The Database Maintenance Plan that is configured to
> backup
> >> the DB every day as the "Remove files older than" option
> >> set to 1 days.
> >>
> >> The old files are no being removed. The backups are
> >> working and placed in the specified folder. The "Backup
> >> file extension:" is "bak". What am I missing?
> >>
> >> Thanx.
> >
> >
> >.
> >|||I've recently taken over management of an SQL 2000 server also o
Windows 2000 server (and relatively new to SQL) and have exactly th
same problem.
Read through this thread but as yet no success to resolving th
problem.
Hector - did you get this resolved?
Anyone have any other ideas not suggested here?
Regards,
Glen.
Did you ever get a fix?
Hector wrote:
> *Hi.
> The Database Maintenance Plan that is configured to backup
> the DB every day as the "Remove files older than" option
> set to 1 days.
> The old files are no being removed. The backups are
> working and placed in the specified folder. The "Backup
> file extension:" is "bak". What am I missing?
> Thanx.
-
Glen Sutti
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message273097.htm|||I don't have access to the old thread, but perhaps below might help?
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
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Glen Suttie" <Glen.Suttie.1hdx2u@.mail.webservertalk.com> wrote in message
news:Glen.Suttie.1hdx2u@.mail.webservertalk.com...
> I've recently taken over management of an SQL 2000 server also on
> Windows 2000 server (and relatively new to SQL) and have exactly the
> same problem.
> Read through this thread but as yet no success to resolving the
> problem.
> Hector - did you get this resolved?
> Anyone have any other ideas not suggested here?
> Regards,
> Glen.
>
>
> Did you ever get a fix?
> Hector wrote:
>> *Hi.
>> The Database Maintenance Plan that is configured to backup
>> the DB every day as the "Remove files older than" option
>> set to 1 days.
>> The old files are no being removed. The backups are
>> working and placed in the specified folder. The "Backup
>> file extension:" is "bak". What am I missing?
>> Thanx. *
>
> --
> Glen Suttie
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message273097.html
>
Sunday, February 19, 2012
"Identical" database, huge performance difference
I have a database that has big performance issue. I backup (complete
backup) the database and restore it with a new name within the same
instance, the performance gets back to normal. The size of both database
is 1,900 MB and space available of both is 1,100 MB.
Any idea about what makes the difference?so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with DB1 have a serious
fragmentation problem?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"qluo" <jluost1@.yahoo.com> wrote in message
news:40042824.2030705@.yahoo.com...
> I have a database that has big performance issue. I backup (complete
> backup) the database and restore it with a new name within the same
> instance, the performance gets back to normal. The size of both database
> is 1,900 MB and space available of both is 1,100 MB.
> Any idea about what makes the difference?
>|||Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes 36ms consistently.
I have not looked at the fragmentation problem yet. But the data file
and log file for both databases are in the same physical location.
Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with DB1 have a serious
> fragmentation problem?
>|||is DB1 a live database? ie, are people connecting to DB1
and making updates to table1?
if so, then the reason DB2 is faster for the same query is
that it knows no one has updated table1 on DB2, hence it
is safe to execute as select count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent
inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
>--Original Message--
>Brian,
>You are correct for the procedures I performed.
>The performance problem is, the Web page with DB1 as the
backend
>database takes too long to load.
>The query I used in Query Analyzer on both DB1 and DB2 is:
>--
>select count(*) from table1
>--
>It returns 72,000 on both database.
>For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
>I have not looked at the fragmentation problem yet. But
the data file
>and log file for both databases are in the same physical
location.
>
>Brian Moran wrote:
>> so you're saying that you can:
>> * start with DB1
>> * back up DB1 and restore it as DB2
>> * you could then run the same exact query on DB1 and
DB2. It would be fast
>> on DB2 and slow on DB1?
>> * If so... what steps do you need to do to then make
DB2 slow? Or does
>> performance stay good forever?
>> Have you looked at SQL Profiler?
>> What are the exact performance problems you're seeing?
>> Are you restoring DB2 to the same disk arrays as DB1?
>> Is it possible that the physical files associated with
DB1 have a serious
>> fragmentation problem?
>.
>|||Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1
> and making updates to table1?
> if so, then the reason DB2 is faster for the same query is
> that it knows no one has updated table1 on DB2, hence it
> is safe to execute as select count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent
> inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
>>--Original Message--
>>Brian,
>>You are correct for the procedures I performed.
>>The performance problem is, the Web page with DB1 as the
> backend
>>database takes too long to load.
>>The query I used in Query Analyzer on both DB1 and DB2 is:
>>--
>>select count(*) from table1
>>--
>>It returns 72,000 on both database.
>>For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
>>I have not looked at the fragmentation problem yet. But
> the data file
>>and log file for both databases are in the same physical
> location.
>>Brian Moran wrote:
>>so you're saying that you can:
>>* start with DB1
>>* back up DB1 and restore it as DB2
>>* you could then run the same exact query on DB1 and
> DB2. It would be fast
>>on DB2 and slow on DB1?
>>* If so... what steps do you need to do to then make
> DB2 slow? Or does
>>performance stay good forever?
>>Have you looked at SQL Profiler?
>>What are the exact performance problems you're seeing?
>>Are you restoring DB2 to the same disk arrays as DB1?
>>Is it possible that the physical files associated with
> DB1 have a serious
>>fragmentation problem?
>>
>>.
>|||You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
connection. There should be a trx isolation level property for your
database connectivity object. Not that this setting will be effective for
all queries passing through the connection.
--
Regards
Ray Mond|||I am reluctant to change the default (Read Committed).
My database is not busy at all. It is used by Web app and every
connection should come and go almost immediately. There shouldn't be a
concurrent(locking) issue like this.
Do you know what else I can do to minimize the locking so that I don't
have to change TRANSACTION ISOLATION LEVEL?
Thank you.
Ray Mond wrote:
> You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> connection. There should be a trx isolation level property for your
> database connectivity object. Not that this setting will be effective for
> all queries passing through the connection.
Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
is DB1 a live database? ie, are people connecting to DB1 and making
updates to table1?
if so, then the reason DB2 is faster for the same query is that it knows
no one has updated table1 on DB2, hence it is safe to execute as select
count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
--Original Message--
Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the
backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
I have not looked at the fragmentation problem yet. But
the data file
and log file for both databases are in the same physical
location.
Brian Moran wrote:
so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and
DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make
DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with
DB1 have a serious
fragmentation problem?
.
>|||I think the only option left here is to use that suggested by Joe Young i.e.
use the (NOLOCK) hint in the queries where you do not care about open
transactions.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:400554D8.8080900@.yahoo.com...
> I am reluctant to change the default (Read Committed).
> My database is not busy at all. It is used by Web app and every
> connection should come and go almost immediately. There shouldn't be a
> concurrent(locking) issue like this.
> Do you know what else I can do to minimize the locking so that I don't
> have to change TRANSACTION ISOLATION LEVEL?
> Thank you.
> Ray Mond wrote:
> > You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> > connection. There should be a trx isolation level property for your
> > database connectivity object. Not that this setting will be effective
for
> > all queries passing through the connection.
> Your point is exactly right. I ran the query with (NOLOCK) on both DB1
> and DB2 and they are taking the same amount of time, and dB1 is quicker
> now than before.
> So, what do I need to do on DB1 to achieve the same performance without
> using (NOLOCK)?
> DB1 is not actually busy and it is used by Web applications.
> Thank you.
>
> joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1 and making
> updates to table1?
> if so, then the reason DB2 is faster for the same query is that it knows
> no one has updated table1 on DB2, hence it is safe to execute as select
> count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
> --Original Message--
> Brian,
> You are correct for the procedures I performed.
> The performance problem is, the Web page with DB1 as the
> backend
> database takes too long to load.
> The query I used in Query Analyzer on both DB1 and DB2 is:
> --
> select count(*) from table1
> --
> It returns 72,000 on both database.
> For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
> I have not looked at the fragmentation problem yet. But
> the data file
> and log file for both databases are in the same physical
> location.
>
> Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and
> DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make
> DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with
> DB1 have a serious
> fragmentation problem?
>
> .
>
> >
>|||I did "Set transaction isolation level read uncommitted" on the database
server, but it made no difference. I think the OLEDB used by PHP Web app
is using "Set transaction isolation level read committed". So no
matter what I have set on database server doesn't matter.
I can use "NOLOCK" hint for each sql statement in the application. But
there are too many of them and I am reluctant to do so.
Again, I backed up DB1 and restore it with the new name DB2. I don't
need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
gain the same performance.
When I check the Process/Locks in SQL Enterprise Manager, I can see that
the locks come and go. When I see no locks for DB1 and then run my
sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
will make the difference for DB1 (it cuts the response time from 1.1
seconds to 0.6 seconds for 746 rows)?
Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
appear to be busy, indeed, it is not busy at all?
Thank you.
Ray Mond wrote:
> I think the only option left here is to use that suggested by Joe Young i.e.
> use the (NOLOCK) hint in the queries where you do not care about open
> transactions.
>|||By any chance, is DB2 (your database, not IBM's :) in read-only mode? Also,
could you pls run SET STATISTICS IO ON in Query Analyzer, run both queries
and post the resulting messages? Then run SET STATISTICS IO OFF and run SET
STATISTICS TIME ON, rerun the queries and post the messages too? Just
curious to see the results.
Thanks.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:4006BF8E.7060302@.yahoo.com...
> I did "Set transaction isolation level read uncommitted" on the database
> server, but it made no difference. I think the OLEDB used by PHP Web app
> is using "Set transaction isolation level read committed". So no
> matter what I have set on database server doesn't matter.
> I can use "NOLOCK" hint for each sql statement in the application. But
> there are too many of them and I am reluctant to do so.
>
> Again, I backed up DB1 and restore it with the new name DB2. I don't
> need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
> gain the same performance.
> When I check the Process/Locks in SQL Enterprise Manager, I can see that >
the locks come and go. When I see no locks for DB1 and then run my
> sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
> will make the difference for DB1 (it cuts the response time from 1.1
> seconds to 0.6 seconds for 746 rows)?
> Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
> appear to be busy, indeed, it is not busy at all?
> Thank you.
>
>
> Ray Mond wrote:
> > I think the only option left here is to use that suggested by Joe Young
i.e.
> > use the (NOLOCK) hint in the queries where you do not care about open
> > transactions.
> >
>
backup) the database and restore it with a new name within the same
instance, the performance gets back to normal. The size of both database
is 1,900 MB and space available of both is 1,100 MB.
Any idea about what makes the difference?so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with DB1 have a serious
fragmentation problem?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"qluo" <jluost1@.yahoo.com> wrote in message
news:40042824.2030705@.yahoo.com...
> I have a database that has big performance issue. I backup (complete
> backup) the database and restore it with a new name within the same
> instance, the performance gets back to normal. The size of both database
> is 1,900 MB and space available of both is 1,100 MB.
> Any idea about what makes the difference?
>|||Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes 36ms consistently.
I have not looked at the fragmentation problem yet. But the data file
and log file for both databases are in the same physical location.
Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with DB1 have a serious
> fragmentation problem?
>|||is DB1 a live database? ie, are people connecting to DB1
and making updates to table1?
if so, then the reason DB2 is faster for the same query is
that it knows no one has updated table1 on DB2, hence it
is safe to execute as select count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent
inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
>--Original Message--
>Brian,
>You are correct for the procedures I performed.
>The performance problem is, the Web page with DB1 as the
backend
>database takes too long to load.
>The query I used in Query Analyzer on both DB1 and DB2 is:
>--
>select count(*) from table1
>--
>It returns 72,000 on both database.
>For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
>I have not looked at the fragmentation problem yet. But
the data file
>and log file for both databases are in the same physical
location.
>
>Brian Moran wrote:
>> so you're saying that you can:
>> * start with DB1
>> * back up DB1 and restore it as DB2
>> * you could then run the same exact query on DB1 and
DB2. It would be fast
>> on DB2 and slow on DB1?
>> * If so... what steps do you need to do to then make
DB2 slow? Or does
>> performance stay good forever?
>> Have you looked at SQL Profiler?
>> What are the exact performance problems you're seeing?
>> Are you restoring DB2 to the same disk arrays as DB1?
>> Is it possible that the physical files associated with
DB1 have a serious
>> fragmentation problem?
>.
>|||Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1
> and making updates to table1?
> if so, then the reason DB2 is faster for the same query is
> that it knows no one has updated table1 on DB2, hence it
> is safe to execute as select count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent
> inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
>>--Original Message--
>>Brian,
>>You are correct for the procedures I performed.
>>The performance problem is, the Web page with DB1 as the
> backend
>>database takes too long to load.
>>The query I used in Query Analyzer on both DB1 and DB2 is:
>>--
>>select count(*) from table1
>>--
>>It returns 72,000 on both database.
>>For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
>>I have not looked at the fragmentation problem yet. But
> the data file
>>and log file for both databases are in the same physical
> location.
>>Brian Moran wrote:
>>so you're saying that you can:
>>* start with DB1
>>* back up DB1 and restore it as DB2
>>* you could then run the same exact query on DB1 and
> DB2. It would be fast
>>on DB2 and slow on DB1?
>>* If so... what steps do you need to do to then make
> DB2 slow? Or does
>>performance stay good forever?
>>Have you looked at SQL Profiler?
>>What are the exact performance problems you're seeing?
>>Are you restoring DB2 to the same disk arrays as DB1?
>>Is it possible that the physical files associated with
> DB1 have a serious
>>fragmentation problem?
>>
>>.
>|||You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
connection. There should be a trx isolation level property for your
database connectivity object. Not that this setting will be effective for
all queries passing through the connection.
--
Regards
Ray Mond|||I am reluctant to change the default (Read Committed).
My database is not busy at all. It is used by Web app and every
connection should come and go almost immediately. There shouldn't be a
concurrent(locking) issue like this.
Do you know what else I can do to minimize the locking so that I don't
have to change TRANSACTION ISOLATION LEVEL?
Thank you.
Ray Mond wrote:
> You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> connection. There should be a trx isolation level property for your
> database connectivity object. Not that this setting will be effective for
> all queries passing through the connection.
Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
is DB1 a live database? ie, are people connecting to DB1 and making
updates to table1?
if so, then the reason DB2 is faster for the same query is that it knows
no one has updated table1 on DB2, hence it is safe to execute as select
count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
--Original Message--
Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the
backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
I have not looked at the fragmentation problem yet. But
the data file
and log file for both databases are in the same physical
location.
Brian Moran wrote:
so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and
DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make
DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with
DB1 have a serious
fragmentation problem?
.
>|||I think the only option left here is to use that suggested by Joe Young i.e.
use the (NOLOCK) hint in the queries where you do not care about open
transactions.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:400554D8.8080900@.yahoo.com...
> I am reluctant to change the default (Read Committed).
> My database is not busy at all. It is used by Web app and every
> connection should come and go almost immediately. There shouldn't be a
> concurrent(locking) issue like this.
> Do you know what else I can do to minimize the locking so that I don't
> have to change TRANSACTION ISOLATION LEVEL?
> Thank you.
> Ray Mond wrote:
> > You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> > connection. There should be a trx isolation level property for your
> > database connectivity object. Not that this setting will be effective
for
> > all queries passing through the connection.
> Your point is exactly right. I ran the query with (NOLOCK) on both DB1
> and DB2 and they are taking the same amount of time, and dB1 is quicker
> now than before.
> So, what do I need to do on DB1 to achieve the same performance without
> using (NOLOCK)?
> DB1 is not actually busy and it is used by Web applications.
> Thank you.
>
> joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1 and making
> updates to table1?
> if so, then the reason DB2 is faster for the same query is that it knows
> no one has updated table1 on DB2, hence it is safe to execute as select
> count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
> --Original Message--
> Brian,
> You are correct for the procedures I performed.
> The performance problem is, the Web page with DB1 as the
> backend
> database takes too long to load.
> The query I used in Query Analyzer on both DB1 and DB2 is:
> --
> select count(*) from table1
> --
> It returns 72,000 on both database.
> For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
> I have not looked at the fragmentation problem yet. But
> the data file
> and log file for both databases are in the same physical
> location.
>
> Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and
> DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make
> DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with
> DB1 have a serious
> fragmentation problem?
>
> .
>
> >
>|||I did "Set transaction isolation level read uncommitted" on the database
server, but it made no difference. I think the OLEDB used by PHP Web app
is using "Set transaction isolation level read committed". So no
matter what I have set on database server doesn't matter.
I can use "NOLOCK" hint for each sql statement in the application. But
there are too many of them and I am reluctant to do so.
Again, I backed up DB1 and restore it with the new name DB2. I don't
need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
gain the same performance.
When I check the Process/Locks in SQL Enterprise Manager, I can see that
the locks come and go. When I see no locks for DB1 and then run my
sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
will make the difference for DB1 (it cuts the response time from 1.1
seconds to 0.6 seconds for 746 rows)?
Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
appear to be busy, indeed, it is not busy at all?
Thank you.
Ray Mond wrote:
> I think the only option left here is to use that suggested by Joe Young i.e.
> use the (NOLOCK) hint in the queries where you do not care about open
> transactions.
>|||By any chance, is DB2 (your database, not IBM's :) in read-only mode? Also,
could you pls run SET STATISTICS IO ON in Query Analyzer, run both queries
and post the resulting messages? Then run SET STATISTICS IO OFF and run SET
STATISTICS TIME ON, rerun the queries and post the messages too? Just
curious to see the results.
Thanks.
--
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:4006BF8E.7060302@.yahoo.com...
> I did "Set transaction isolation level read uncommitted" on the database
> server, but it made no difference. I think the OLEDB used by PHP Web app
> is using "Set transaction isolation level read committed". So no
> matter what I have set on database server doesn't matter.
> I can use "NOLOCK" hint for each sql statement in the application. But
> there are too many of them and I am reluctant to do so.
>
> Again, I backed up DB1 and restore it with the new name DB2. I don't
> need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
> gain the same performance.
> When I check the Process/Locks in SQL Enterprise Manager, I can see that >
the locks come and go. When I see no locks for DB1 and then run my
> sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
> will make the difference for DB1 (it cuts the response time from 1.1
> seconds to 0.6 seconds for 746 rows)?
> Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
> appear to be busy, indeed, it is not busy at all?
> Thank you.
>
>
> Ray Mond wrote:
> > I think the only option left here is to use that suggested by Joe Young
i.e.
> > use the (NOLOCK) hint in the queries where you do not care about open
> > transactions.
> >
>
"Identical" database, huge performance difference
I have a database that has big performance issue. I backup (complete
backup) the database and restore it with a new name within the same
instance, the performance gets back to normal. The size of both database
is 1,900 MB and space available of both is 1,100 MB.
Any idea about what makes the difference?so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with DB1 have a serious
fragmentation problem?
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"qluo" <jluost1@.yahoo.com> wrote in message
news:40042824.2030705@.yahoo.com...
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes 36ms consistently.
I have not looked at the fragmentation problem yet. But the data file
and log file for both databases are in the same physical location.
Brian Moran wrote:
and making updates to table1?
if so, then the reason DB2 is faster for the same query is
that it knows no one has updated table1 on DB2, hence it
is safe to execute as select count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent
inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
backend
36ms consistently.
the data file
location.
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
connection. There should be a trx isolation level property for your
database connectivity object. Not that this setting will be effective for
all queries passing through the connection.
Regards
Ray Mond|||I am reluctant to change the default (Read Committed).
My database is not busy at all. It is used by Web app and every
connection should come and go almost immediately. There shouldn't be a
concurrent(locking) issue like this.
Do you know what else I can do to minimize the locking so that I don't
have to change TRANSACTION ISOLATION LEVEL?
Thank you.
Ray Mond wrote:
Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
is DB1 a live database? ie, are people connecting to DB1 and making
updates to table1?
if so, then the reason DB2 is faster for the same query is that it knows
no one has updated table1 on DB2, hence it is safe to execute as select
count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
--Original Message--
Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the
backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
I have not looked at the fragmentation problem yet. But
the data file
and log file for both databases are in the same physical
location.
Brian Moran wrote:
so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and
DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make
DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with
DB1 have a serious
fragmentation problem?
.
use the (NOLOCK) hint in the queries where you do not care about open
transactions.
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:400554D8.8080900@.yahoo.com...
server, but it made no difference. I think the OLEDB used by php Web app
is using "Set transaction isolation level read committed". So no
matter what I have set on database server doesn't matter.
I can use "NOLOCK" hint for each sql statement in the application. But
there are too many of them and I am reluctant to do so.
Again, I backed up DB1 and restore it with the new name DB2. I don't
need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
gain the same performance.
When I check the Process/Locks in SQL Enterprise Manager, I can see that
the locks come and go. When I see no locks for DB1 and then run my
sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
will make the difference for DB1 (it cuts the response time from 1.1
seconds to 0.6 seconds for 746 rows)?
Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
appear to be busy, indeed, it is not busy at all?
Thank you.
Ray Mond wrote:
in read-only mode? Also,
could you pls run SET STATISTICS IO ON in Query Analyzer, run both queries
and post the resulting messages? Then run SET STATISTICS IO OFF and run SET
STATISTICS TIME ON, rerun the queries and post the messages too? Just
curious to see the results.
Thanks.
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:4006BF8E.7060302@.yahoo.com...
the locks come and go. When I see no locks for DB1 and then run my
backup) the database and restore it with a new name within the same
instance, the performance gets back to normal. The size of both database
is 1,900 MB and space available of both is 1,100 MB.
Any idea about what makes the difference?so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with DB1 have a serious
fragmentation problem?
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"qluo" <jluost1@.yahoo.com> wrote in message
news:40042824.2030705@.yahoo.com...
quote:|||Brian,
> I have a database that has big performance issue. I backup (complete
> backup) the database and restore it with a new name within the same
> instance, the performance gets back to normal. The size of both database
> is 1,900 MB and space available of both is 1,100 MB.
> Any idea about what makes the difference?
>
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes 36ms consistently.
I have not looked at the fragmentation problem yet. But the data file
and log file for both databases are in the same physical location.
Brian Moran wrote:
quote:|||is DB1 a live database? ie, are people connecting to DB1
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with DB1 have a serious
> fragmentation problem?
>
and making updates to table1?
if so, then the reason DB2 is faster for the same query is
that it knows no one has updated table1 on DB2, hence it
is safe to execute as select count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent
inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
quote:
>--Original Message--
>Brian,
>You are correct for the procedures I performed.
>The performance problem is, the Web page with DB1 as the
backend
quote:
>database takes too long to load.
>The query I used in Query Analyzer on both DB1 and DB2 is:
>--
>select count(*) from table1
>--
>It returns 72,000 on both database.
>For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
quote:
>I have not looked at the fragmentation problem yet. But
the data file
quote:
>and log file for both databases are in the same physical
location.
quote:|||Your point is exactly right. I ran the query with (NOLOCK) on both DB1
>
>Brian Moran wrote:
DB2. It would be fast[QUOTE]
DB2 slow? Or does[QUOTE]
DB1 have a serious[QUOTE]
>.
>
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
quote:|||You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> is DB1 a live database? ie, are people connecting to DB1
> and making updates to table1?
> if so, then the reason DB2 is faster for the same query is
> that it knows no one has updated table1 on DB2, hence it
> is safe to execute as select count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent
> inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
>
> backend
>
> 36ms consistently.
>
> the data file
>
> location.
>
> DB2. It would be fast
>
> DB2 slow? Or does
>
> DB1 have a serious
>
>
connection. There should be a trx isolation level property for your
database connectivity object. Not that this setting will be effective for
all queries passing through the connection.
Regards
Ray Mond|||I am reluctant to change the default (Read Committed).
My database is not busy at all. It is used by Web app and every
connection should come and go almost immediately. There shouldn't be a
concurrent(locking) issue like this.
Do you know what else I can do to minimize the locking so that I don't
have to change TRANSACTION ISOLATION LEVEL?
Thank you.
Ray Mond wrote:
quote:
> You could set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for your
> connection. There should be a trx isolation level property for your
> database connectivity object. Not that this setting will be effective for
> all queries passing through the connection.
Your point is exactly right. I ran the query with (NOLOCK) on both DB1
and DB2 and they are taking the same amount of time, and dB1 is quicker
now than before.
So, what do I need to do on DB1 to achieve the same performance without
using (NOLOCK)?
DB1 is not actually busy and it is used by Web applications.
Thank you.
joe chang wrote:
is DB1 a live database? ie, are people connecting to DB1 and making
updates to table1?
if so, then the reason DB2 is faster for the same query is that it knows
no one has updated table1 on DB2, hence it is safe to execute as select
count(*) from table1 (NOLOCK)
while DB1 must row lock if there were recent inserts/upd/del to table1
try
select count(*) from table1 (NOLOCK)
on both
-joe
--Original Message--
Brian,
You are correct for the procedures I performed.
The performance problem is, the Web page with DB1 as the
backend
database takes too long to load.
The query I used in Query Analyzer on both DB1 and DB2 is:
--
select count(*) from table1
--
It returns 72,000 on both database.
For DB1, it takes 220 ms consistently; for DB2, it takes
36ms consistently.
I have not looked at the fragmentation problem yet. But
the data file
and log file for both databases are in the same physical
location.
Brian Moran wrote:
so you're saying that you can:
* start with DB1
* back up DB1 and restore it as DB2
* you could then run the same exact query on DB1 and
DB2. It would be fast
on DB2 and slow on DB1?
* If so... what steps do you need to do to then make
DB2 slow? Or does
performance stay good forever?
Have you looked at SQL Profiler?
What are the exact performance problems you're seeing?
Are you restoring DB2 to the same disk arrays as DB1?
Is it possible that the physical files associated with
DB1 have a serious
fragmentation problem?
.
quote:|||I think the only option left here is to use that suggested by Joe Young i.e.
>
use the (NOLOCK) hint in the queries where you do not care about open
transactions.
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:400554D8.8080900@.yahoo.com...
quote:|||I did "Set transaction isolation level read uncommitted" on the database
> I am reluctant to change the default (Read Committed).
> My database is not busy at all. It is used by Web app and every
> connection should come and go almost immediately. There shouldn't be a
> concurrent(locking) issue like this.
> Do you know what else I can do to minimize the locking so that I don't
> have to change TRANSACTION ISOLATION LEVEL?
> Thank you.
> Ray Mond wrote:
for[QUOTE]
> Your point is exactly right. I ran the query with (NOLOCK) on both DB1
> and DB2 and they are taking the same amount of time, and dB1 is quicker
> now than before.
> So, what do I need to do on DB1 to achieve the same performance without
> using (NOLOCK)?
> DB1 is not actually busy and it is used by Web applications.
> Thank you.
>
> joe chang wrote:
> is DB1 a live database? ie, are people connecting to DB1 and making
> updates to table1?
> if so, then the reason DB2 is faster for the same query is that it knows
> no one has updated table1 on DB2, hence it is safe to execute as select
> count(*) from table1 (NOLOCK)
> while DB1 must row lock if there were recent inserts/upd/del to table1
> try
> select count(*) from table1 (NOLOCK)
> on both
> -joe
> --Original Message--
> Brian,
> You are correct for the procedures I performed.
> The performance problem is, the Web page with DB1 as the
> backend
> database takes too long to load.
> The query I used in Query Analyzer on both DB1 and DB2 is:
> --
> select count(*) from table1
> --
> It returns 72,000 on both database.
> For DB1, it takes 220 ms consistently; for DB2, it takes
> 36ms consistently.
> I have not looked at the fragmentation problem yet. But
> the data file
> and log file for both databases are in the same physical
> location.
>
> Brian Moran wrote:
> so you're saying that you can:
> * start with DB1
> * back up DB1 and restore it as DB2
> * you could then run the same exact query on DB1 and
> DB2. It would be fast
> on DB2 and slow on DB1?
> * If so... what steps do you need to do to then make
> DB2 slow? Or does
> performance stay good forever?
> Have you looked at SQL Profiler?
> What are the exact performance problems you're seeing?
> Are you restoring DB2 to the same disk arrays as DB1?
> Is it possible that the physical files associated with
> DB1 have a serious
> fragmentation problem?
>
> .
>
>
>
server, but it made no difference. I think the OLEDB used by php Web app
is using "Set transaction isolation level read committed". So no
matter what I have set on database server doesn't matter.
I can use "NOLOCK" hint for each sql statement in the application. But
there are too many of them and I am reluctant to do so.
Again, I backed up DB1 and restore it with the new name DB2. I don't
need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
gain the same performance.
When I check the Process/Locks in SQL Enterprise Manager, I can see that
the locks come and go. When I see no locks for DB1 and then run my
sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
will make the difference for DB1 (it cuts the response time from 1.1
seconds to 0.6 seconds for 746 rows)?
Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
appear to be busy, indeed, it is not busy at all?
Thank you.
Ray Mond wrote:
quote:|||By any chance, is DB2 (your database, not IBM's
> I think the only option left here is to use that suggested by Joe Young i.
e.
> use the (NOLOCK) hint in the queries where you do not care about open
> transactions.
>
in read-only mode? Also,could you pls run SET STATISTICS IO ON in Query Analyzer, run both queries
and post the resulting messages? Then run SET STATISTICS IO OFF and run SET
STATISTICS TIME ON, rerun the queries and post the messages too? Just
curious to see the results.
Thanks.
Regards
Ray Mond
"qluo" <jluost1@.yahoo.com> wrote in message
news:4006BF8E.7060302@.yahoo.com...
quote:
> I did "Set transaction isolation level read uncommitted" on the database
> server, but it made no difference. I think the OLEDB used by php Web app
> is using "Set transaction isolation level read committed". So no
> matter what I have set on database server doesn't matter.
> I can use "NOLOCK" hint for each sql statement in the application. But
> there are too many of them and I am reluctant to do so.
>
quote:
> Again, I backed up DB1 and restore it with the new name DB2. I don't
> need to use "NOLOCK" hint on DB2. But for DB1, I have to use the hint to
> gain the same performance.
> When I check the Process/Locks in SQL Enterprise Manager, I can see that >
the locks come and go. When I see no locks for DB1 and then run my
quote:
> sql, I can see the locks by my sql are the only locks. Why NOLOCK hint
> will make the difference for DB1 (it cuts the response time from 1.1
> seconds to 0.6 seconds for 746 rows)?
> Joe Young said the reason is DB1 is "busy" and DB2 is not. Why does DB1
> appear to be busy, indeed, it is not busy at all?
> Thank you.
>
>
> Ray Mond wrote:
i.e.[QUOTE]
>
Thursday, February 16, 2012
"exclusive access could not be obtained.." while restoring
Hello
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with something like this
Private Sub Backup(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT
.ExecuteNonQuery(
End Wit
MsgBox("Database backed up successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
but when I do restore using something like this
Private Sub Restore(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY
.ExecuteNonQuery(
End Wit
MsgBox("Database restored successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
I get the "exclusive access could not be obtained.. .. database is in use" error message
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK ='D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK ='D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with something like this
Private Sub Backup(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT
.ExecuteNonQuery(
End Wit
MsgBox("Database backed up successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
but when I do restore using something like this
Private Sub Restore(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY
.ExecuteNonQuery(
End Wit
MsgBox("Database restored successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
I get the "exclusive access could not be obtained.. .. database is in use" error message
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK ='D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK ='D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
Monday, February 13, 2012
"exclusive access could not be obtained.." while restoring
Hello,
I am using MSDE with my application and providing our users UI to backup/res
tore the database. My app has just 1 database and 1 login mapped to 1 user
(MyAppUser). Backup and restore functionality is using inline sql commands.
Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RE
COVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" e
rror message.
I did "Use Master" in query analyser and ran the same restore sql command an
d it worked fine. I do not know how to use "Use master" here in ado.net. I
know it has something to do with sp_Who but not sure how the syntax will fi
t in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Pa
ssword = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
I am using MSDE with my application and providing our users UI to backup/res
tore the database. My app has just 1 database and 1 login mapped to 1 user
(MyAppUser). Backup and restore functionality is using inline sql commands.
Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RE
COVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" e
rror message.
I did "Use Master" in query analyser and ran the same restore sql command an
d it worked fine. I do not know how to use "Use master" here in ado.net. I
know it has something to do with sp_Who but not sure how the syntax will fi
t in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Pa
ssword = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
"exclusive access could not be obtained.." while restoring
Hello,
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" error message.
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.
You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" error message.
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.
You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.
"Detach Database" in SQL Server 2005 apparently dropped the database instead
Hello,
I am still trying to get my head around this one. I have been working
on a new version of a website and decided I wanted to make a backup of
the old database file before I tried running any scripts against it.
Since the production website is currently down, I decided to detach
the database, copy the file, and then reattach it. Instead of using
scripts, I used MS SQL Server Management Studio to handle the task.
After I ran the detach command on the database (with the default
options selected), I went to the directory where all of the database
files are stored on the server and the file doesn't exist!!
So, I tried to back up the database file because I didn't have a copy,
and now I don't even have the original. I did a complete Windows file
search on my entire network to try to locate the file, but it is
gone. Well, it isn't the end of the world in my case because I can go
to a prior backup and just move forward, but I really would like to
know what happened so it doesn't happen again.
For starters, is there any way I can query a system table to determine
what the file location was of the database I detached? Second, is
there some "memory only" mode that a database can be put in so when it
is detached it will disappear completely?
TIAHi
It is really strange . I did just at least ten testes to detach the database
via SSMS and it worked just fine
I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem for
another non-produiction database?
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188456828.420852.301950@.m37g2000prh.googlegroups.com...
> Hello,
> I am still trying to get my head around this one. I have been working
> on a new version of a website and decided I wanted to make a backup of
> the old database file before I tried running any scripts against it.
> Since the production website is currently down, I decided to detach
> the database, copy the file, and then reattach it. Instead of using
> scripts, I used MS SQL Server Management Studio to handle the task.
> After I ran the detach command on the database (with the default
> options selected), I went to the directory where all of the database
> files are stored on the server and the file doesn't exist!!
> So, I tried to back up the database file because I didn't have a copy,
> and now I don't even have the original. I did a complete Windows file
> search on my entire network to try to locate the file, but it is
> gone. Well, it isn't the end of the world in my case because I can go
> to a prior backup and just move forward, but I really would like to
> know what happened so it doesn't happen again.
> For starters, is there any way I can query a system table to determine
> what the file location was of the database I detached? Second, is
> there some "memory only" mode that a database can be put in so when it
> is detached it will disappear completely?
> TIA
>|||Sounds weird. No chance SSMS to drop a database when you detach it. There is
only "drop connection" option which contains "drop" action and it doesn't
drop the database.
You said you did a complete seach on your HDD(s) but I guess it must be
around somewhere on your HDD. Maybe you forgot it's name or something?
--
Ekrem Önsoy
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188456828.420852.301950@.m37g2000prh.googlegroups.com...
> Hello,
> I am still trying to get my head around this one. I have been working
> on a new version of a website and decided I wanted to make a backup of
> the old database file before I tried running any scripts against it.
> Since the production website is currently down, I decided to detach
> the database, copy the file, and then reattach it. Instead of using
> scripts, I used MS SQL Server Management Studio to handle the task.
> After I ran the detach command on the database (with the default
> options selected), I went to the directory where all of the database
> files are stored on the server and the file doesn't exist!!
> So, I tried to back up the database file because I didn't have a copy,
> and now I don't even have the original. I did a complete Windows file
> search on my entire network to try to locate the file, but it is
> gone. Well, it isn't the end of the world in my case because I can go
> to a prior backup and just move forward, but I really would like to
> know what happened so it doesn't happen again.
> For starters, is there any way I can query a system table to determine
> what the file location was of the database I detached? Second, is
> there some "memory only" mode that a database can be put in so when it
> is detached it will disappear completely?
> TIA
>|||On Aug 30, 12:12 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> It is really strange . I did just at least ten testes to detach the database
> via SSMS and it worked just fine
> I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem for
> another non-produiction database?
>
I tried creating a new sample database on the same server and
detaching it, but the files remained. So to your answer your
question, no I cannot repeat the problem.
I also know for sure I used the detach and not the delete command on
the previous database because the dialogs are different.
Is there any SQL command you know of that I can use to determine what
exact file location was detached?|||On Aug 30, 12:24 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> Sounds weird. No chance SSMS to drop a database when you detach it. There= is
> only "drop connection" option which contains "drop" action and it doesn't
> drop the database.
> You said you did a complete seach on your HDD(s) but I guess it must be
> around somewhere on your HDD. Maybe you forgot it's name or something?
> --
> Ekrem =D6nsoy
>
Believe me, I didn't forget the name. And I scanned every server (and
even every workstation) using "*.mdf". No dice. Since I set the
server up myself, I am pretty certain that I put the file on the local
server. I have never lost a database before and I have been working
with them for about 10 years.
About a week ago, I shut down the MSSQL service and copied all of the
database files from the server to another location. However, this one
database file wasn't included. This makes me wonder if there was just
a "ghost" database object with no actual file behind it or something.
That is why I would like to try to see what it was referencing if
possible.|||> Is there any SQL command you know of that I can use to determine what
> exact file location was detached?
Its probably where all your user databases are located isn't it?
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188458888.053113.228460@.x35g2000prf.googlegroups.com...
>
> On Aug 30, 12:12 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> It is really strange . I did just at least ten testes to detach the
>> database
>> via SSMS and it worked just fine
>> I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem
>> for
>> another non-produiction database?
> I tried creating a new sample database on the same server and
> detaching it, but the files remained. So to your answer your
> question, no I cannot repeat the problem.
> I also know for sure I used the detach and not the delete command on
> the previous database because the dialogs are different.
> Is there any SQL command you know of that I can use to determine what
> exact file location was detached?
>|||Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
check the file location.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188459431.752325.247190@.x35g2000prf.googlegroups.com...
On Aug 30, 12:24 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Sounds weird. No chance SSMS to drop a database when you detach it. There is
> only "drop connection" option which contains "drop" action and it doesn't
> drop the database.
> You said you did a complete seach on your HDD(s) but I guess it must be
> around somewhere on your HDD. Maybe you forgot it's name or something?
> --
> Ekrem Önsoy
>
Believe me, I didn't forget the name. And I scanned every server (and
even every workstation) using "*.mdf". No dice. Since I set the
server up myself, I am pretty certain that I put the file on the local
server. I have never lost a database before and I have been working
with them for about 10 years.
About a week ago, I shut down the MSSQL service and copied all of the
database files from the server to another location. However, this one
database file wasn't included. This makes me wonder if there was just
a "ghost" database object with no actual file behind it or something.
That is why I would like to try to see what it was referencing if
possible.|||On Aug 30, 1:19 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
> check the file location.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
Actually all I have is a database that I created on another computer
and then used DTS to copy tables with data over to it. This was done
before I upgraded to SQL 2005. I don't technically have a "backup" in
the SQL Server sense of the word.|||Do you have an old backup of the master database on the computer where the database existed? If so,
you could restore that backup somewhere and check the sysaltfiles table. Will only give you the mdf
file, but that might be good enough...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188467622.686367.250240@.e9g2000prf.googlegroups.com...
> On Aug 30, 1:19 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
>> check the file location.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> Actually all I have is a database that I created on another computer
> and then used DTS to copy tables with data over to it. This was done
> before I upgraded to SQL 2005. I don't technically have a "backup" in
> the SQL Server sense of the word.
>|||No, I don't have any backups of the master database. This server has
been offline for almost a year. I was just in the process of backing
up the data in the primary database on the server by making a copy of
the file. I plan to set up backups going forward but I didn't have
any before (which is why I decided to detach the database in the first
place).
The file either disappeared when I ran the command to detach it or it
didn't exist before that. If it didn't exist that is another
inexplicable problem - I know I left it in place. Not to mention, I
didn't get any sql errors that the file was missing or anything like
that.
On Aug 30, 3:08 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Do you have an old backup of the master database on the computer where the database existed? If so,
> you could restore that backup somewhere and check the sysaltfiles table. Will only give you the mdf
> file, but that might be good enough...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "NightOwl888" <sstorh...@.webuniverse.net> wrote in message
> news:1188467622.686367.250240@.e9g2000prf.googlegroups.com...|||I figured out the problem. The production database file was prefixed
by the word "Test", the same as the test database. They were in 2
different directories, but they were all on the same server. I
thought that the production database was gone, but it was just named
"Test".
At some point I must have copied the Test database and attached it as
production. This makes sense because still now I am having issues
moving tables from one database to another.
Well, I am glad that I don't have to revert to an older copy and lose
some data. I will definitely back up master and set up some backup
jobs now, too.|||backup...backup..backup...;-)
Make it a best practice to always backup the master database anytime you do
something on the server even if the server is an old one. You'll never know
when it will become handy
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188520731.793321.109640@.q3g2000prf.googlegroups.com...
>I figured out the problem. The production database file was prefixed
> by the word "Test", the same as the test database. They were in 2
> different directories, but they were all on the same server. I
> thought that the production database was gone, but it was just named
> "Test".
> At some point I must have copied the Test database and attached it as
> production. This makes sense because still now I am having issues
> moving tables from one database to another.
> Well, I am glad that I don't have to revert to an older copy and lose
> some data. I will definitely back up master and set up some backup
> jobs now, too.
>
I am still trying to get my head around this one. I have been working
on a new version of a website and decided I wanted to make a backup of
the old database file before I tried running any scripts against it.
Since the production website is currently down, I decided to detach
the database, copy the file, and then reattach it. Instead of using
scripts, I used MS SQL Server Management Studio to handle the task.
After I ran the detach command on the database (with the default
options selected), I went to the directory where all of the database
files are stored on the server and the file doesn't exist!!
So, I tried to back up the database file because I didn't have a copy,
and now I don't even have the original. I did a complete Windows file
search on my entire network to try to locate the file, but it is
gone. Well, it isn't the end of the world in my case because I can go
to a prior backup and just move forward, but I really would like to
know what happened so it doesn't happen again.
For starters, is there any way I can query a system table to determine
what the file location was of the database I detached? Second, is
there some "memory only" mode that a database can be put in so when it
is detached it will disappear completely?
TIAHi
It is really strange . I did just at least ten testes to detach the database
via SSMS and it worked just fine
I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem for
another non-produiction database?
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188456828.420852.301950@.m37g2000prh.googlegroups.com...
> Hello,
> I am still trying to get my head around this one. I have been working
> on a new version of a website and decided I wanted to make a backup of
> the old database file before I tried running any scripts against it.
> Since the production website is currently down, I decided to detach
> the database, copy the file, and then reattach it. Instead of using
> scripts, I used MS SQL Server Management Studio to handle the task.
> After I ran the detach command on the database (with the default
> options selected), I went to the directory where all of the database
> files are stored on the server and the file doesn't exist!!
> So, I tried to back up the database file because I didn't have a copy,
> and now I don't even have the original. I did a complete Windows file
> search on my entire network to try to locate the file, but it is
> gone. Well, it isn't the end of the world in my case because I can go
> to a prior backup and just move forward, but I really would like to
> know what happened so it doesn't happen again.
> For starters, is there any way I can query a system table to determine
> what the file location was of the database I detached? Second, is
> there some "memory only" mode that a database can be put in so when it
> is detached it will disappear completely?
> TIA
>|||Sounds weird. No chance SSMS to drop a database when you detach it. There is
only "drop connection" option which contains "drop" action and it doesn't
drop the database.
You said you did a complete seach on your HDD(s) but I guess it must be
around somewhere on your HDD. Maybe you forgot it's name or something?
--
Ekrem Önsoy
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188456828.420852.301950@.m37g2000prh.googlegroups.com...
> Hello,
> I am still trying to get my head around this one. I have been working
> on a new version of a website and decided I wanted to make a backup of
> the old database file before I tried running any scripts against it.
> Since the production website is currently down, I decided to detach
> the database, copy the file, and then reattach it. Instead of using
> scripts, I used MS SQL Server Management Studio to handle the task.
> After I ran the detach command on the database (with the default
> options selected), I went to the directory where all of the database
> files are stored on the server and the file doesn't exist!!
> So, I tried to back up the database file because I didn't have a copy,
> and now I don't even have the original. I did a complete Windows file
> search on my entire network to try to locate the file, but it is
> gone. Well, it isn't the end of the world in my case because I can go
> to a prior backup and just move forward, but I really would like to
> know what happened so it doesn't happen again.
> For starters, is there any way I can query a system table to determine
> what the file location was of the database I detached? Second, is
> there some "memory only" mode that a database can be put in so when it
> is detached it will disappear completely?
> TIA
>|||On Aug 30, 12:12 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> It is really strange . I did just at least ten testes to detach the database
> via SSMS and it worked just fine
> I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem for
> another non-produiction database?
>
I tried creating a new sample database on the same server and
detaching it, but the files remained. So to your answer your
question, no I cannot repeat the problem.
I also know for sure I used the detach and not the delete command on
the previous database because the dialogs are different.
Is there any SQL command you know of that I can use to determine what
exact file location was detached?|||On Aug 30, 12:24 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> Sounds weird. No chance SSMS to drop a database when you detach it. There= is
> only "drop connection" option which contains "drop" action and it doesn't
> drop the database.
> You said you did a complete seach on your HDD(s) but I guess it must be
> around somewhere on your HDD. Maybe you forgot it's name or something?
> --
> Ekrem =D6nsoy
>
Believe me, I didn't forget the name. And I scanned every server (and
even every workstation) using "*.mdf". No dice. Since I set the
server up myself, I am pretty certain that I put the file on the local
server. I have never lost a database before and I have been working
with them for about 10 years.
About a week ago, I shut down the MSSQL service and copied all of the
database files from the server to another location. However, this one
database file wasn't included. This makes me wonder if there was just
a "ghost" database object with no actual file behind it or something.
That is why I would like to try to see what it was referencing if
possible.|||> Is there any SQL command you know of that I can use to determine what
> exact file location was detached?
Its probably where all your user databases are located isn't it?
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188458888.053113.228460@.x35g2000prf.googlegroups.com...
>
> On Aug 30, 12:12 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> It is really strange . I did just at least ten testes to detach the
>> database
>> via SSMS and it worked just fine
>> I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem
>> for
>> another non-produiction database?
> I tried creating a new sample database on the same server and
> detaching it, but the files remained. So to your answer your
> question, no I cannot repeat the problem.
> I also know for sure I used the detach and not the delete command on
> the previous database because the dialogs are different.
> Is there any SQL command you know of that I can use to determine what
> exact file location was detached?
>|||Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
check the file location.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188459431.752325.247190@.x35g2000prf.googlegroups.com...
On Aug 30, 12:24 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Sounds weird. No chance SSMS to drop a database when you detach it. There is
> only "drop connection" option which contains "drop" action and it doesn't
> drop the database.
> You said you did a complete seach on your HDD(s) but I guess it must be
> around somewhere on your HDD. Maybe you forgot it's name or something?
> --
> Ekrem Önsoy
>
Believe me, I didn't forget the name. And I scanned every server (and
even every workstation) using "*.mdf". No dice. Since I set the
server up myself, I am pretty certain that I put the file on the local
server. I have never lost a database before and I have been working
with them for about 10 years.
About a week ago, I shut down the MSSQL service and copied all of the
database files from the server to another location. However, this one
database file wasn't included. This makes me wonder if there was just
a "ghost" database object with no actual file behind it or something.
That is why I would like to try to see what it was referencing if
possible.|||On Aug 30, 1:19 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
> check the file location.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
Actually all I have is a database that I created on another computer
and then used DTS to copy tables with data over to it. This was done
before I upgraded to SQL 2005. I don't technically have a "backup" in
the SQL Server sense of the word.|||Do you have an old backup of the master database on the computer where the database existed? If so,
you could restore that backup somewhere and check the sysaltfiles table. Will only give you the mdf
file, but that might be good enough...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188467622.686367.250240@.e9g2000prf.googlegroups.com...
> On Aug 30, 1:19 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
>> check the file location.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> Actually all I have is a database that I created on another computer
> and then used DTS to copy tables with data over to it. This was done
> before I upgraded to SQL 2005. I don't technically have a "backup" in
> the SQL Server sense of the word.
>|||No, I don't have any backups of the master database. This server has
been offline for almost a year. I was just in the process of backing
up the data in the primary database on the server by making a copy of
the file. I plan to set up backups going forward but I didn't have
any before (which is why I decided to detach the database in the first
place).
The file either disappeared when I ran the command to detach it or it
didn't exist before that. If it didn't exist that is another
inexplicable problem - I know I left it in place. Not to mention, I
didn't get any sql errors that the file was missing or anything like
that.
On Aug 30, 3:08 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Do you have an old backup of the master database on the computer where the database existed? If so,
> you could restore that backup somewhere and check the sysaltfiles table. Will only give you the mdf
> file, but that might be good enough...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "NightOwl888" <sstorh...@.webuniverse.net> wrote in message
> news:1188467622.686367.250240@.e9g2000prf.googlegroups.com...|||I figured out the problem. The production database file was prefixed
by the word "Test", the same as the test database. They were in 2
different directories, but they were all on the same server. I
thought that the production database was gone, but it was just named
"Test".
At some point I must have copied the Test database and attached it as
production. This makes sense because still now I am having issues
moving tables from one database to another.
Well, I am glad that I don't have to revert to an older copy and lose
some data. I will definitely back up master and set up some backup
jobs now, too.|||backup...backup..backup...;-)
Make it a best practice to always backup the master database anytime you do
something on the server even if the server is an old one. You'll never know
when it will become handy
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188520731.793321.109640@.q3g2000prf.googlegroups.com...
>I figured out the problem. The production database file was prefixed
> by the word "Test", the same as the test database. They were in 2
> different directories, but they were all on the same server. I
> thought that the production database was gone, but it was just named
> "Test".
> At some point I must have copied the Test database and attached it as
> production. This makes sense because still now I am having issues
> moving tables from one database to another.
> Well, I am glad that I don't have to revert to an older copy and lose
> some data. I will definitely back up master and set up some backup
> jobs now, too.
>
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?
> >
>
Subscribe to:
Posts (Atom)
