Hi,
We have the need to roll the time of the database server forward to perform
some time sensitive testing. The problem is that the database test server
is part of a production Windows 2000 environment. I suppose the optimal
thing to do would be to move the clock on the server forward X number of
hours, and then test our procedures. The problem is that Windows 2000 will
automatically sync up the time because of the Kerberos security. We can't
change the time on all of the servers. Also, when our SQL queries are
retrieving the date/time, it calls GetDate().
Does anybody have any ideas of what we could do to simulate time? I know
one quick and easy answer is to set up a completely separate environment
perhaps with one server. We could put Win2k and SQL Server on that box. It
would be its own domain, so we coould play with the time however we want. I
was wondering if there was a better way of doing this.
Thanks in advance,
cjPull the server out of domain and see if it works. You may need to
reconfigure the service account
--
Thanks
Ravi
"Curtis Justus" wrote:
> Hi,
> We have the need to roll the time of the database server forward to perform
> some time sensitive testing. The problem is that the database test server
> is part of a production Windows 2000 environment. I suppose the optimal
> thing to do would be to move the clock on the server forward X number of
> hours, and then test our procedures. The problem is that Windows 2000 will
> automatically sync up the time because of the Kerberos security. We can't
> change the time on all of the servers. Also, when our SQL queries are
> retrieving the date/time, it calls GetDate().
> Does anybody have any ideas of what we could do to simulate time? I know
> one quick and easy answer is to set up a completely separate environment
> perhaps with one server. We could put Win2k and SQL Server on that box. It
> would be its own domain, so we coould play with the time however we want. I
> was wondering if there was a better way of doing this.
> Thanks in advance,
> cj
>
>|||Firstly and most importantly, why would you even consider doing this kind of
test on a production server?
I generally make it a rule to avoid writing time-sensitive code precisely
because of the obvious testing problems. If you need to reference the
current date and time then parameterize it or make your own class or
function to retrieve the clock information. That way you have a single point
at which you can interpose your own time value for testing purposes.
--
David Portas
SQL Server MVP
--|||Ravi,
That is what I figured I would have to do. Thanks for the confirmation.
Take care,
cj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:D4301F76-89AD-4554-9B8F-33EE8C686284@.microsoft.com...
> Pull the server out of domain and see if it works. You may need to
> reconfigure the service account
> --
> Thanks
> Ravi
>
> "Curtis Justus" wrote:
>> Hi,
>> We have the need to roll the time of the database server forward to
>> perform
>> some time sensitive testing. The problem is that the database test
>> server
>> is part of a production Windows 2000 environment. I suppose the optimal
>> thing to do would be to move the clock on the server forward X number of
>> hours, and then test our procedures. The problem is that Windows 2000
>> will
>> automatically sync up the time because of the Kerberos security. We
>> can't
>> change the time on all of the servers. Also, when our SQL queries are
>> retrieving the date/time, it calls GetDate().
>> Does anybody have any ideas of what we could do to simulate time? I know
>> one quick and easy answer is to set up a completely separate environment
>> perhaps with one server. We could put Win2k and SQL Server on that box.
>> It
>> would be its own domain, so we coould play with the time however we want.
>> I
>> was wondering if there was a better way of doing this.
>> Thanks in advance,
>> cj
>>|||David,
To answer your first question: there aren't any other production databases
on this "production" server. The only reason why I called it a production
server is because it is located in an active domain. They are converting
from a Netware network to the Win2K-based system and are slowly
transitioning over. I'm sorry for not sharing that information.
The date thing was something I had asked our database people about. However
it is too late in the game to change everything.
With that said, do you have any other suggestions? Perhaps going through
our stored procs and replacing GetDate with a call to a UDF might do the
trick.
Thanks,
cj
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:WpKdnYLmt7VwlV7fRVn-rQ@.giganews.com...
> Firstly and most importantly, why would you even consider doing this kind
> of test on a production server?
> I generally make it a rule to avoid writing time-sensitive code precisely
> because of the obvious testing problems. If you need to reference the
> current date and time then parameterize it or make your own class or
> function to retrieve the clock information. That way you have a single
> point at which you can interpose your own time value for testing purposes.
> --
> David Portas
> SQL Server MVP
> --
>|||"Curtis Justus" <sure@.you.wont.spam.me.org> wrote in
news:uNp4NMSfFHA.2472@.TK2MSFTNGP15.phx.gbl:
> With that said, do you have any other suggestions? Perhaps going
> through our stored procs and replacing GetDate with a call to a UDF
> might do the trick.
Look out for CURRENT_TIMESTAMP as well.
Here's two crazy ideas:
- find or write a utility that traps all calls to the Windows API that get
the current time, returning a strange result. If necessary, have MSSQL.EXE
be spawned by the utility (instead of the normal service start).
- set the timezone offset to several hundred hours.
Showing posts with label perform. Show all posts
Showing posts with label perform. Show all posts
Monday, March 19, 2012
Best Practice for SQL Server Null Values / Empty Strings
You are totally right in your reseaech of null fields, did
you know that if you perform a string concatination with a
null it will always result in a null i.e
@.Forename = 'Denise'
@.Middlename = null
@.Surname = 'Smith'
Set @.Fullname = @.Forename + ' ' + @.Middlename + ' ' +
@.Surname
Will mean @.Fullname will be null.
However Nulls can also be useful i.e looking for NOT NULL,
and see the COALESCE statement, it really up to you.
If you want to get rid of null then you can use defaults.
The default will change a null to anything you want it to
be i.e '' or empty string.
To create a default
1. In EA go to the database
2. Select Defualts
3. Right click - select new defaults
4. Give it a name such as 'EmptyString'
Then when you create a column you can assign it the
default. This however will only work for new records and
not for existing ones.
J
>--Original Message--
>I'm fairly new to SQL Server. Coming from Acces, I see
that Null values are handled differently. I've read many
of the posts on querying Null values, but I want to know
what is the best practice for designing a new system (SQL
Server 2000) that could contain empty fields.
>For example, suppose I have a 'Phone' field that is
often, but not always, filled in. If the user blanks out
a phone number, the .NET DataAdapter .Update method will
save the field as an empty string instead of a NULL. This
of course makes every query more complex having to check
for both nulls and empty strings.
>Is there any practical way to prevent, at the database
level, the empty strings from getting into the database?
(Perhaps triggers or some global setting?) Or should the
string fields be empty strings and never nulls...? I
could re-write the data adapter, but I don't know if I can
trust that every program that touches the database will
have handled the issue correctly.
>Any opionions?
>Thanks,
>Denise
>
>using VB.Net and ADO.Net code and if the user blanks out
a field, ADO.Net by default it sometimes saves them as an
empty string
>.
>Julie,
(just to point out that the first part of your response is not always
necessarily the case
SET CONCAT_NULL_YIELDS_NULL ON
select null + 'hello'
SET CONCAT_NULL_YIELDS_NULL OFF
select null + 'hello'
Regards,
Paul Ibison
you know that if you perform a string concatination with a
null it will always result in a null i.e
@.Forename = 'Denise'
@.Middlename = null
@.Surname = 'Smith'
Set @.Fullname = @.Forename + ' ' + @.Middlename + ' ' +
@.Surname
Will mean @.Fullname will be null.
However Nulls can also be useful i.e looking for NOT NULL,
and see the COALESCE statement, it really up to you.
If you want to get rid of null then you can use defaults.
The default will change a null to anything you want it to
be i.e '' or empty string.
To create a default
1. In EA go to the database
2. Select Defualts
3. Right click - select new defaults
4. Give it a name such as 'EmptyString'
Then when you create a column you can assign it the
default. This however will only work for new records and
not for existing ones.
J
>--Original Message--
>I'm fairly new to SQL Server. Coming from Acces, I see
that Null values are handled differently. I've read many
of the posts on querying Null values, but I want to know
what is the best practice for designing a new system (SQL
Server 2000) that could contain empty fields.
>For example, suppose I have a 'Phone' field that is
often, but not always, filled in. If the user blanks out
a phone number, the .NET DataAdapter .Update method will
save the field as an empty string instead of a NULL. This
of course makes every query more complex having to check
for both nulls and empty strings.
>Is there any practical way to prevent, at the database
level, the empty strings from getting into the database?
(Perhaps triggers or some global setting?) Or should the
string fields be empty strings and never nulls...? I
could re-write the data adapter, but I don't know if I can
trust that every program that touches the database will
have handled the issue correctly.
>Any opionions?
>Thanks,
>Denise
>
>using VB.Net and ADO.Net code and if the user blanks out
a field, ADO.Net by default it sometimes saves them as an
empty string
>.
>Julie,
(just to point out that the first part of your response is not always
necessarily the case
SET CONCAT_NULL_YIELDS_NULL ON
select null + 'hello'
SET CONCAT_NULL_YIELDS_NULL OFF
select null + 'hello'
Regards,
Paul Ibison
Sunday, March 11, 2012
Best Practice for SQL Server Null Values / Empty Strings
You are totally right in your reseaech of null fields, did
you know that if you perform a string concatination with a
null it will always result in a null i.e
@.Forename = 'Denise'
@.Middlename = null
@.Surname = 'Smith'
Set @.Fullname = @.Forename + ' ' + @.Middlename + ' ' +
@.Surname
Will mean @.Fullname will be null.
However Nulls can also be useful i.e looking for NOT NULL,
and see the COALESCE statement, it really up to you.
If you want to get rid of null then you can use defaults.
The default will change a null to anything you want it to
be i.e '' or empty string.
To create a default
1. In EA go to the database
2. Select Defualts
3. Right click - select new defaults
4. Give it a name such as 'EmptyString'
Then when you create a column you can assign it the
default. This however will only work for new records and
not for existing ones.
J
>--Original Message--
>I'm fairly new to SQL Server. Coming from Acces, I see
that Null values are handled differently. I've read many
of the posts on querying Null values, but I want to know
what is the best practice for designing a new system (SQL
Server 2000) that could contain empty fields.
>For example, suppose I have a 'Phone' field that is
often, but not always, filled in. If the user blanks out
a phone number, the .NET DataAdapter .Update method will
save the field as an empty string instead of a NULL. This
of course makes every query more complex having to check
for both nulls and empty strings.
>Is there any practical way to prevent, at the database
level, the empty strings from getting into the database?
(Perhaps triggers or some global setting?) Or should the
string fields be empty strings and never nulls...? I
could re-write the data adapter, but I don't know if I can
trust that every program that touches the database will
have handled the issue correctly.
>Any opionions?
>Thanks,
>Denise
>
>using VB.Net and ADO.Net code and if the user blanks out
a field, ADO.Net by default it sometimes saves them as an
empty string
>.
>
Julie,
(just to point out that the first part of your response is not always
necessarily the case
SET CONCAT_NULL_YIELDS_NULL ON
select null + 'hello'
SET CONCAT_NULL_YIELDS_NULL OFF
select null + 'hello'
Regards,
Paul Ibison
you know that if you perform a string concatination with a
null it will always result in a null i.e
@.Forename = 'Denise'
@.Middlename = null
@.Surname = 'Smith'
Set @.Fullname = @.Forename + ' ' + @.Middlename + ' ' +
@.Surname
Will mean @.Fullname will be null.
However Nulls can also be useful i.e looking for NOT NULL,
and see the COALESCE statement, it really up to you.
If you want to get rid of null then you can use defaults.
The default will change a null to anything you want it to
be i.e '' or empty string.
To create a default
1. In EA go to the database
2. Select Defualts
3. Right click - select new defaults
4. Give it a name such as 'EmptyString'
Then when you create a column you can assign it the
default. This however will only work for new records and
not for existing ones.
J
>--Original Message--
>I'm fairly new to SQL Server. Coming from Acces, I see
that Null values are handled differently. I've read many
of the posts on querying Null values, but I want to know
what is the best practice for designing a new system (SQL
Server 2000) that could contain empty fields.
>For example, suppose I have a 'Phone' field that is
often, but not always, filled in. If the user blanks out
a phone number, the .NET DataAdapter .Update method will
save the field as an empty string instead of a NULL. This
of course makes every query more complex having to check
for both nulls and empty strings.
>Is there any practical way to prevent, at the database
level, the empty strings from getting into the database?
(Perhaps triggers or some global setting?) Or should the
string fields be empty strings and never nulls...? I
could re-write the data adapter, but I don't know if I can
trust that every program that touches the database will
have handled the issue correctly.
>Any opionions?
>Thanks,
>Denise
>
>using VB.Net and ADO.Net code and if the user blanks out
a field, ADO.Net by default it sometimes saves them as an
empty string
>.
>
Julie,
(just to point out that the first part of your response is not always
necessarily the case
SET CONCAT_NULL_YIELDS_NULL ON
select null + 'hello'
SET CONCAT_NULL_YIELDS_NULL OFF
select null + 'hello'
Regards,
Paul Ibison
Wednesday, March 7, 2012
Best MSSQL management tools
Hi everyone.
I manage 100+ databases spread across the country. When it comes to perform an update or offsite backup, it is a nightmare. I have to repeat the same code and the same process for 100+ times.
Is there any tools other than MS Enterprise manager we can use to perform this type of maintenance works in a batch manner? For example, we place a update sql file within the program, and the program does the rest (of course, we predefine the login credential for each databases)
Cheers~
gmefmax:angel:You can use the osql command line tool for SQL Server 2000 or the sqlcmd command line tool for SQL Server 2005.
Example:
osql -S myservername -d myDBname -U myuser -P mypwd -Q "backup database myDBname to disk='g:\backup\myDBname070821.bak'|||You could also write your own. Essentially a wrapper for osql that would allow you to select which instances and which scripts to apply (and also in which order).
It's not impossible, but I admit that I'm not up to the challenge right now.
Regards,
hmscott|||I talked to few guys and it seems I have write my own software in order to do this type of work. I just wondering how other people in the industry manage large number of databases across the country. Any Idea?|||I don't really manage servers, but when I do need to execute the same script against many different server/databases, I use sqlcmd combined with some batch files that make use of the FOR keyword, looping over the values in a .txt file, calling sqlcmd for each.
basically you specify the server/db/credentials in an external .txt file and then loop over each value using FOR.|||Thanks Jezemine. I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?|||I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?
Then use osql.exe instead.|||I use sqlmaint.exe wrapped in a .cmd file|||I prefer DBArtisan for Oracle and SQL Server, just costs a boatload of $$$.|||I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.|||I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.
I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.
Anybody use PowerShell yet for scripting ?|||Any Tutorials site you recommend?
It sounds bit advanced to me?
Thanks again guys.|||I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.
Anybody use PowerShell yet for scripting ?
I keep meaning to get into powershell, but haven't yet.
ps combined with SMO would be a nice combo:
http://www.google.com/search?q=smo+powershell|||Any Tutorials site you recommend?
It sounds bit advanced to me?
Thanks again guys.
I have a book called "Windows NT Shell Scripting" by Tim Hill published by New Riders, it's a bit old (1998) but it has very good examples that come in handy.
I manage 100+ databases spread across the country. When it comes to perform an update or offsite backup, it is a nightmare. I have to repeat the same code and the same process for 100+ times.
Is there any tools other than MS Enterprise manager we can use to perform this type of maintenance works in a batch manner? For example, we place a update sql file within the program, and the program does the rest (of course, we predefine the login credential for each databases)
Cheers~
gmefmax:angel:You can use the osql command line tool for SQL Server 2000 or the sqlcmd command line tool for SQL Server 2005.
Example:
osql -S myservername -d myDBname -U myuser -P mypwd -Q "backup database myDBname to disk='g:\backup\myDBname070821.bak'|||You could also write your own. Essentially a wrapper for osql that would allow you to select which instances and which scripts to apply (and also in which order).
It's not impossible, but I admit that I'm not up to the challenge right now.
Regards,
hmscott|||I talked to few guys and it seems I have write my own software in order to do this type of work. I just wondering how other people in the industry manage large number of databases across the country. Any Idea?|||I don't really manage servers, but when I do need to execute the same script against many different server/databases, I use sqlcmd combined with some batch files that make use of the FOR keyword, looping over the values in a .txt file, calling sqlcmd for each.
basically you specify the server/db/credentials in an external .txt file and then loop over each value using FOR.|||Thanks Jezemine. I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?|||I tried it and only works for SQL2005, a lot of databases I manage are still using version 7 (YES! It still using SQL7.) Any other ideas?
Then use osql.exe instead.|||I use sqlmaint.exe wrapped in a .cmd file|||I prefer DBArtisan for Oracle and SQL Server, just costs a boatload of $$$.|||I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.|||I actually wrote a script which reads from a table, all the server names\instances, and then loop through each server\instace, log into each one (with a service acocunt), and run whatever command you want.
I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.
Anybody use PowerShell yet for scripting ?|||Any Tutorials site you recommend?
It sounds bit advanced to me?
Thanks again guys.|||I've pretty much been able to do with DOS cmd scripting what I did with UNIX kshell, a bit more clumsy though.
Anybody use PowerShell yet for scripting ?
I keep meaning to get into powershell, but haven't yet.
ps combined with SMO would be a nice combo:
http://www.google.com/search?q=smo+powershell|||Any Tutorials site you recommend?
It sounds bit advanced to me?
Thanks again guys.
I have a book called "Windows NT Shell Scripting" by Tim Hill published by New Riders, it's a bit old (1998) but it has very good examples that come in handy.
Sunday, February 19, 2012
Benchmarking Tools
Hello
Does anyone know of any benchmarking tools to perform load testing and
performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
64-bit?
I was looking at Benchmark Factory from Quest Software but it does not
seem to work with 64-bit.
Thanks
Sameer
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
You should verify with Quest to determine whether that's the case. My
understanding is that it's a client app, and you don't have to run it on the
server itself (shouldn't run it on server as a matter of fact). You can
always run it on a 32-bit machine adn access a remote SQL instance. So I'd be
surprised if it doesn't work with an x64 SQL2005 instance.
Linchi
"Sameer" wrote:
> Hello
> Does anyone know of any benchmarking tools to perform load testing and
> performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
> 64-bit?
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
> Thanks
> Sameer
>
Does anyone know of any benchmarking tools to perform load testing and
performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
64-bit?
I was looking at Benchmark Factory from Quest Software but it does not
seem to work with 64-bit.
Thanks
Sameer
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
You should verify with Quest to determine whether that's the case. My
understanding is that it's a client app, and you don't have to run it on the
server itself (shouldn't run it on server as a matter of fact). You can
always run it on a 32-bit machine adn access a remote SQL instance. So I'd be
surprised if it doesn't work with an x64 SQL2005 instance.
Linchi
"Sameer" wrote:
> Hello
> Does anyone know of any benchmarking tools to perform load testing and
> performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
> 64-bit?
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
> Thanks
> Sameer
>
Benchmarking Tools
Hello
Does anyone know of any benchmarking tools to perform load testing and
performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
64-bit?
I was looking at Benchmark Factory from Quest Software but it does not
seem to work with 64-bit.
Thanks
Sameer
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
You should verify with Quest to determine whether that's the case. My
understanding is that it's a client app, and you don't have to run it on the
server itself (shouldn't run it on server as a matter of fact). You can
always run it on a 32-bit machine adn access a remote SQL instance. So I'd be
surprised if it doesn't work with an x64 SQL2005 instance.
Linchi
"Sameer" wrote:
> Hello
> Does anyone know of any benchmarking tools to perform load testing and
> performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
> 64-bit?
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
> Thanks
> Sameer
>
Does anyone know of any benchmarking tools to perform load testing and
performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
64-bit?
I was looking at Benchmark Factory from Quest Software but it does not
seem to work with 64-bit.
Thanks
Sameer
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
You should verify with Quest to determine whether that's the case. My
understanding is that it's a client app, and you don't have to run it on the
server itself (shouldn't run it on server as a matter of fact). You can
always run it on a 32-bit machine adn access a remote SQL instance. So I'd be
surprised if it doesn't work with an x64 SQL2005 instance.
Linchi
"Sameer" wrote:
> Hello
> Does anyone know of any benchmarking tools to perform load testing and
> performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
> 64-bit?
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
> Thanks
> Sameer
>
Benchmarking Tools
Hello
Does anyone know of any benchmarking tools to perform load testing and
performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
64-bit?
I was looking at Benchmark Factory from Quest Software but it does not
seem to work with 64-bit.
Thanks
Sameer> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
You should verify with Quest to determine whether that's the case. My
understanding is that it's a client app, and you don't have to run it on the
server itself (shouldn't run it on server as a matter of fact). You can
always run it on a 32-bit machine adn access a remote SQL instance. So I'd b
e
surprised if it doesn't work with an x64 SQL2005 instance.
Linchi
"Sameer" wrote:
> Hello
> Does anyone know of any benchmarking tools to perform load testing and
> performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
> 64-bit?
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
> Thanks
> Sameer
>
Does anyone know of any benchmarking tools to perform load testing and
performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
64-bit?
I was looking at Benchmark Factory from Quest Software but it does not
seem to work with 64-bit.
Thanks
Sameer> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
You should verify with Quest to determine whether that's the case. My
understanding is that it's a client app, and you don't have to run it on the
server itself (shouldn't run it on server as a matter of fact). You can
always run it on a 32-bit machine adn access a remote SQL instance. So I'd b
e
surprised if it doesn't work with an x64 SQL2005 instance.
Linchi
"Sameer" wrote:
> Hello
> Does anyone know of any benchmarking tools to perform load testing and
> performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
> 64-bit?
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
> Thanks
> Sameer
>
Friday, February 10, 2012
begin and end transaction and transaction log
Hello everyone,
This is more of an architectural question about SQL Server. Can
someone please explain why when I perform a query such as the one
below that updates a table using begin and end transaction I am unable
to programmatically truncate the transaction log. The only way I have
found to truncate the transaction log is to stop and start the SQL
Server Service. Does this transaction use the tempdb? Is that why I
am unable to truncate the transaction log? Is there a better way to
do this?
Why would you truncate the transaction log in the first place?
>
>
>
>
Why would you truncate the transaction log in the first place?
>
If you run with full recovery and want to be table to restore to a point
in time, the you should backup your transaction log regularly.
>
If you don't care about the point-in-time restores but are content with
restoring from a full backup in case of a failure, you should set the
database in simple recovery.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -|||"Kruton" <wmlyerly@.gmail.comwrote in message
news:a8d08495-59a1-4090-8906-2a9ff8b01945@.o42g2000hsc.googlegroups.com...
Then your DBA needs to set the DBA to simple recovery.
>>
>>
>>
>>
>Why would you truncate the transaction log in the first place?
>>
>If you run with full recovery and want to be table to restore to a point
>in time, the you should backup your transaction log regularly.
>>
>If you don't care about the point-in-time restores but are content with
>restoring from a full backup in case of a failure, you should set the
>database in simple recovery.
>>
>--
>Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>>
>Books Online for SQL Server 2005
>athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>Books Online for SQL Server 2000
>athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide
>quoted text -
>>
>- Show quoted text -
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Kruton (wmlyerly@.gmail.com) writes:
Then you need simple recovery. What I failed to say is that with simple
recovery, SQL Server will regularly truncate the transaction log, and thus
keep it in check. The one thing to keep in mind is that truncation never
goes past the open transaction, so if you have a long-running transaction
the log can grow never the less.
>
Then you need simple recovery. What I failed to say is that with simple
recovery, SQL Server will regularly truncate the transaction log, and thus
keep it in check. The one thing to keep in mind is that truncation never
goes past the open transaction, so if you have a long-running transaction
the log can grow never the less.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
This is more of an architectural question about SQL Server. Can
someone please explain why when I perform a query such as the one
below that updates a table using begin and end transaction I am unable
to programmatically truncate the transaction log. The only way I have
found to truncate the transaction log is to stop and start the SQL
Server Service. Does this transaction use the tempdb? Is that why I
am unable to truncate the transaction log? Is there a better way to
do this?
Begin trans T1
Update sometable
Set random_row = 'blah'
End trans T1
Thanks!Kruton (wmlyerly@.gmail.com) writes:
Quote:
Originally Posted by
This is more of an architectural question about SQL Server. Can
someone please explain why when I perform a query such as the one
below that updates a table using begin and end transaction I am unable
to programmatically truncate the transaction log. The only way I have
found to truncate the transaction log is to stop and start the SQL
Server Service. Does this transaction use the tempdb? Is that why I
am unable to truncate the transaction log? Is there a better way to
do this?
>
Begin trans T1
>
Update sometable
Set random_row = 'blah'
>
End trans T1
Why would you truncate the transaction log in the first place?
If you run with full recovery and want to be table to restore to a point
in time, the you should backup your transaction log regularly.
If you don't care about the point-in-time restores but are content with
restoring from a full backup in case of a failure, you should set the
database in simple recovery.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erlang,
This is part of a large OLAP process that runs many times a day. I do
not want to / need to restore to a particular time. I have a dba that
does full backups on a regular basis. I would agree with you to a
certain extent if this were OLTP but it is not.
Thanks.
On Dec 12, 2:18 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
Kruton (wmlye...@.gmail.com) writes:
Quote:
Originally Posted by
This is more of an architectural question about SQL Server. Can
someone please explain why when I perform a query such as the one
below that updates a table using begin and end transaction I am unable
to programmatically truncate the transaction log. The only way I have
found to truncate the transaction log is to stop and start the SQL
Server Service. Does this transaction use the tempdb? Is that why I
am unable to truncate the transaction log? Is there a better way to
do this?
>
Quote:
Originally Posted by
Begin trans T1
>
Quote:
Originally Posted by
Update sometable
Set random_row = 'blah'
>
Quote:
Originally Posted by
End trans T1
>
Why would you truncate the transaction log in the first place?
>
If you run with full recovery and want to be table to restore to a point
in time, the you should backup your transaction log regularly.
>
If you don't care about the point-in-time restores but are content with
restoring from a full backup in case of a failure, you should set the
database in simple recovery.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -|||"Kruton" <wmlyerly@.gmail.comwrote in message
news:a8d08495-59a1-4090-8906-2a9ff8b01945@.o42g2000hsc.googlegroups.com...
Quote:
Originally Posted by
Hi Erlang,
This is part of a large OLAP process that runs many times a day. I do
not want to / need to restore to a particular time. I have a dba that
does full backups on a regular basis. I would agree with you to a
certain extent if this were OLTP but it is not.
Then your DBA needs to set the DBA to simple recovery.
Quote:
Originally Posted by
>
Thanks.
>
On Dec 12, 2:18 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
>Kruton (wmlye...@.gmail.com) writes:
Quote:
Originally Posted by
This is more of an architectural question about SQL Server. Can
someone please explain why when I perform a query such as the one
below that updates a table using begin and end transaction I am unable
to programmatically truncate the transaction log. The only way I have
found to truncate the transaction log is to stop and start the SQL
Server Service. Does this transaction use the tempdb? Is that why I
am unable to truncate the transaction log? Is there a better way to
do this?
>>
Quote:
Originally Posted by
Begin trans T1
>>
Quote:
Originally Posted by
Update sometable
Set random_row = 'blah'
>>
Quote:
Originally Posted by
End trans T1
>>
>Why would you truncate the transaction log in the first place?
>>
>If you run with full recovery and want to be table to restore to a point
>in time, the you should backup your transaction log regularly.
>>
>If you don't care about the point-in-time restores but are content with
>restoring from a full backup in case of a failure, you should set the
>database in simple recovery.
>>
>--
>Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>>
>Books Online for SQL Server 2005
>athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>Books Online for SQL Server 2000
>athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide
>quoted text -
>>
>- Show quoted text -
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Kruton (wmlyerly@.gmail.com) writes:
Quote:
Originally Posted by
This is part of a large OLAP process that runs many times a day. I do
not want to / need to restore to a particular time. I have a dba that
does full backups on a regular basis. I would agree with you to a
certain extent if this were OLTP but it is not.
Then you need simple recovery. What I failed to say is that with simple
recovery, SQL Server will regularly truncate the transaction log, and thus
keep it in check. The one thing to keep in mind is that truncation never
goes past the open transaction, so if you have a long-running transaction
the log can grow never the less.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
This sounds like it could be it. I will give it a try. Thanks
On Dec 13, 12:21 am, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
Kruton (wmlye...@.gmail.com) writes:
Quote:
Originally Posted by
This is part of a large OLAP process that runs many times a day. I do
not want to / need to restore to a particular time. I have a dba that
does full backups on a regular basis. I would agree with you to a
certain extent if this were OLTP but it is not.
>
Then you need simple recovery. What I failed to say is that with simple
recovery, SQL Server will regularly truncate the transaction log, and thus
keep it in check. The one thing to keep in mind is that truncation never
goes past the open transaction, so if you have a long-running transaction
the log can grow never the less.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Subscribe to:
Posts (Atom)