it's about the Maintance plan clean up task...in sql2k, you can just
specify all system databases in maintenance plan and purge files older
than a specified time (i.e. 2 days old), but in 2k5, there is a
seperate maintenance clean up task, you can't specify the database
(i.e. all system dbs) or directory (and include sub folders). does that
mean I have to create 3 tasks just to delete old files for master,
msdb, and model? that would be really dumb, or am I missing something?
Thank you.I have the exact same question.
Anybody can help ?
=== Steve L === wrote:
> it's about the Maintance plan clean up task...in sql2k, you can just
> specify all system databases in maintenance plan and purge files older
> than a specified time (i.e. 2 days old), but in 2k5, there is a
> seperate maintenance clean up task, you can't specify the database
> (i.e. all system dbs) or directory (and include sub folders). does that
> mean I have to create 3 tasks just to delete old files for master,
> msdb, and model? that would be really dumb, or am I missing something?
> Thank you.|||That is the way it is designed. Consider proposing a change at
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<xiaoyuh.huang@.gmail.com> wrote in message
news:1143835327.889394.267740@.z34g2000cwc.googlegroups.com...
>I have the exact same question.
> Anybody can help ?
> === Steve L === wrote:
>
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Friday, March 30, 2012
Question about scheduled job
You are the administrator of SQL Server 2000 computer. You create a job that
performs several maintenance tasks on the server’s databases. You want the
job to run whenever the server’s processor utilization falls below 5 percent.
You create a new schedule for the job and specify the start whenever the CPU(
s) become idle option. After several days, you notice that the job has never
executed although the server’s processor utilization has fallen below 5
percent several times.
What should you do?
A. Modify SQL Server Agent properties and specify a smaller idle time.
B. Modify SQL server agent properties and specify a larger idle time.
C. Write a stored procedure that executes the job whenever the @.@. IDLE
system variable is less than 5.
D. Write a stored procedure that executes the job whenever the @.@. IDLE
system variable is greater
than 1.
The processor utilization option works in conjunction with the
settings for the idle time or number of seconds the cpu remains below
the utilization percent specified. If you fall below the utilization
and the job never fires, it's generally due to the number of seconds
you have specified for the idle time never being hit so you'd want to
try lowering the number of seconds that the processor utilization
needs to be at the specified percentage. Once you hit the specified
number of seconds, the CPU is considered idle.
-Sue
On Wed, 8 Sep 2004 07:33:03 -0700, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>You are the administrator of SQL Server 2000 computer. You create a job that
>performs several maintenance tasks on the servers databases. You want the
>job to run whenever the servers processor utilization falls below 5 percent.
>You create a new schedule for the job and specify the start whenever the CPU(
>s) become idle option. After several days, you notice that the job has never
>executed although the servers processor utilization has fallen below 5
>percent several times.
>What should you do?
>A. Modify SQL Server Agent properties and specify a smaller idle time.
>B. Modify SQL server agent properties and specify a larger idle time.
>C. Write a stored procedure that executes the job whenever the @.@. IDLE
>system variable is less than 5.
>D. Write a stored procedure that executes the job whenever the @.@. IDLE
>system variable is greater
>than 1.
performs several maintenance tasks on the server’s databases. You want the
job to run whenever the server’s processor utilization falls below 5 percent.
You create a new schedule for the job and specify the start whenever the CPU(
s) become idle option. After several days, you notice that the job has never
executed although the server’s processor utilization has fallen below 5
percent several times.
What should you do?
A. Modify SQL Server Agent properties and specify a smaller idle time.
B. Modify SQL server agent properties and specify a larger idle time.
C. Write a stored procedure that executes the job whenever the @.@. IDLE
system variable is less than 5.
D. Write a stored procedure that executes the job whenever the @.@. IDLE
system variable is greater
than 1.
The processor utilization option works in conjunction with the
settings for the idle time or number of seconds the cpu remains below
the utilization percent specified. If you fall below the utilization
and the job never fires, it's generally due to the number of seconds
you have specified for the idle time never being hit so you'd want to
try lowering the number of seconds that the processor utilization
needs to be at the specified percentage. Once you hit the specified
number of seconds, the CPU is considered idle.
-Sue
On Wed, 8 Sep 2004 07:33:03 -0700, "Eric"
<Eric@.discussions.microsoft.com> wrote:
>You are the administrator of SQL Server 2000 computer. You create a job that
>performs several maintenance tasks on the servers databases. You want the
>job to run whenever the servers processor utilization falls below 5 percent.
>You create a new schedule for the job and specify the start whenever the CPU(
>s) become idle option. After several days, you notice that the job has never
>executed although the servers processor utilization has fallen below 5
>percent several times.
>What should you do?
>A. Modify SQL Server Agent properties and specify a smaller idle time.
>B. Modify SQL server agent properties and specify a larger idle time.
>C. Write a stored procedure that executes the job whenever the @.@. IDLE
>system variable is less than 5.
>D. Write a stored procedure that executes the job whenever the @.@. IDLE
>system variable is greater
>than 1.
Friday, March 23, 2012
question about master system database
Hi, i use sql server 2005 express where i created several databases. When opening Management Studio, i can see, besides my databases, also Master, Model etc .. which manage the databases. Now i also use MDF files which are disseminated on the same server. I see that for one given user, there is also a Master, Model etc ... in c:\document and settings\that user\ ....
My question is: are those local Master, Model etc ... related with the central Master, Model ... of the central database system or are they independant? What if i delete the MDF files and its related system files in c:\.doc and settings\.. ?
Thanks
When you check under properties for the database in the Management Studio you should see the database is pointing to a particular file on the drives. So other than this MDF file you could delete any other MDF files with same name if you are sure no one else is using the files. You could try moving the files to a different location and if it lets you do it then no one is using it. If the files are being used you would get an '..Access violation..' or ' file in use..' error.
|||thanksTuesday, March 20, 2012
Question about IDENTITY columns.
We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
This table is present in 2 databases Db1 and Db2. We want to keep them
both in sync daily. The mapping of Name -> ID should be the same in
both the tables. We allow only Db1 tables to be updated. So we would
like to have a program that daily truncates [Db2].[TAB1] and creates
and executes a DTS package to transfer the table from DB1 to DB2.
My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
will get copied to DB2. If we delete the row with ID 3, will the next
syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
the table as 1,2,4,5?
Thanks
YashIf you create the field in table 2 as an identity field you will have
1,2,3,4 rather than 1,2,4,5 when trucating and re-populating. You could use
a table trigger to keep both tables in sync.
groutme in SO Cal
groutme_alternate@.sbcglobal.net
<yashgt@.yahoo.com> wrote in message
news:1109006625.544445.159200@.c13g2000cwb.googlegroups.com...
> We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
> This table is present in 2 databases Db1 and Db2. We want to keep them
> both in sync daily. The mapping of Name -> ID should be the same in
> both the tables. We allow only Db1 tables to be updated. So we would
> like to have a program that daily truncates [Db2].[TAB1] and creates
> and executes a DTS package to transfer the table from DB1 to DB2.
> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
> Thanks
> Yash
>|||> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
It will copy 1, 2, 4,5. But you have to set the property "Enable Identity
insert" in the options tab of the "Transform Data Task" properties, in order
to allow explicit values to be inserted into the identity column.
AMB
"yashgt@.yahoo.com" wrote:
> We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
> This table is present in 2 databases Db1 and Db2. We want to keep them
> both in sync daily. The mapping of Name -> ID should be the same in
> both the tables. We allow only Db1 tables to be updated. So we would
> like to have a program that daily truncates [Db2].[TAB1] and creates
> and executes a DTS package to transfer the table from DB1 to DB2.
> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
> Thanks
> Yash
>|||>> If we delete the row with ID 3, will the next syncing execution cause the
There is an option in DTS which allow identity inserts under the
transformation section. When this is off the values are inserted without
generting new ones. Otherwise the new values will be generated in the
sequence based on the seed and increment set on the identity column for the
table.
Anith|||If the data in DB2 gets replaced every day then what's the point of giving i
t
an IDENTITY column? Make it a regular numeric column so that you have full
control over what values go in there.
David Portas
SQL Server MVP
--
This table is present in 2 databases Db1 and Db2. We want to keep them
both in sync daily. The mapping of Name -> ID should be the same in
both the tables. We allow only Db1 tables to be updated. So we would
like to have a program that daily truncates [Db2].[TAB1] and creates
and executes a DTS package to transfer the table from DB1 to DB2.
My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
will get copied to DB2. If we delete the row with ID 3, will the next
syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
the table as 1,2,4,5?
Thanks
YashIf you create the field in table 2 as an identity field you will have
1,2,3,4 rather than 1,2,4,5 when trucating and re-populating. You could use
a table trigger to keep both tables in sync.
groutme in SO Cal
groutme_alternate@.sbcglobal.net
<yashgt@.yahoo.com> wrote in message
news:1109006625.544445.159200@.c13g2000cwb.googlegroups.com...
> We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
> This table is present in 2 databases Db1 and Db2. We want to keep them
> both in sync daily. The mapping of Name -> ID should be the same in
> both the tables. We allow only Db1 tables to be updated. So we would
> like to have a program that daily truncates [Db2].[TAB1] and creates
> and executes a DTS package to transfer the table from DB1 to DB2.
> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
> Thanks
> Yash
>|||> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
It will copy 1, 2, 4,5. But you have to set the property "Enable Identity
insert" in the options tab of the "Transform Data Task" properties, in order
to allow explicit values to be inserted into the identity column.
AMB
"yashgt@.yahoo.com" wrote:
> We have a atble TAB1[ ID IDENTITY integer, NAME VARCHAR[30], ... ].
> This table is present in 2 databases Db1 and Db2. We want to keep them
> both in sync daily. The mapping of Name -> ID should be the same in
> both the tables. We allow only Db1 tables to be updated. So we would
> like to have a program that daily truncates [Db2].[TAB1] and creates
> and executes a DTS package to transfer the table from DB1 to DB2.
> My question is: If there are entries in DB1 ith IDs 1,2,3,4,5. The same
> will get copied to DB2. If we delete the row with ID 3, will the next
> syncing execution cause the DB2 table to have 1,2,3,4 or will it copy
> the table as 1,2,4,5?
> Thanks
> Yash
>|||>> If we delete the row with ID 3, will the next syncing execution cause the
There is an option in DTS which allow identity inserts under the
transformation section. When this is off the values are inserted without
generting new ones. Otherwise the new values will be generated in the
sequence based on the seed and increment set on the identity column for the
table.
Anith|||If the data in DB2 gets replaced every day then what's the point of giving i
t
an IDENTITY column? Make it a regular numeric column so that you have full
control over what values go in there.
David Portas
SQL Server MVP
--
Monday, March 12, 2012
Question about detaching databases.
I need to have someone clarify a couple things for me.
1. If a system running MSDE is rebooted in the middle of a transaction, I
am assuming at some point when the system is restarted, the transaction log
file is rolled back into the main database file. However, if a database is
detached before anyone connects to it, will the uncommitted transaction log
data be rolled into the main database file before the detach is performed?
2. When exactly does the uncommitted transaction log data get rolled back
into the database after a reboot in the middle of a transaction?
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
hi Ken,
Ken Varn wrote:
> I need to have someone clarify a couple things for me.
> 1. If a system running MSDE is rebooted in the middle of a
> transaction, I am assuming at some point when the system is
> restarted, the transaction log file is rolled back into the main
> database file. However, if a database is detached before anyone
> connects to it, will the uncommitted transaction log data be rolled
> into the main database file before the detach is performed?
when a system stops (crashes or is unproperly shut down), all the pending
transactions are aborted ..
when the SQL Service restarts, it will stars and recover all databases...
that's to say committed transaction will be rolled forward and uncommitted
rolled back.. the algorithm is quiet complex as SQL Server searches the best
method to perform this task to put the db on line and available as soon as
possible..
so you are not entitled to detach the db before the db has been recovered..
you can perhaps move the physical files before starting SQL Server, but then
the db will be marked as corrupted as SQL Server can no longer find the db
files as registered in it's system tables, so this is not a good idea...
> 2. When exactly does the uncommitted transaction log data get rolled
> back into the database after a reboot in the middle of a transaction?
as soon as the db is recovered... if you open the log file of SQL Server, in
the \Log folder, and inspect one of the ERRORLOG files you'll see something
like
2005-08-11 16:33:22.45 spid8 Starting up database 'msdb'.
2005-08-11 16:33:22.48 spid9 Starting up database 'pubs'.
2005-08-11 16:33:22.49 spid10 Starting up database 'Northwind'.
2005-08-11 16:33:22.51 spid11 Starting up database 'a'.
2005-08-11 16:33:23.17 spid5 Clearing tempdb database.
2005-08-11 16:33:23.21 spid11 Starting up database 'atest'.
2005-08-11 16:33:24.35 spid5 Starting up database 'tempdb'.
2005-08-11 16:33:24.45 spid3 Recovery complete.
....
if something is pending fo a specific database you'll see rollforwad and
rollback operations...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> so you are not entitled to detach the db before the db has been
recovered..
> you can perhaps move the physical files before starting SQL Server, but
then
> the db will be marked as corrupted as SQL Server can no longer find the db
> files as registered in it's system tables, so this is not a good idea...
So that I can clarify what you are saying here. SQL Server will not allow
you to detach a database until it has completed the recovery process?
I basically want to know if it is safe to get rid of the LDF file after a
database has been detached without any adverse ramifications, like database
corruption.
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m3sgtF159l9rU1@.individual.net...
> hi Ken,
> Ken Varn wrote:
> when a system stops (crashes or is unproperly shut down), all the pending
> transactions are aborted ..
> when the SQL Service restarts, it will stars and recover all databases...
> that's to say committed transaction will be rolled forward and uncommitted
> rolled back.. the algorithm is quiet complex as SQL Server searches the
best
> method to perform this task to put the db on line and available as soon as
> possible..
> so you are not entitled to detach the db before the db has been
recovered..
> you can perhaps move the physical files before starting SQL Server, but
then
> the db will be marked as corrupted as SQL Server can no longer find the db
> files as registered in it's system tables, so this is not a good idea...
> as soon as the db is recovered... if you open the log file of SQL Server,
in
> the \Log folder, and inspect one of the ERRORLOG files you'll see
something
> like
> 2005-08-11 16:33:22.45 spid8 Starting up database 'msdb'.
> 2005-08-11 16:33:22.48 spid9 Starting up database 'pubs'.
> 2005-08-11 16:33:22.49 spid10 Starting up database 'Northwind'.
> 2005-08-11 16:33:22.51 spid11 Starting up database 'a'.
> 2005-08-11 16:33:23.17 spid5 Clearing tempdb database.
> 2005-08-11 16:33:23.21 spid11 Starting up database 'atest'.
> 2005-08-11 16:33:24.35 spid5 Starting up database 'tempdb'.
> 2005-08-11 16:33:24.45 spid3 Recovery complete.
> ...
> if something is pending fo a specific database you'll see rollforwad and
> rollback operations...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ken,
Ken Varn wrote:
> So that I can clarify what you are saying here. SQL Server will not
> allow you to detach a database until it has completed the recovery
> process?
until the recovery has been completed, SQL Server does not allow to touch
all dbs at all... it is still "starting"...
> I basically want to know if it is safe to get rid of the LDF file
> after a database has been detached without any adverse ramifications,
> like database corruption.
actually you should not get rid of the LDF file at all, as it is a needed
part of a database... even when the simple recovery model is used you should
always provide all transaction log files... you can use
sp_attach_single_file_db when they are not present but best practice implies
forwarding them too...
say you have a db file named [myDb] with 1 physical data file as
C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
you only provide me the C:\..\myDb.Mdf instructing me to attach it via
sp_attach_single_file_db ...
on my system I already have another company sw which uses a db with the very
same physical data files (and names)...
the corresponding datafile will be C:\..\myDb.Mdf as the log file in
C:\..\myDb.Ldf
as I can not put your db in the same folder I move it to
C:\anotherDir\myDb.Mdf and use sp_attach_single_file_db ... the problem,
now... SQL Server try to create a new t-log file using the available info
(sysfiles and sysfiles1 system table) and will point to the other company
database C:\..\myDb.Ldf physical file...
that log file will be pointed by 2 db and will immediately raises exception
marking 1 of the 2 db as suspect...
to solve this problem I need the t-log of your db and the use of
sp_attach_db indicating all the required physical files..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> actually you should not get rid of the LDF file at all, as it is a needed
> part of a database... even when the simple recovery model is used you
should
> always provide all transaction log files... you can use
> sp_attach_single_file_db when they are not present but best practice
implies
> forwarding them too...
> say you have a db file named [myDb] with 1 physical data file as
> C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
> you only provide me the C:\..\myDb.Mdf instructing me to attach it via
> sp_attach_single_file_db ...
> on my system I already have another company sw which uses a db with the
very
> same physical data files (and names)...
> the corresponding datafile will be C:\..\myDb.Mdf as the log file in
> C:\..\myDb.Ldf
It sounds as if as long as I do not try to attach a database onto a system
that has the same database name I can use sp_attach_single_file_db with no
adverse affects. Or, are there other issues that I should be concerned
with?
Basically, without trying to go into too much detail here, our system has an
automated process that periodically detaches databases, removes the ldf file
and archives the mdf file. The system may re-attach any of these databases
again at a later time depending on certain circumstances. We have been
seeing some database corruption issues and have tried to determine if this
process is the cause. We do know that occasionally the system is restarted
due to power failures (although the UPS is doing an orderly shutdown) and we
want to figure out if our detach and removal of LDF file process could be
the cause of databases getting corrupt after we re-attach them during a
restart from a power fail.
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m444tF155s4gU1@.individual.net...
> hi Ken,
> Ken Varn wrote:
> until the recovery has been completed, SQL Server does not allow to touch
> all dbs at all... it is still "starting"...
>
> actually you should not get rid of the LDF file at all, as it is a needed
> part of a database... even when the simple recovery model is used you
should
> always provide all transaction log files... you can use
> sp_attach_single_file_db when they are not present but best practice
implies
> forwarding them too...
> say you have a db file named [myDb] with 1 physical data file as
> C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
> you only provide me the C:\..\myDb.Mdf instructing me to attach it via
> sp_attach_single_file_db ...
> on my system I already have another company sw which uses a db with the
very
> same physical data files (and names)...
> the corresponding datafile will be C:\..\myDb.Mdf as the log file in
> C:\..\myDb.Ldf
> as I can not put your db in the same folder I move it to
> C:\anotherDir\myDb.Mdf and use sp_attach_single_file_db ... the problem,
> now... SQL Server try to create a new t-log file using the available info
> (sysfiles and sysfiles1 system table) and will point to the other company
> database C:\..\myDb.Ldf physical file...
> that log file will be pointed by 2 db and will immediately raises
exception
> marking 1 of the 2 db as suspect...
> to solve this problem I need the t-log of your db and the use of
> sp_attach_db indicating all the required physical files..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ken,
> It sounds as if as long as I do not try to attach a database onto a
> system that has the same database name I can use
> sp_attach_single_file_db with no adverse affects. Or, are there
> other issues that I should be concerned with?
should not... SQL Server will try to recreate the log file(s) based on the
original info... and if no "problem" is found it should be able to correctly
attach the db..
> Basically, without trying to go into too much detail here, our system
> has an automated process that periodically detaches databases,
> removes the ldf file and archives the mdf file. The system may
> re-attach any of these databases again at a later time depending on
> certain circumstances. We have been seeing some database corruption
> issues and have tried to determine if this process is the cause. We
> do know that occasionally the system is restarted due to power
> failures (although the UPS is doing an orderly shutdown) and we want
> to figure out if our detach and removal of LDF file process could be
> the cause of databases getting corrupt after we re-attach them during
> a restart from a power fail.
missing log files in recovery moment can be a problem... I'd archive the log
files as well, even in simple recovery model..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
1. If a system running MSDE is rebooted in the middle of a transaction, I
am assuming at some point when the system is restarted, the transaction log
file is rolled back into the main database file. However, if a database is
detached before anyone connects to it, will the uncommitted transaction log
data be rolled into the main database file before the detach is performed?
2. When exactly does the uncommitted transaction log data get rolled back
into the database after a reboot in the middle of a transaction?
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
hi Ken,
Ken Varn wrote:
> I need to have someone clarify a couple things for me.
> 1. If a system running MSDE is rebooted in the middle of a
> transaction, I am assuming at some point when the system is
> restarted, the transaction log file is rolled back into the main
> database file. However, if a database is detached before anyone
> connects to it, will the uncommitted transaction log data be rolled
> into the main database file before the detach is performed?
when a system stops (crashes or is unproperly shut down), all the pending
transactions are aborted ..
when the SQL Service restarts, it will stars and recover all databases...
that's to say committed transaction will be rolled forward and uncommitted
rolled back.. the algorithm is quiet complex as SQL Server searches the best
method to perform this task to put the db on line and available as soon as
possible..
so you are not entitled to detach the db before the db has been recovered..
you can perhaps move the physical files before starting SQL Server, but then
the db will be marked as corrupted as SQL Server can no longer find the db
files as registered in it's system tables, so this is not a good idea...
> 2. When exactly does the uncommitted transaction log data get rolled
> back into the database after a reboot in the middle of a transaction?
as soon as the db is recovered... if you open the log file of SQL Server, in
the \Log folder, and inspect one of the ERRORLOG files you'll see something
like
2005-08-11 16:33:22.45 spid8 Starting up database 'msdb'.
2005-08-11 16:33:22.48 spid9 Starting up database 'pubs'.
2005-08-11 16:33:22.49 spid10 Starting up database 'Northwind'.
2005-08-11 16:33:22.51 spid11 Starting up database 'a'.
2005-08-11 16:33:23.17 spid5 Clearing tempdb database.
2005-08-11 16:33:23.21 spid11 Starting up database 'atest'.
2005-08-11 16:33:24.35 spid5 Starting up database 'tempdb'.
2005-08-11 16:33:24.45 spid3 Recovery complete.
....
if something is pending fo a specific database you'll see rollforwad and
rollback operations...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> so you are not entitled to detach the db before the db has been
recovered..
> you can perhaps move the physical files before starting SQL Server, but
then
> the db will be marked as corrupted as SQL Server can no longer find the db
> files as registered in it's system tables, so this is not a good idea...
So that I can clarify what you are saying here. SQL Server will not allow
you to detach a database until it has completed the recovery process?
I basically want to know if it is safe to get rid of the LDF file after a
database has been detached without any adverse ramifications, like database
corruption.
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m3sgtF159l9rU1@.individual.net...
> hi Ken,
> Ken Varn wrote:
> when a system stops (crashes or is unproperly shut down), all the pending
> transactions are aborted ..
> when the SQL Service restarts, it will stars and recover all databases...
> that's to say committed transaction will be rolled forward and uncommitted
> rolled back.. the algorithm is quiet complex as SQL Server searches the
best
> method to perform this task to put the db on line and available as soon as
> possible..
> so you are not entitled to detach the db before the db has been
recovered..
> you can perhaps move the physical files before starting SQL Server, but
then
> the db will be marked as corrupted as SQL Server can no longer find the db
> files as registered in it's system tables, so this is not a good idea...
> as soon as the db is recovered... if you open the log file of SQL Server,
in
> the \Log folder, and inspect one of the ERRORLOG files you'll see
something
> like
> 2005-08-11 16:33:22.45 spid8 Starting up database 'msdb'.
> 2005-08-11 16:33:22.48 spid9 Starting up database 'pubs'.
> 2005-08-11 16:33:22.49 spid10 Starting up database 'Northwind'.
> 2005-08-11 16:33:22.51 spid11 Starting up database 'a'.
> 2005-08-11 16:33:23.17 spid5 Clearing tempdb database.
> 2005-08-11 16:33:23.21 spid11 Starting up database 'atest'.
> 2005-08-11 16:33:24.35 spid5 Starting up database 'tempdb'.
> 2005-08-11 16:33:24.45 spid3 Recovery complete.
> ...
> if something is pending fo a specific database you'll see rollforwad and
> rollback operations...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ken,
Ken Varn wrote:
> So that I can clarify what you are saying here. SQL Server will not
> allow you to detach a database until it has completed the recovery
> process?
until the recovery has been completed, SQL Server does not allow to touch
all dbs at all... it is still "starting"...
> I basically want to know if it is safe to get rid of the LDF file
> after a database has been detached without any adverse ramifications,
> like database corruption.
actually you should not get rid of the LDF file at all, as it is a needed
part of a database... even when the simple recovery model is used you should
always provide all transaction log files... you can use
sp_attach_single_file_db when they are not present but best practice implies
forwarding them too...
say you have a db file named [myDb] with 1 physical data file as
C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
you only provide me the C:\..\myDb.Mdf instructing me to attach it via
sp_attach_single_file_db ...
on my system I already have another company sw which uses a db with the very
same physical data files (and names)...
the corresponding datafile will be C:\..\myDb.Mdf as the log file in
C:\..\myDb.Ldf
as I can not put your db in the same folder I move it to
C:\anotherDir\myDb.Mdf and use sp_attach_single_file_db ... the problem,
now... SQL Server try to create a new t-log file using the available info
(sysfiles and sysfiles1 system table) and will point to the other company
database C:\..\myDb.Ldf physical file...
that log file will be pointed by 2 db and will immediately raises exception
marking 1 of the 2 db as suspect...
to solve this problem I need the t-log of your db and the use of
sp_attach_db indicating all the required physical files..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> actually you should not get rid of the LDF file at all, as it is a needed
> part of a database... even when the simple recovery model is used you
should
> always provide all transaction log files... you can use
> sp_attach_single_file_db when they are not present but best practice
implies
> forwarding them too...
> say you have a db file named [myDb] with 1 physical data file as
> C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
> you only provide me the C:\..\myDb.Mdf instructing me to attach it via
> sp_attach_single_file_db ...
> on my system I already have another company sw which uses a db with the
very
> same physical data files (and names)...
> the corresponding datafile will be C:\..\myDb.Mdf as the log file in
> C:\..\myDb.Ldf
It sounds as if as long as I do not try to attach a database onto a system
that has the same database name I can use sp_attach_single_file_db with no
adverse affects. Or, are there other issues that I should be concerned
with?
Basically, without trying to go into too much detail here, our system has an
automated process that periodically detaches databases, removes the ldf file
and archives the mdf file. The system may re-attach any of these databases
again at a later time depending on certain circumstances. We have been
seeing some database corruption issues and have tried to determine if this
process is the cause. We do know that occasionally the system is restarted
due to power failures (although the UPS is doing an orderly shutdown) and we
want to figure out if our detach and removal of LDF file process could be
the cause of databases getting corrupt after we re-attach them during a
restart from a power fail.
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m444tF155s4gU1@.individual.net...
> hi Ken,
> Ken Varn wrote:
> until the recovery has been completed, SQL Server does not allow to touch
> all dbs at all... it is still "starting"...
>
> actually you should not get rid of the LDF file at all, as it is a needed
> part of a database... even when the simple recovery model is used you
should
> always provide all transaction log files... you can use
> sp_attach_single_file_db when they are not present but best practice
implies
> forwarding them too...
> say you have a db file named [myDb] with 1 physical data file as
> C:\..\myDb.Mdf and a t-log file C:\..\myDb.Ldf...
> you only provide me the C:\..\myDb.Mdf instructing me to attach it via
> sp_attach_single_file_db ...
> on my system I already have another company sw which uses a db with the
very
> same physical data files (and names)...
> the corresponding datafile will be C:\..\myDb.Mdf as the log file in
> C:\..\myDb.Ldf
> as I can not put your db in the same folder I move it to
> C:\anotherDir\myDb.Mdf and use sp_attach_single_file_db ... the problem,
> now... SQL Server try to create a new t-log file using the available info
> (sysfiles and sysfiles1 system table) and will point to the other company
> database C:\..\myDb.Ldf physical file...
> that log file will be pointed by 2 db and will immediately raises
exception
> marking 1 of the 2 db as suspect...
> to solve this problem I need the t-log of your db and the use of
> sp_attach_db indicating all the required physical files..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ken,
> It sounds as if as long as I do not try to attach a database onto a
> system that has the same database name I can use
> sp_attach_single_file_db with no adverse affects. Or, are there
> other issues that I should be concerned with?
should not... SQL Server will try to recreate the log file(s) based on the
original info... and if no "problem" is found it should be able to correctly
attach the db..
> Basically, without trying to go into too much detail here, our system
> has an automated process that periodically detaches databases,
> removes the ldf file and archives the mdf file. The system may
> re-attach any of these databases again at a later time depending on
> certain circumstances. We have been seeing some database corruption
> issues and have tried to determine if this process is the cause. We
> do know that occasionally the system is restarted due to power
> failures (although the UPS is doing an orderly shutdown) and we want
> to figure out if our detach and removal of LDF file process could be
> the cause of databases getting corrupt after we re-attach them during
> a restart from a power fail.
missing log files in recovery moment can be a problem... I'd archive the log
files as well, even in simple recovery model..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Friday, March 9, 2012
Question about Database Collations
I sent a couple of detached databases to a customer. When he installed his SQL Server he got database collation value of Latin1_General_CI_AS however, when he attached my databases they have a collation value of SQL_Latin1_General_CPI_CI_AS. |||
Now my stored procedures that use temp files are failing and a message is coming out SQL Server "Cannot resolve collation conflict for equal to operation"
I am assuming that the temp database has one collation value and my databases has another.
Q. Can anyone tell me the difference between the two collations? He is in Australia, I am in Canada - would he get a different default than I do?
Q. Can I safely alter the collation sequence of either the databases I sent or the temp database so that they match?
I have never run across this before but this is the first time I shipped the database offshore.
Thanks for any help you can give me. I am going to have to send my database to the Caribbean pretty soon and I need to know if this is going to happen there as well.
Well now that I've solved the problem I wonder what is the reasoning behind the defaults chosen by install.
First I can find no provision in the SQL Install to change the Default collation value.
My customer in Australia installed SQL Server on a new installation of Windows Server 2003 and got Latin1_General_CI_AS.
I built a brand new instance of Windows Server 2003 and when I installed SQL I got SQL_Latin1_General_CP1_CI_AS
The problem with my databases was in the stored procedures that used temporary tables - the collation values were different in the two databases and any comparisons on character fields failed.
I can work around the problem by altering all the stored procedures to include the collation clause to override the collation of tempdb. Is this the best way? I'm not sure. Is it a good practice to always include the collation clause when defining a table? I've always looked at it as kind of an annoyance -- never again
|||
I am replying to this fairly old post because others may be wondering why they get the SQL_Latin1_General_CP1_CI_AS collation.
I believe that this is the 'old fashioned' collation used by versions of SQL Server prior to 2000. It seems to be offered on upgrade, whereas Latin1_General_CI_AS is used in from scratch installations. Certainly the latter is preferred.
I'm disappointed that SQL2005 doesn't have a change collation wizard and you need to create a DMO script in order to change the collation of existing databases.
|||did you know which were the installation parameters you choose to get SQL_Latin1_General_CP1_CI_AS?
thanks for your help
Andres,
Upon installation, you need to select "Collation designator and sort order:"
Set it to "Latin1_General"
then, check the box for "Accent - sensitive"
That should do it!
Question about Database Collations
I sent a couple of detached databases to a customer. When he installed his SQL Server he got database collation value of Latin1_General_CI_AS however, when he attached my databases they have a collation value of SQL_Latin1_General_CPI_CI_AS. |||
Now my stored procedures that use temp files are failing and a message is coming out SQL Server "Cannot resolve collation conflict for equal to operation"
I am assuming that the temp database has one collation value and my databases has another.
Q. Can anyone tell me the difference between the two collations? He is in Australia, I am in Canada - would he get a different default than I do?
Q. Can I safely alter the collation sequence of either the databases I sent or the temp database so that they match?
I have never run across this before but this is the first time I shipped the database offshore.
Thanks for any help you can give me. I am going to have to send my database to the Caribbean pretty soon and I need to know if this is going to happen there as well.
Well now that I've solved the problem I wonder what is the reasoning behind the defaults chosen by install.
First I can find no provision in the SQL Install to change the Default collation value.
My customer in Australia installed SQL Server on a new installation of Windows Server 2003 and got Latin1_General_CI_AS.
I built a brand new instance of Windows Server 2003 and when I installed SQL I got SQL_Latin1_General_CP1_CI_AS
The problem with my databases was in the stored procedures that used temporary tables - the collation values were different in the two databases and any comparisons on character fields failed.
I can work around the problem by altering all the stored procedures to include the collation clause to override the collation of tempdb. Is this the best way? I'm not sure. Is it a good practice to always include the collation clause when defining a table? I've always looked at it as kind of an annoyance -- never again
|||
I am replying to this fairly old post because others may be wondering why they get the SQL_Latin1_General_CP1_CI_AS collation.
I believe that this is the 'old fashioned' collation used by versions of SQL Server prior to 2000. It seems to be offered on upgrade, whereas Latin1_General_CI_AS is used in from scratch installations. Certainly the latter is preferred.
I'm disappointed that SQL2005 doesn't have a change collation wizard and you need to create a DMO script in order to change the collation of existing databases.
|||did you know which were the installation parameters you choose to get SQL_Latin1_General_CP1_CI_AS?
thanks for your help
Andres,
Upon installation, you need to select "Collation designator and sort order:"
Set it to "Latin1_General"
then, check the box for "Accent - sensitive"
That should do it!
Question about data access to SQL Compact DB / Server
I am used to work with relational SQL Server databases in other editions and am fairly new to SQL compact. I have installed the compact edition and successfully opened a connection and worked with DB objects in the Compact edition, using Server Management Studio.
I have 2-3 questions:
- Are there any means / drivers / data access installations that would allow me to connect to a SQL Compact database using the data access means that I am used to in other editions of SQL Server? In other words, can I install a data-access "converter" that would know how to talk to the SQL Compact databases over the usual TCP/1434 protocol (or any other port)? I am looking for this feature in order to connect to the SQL Compact the same way I connect to any other relational database. Right now, I am using Toad for SQL Server and it would be nice if I could connect to a SQL Compact.
- With other (non-Compact) editions, I can create an alias to a SQL Server and then specify the means to connect to the server. Can I create an alias to a SQL Compact database/server? I tried but unsuccessfully. This relates to my previous question- If I could create an alias, then I could (I imagine) connect to the Compact databases/server the same way I connect to any relational database using the alias.
- Are there any tutorials for people who are used to working with other (non-Compact) editions that are starting to work with Compact? Any BOL sections that explain the differences between other editions and Compact, from a management-tools/connectivity standpoint? I found some literature; wondering if there's something more detailed out there.
Thank you in advance.
Please be aware that SQL Server CE is a completely different beast from SQL Server. It is a in-process, embedded database, for use by a single user/application locally, and has options for synchronising with the full SQL Server product. Therefore, you caanot connect to SQL SErver CE over the network, but must load the engine, and open the SDF files of SQL CE directly from within your tool/app. A good starting point for SQL CE is in my opinion the book by Bill Vaughn, which you can get from here: http://www.hitchhikerguides.net/EBooks/default.aspx|||Erik,
Thank you for your reply.
Theoretically speaking, I imagine that I could load the engine over 1434 TCP. Do you know whether there are any such providers that would translate between the loading of the engine to the standard communication protocol?
I will look at the link you provided. Thank you again.
|||Revising my original question a little- is there something equivalent to the SMO that allows one to interface with SQL Compact?
|||No, you must use SQL Server Management Studio Express SP2 (free) or Visual Studio 2005 to manage SQL CE files.
Monday, February 20, 2012
Question
Does anyone know why:
i have an application running and it depends on my sql
databases
when i execute a query in QA, it runs smoothly with no
problems.
when i execute the same query using sql data tools
(enterprise manager -> open database -> choose table right
click and choose query -> execute query there) it causes
locks with the queries executed by the application...
any ideas?EM uses cursors and stuff in order to present the result in the grid. EM was
never designed to be an end-user data entry tool. I suggest you do your
query test in QA and let the users modify the data using your application.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Claudy" <anonymous@.discussions.microsoft.com> wrote in message
news:507001c3ffa5$55611ba0$a101280a@.phx.gbl...
> Does anyone know why:
> i have an application running and it depends on my sql
> databases
> when i execute a query in QA, it runs smoothly with no
> problems.
> when i execute the same query using sql data tools
> (enterprise manager -> open database -> choose table right
> click and choose query -> execute query there) it causes
> locks with the queries executed by the application...
> any ideas?
i have an application running and it depends on my sql
databases
when i execute a query in QA, it runs smoothly with no
problems.
when i execute the same query using sql data tools
(enterprise manager -> open database -> choose table right
click and choose query -> execute query there) it causes
locks with the queries executed by the application...
any ideas?EM uses cursors and stuff in order to present the result in the grid. EM was
never designed to be an end-user data entry tool. I suggest you do your
query test in QA and let the users modify the data using your application.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Claudy" <anonymous@.discussions.microsoft.com> wrote in message
news:507001c3ffa5$55611ba0$a101280a@.phx.gbl...
> Does anyone know why:
> i have an application running and it depends on my sql
> databases
> when i execute a query in QA, it runs smoothly with no
> problems.
> when i execute the same query using sql data tools
> (enterprise manager -> open database -> choose table right
> click and choose query -> execute query there) it causes
> locks with the queries executed by the application...
> any ideas?
Question
Does anyone know if it is possible (and if it is how) to
hide system databases in EM?
Thanxs!Yes, Right click your server in SEM , and select Edit Registration
properties.
"?" <anonymous@.discussions.microsoft.com> wrote in message
news:aa9501c3ebe2$55f80640$a401280a@.phx.gbl...
> Does anyone know if it is possible (and if it is how) to
> hide system databases in EM?
> Thanxs!
hide system databases in EM?
Thanxs!Yes, Right click your server in SEM , and select Edit Registration
properties.
"?" <anonymous@.discussions.microsoft.com> wrote in message
news:aa9501c3ebe2$55f80640$a401280a@.phx.gbl...
> Does anyone know if it is possible (and if it is how) to
> hide system databases in EM?
> Thanxs!
Question
Does anyone know if it is possible (and if it is how) to
hide system databases in EM?
Thanxs!Yes, Right click your server in SEM , and select Edit Registration
properties.
"?" <anonymous@.discussions.microsoft.com> wrote in message
news:aa9501c3ebe2$55f80640$a401280a@.phx.gbl...
hide system databases in EM?
Thanxs!Yes, Right click your server in SEM , and select Edit Registration
properties.
"?" <anonymous@.discussions.microsoft.com> wrote in message
news:aa9501c3ebe2$55f80640$a401280a@.phx.gbl...
quote:
> Does anyone know if it is possible (and if it is how) to
> hide system databases in EM?
> Thanxs!
Subscribe to:
Posts (Atom)