I have a SQL Server 2000 database that has three transaction log files. I
would like to delete one of the files. I know the file has to be empty
before you can delete it.
Presently the one transaction file that I would like to delete is 400 MB in
size. I have tried several days to shrink the file but no luck. Is there
away to concatenate these files?
Any recommendations on procedures to delete a transaction log file.
Thanks,Use DBCC SHRINKFILE with the Emptyfile option on the file that was created
last. Once that is run and there are no open or active transactions in that
file you can remove it with the alter database command. Then do that for
the second one. You can not remove the primary (the one created first) one.
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:5E20DA95-B2E2-4E10-95EE-BA09C55AF5B1@.microsoft.com...
> I have a SQL Server 2000 database that has three transaction log files. I
> would like to delete one of the files. I know the file has to be empty
> before you can delete it.
> Presently the one transaction file that I would like to delete is 400 MB
> in
> size. I have tried several days to shrink the file but no luck. Is there
> away to concatenate these files?
> Any recommendations on procedures to delete a transaction log file.
> Thanks,
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Thursday, March 29, 2012
Delete DB Transaction Log file
I have a SQL Server 2000 database that has three transaction log files. I
would like to delete one of the files. I know the file has to be empty
before you can delete it.
Presently the one transaction file that I would like to delete is 400 MB in
size. I have tried several days to shrink the file but no luck. Is there
away to concatenate these files?
Any recommendations on procedures to delete a transaction log file.
Thanks,Use DBCC SHRINKFILE with the Emptyfile option on the file that was created
last. Once that is run and there are no open or active transactions in that
file you can remove it with the alter database command. Then do that for
the second one. You can not remove the primary (the one created first) one.
--
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:5E20DA95-B2E2-4E10-95EE-BA09C55AF5B1@.microsoft.com...
> I have a SQL Server 2000 database that has three transaction log files. I
> would like to delete one of the files. I know the file has to be empty
> before you can delete it.
> Presently the one transaction file that I would like to delete is 400 MB
> in
> size. I have tried several days to shrink the file but no luck. Is there
> away to concatenate these files?
> Any recommendations on procedures to delete a transaction log file.
> Thanks,
would like to delete one of the files. I know the file has to be empty
before you can delete it.
Presently the one transaction file that I would like to delete is 400 MB in
size. I have tried several days to shrink the file but no luck. Is there
away to concatenate these files?
Any recommendations on procedures to delete a transaction log file.
Thanks,Use DBCC SHRINKFILE with the Emptyfile option on the file that was created
last. Once that is run and there are no open or active transactions in that
file you can remove it with the alter database command. Then do that for
the second one. You can not remove the primary (the one created first) one.
--
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:5E20DA95-B2E2-4E10-95EE-BA09C55AF5B1@.microsoft.com...
> I have a SQL Server 2000 database that has three transaction log files. I
> would like to delete one of the files. I know the file has to be empty
> before you can delete it.
> Presently the one transaction file that I would like to delete is 400 MB
> in
> size. I have tried several days to shrink the file but no luck. Is there
> away to concatenate these files?
> Any recommendations on procedures to delete a transaction log file.
> Thanks,
Delete DB Transaction Log file
I have a SQL Server 2000 database that has three transaction log files. I
would like to delete one of the files. I know the file has to be empty
before you can delete it.
Presently the one transaction file that I would like to delete is 400 MB in
size. I have tried several days to shrink the file but no luck. Is there
away to concatenate these files?
Any recommendations on procedures to delete a transaction log file.
Thanks,
Use DBCC SHRINKFILE with the Emptyfile option on the file that was created
last. Once that is run and there are no open or active transactions in that
file you can remove it with the alter database command. Then do that for
the second one. You can not remove the primary (the one created first) one.
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:5E20DA95-B2E2-4E10-95EE-BA09C55AF5B1@.microsoft.com...
> I have a SQL Server 2000 database that has three transaction log files. I
> would like to delete one of the files. I know the file has to be empty
> before you can delete it.
> Presently the one transaction file that I would like to delete is 400 MB
> in
> size. I have tried several days to shrink the file but no luck. Is there
> away to concatenate these files?
> Any recommendations on procedures to delete a transaction log file.
> Thanks,
would like to delete one of the files. I know the file has to be empty
before you can delete it.
Presently the one transaction file that I would like to delete is 400 MB in
size. I have tried several days to shrink the file but no luck. Is there
away to concatenate these files?
Any recommendations on procedures to delete a transaction log file.
Thanks,
Use DBCC SHRINKFILE with the Emptyfile option on the file that was created
last. Once that is run and there are no open or active transactions in that
file you can remove it with the alter database command. Then do that for
the second one. You can not remove the primary (the one created first) one.
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:5E20DA95-B2E2-4E10-95EE-BA09C55AF5B1@.microsoft.com...
> I have a SQL Server 2000 database that has three transaction log files. I
> would like to delete one of the files. I know the file has to be empty
> before you can delete it.
> Presently the one transaction file that I would like to delete is 400 MB
> in
> size. I have tried several days to shrink the file but no luck. Is there
> away to concatenate these files?
> Any recommendations on procedures to delete a transaction log file.
> Thanks,
Delete Dates > 6 months
Hello,
I have created a Table Called Log in SQL Server 7. I use this table as you can guess to log all transaction performed in my program. However, I would like my program to run an SQL Statement that will flush all entries in the table Log that are 6 months old from today's date.
I am unable to find the statement that will execute this operation.
Anyone has an idea please.
Thanks
Markdelete from Log where Recorded_date < dateadd(month, -6, getdate())
Assuming you did record the datetime in your table.|||You mean like:
DELETE FROM yourTable
WHERE DATEDIFF(d,DateCole,GetDate()) > 180|||delete from Log where Recorded_date < dateadd(month, -6, getdate())
Assuming you did record the datetime in your table.
Be careful about months...anywhere in a month counts...
I like days better...
No?|||Be careful about months...anywhere in a month counts...
I like days better...
No?
But when you adjust by months, it just advances/decreases the month value of the date, maintaining the same day value (unless you are advancing/decreasing from a month with 31 days to a month with 30 days, then the days are adjusted down by one). When you adjust by days, you can't take into account 30 days verses 31 days. Adding 30 days to the current date (5/6/2004) you get 6/5/2004. Adding 1 month to the current date gives you 6/6/2004. I've never seen SQL Server arbitrarily select a day when adjusting by months.
When you execute these two statements:
SELECT DATEADD(m, -6, GETDATE())
SELECT DATEADD(d, -180, GETDATE())
You get two very different results:
2003-11-06 13:24:14.700
2003-11-08 13:24:14.700|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.|||Datediff returns integer values, but the DateAdd function is acceptable. I'd use:
where record_date < dateadd (month, -6, getdate())
...since the poster did specify months.|||I agree with Brett I think. I think working with days is better and safer than months. 6 months makes about 182 days.
I must say that I did not expect so many responses in that short laps of time. I'll try what you all said and let you know.
Thanks
Mark|||Both are equally "safe". It's just a matter of whether you specs call for X months or X days.|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.
I'd still go with Days...
But the above is THE most important point...
My Original would cause a stage 2 predicat (nonsargable).
Very Bad
Would cause a scan...
What he hell wa I thinking (And there you go...not thinking...AGAIN...I guess the need to focus never arose *)
* JBuffet
Thanks for pointing that out|||I think there are times when you need to calculate by months, and times you need to calculate by days. I think blindman put it right, it depends on the requirements of the project. If I need to do something every 30 days, regardless of the month, then I'd adjust by days, but if I need to do something on a specific day six months from now, I'd use months. Otherwise I'd have to find out which months have 30 days, and which have 31, (or 29 for a leap year) then add them together before I use dateadd() by day.
By just adjusting by days you'd be off 7 days on average per year, or 5 if you're adjusting by 31 (not counting leap years), unless you manually compensate.|||In Fact I used months... It works fine and suits my needs.
DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6
Thanks a lot to all of you who posted.
Mark|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.
Ahhhhh...I seem to remember something about this from my old SQL days...Can you explain, or point me in the direction of where I can figure out the REASON the index would be invalidated? Or is it simply because the function call would effectively "hide" the table column being considered?
I think the gist of this is that the index column needs to be "accessable to the query" - i.e., the optimizer can't "see" into the function to know the target column is there?
*LOL* I THINK I understand, but probably not regurgitating it in a palatable way (now THAT's just GROSS!!!)|||In Fact I used months... It works fine and suits my needs.
DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6
Thanks a lot to all of you who posted.
Mark
Read back in some of these posts... They make mention of doing the SQL this way invalidates the index. It would be better to do:
DELETE FROM Log WHERE DateL < DATEADD(mm, -6, GETDATE())|||"dateadd (dd, -180, getdate())" need only be evaluated once. It returns the same value for each row, which can then be matched against an index in the table.
"DATEDIFF(mm, DateL, GETDATE()) > 6" must be evaluated separately for each row, because the rows can and probable do return different results.|||and the reason a function call must be evaluated for each value, is because how do you know what month a date of 28488 is?
dates are stored as integers
an index on a date column is an index of integers, and if you can't decide whether an index value is one you want without doing a function on it, the optimizer says "sheesh, if i gotta evaluate all index values, i might as well just scan the table"
dateadd (dd,-180,getdate()), as blindman pointed out, gets evaluated once, prior to query execution, and its value, which happens to be 37933, can then be used in an efficient index search|||Outstanding guys...well done, and in "wow, I haven't done SQL work for 5 years or so, but I THINK I remember something about that..." language!
But now I need to go back and look at all the query work in my project *LOL*sql
I have created a Table Called Log in SQL Server 7. I use this table as you can guess to log all transaction performed in my program. However, I would like my program to run an SQL Statement that will flush all entries in the table Log that are 6 months old from today's date.
I am unable to find the statement that will execute this operation.
Anyone has an idea please.
Thanks
Markdelete from Log where Recorded_date < dateadd(month, -6, getdate())
Assuming you did record the datetime in your table.|||You mean like:
DELETE FROM yourTable
WHERE DATEDIFF(d,DateCole,GetDate()) > 180|||delete from Log where Recorded_date < dateadd(month, -6, getdate())
Assuming you did record the datetime in your table.
Be careful about months...anywhere in a month counts...
I like days better...
No?|||Be careful about months...anywhere in a month counts...
I like days better...
No?
But when you adjust by months, it just advances/decreases the month value of the date, maintaining the same day value (unless you are advancing/decreasing from a month with 31 days to a month with 30 days, then the days are adjusted down by one). When you adjust by days, you can't take into account 30 days verses 31 days. Adding 30 days to the current date (5/6/2004) you get 6/5/2004. Adding 1 month to the current date gives you 6/6/2004. I've never seen SQL Server arbitrarily select a day when adjusting by months.
When you execute these two statements:
SELECT DATEADD(m, -6, GETDATE())
SELECT DATEADD(d, -180, GETDATE())
You get two very different results:
2003-11-06 13:24:14.700
2003-11-08 13:24:14.700|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.|||Datediff returns integer values, but the DateAdd function is acceptable. I'd use:
where record_date < dateadd (month, -6, getdate())
...since the poster did specify months.|||I agree with Brett I think. I think working with days is better and safer than months. 6 months makes about 182 days.
I must say that I did not expect so many responses in that short laps of time. I'll try what you all said and let you know.
Thanks
Mark|||Both are equally "safe". It's just a matter of whether you specs call for X months or X days.|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.
I'd still go with Days...
But the above is THE most important point...
My Original would cause a stage 2 predicat (nonsargable).
Very Bad
Would cause a scan...
What he hell wa I thinking (And there you go...not thinking...AGAIN...I guess the need to focus never arose *)
* JBuffet
Thanks for pointing that out|||I think there are times when you need to calculate by months, and times you need to calculate by days. I think blindman put it right, it depends on the requirements of the project. If I need to do something every 30 days, regardless of the month, then I'd adjust by days, but if I need to do something on a specific day six months from now, I'd use months. Otherwise I'd have to find out which months have 30 days, and which have 31, (or 29 for a leap year) then add them together before I use dateadd() by day.
By just adjusting by days you'd be off 7 days on average per year, or 5 if you're adjusting by 31 (not counting leap years), unless you manually compensate.|||In Fact I used months... It works fine and suits my needs.
DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6
Thanks a lot to all of you who posted.
Mark|||Brett: It would be better to have something like
record_date < dateadd (dd, -180, getdate())
Putting the column in the function call would invalidate any index on record_date.
Ahhhhh...I seem to remember something about this from my old SQL days...Can you explain, or point me in the direction of where I can figure out the REASON the index would be invalidated? Or is it simply because the function call would effectively "hide" the table column being considered?
I think the gist of this is that the index column needs to be "accessable to the query" - i.e., the optimizer can't "see" into the function to know the target column is there?
*LOL* I THINK I understand, but probably not regurgitating it in a palatable way (now THAT's just GROSS!!!)|||In Fact I used months... It works fine and suits my needs.
DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6
Thanks a lot to all of you who posted.
Mark
Read back in some of these posts... They make mention of doing the SQL this way invalidates the index. It would be better to do:
DELETE FROM Log WHERE DateL < DATEADD(mm, -6, GETDATE())|||"dateadd (dd, -180, getdate())" need only be evaluated once. It returns the same value for each row, which can then be matched against an index in the table.
"DATEDIFF(mm, DateL, GETDATE()) > 6" must be evaluated separately for each row, because the rows can and probable do return different results.|||and the reason a function call must be evaluated for each value, is because how do you know what month a date of 28488 is?
dates are stored as integers
an index on a date column is an index of integers, and if you can't decide whether an index value is one you want without doing a function on it, the optimizer says "sheesh, if i gotta evaluate all index values, i might as well just scan the table"
dateadd (dd,-180,getdate()), as blindman pointed out, gets evaluated once, prior to query execution, and its value, which happens to be 37933, can then be used in an efficient index search|||Outstanding guys...well done, and in "wow, I haven't done SQL work for 5 years or so, but I THINK I remember something about that..." language!
But now I need to go back and look at all the query work in my project *LOL*sql
Delete Database Transaction Log File
My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
Dan
I believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan
|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
Dan
I believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan
|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan
Delete Database Transaction Log File
My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
DanI believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
DanI believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan
Delete Database Transaction Log File
My database transaction log file grew very fast and used
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
DanI believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan
all the space on F: drive except 25 MB on my server. So I
created another database transaction log file on my K:
drive on this server.
I have resolved the problem with transaction log growing
very large.
I would like to delete the second transaction log file on
the K: drive. How can I completed this task?
Thanks,
DanI believe the command goes something like:
ALTER DATABASE youdb
REMOVE FILE tranlog_on_K_drive
The Tranlog file of course will need to be removed.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan|||USE databasename
GO
-- if you need to, get the filename from EXEC sp_helpfile
-- you may also want to:
-- BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(filename, EMPTYFILE)
GO
USE Master
GO
ALTER DATABASE databasename REMOVE FILE filename
GO
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:f34d01c43db7$04322080$a601280a@.phx.gbl...
> My database transaction log file grew very fast and used
> all the space on F: drive except 25 MB on my server. So I
> created another database transaction log file on my K:
> drive on this server.
> I have resolved the problem with transaction log growing
> very large.
> I would like to delete the second transaction log file on
> the K: drive. How can I completed this task?
> Thanks,
> Dan
Tuesday, March 27, 2012
Delete and insert data in same transaction!
Hi!
I try to add records in one table from a VB6 com+ component using ADO
(provider=SQLOLEDB, SQL-server2000).
Basically what I try to do is to
1. Start a transaction
2. Delete old records in one table
3. Add new values in the same table
4. commit transaction
The problem is that I get a duplicate key when I insert my new data in
step3. If I commit the data between step 2 and 3 everything works fine. If
step 4 then fails I will end up with no data in the table which I don't want
(i mean that is what transaction is used for).
This must be a pretty common scenario so I hope there will be a solution
that will not force me to commit the transaction in the middle.
Regards
/HansHi Hans,
as you descibed the scenario should be fine, allowing the transaction
to commit without problems, perhaps there is a logical problem in your
code. Could you please post the code here. This would make error
searching much easier for us.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hans wrote:
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't wa
nt
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
Do you mean you want to delete and then insert new row(s) with the same
key values? That may not be an optimal solution since you could
accomplish the same thing with an UPDATE. The following works for me.
If this example doesn't help then please post some code so that we can
reproduce the problem.
CREATE TABLE tbl (x INT PRIMARY KEY);
INSERT INTO tbl(x) VALUES (1);
BEGIN TRAN;
DELETE FROM tbl WHERE x=1;
INSERT INTO tbl(x) VALUES (1);
COMMIT TRAN;
SELECT x FROM tbl;
I recommend you put the DELETE/INSERT code in a stored procedure and
execute the proc from your VB code.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> The problem is that I get a duplicate key when I insert my new data in
> step3.
This indicates that INSERTs are occurring on a different connection that the
DELETE and not within the same transaction context. You can run a SQL
Profiler trace to see the actual behavior.
Note that ADO is particularly nasty about opening additional connections
behind your back. It is important to include 'SET NOCOUNT ON' in
procs/scripts and process all results returned so that connections can be
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:OXTNpYnSGHA.2156@.tk2msftngp13.phx.gbl...
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't
> want
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
>
>|||Hi Jens, David and Dan!
Thanks for your replies.
Here is the code. The code is used to store default values for a user. The
table have fields for which user it is (idUser), which field (idfld) and
some other fields about the default values. The code is most likely not the
most efficient (the "IN" operator is slow but we are talking about pretty
small tables here with a couple of 1000 records) but it is only executed a
couple of times/year for a normal user (and it only takes like 100
milliseconds to excecute as it is). The key in the table is idUser (user id)
and idfld (Field id) together and I'm not sure if there is a way to update
or add in one single SQL-statement and also delete records where I earlier
had defaultvalues but where the user no longer want to have default values.
Therfor I delete all defaultvalues for the current user for the current
table (I join in another table which holds the table id). For example there
may be 5 rows before the save and maybe only 3 rows left in the table after
the update.
If OS_WIN2000 Then
Set rs = CreateObject("ADODB.Recordset")
Set con = CreateObject("ADODB.Connection")
Else
Set rs = CtxCreateObject("ADODB.Recordset")
Set con = CtxCreateObject("ADODB.Connection")
End If
con.Open GetConnectionString()
'Use transaction
con.BeginTrans
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where iduser="
& userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & " where
vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
rs.Open sSQL, con
'If I commit here it works OK but I want to commit after the entire
operation is finished
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
rs.Open sSQL, con
End If
Next l
Next i
con.CommitTrans
Regards
/Hans|||The main problem is that you are using recordsets when no data are returned.
Additional connections are probably acquired for the subsequent INSERT
statements and outside scope of the first transaction.
The example below shows how to use Command objects for these DML statements.
I would also suggest using parameters instead of concatenating literal
values. Parameters are more secure, eliminate the need to quote values,
escape quotes, format dates, etc.
Set con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
con.Open GetConnectionString()
cmd.ActiveConnection = con
con.BeginTrans
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "SET NOCOUNT ON Delete from " & _
TSP("vmo_base_defaultvalues") & _
" where iduser=" & _
userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & _
" where vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
cmd.CommandText = sSQL
cmd.Execute
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "SET NOCOUNT ON Insert into " & _
TSP("vmo_base_defaultvalues") & _
"(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
cmd.CommandText = sSQL
cmd.Execute
End If
Next l
Next i
con.CommitTrans
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:%23%23EoJT2SGHA.1148@.TK2MSFTNGP10.phx.gbl...
> Hi Jens, David and Dan!
> Thanks for your replies.
> Here is the code. The code is used to store default values for a user. The
> table have fields for which user it is (idUser), which field (idfld) and
> some other fields about the default values. The code is most likely not
> the
> most efficient (the "IN" operator is slow but we are talking about pretty
> small tables here with a couple of 1000 records) but it is only executed a
> couple of times/year for a normal user (and it only takes like 100
> milliseconds to excecute as it is). The key in the table is idUser (user
> id)
> and idfld (Field id) together and I'm not sure if there is a way to update
> or add in one single SQL-statement and also delete records where I earlier
> had defaultvalues but where the user no longer want to have default
> values.
> Therfor I delete all defaultvalues for the current user for the current
> table (I join in another table which holds the table id). For example
> there
> may be 5 rows before the save and maybe only 3 rows left in the table
> after
> the update.
> If OS_WIN2000 Then
> Set rs = CreateObject("ADODB.Recordset")
> Set con = CreateObject("ADODB.Connection")
> Else
> Set rs = CtxCreateObject("ADODB.Recordset")
> Set con = CtxCreateObject("ADODB.Connection")
> End If
> con.Open GetConnectionString()
> 'Use transaction
> con.BeginTrans
> rs.CursorLocation = adUseClient
> rs.CursorType = adOpenStatic
> rs.LockType = adLockOptimistic
> 'idtbl and idUser is already singlequoted
> For i = LBound(userList) To UBound(userList)
> sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where
> iduser="
> & userList(i) & " and "
> sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
> TSP("vmo_base_defaultvalues") & ","
> sSQL = sSQL & TSP("vmo_base_field") & " where
> vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
> sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
> rs.Open sSQL, con
> 'If I commit here it works OK but I want to commit after the entire
> operation is finished
> For l = LBound(sArg) To UBound(sArg) Step 4
> If sArg(l) <> "" And sArg(l + 1) <> "" Then
> sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
> (idUser,idfld,vValue,datevalue, deftype) " & _
> "values (" & _
> userList(i) & "," & _
> sArg(l) & "," & _
> sArg(l + 1) & "," & _
> sArg(l + 2) & "," & _
> sArg(l + 3) & ")"
> rs.Open sSQL, con
> End If
> Next l
> Next i
> con.CommitTrans
>
> Regards
> /Hans
>|||Thanks Dan for the tip!
Yes that seems to fix the problem. The code is pretty old and written for
Oracle in the first place where I did not have any problems with the
transaction (so I guess the real problem is inside the oledb provider). Yes
you are right using parameters is much safer but at least I don't like
touching code that has been working for years, well at least for other
databases than SQL-server :-)
/Hans
I try to add records in one table from a VB6 com+ component using ADO
(provider=SQLOLEDB, SQL-server2000).
Basically what I try to do is to
1. Start a transaction
2. Delete old records in one table
3. Add new values in the same table
4. commit transaction
The problem is that I get a duplicate key when I insert my new data in
step3. If I commit the data between step 2 and 3 everything works fine. If
step 4 then fails I will end up with no data in the table which I don't want
(i mean that is what transaction is used for).
This must be a pretty common scenario so I hope there will be a solution
that will not force me to commit the transaction in the middle.
Regards
/HansHi Hans,
as you descibed the scenario should be fine, allowing the transaction
to commit without problems, perhaps there is a logical problem in your
code. Could you please post the code here. This would make error
searching much easier for us.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hans wrote:
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't wa
nt
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
Do you mean you want to delete and then insert new row(s) with the same
key values? That may not be an optimal solution since you could
accomplish the same thing with an UPDATE. The following works for me.
If this example doesn't help then please post some code so that we can
reproduce the problem.
CREATE TABLE tbl (x INT PRIMARY KEY);
INSERT INTO tbl(x) VALUES (1);
BEGIN TRAN;
DELETE FROM tbl WHERE x=1;
INSERT INTO tbl(x) VALUES (1);
COMMIT TRAN;
SELECT x FROM tbl;
I recommend you put the DELETE/INSERT code in a stored procedure and
execute the proc from your VB code.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> The problem is that I get a duplicate key when I insert my new data in
> step3.
This indicates that INSERTs are occurring on a different connection that the
DELETE and not within the same transaction context. You can run a SQL
Profiler trace to see the actual behavior.
Note that ADO is particularly nasty about opening additional connections
behind your back. It is important to include 'SET NOCOUNT ON' in
procs/scripts and process all results returned so that connections can be
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:OXTNpYnSGHA.2156@.tk2msftngp13.phx.gbl...
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't
> want
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
>
>|||Hi Jens, David and Dan!
Thanks for your replies.
Here is the code. The code is used to store default values for a user. The
table have fields for which user it is (idUser), which field (idfld) and
some other fields about the default values. The code is most likely not the
most efficient (the "IN" operator is slow but we are talking about pretty
small tables here with a couple of 1000 records) but it is only executed a
couple of times/year for a normal user (and it only takes like 100
milliseconds to excecute as it is). The key in the table is idUser (user id)
and idfld (Field id) together and I'm not sure if there is a way to update
or add in one single SQL-statement and also delete records where I earlier
had defaultvalues but where the user no longer want to have default values.
Therfor I delete all defaultvalues for the current user for the current
table (I join in another table which holds the table id). For example there
may be 5 rows before the save and maybe only 3 rows left in the table after
the update.
If OS_WIN2000 Then
Set rs = CreateObject("ADODB.Recordset")
Set con = CreateObject("ADODB.Connection")
Else
Set rs = CtxCreateObject("ADODB.Recordset")
Set con = CtxCreateObject("ADODB.Connection")
End If
con.Open GetConnectionString()
'Use transaction
con.BeginTrans
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where iduser="
& userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & " where
vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
rs.Open sSQL, con
'If I commit here it works OK but I want to commit after the entire
operation is finished
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
rs.Open sSQL, con
End If
Next l
Next i
con.CommitTrans
Regards
/Hans|||The main problem is that you are using recordsets when no data are returned.
Additional connections are probably acquired for the subsequent INSERT
statements and outside scope of the first transaction.
The example below shows how to use Command objects for these DML statements.
I would also suggest using parameters instead of concatenating literal
values. Parameters are more secure, eliminate the need to quote values,
escape quotes, format dates, etc.
Set con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
con.Open GetConnectionString()
cmd.ActiveConnection = con
con.BeginTrans
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "SET NOCOUNT ON Delete from " & _
TSP("vmo_base_defaultvalues") & _
" where iduser=" & _
userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & _
" where vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
cmd.CommandText = sSQL
cmd.Execute
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "SET NOCOUNT ON Insert into " & _
TSP("vmo_base_defaultvalues") & _
"(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
cmd.CommandText = sSQL
cmd.Execute
End If
Next l
Next i
con.CommitTrans
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:%23%23EoJT2SGHA.1148@.TK2MSFTNGP10.phx.gbl...
> Hi Jens, David and Dan!
> Thanks for your replies.
> Here is the code. The code is used to store default values for a user. The
> table have fields for which user it is (idUser), which field (idfld) and
> some other fields about the default values. The code is most likely not
> the
> most efficient (the "IN" operator is slow but we are talking about pretty
> small tables here with a couple of 1000 records) but it is only executed a
> couple of times/year for a normal user (and it only takes like 100
> milliseconds to excecute as it is). The key in the table is idUser (user
> id)
> and idfld (Field id) together and I'm not sure if there is a way to update
> or add in one single SQL-statement and also delete records where I earlier
> had defaultvalues but where the user no longer want to have default
> values.
> Therfor I delete all defaultvalues for the current user for the current
> table (I join in another table which holds the table id). For example
> there
> may be 5 rows before the save and maybe only 3 rows left in the table
> after
> the update.
> If OS_WIN2000 Then
> Set rs = CreateObject("ADODB.Recordset")
> Set con = CreateObject("ADODB.Connection")
> Else
> Set rs = CtxCreateObject("ADODB.Recordset")
> Set con = CtxCreateObject("ADODB.Connection")
> End If
> con.Open GetConnectionString()
> 'Use transaction
> con.BeginTrans
> rs.CursorLocation = adUseClient
> rs.CursorType = adOpenStatic
> rs.LockType = adLockOptimistic
> 'idtbl and idUser is already singlequoted
> For i = LBound(userList) To UBound(userList)
> sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where
> iduser="
> & userList(i) & " and "
> sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
> TSP("vmo_base_defaultvalues") & ","
> sSQL = sSQL & TSP("vmo_base_field") & " where
> vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
> sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
> rs.Open sSQL, con
> 'If I commit here it works OK but I want to commit after the entire
> operation is finished
> For l = LBound(sArg) To UBound(sArg) Step 4
> If sArg(l) <> "" And sArg(l + 1) <> "" Then
> sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
> (idUser,idfld,vValue,datevalue, deftype) " & _
> "values (" & _
> userList(i) & "," & _
> sArg(l) & "," & _
> sArg(l + 1) & "," & _
> sArg(l + 2) & "," & _
> sArg(l + 3) & ")"
> rs.Open sSQL, con
> End If
> Next l
> Next i
> con.CommitTrans
>
> Regards
> /Hans
>|||Thanks Dan for the tip!
Yes that seems to fix the problem. The code is pretty old and written for
Oracle in the first place where I did not have any problems with the
transaction (so I guess the real problem is inside the oledb provider). Yes
you are right using parameters is much safer but at least I don't like
touching code that has been working for years, well at least for other
databases than SQL-server :-)
/Hans
Sunday, March 25, 2012
Delete a transaction log
Hi, i have that due a problem have 2 transaction log, i need a procedure to
delete one log and manteien the other.
Thanks
Use the Alter Database <dbname> Remove File <TransactionLogFilename> to
remove the extraneous .LDF file. Or detach your database, delete one of the
..LDF files and then re-attach your database.
Make sure to do a complete backup of your DB BEFORE using either of these
options though. That will clear out the Transaction Logs and preserve your
info in case something goes horribly wrong.
"Adrian Lambardi" wrote:
> Hi, i have that due a problem have 2 transaction log, i need a procedure to
> delete one log and manteien the other.
> Thanks
|||> Or detach your database, delete one of the
> .LDF files and then re-attach your database.
Why not use the documented and supported methods instead of deleting the log, which, as you say,
might very well destroy the database?
ALTER DATABASE ... REMOVE FILE is the supported way to remove a database file. You need to DBCC
SHRINKFILE with the EMPTYFILE option first. You also need to make sure that the file is unused, the
undocumented DBCC LOGINFO can be useful for checking (Google and you will find).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
news:C9D4FC72-AD23-4E6E-9916-EC09BDF7BDB8@.microsoft.com...[vbcol=seagreen]
> Use the Alter Database <dbname> Remove File <TransactionLogFilename> to
> remove the extraneous .LDF file. Or detach your database, delete one of the
> .LDF files and then re-attach your database.
> Make sure to do a complete backup of your DB BEFORE using either of these
> options though. That will clear out the Transaction Logs and preserve your
> info in case something goes horribly wrong.
>
> "Adrian Lambardi" wrote:
sql
delete one log and manteien the other.
Thanks
Use the Alter Database <dbname> Remove File <TransactionLogFilename> to
remove the extraneous .LDF file. Or detach your database, delete one of the
..LDF files and then re-attach your database.
Make sure to do a complete backup of your DB BEFORE using either of these
options though. That will clear out the Transaction Logs and preserve your
info in case something goes horribly wrong.
"Adrian Lambardi" wrote:
> Hi, i have that due a problem have 2 transaction log, i need a procedure to
> delete one log and manteien the other.
> Thanks
|||> Or detach your database, delete one of the
> .LDF files and then re-attach your database.
Why not use the documented and supported methods instead of deleting the log, which, as you say,
might very well destroy the database?
ALTER DATABASE ... REMOVE FILE is the supported way to remove a database file. You need to DBCC
SHRINKFILE with the EMPTYFILE option first. You also need to make sure that the file is unused, the
undocumented DBCC LOGINFO can be useful for checking (Google and you will find).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
news:C9D4FC72-AD23-4E6E-9916-EC09BDF7BDB8@.microsoft.com...[vbcol=seagreen]
> Use the Alter Database <dbname> Remove File <TransactionLogFilename> to
> remove the extraneous .LDF file. Or detach your database, delete one of the
> .LDF files and then re-attach your database.
> Make sure to do a complete backup of your DB BEFORE using either of these
> options though. That will clear out the Transaction Logs and preserve your
> info in case something goes horribly wrong.
>
> "Adrian Lambardi" wrote:
sql
Delete a transaction log
Hi, i have that due a problem have 2 transaction log, i need a procedure to
delete one log and manteien the other.
ThanksUse the Alter Database <dbname> Remove File <TransactionLogFilename> to
remove the extraneous .LDF file. Or detach your database, delete one of the
.LDF files and then re-attach your database.
Make sure to do a complete backup of your DB BEFORE using either of these
options though. That will clear out the Transaction Logs and preserve your
info in case something goes horribly wrong.
"Adrian Lambardi" wrote:
> Hi, i have that due a problem have 2 transaction log, i need a procedure to
> delete one log and manteien the other.
> Thanks|||> Or detach your database, delete one of the
> .LDF files and then re-attach your database.
Why not use the documented and supported methods instead of deleting the log, which, as you say,
might very well destroy the database?
ALTER DATABASE ... REMOVE FILE is the supported way to remove a database file. You need to DBCC
SHRINKFILE with the EMPTYFILE option first. You also need to make sure that the file is unused, the
undocumented DBCC LOGINFO can be useful for checking (Google and you will find).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
news:C9D4FC72-AD23-4E6E-9916-EC09BDF7BDB8@.microsoft.com...
> Use the Alter Database <dbname> Remove File <TransactionLogFilename> to
> remove the extraneous .LDF file. Or detach your database, delete one of the
> .LDF files and then re-attach your database.
> Make sure to do a complete backup of your DB BEFORE using either of these
> options though. That will clear out the Transaction Logs and preserve your
> info in case something goes horribly wrong.
>
> "Adrian Lambardi" wrote:
>> Hi, i have that due a problem have 2 transaction log, i need a procedure to
>> delete one log and manteien the other.
>> Thanks
delete one log and manteien the other.
ThanksUse the Alter Database <dbname> Remove File <TransactionLogFilename> to
remove the extraneous .LDF file. Or detach your database, delete one of the
.LDF files and then re-attach your database.
Make sure to do a complete backup of your DB BEFORE using either of these
options though. That will clear out the Transaction Logs and preserve your
info in case something goes horribly wrong.
"Adrian Lambardi" wrote:
> Hi, i have that due a problem have 2 transaction log, i need a procedure to
> delete one log and manteien the other.
> Thanks|||> Or detach your database, delete one of the
> .LDF files and then re-attach your database.
Why not use the documented and supported methods instead of deleting the log, which, as you say,
might very well destroy the database?
ALTER DATABASE ... REMOVE FILE is the supported way to remove a database file. You need to DBCC
SHRINKFILE with the EMPTYFILE option first. You also need to make sure that the file is unused, the
undocumented DBCC LOGINFO can be useful for checking (Google and you will find).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
news:C9D4FC72-AD23-4E6E-9916-EC09BDF7BDB8@.microsoft.com...
> Use the Alter Database <dbname> Remove File <TransactionLogFilename> to
> remove the extraneous .LDF file. Or detach your database, delete one of the
> .LDF files and then re-attach your database.
> Make sure to do a complete backup of your DB BEFORE using either of these
> options though. That will clear out the Transaction Logs and preserve your
> info in case something goes horribly wrong.
>
> "Adrian Lambardi" wrote:
>> Hi, i have that due a problem have 2 transaction log, i need a procedure to
>> delete one log and manteien the other.
>> Thanks
Delete a transaction log
Hi, i have that due a problem have 2 transaction log, i need a procedure to
delete one log and manteien the other.
ThanksUse the Alter Database <dbname> Remove File <TransactionLogFilename> to
remove the extraneous .LDF file. Or detach your database, delete one of the
.LDF files and then re-attach your database.
Make sure to do a complete backup of your DB BEFORE using either of these
options though. That will clear out the Transaction Logs and preserve your
info in case something goes horribly wrong.
"Adrian Lambardi" wrote:
> Hi, i have that due a problem have 2 transaction log, i need a procedure t
o
> delete one log and manteien the other.
> Thanks|||> Or detach your database, delete one of the
> .LDF files and then re-attach your database.
Why not use the documented and supported methods instead of deleting the log
, which, as you say,
might very well destroy the database?
ALTER DATABASE ... REMOVE FILE is the supported way to remove a database fil
e. You need to DBCC
SHRINKFILE with the EMPTYFILE option first. You also need to make sure that
the file is unused, the
undocumented DBCC LOGINFO can be useful for checking (Google and you will fi
nd).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
news:C9D4FC72-AD23-4E6E-9916-EC09BDF7BDB8@.microsoft.com...[vbcol=seagreen]
> Use the Alter Database <dbname> Remove File <TransactionLogFilename> to
> remove the extraneous .LDF file. Or detach your database, delete one of t
he
> .LDF files and then re-attach your database.
> Make sure to do a complete backup of your DB BEFORE using either of these
> options though. That will clear out the Transaction Logs and preserve you
r
> info in case something goes horribly wrong.
>
> "Adrian Lambardi" wrote:
>
delete one log and manteien the other.
ThanksUse the Alter Database <dbname> Remove File <TransactionLogFilename> to
remove the extraneous .LDF file. Or detach your database, delete one of the
.LDF files and then re-attach your database.
Make sure to do a complete backup of your DB BEFORE using either of these
options though. That will clear out the Transaction Logs and preserve your
info in case something goes horribly wrong.
"Adrian Lambardi" wrote:
> Hi, i have that due a problem have 2 transaction log, i need a procedure t
o
> delete one log and manteien the other.
> Thanks|||> Or detach your database, delete one of the
> .LDF files and then re-attach your database.
Why not use the documented and supported methods instead of deleting the log
, which, as you say,
might very well destroy the database?
ALTER DATABASE ... REMOVE FILE is the supported way to remove a database fil
e. You need to DBCC
SHRINKFILE with the EMPTYFILE option first. You also need to make sure that
the file is unused, the
undocumented DBCC LOGINFO can be useful for checking (Google and you will fi
nd).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
news:C9D4FC72-AD23-4E6E-9916-EC09BDF7BDB8@.microsoft.com...[vbcol=seagreen]
> Use the Alter Database <dbname> Remove File <TransactionLogFilename> to
> remove the extraneous .LDF file. Or detach your database, delete one of t
he
> .LDF files and then re-attach your database.
> Make sure to do a complete backup of your DB BEFORE using either of these
> options though. That will clear out the Transaction Logs and preserve you
r
> info in case something goes horribly wrong.
>
> "Adrian Lambardi" wrote:
>
Thursday, March 22, 2012
Delete 2nd transaction log file
Hello
We have a SQL Server 2000 database with 2 transaction log files.
The 2nd file was created when we were running out of disk space and the person creating it was not familiar with the dbcc shrink command.
I now want to get rid of the 2nd log file. I ran the following steps with no success:
DBCC SHRINKFILE ('Log_file', EMPTYFILE )
--Message: Cannot shrink log file 3 (log_file) because all logical log files are in use.
ALTER DATABASE db1 REMOVE FILE 'Log_file'
--Message: The file 'Log_file' cannot be removed because it is not empty.
There are no users or open transactions in the database. I have also tried sp_detach_db and sp_attach_single_file_db but that does not work either as the database attaches both the transaction logs back.
Please advise.
Thanks
NinaTry running "checkpoint" after the DBCC SHRINKFILE. I think this was a requirement in the SQL 7.0 days, but I am not sure about SQL 2000.|||Hey
thanks for the help but it worked fine after i truncated the log file.
We have a SQL Server 2000 database with 2 transaction log files.
The 2nd file was created when we were running out of disk space and the person creating it was not familiar with the dbcc shrink command.
I now want to get rid of the 2nd log file. I ran the following steps with no success:
DBCC SHRINKFILE ('Log_file', EMPTYFILE )
--Message: Cannot shrink log file 3 (log_file) because all logical log files are in use.
ALTER DATABASE db1 REMOVE FILE 'Log_file'
--Message: The file 'Log_file' cannot be removed because it is not empty.
There are no users or open transactions in the database. I have also tried sp_detach_db and sp_attach_single_file_db but that does not work either as the database attaches both the transaction logs back.
Please advise.
Thanks
NinaTry running "checkpoint" after the DBCC SHRINKFILE. I think this was a requirement in the SQL 7.0 days, but I am not sure about SQL 2000.|||Hey
thanks for the help but it worked fine after i truncated the log file.
Wednesday, March 21, 2012
Delay before uncomplete transaction removed
When a workstation losts connection to server,
it can leave an uncomplete transaction. Then
SQL Server removes the transaction.
Could anyone guide me how to set the delay
before SQL Server do it ?
it can leave an uncomplete transaction. Then
SQL Server removes the transaction.
Could anyone guide me how to set the delay
before SQL Server do it ?
Thanks in advance
John S.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!john smile <nospam> wrote in message news:<40fda2d1$0$16449$c397aba@.news.newsgroups.ws>...
> When a workstation losts connection to server,
> it can leave an uncomplete transaction. Then
> SQL Server removes the transaction.
> Could anyone guide me how to set the delay
> before SQL Server do it ?
> Thanks in advance
> John S.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
I'm not sure I understand your question - as far as I know, MSSQL will
rollback the transaction immediately, but why would you want to delay
the rollback? Perhaps if you can explain what problem you are trying
to solve, someone will be able to make a suggestion.
Simon|||Simon, thanks for the response.
>>I'm not sure I understand your question -
as far as I know, MSSQL will
rollback the transaction immediately, >
It means : I want to know for sure how many seconds
is it from connection failure to the beginning
of rollback.
>>but why would you want to delay
the rollback? >
No, I do not want to delay it.
If possible, I want to make the delay short,
as short as possible by adjusting any parameters
(like in Novell Netware : "number of watchdog -
packets", "delay between watchdog packets").
>>Perhaps if you can explain what problem you are trying
to solve, someone will be able to make a suggestion. >
Currently, I have no problem. It is only a question.
I also want to ask :
If I found that SQL Server took long to rollback
an uncommitted small transaction caused by a network
failure, what should I do ?
Regards
John S.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"john smile" <nospam> wrote in message
news:40fe5998$0$16470$c397aba@.news.newsgroups.ws.. .
> Currently, I have no problem. It is only a question.
> I also want to ask :
> If I found that SQL Server took long to rollback
> an uncommitted small transaction caused by a network
> failure, what should I do ?
Design your transactions better.
Seriously, this is one of the issues we've looked at as we're moving to
clustering. Clustering basically involves shutting down and restarting SQL
Server, so any long transactions affect your failover time. And if for
example you had transactions that take 15 minutes to rollforward or back,
your failover will take at least that long to occur. Which sorta negates
the usefullness in most cases.
Generally small transactions won't take long at all to rollback. Even lots
of them.
> Regards
> John S.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql
Wednesday, March 7, 2012
Deferring constraint checking in a transaction
Hello,
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.
Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/a...1/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.
Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/a...1/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>
Deferring constraint checking in a transaction
Hello,
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>
Deferring constraint checking in a transaction
Hello,
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/...01/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/...01/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>
Subscribe to:
Posts (Atom)