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 log. Show all posts
Showing posts with label log. 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
delete data without writing to the log file
I will be deleting a large amount of data from our database , is there a way
to prevent this from writing to the log file?
Thanks in advance.As long as your tables do not have foreign keys AND you want to delete =all the data from a specific table you could use TRUNCATE. Otherwise =deleting in batches and then clearing the transaction log is your best =method.
-- Keith
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message =news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> I will be deleting a large amount of data from our database , is there =a way
> to prevent this from writing to the log file?
> > Thanks in advance.
> >|||Hi
You could use the simple recovery model, but this is not advisable for a
production system.
John
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> I will be deleting a large amount of data from our database , is there a
way
> to prevent this from writing to the log file?
> Thanks in advance.
>|||That won't prevent the data from being written to the log. SQL Server will
auto-truncate the log when the checkpoint process wakes up... but
transactional integrity is maintianed in SIMPLE which means the entirer
transaction and all it's IO still goes to the log...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:k86Ab.110$NX6.916578@.news-text.cableinet.net...
> Hi
> You could use the simple recovery model, but this is not advisable for a
> production system.
> John
> "Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
> news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> > I will be deleting a large amount of data from our database , is there a
> way
> > to prevent this from writing to the log file?
> >
> > Thanks in advance.
> >
> >
>
to prevent this from writing to the log file?
Thanks in advance.As long as your tables do not have foreign keys AND you want to delete =all the data from a specific table you could use TRUNCATE. Otherwise =deleting in batches and then clearing the transaction log is your best =method.
-- Keith
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message =news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> I will be deleting a large amount of data from our database , is there =a way
> to prevent this from writing to the log file?
> > Thanks in advance.
> >|||Hi
You could use the simple recovery model, but this is not advisable for a
production system.
John
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> I will be deleting a large amount of data from our database , is there a
way
> to prevent this from writing to the log file?
> Thanks in advance.
>|||That won't prevent the data from being written to the log. SQL Server will
auto-truncate the log when the checkpoint process wakes up... but
transactional integrity is maintianed in SIMPLE which means the entirer
transaction and all it's IO still goes to the log...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:k86Ab.110$NX6.916578@.news-text.cableinet.net...
> Hi
> You could use the simple recovery model, but this is not advisable for a
> production system.
> John
> "Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
> news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> > I will be deleting a large amount of data from our database , is there a
> way
> > to prevent this from writing to the log file?
> >
> > Thanks in advance.
> >
> >
>
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.
Delete + Log file
A) When i run a delete against a table that has 50 million records based
upon a where clause , what entries does the T log file hold ? Does it log 50
million delete statements along with 50 million inserts just incase it needs
to rollback.
B) Also if there is a clustered index on the coulimn thats part of the where
clause, what does the Tlog file hold ?
C) If there was a clustered index but not part of the column in the where
clause, what does the Tlog contain ?
D) During the time the delete is occuring, does it go ahead and start
deleting entries from the data pages in the data files or does it first log
entries in the Log file and then deletes ?
E) Finally if i did a backup log during the time the delete is occuring (
Say i noticed that the table with (nolock) option was decrementing ) and
then restored the logs on another database , will part of the deletes be
reflected on the other database if i specify the (nolock) option since the
delete did not complete
I am just trying to understand what entries the TLog contains.. I would
appreciate if you could provide answers to all the 5 parts. I am using SQL
2000
Thank youIf you want to see what is in the tran log you can run
this before you do a log backup:
select * from ::fn_dblog(null,null)
I will attempt to answer your questions
a) It logs 50 million delete statements (if you need to
delete everything out of a table and don't need the
ability to rollback, you might use truncate table as it is
faster because it is minimally logged)
b)I don't believe it matters if there is a clustered index
or not in regards to the T-log
c)I don't believe it matters if there is a clustered index
or not in regards to the T-log
d) Everything hits the log first, it hits the data when
the T-log does a checkpoint
e)That one I'm not sure on, would have to test that one or
maybe someone has tried this before that reads these
newsgroups, I would guess that if you tried backing up the
log during this transaction, it would wait until the tran
was finished, selecting from it with a nolog would only
show you, not the T-log, the data while in the transaction.
Again, not sure on that one.
HTH
Ray Higdon MCSE, MCDBA, CCNA
>--Original Message--
>A) When i run a delete against a table that has 50
million records based
>upon a where clause , what entries does the T log file
hold ? Does it log 50
>million delete statements along with 50 million inserts
just incase it needs
>to rollback.
>
>B) Also if there is a clustered index on the coulimn
thats part of the where
>clause, what does the Tlog file hold ?
>C) If there was a clustered index but not part of the
column in the where
>clause, what does the Tlog contain ?
>D) During the time the delete is occuring, does it go
ahead and start
>deleting entries from the data pages in the data files or
does it first log
>entries in the Log file and then deletes ?
>E) Finally if i did a backup log during the time the
delete is occuring (
>Say i noticed that the table with (nolock) option was
decrementing ) and
>then restored the logs on another database , will part of
the deletes be
>reflected on the other database if i specify the (nolock)
option since the
>delete did not complete
>I am just trying to understand what entries the TLog
contains.. I would
>appreciate if you could provide answers to all the 5
parts. I am using SQL
>2000
>Thank you
>
>.
>|||--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> A) When i run a delete against a table that has 50 million records based
> upon a where clause , what entries does the T log file hold ? Does it log
50
> million delete statements along with 50 million inserts just incase it
needs
> to rollback.
>
The log holds a copy of the record which was deleted...
> B) Also if there is a clustered index on the coulimn thats part of the
where
> clause, what does the Tlog file hold ?
NO change, the log has the copy of the deleted record.
> C) If there was a clustered index but not part of the column in the where
> clause, what does the Tlog contain ?
no change.
> D) During the time the delete is occuring, does it go ahead and start
> deleting entries from the data pages in the data files or does it first
log
> entries in the Log file and then deletes ?
>
Logging occurs first.
> E) Finally if i did a backup log during the time the delete is occuring (
> Say i noticed that the table with (nolock) option was decrementing ) and
> then restored the logs on another database , will part of the deletes be
> reflected on the other database if i specify the (nolock) option since the
> delete did not complete
>
After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction.
> I am just trying to understand what entries the TLog contains.. I would
> appreciate if you could provide answers to all the 5 parts. I am using SQL
> 2000
> Thank you
>
>|||Thanks so to answer 5, where you said
"After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction."
What if i restored log with standby mode so users can read from this standby
database, will i see some deletes in effect with (nolock)
Thanks once again to you all
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:eTPtkTCWDHA.2008@.TK2MSFTNGP11.phx.gbl...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> > A) When i run a delete against a table that has 50 million records based
> > upon a where clause , what entries does the T log file hold ? Does it
log
> 50
> > million delete statements along with 50 million inserts just incase it
> needs
> > to rollback.
> >
> The log holds a copy of the record which was deleted...
> >
> > B) Also if there is a clustered index on the coulimn thats part of the
> where
> > clause, what does the Tlog file hold ?
> NO change, the log has the copy of the deleted record.
>
> >
> > C) If there was a clustered index but not part of the column in the
where
> > clause, what does the Tlog contain ?
> no change.
> >
> > D) During the time the delete is occuring, does it go ahead and start
> > deleting entries from the data pages in the data files or does it first
> log
> > entries in the Log file and then deletes ?
> >
> Logging occurs first.
> > E) Finally if i did a backup log during the time the delete is occuring
(
> > Say i noticed that the table with (nolock) option was decrementing ) and
> > then restored the logs on another database , will part of the deletes be
> > reflected on the other database if i specify the (nolock) option since
the
> > delete did not complete
> >
> After the restore, and recovery has run either all of the records will be
> present or none of them...Each statement is a transaction.
> > I am just trying to understand what entries the TLog contains.. I would
> > appreciate if you could provide answers to all the 5 parts. I am using
SQL
> > 2000
> >
> > Thank you
> >
> >
> >
>sql
upon a where clause , what entries does the T log file hold ? Does it log 50
million delete statements along with 50 million inserts just incase it needs
to rollback.
B) Also if there is a clustered index on the coulimn thats part of the where
clause, what does the Tlog file hold ?
C) If there was a clustered index but not part of the column in the where
clause, what does the Tlog contain ?
D) During the time the delete is occuring, does it go ahead and start
deleting entries from the data pages in the data files or does it first log
entries in the Log file and then deletes ?
E) Finally if i did a backup log during the time the delete is occuring (
Say i noticed that the table with (nolock) option was decrementing ) and
then restored the logs on another database , will part of the deletes be
reflected on the other database if i specify the (nolock) option since the
delete did not complete
I am just trying to understand what entries the TLog contains.. I would
appreciate if you could provide answers to all the 5 parts. I am using SQL
2000
Thank youIf you want to see what is in the tran log you can run
this before you do a log backup:
select * from ::fn_dblog(null,null)
I will attempt to answer your questions
a) It logs 50 million delete statements (if you need to
delete everything out of a table and don't need the
ability to rollback, you might use truncate table as it is
faster because it is minimally logged)
b)I don't believe it matters if there is a clustered index
or not in regards to the T-log
c)I don't believe it matters if there is a clustered index
or not in regards to the T-log
d) Everything hits the log first, it hits the data when
the T-log does a checkpoint
e)That one I'm not sure on, would have to test that one or
maybe someone has tried this before that reads these
newsgroups, I would guess that if you tried backing up the
log during this transaction, it would wait until the tran
was finished, selecting from it with a nolog would only
show you, not the T-log, the data while in the transaction.
Again, not sure on that one.
HTH
Ray Higdon MCSE, MCDBA, CCNA
>--Original Message--
>A) When i run a delete against a table that has 50
million records based
>upon a where clause , what entries does the T log file
hold ? Does it log 50
>million delete statements along with 50 million inserts
just incase it needs
>to rollback.
>
>B) Also if there is a clustered index on the coulimn
thats part of the where
>clause, what does the Tlog file hold ?
>C) If there was a clustered index but not part of the
column in the where
>clause, what does the Tlog contain ?
>D) During the time the delete is occuring, does it go
ahead and start
>deleting entries from the data pages in the data files or
does it first log
>entries in the Log file and then deletes ?
>E) Finally if i did a backup log during the time the
delete is occuring (
>Say i noticed that the table with (nolock) option was
decrementing ) and
>then restored the logs on another database , will part of
the deletes be
>reflected on the other database if i specify the (nolock)
option since the
>delete did not complete
>I am just trying to understand what entries the TLog
contains.. I would
>appreciate if you could provide answers to all the 5
parts. I am using SQL
>2000
>Thank you
>
>.
>|||--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> A) When i run a delete against a table that has 50 million records based
> upon a where clause , what entries does the T log file hold ? Does it log
50
> million delete statements along with 50 million inserts just incase it
needs
> to rollback.
>
The log holds a copy of the record which was deleted...
> B) Also if there is a clustered index on the coulimn thats part of the
where
> clause, what does the Tlog file hold ?
NO change, the log has the copy of the deleted record.
> C) If there was a clustered index but not part of the column in the where
> clause, what does the Tlog contain ?
no change.
> D) During the time the delete is occuring, does it go ahead and start
> deleting entries from the data pages in the data files or does it first
log
> entries in the Log file and then deletes ?
>
Logging occurs first.
> E) Finally if i did a backup log during the time the delete is occuring (
> Say i noticed that the table with (nolock) option was decrementing ) and
> then restored the logs on another database , will part of the deletes be
> reflected on the other database if i specify the (nolock) option since the
> delete did not complete
>
After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction.
> I am just trying to understand what entries the TLog contains.. I would
> appreciate if you could provide answers to all the 5 parts. I am using SQL
> 2000
> Thank you
>
>|||Thanks so to answer 5, where you said
"After the restore, and recovery has run either all of the records will be
present or none of them...Each statement is a transaction."
What if i restored log with standby mode so users can read from this standby
database, will i see some deletes in effect with (nolock)
Thanks once again to you all
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:eTPtkTCWDHA.2008@.TK2MSFTNGP11.phx.gbl...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OuJAVQ#VDHA.392@.TK2MSFTNGP11.phx.gbl...
> > A) When i run a delete against a table that has 50 million records based
> > upon a where clause , what entries does the T log file hold ? Does it
log
> 50
> > million delete statements along with 50 million inserts just incase it
> needs
> > to rollback.
> >
> The log holds a copy of the record which was deleted...
> >
> > B) Also if there is a clustered index on the coulimn thats part of the
> where
> > clause, what does the Tlog file hold ?
> NO change, the log has the copy of the deleted record.
>
> >
> > C) If there was a clustered index but not part of the column in the
where
> > clause, what does the Tlog contain ?
> no change.
> >
> > D) During the time the delete is occuring, does it go ahead and start
> > deleting entries from the data pages in the data files or does it first
> log
> > entries in the Log file and then deletes ?
> >
> Logging occurs first.
> > E) Finally if i did a backup log during the time the delete is occuring
(
> > Say i noticed that the table with (nolock) option was decrementing ) and
> > then restored the logs on another database , will part of the deletes be
> > reflected on the other database if i specify the (nolock) option since
the
> > delete did not complete
> >
> After the restore, and recovery has run either all of the records will be
> present or none of them...Each statement is a transaction.
> > I am just trying to understand what entries the TLog contains.. I would
> > appreciate if you could provide answers to all the 5 parts. I am using
SQL
> > 2000
> >
> > Thank you
> >
> >
> >
>sql
Monday, March 19, 2012
Defragmenting Local Disk
Is there any performance gain from defragmenting the SQL
SERVER data and log file disk subsystem (or OS). I have
run the "Analyze" part of the windows disk defragmenter
and several of the data and log files have several
fragments within the file. Some of them have 20
fragments. Is there another tool that is preferred? Any
advice or past experienced is much appreciated.
Thanks,
Andy20 fragments is not much compared to the fragmentation you probably have int
ernally in the files. But sure,
stop the SQL Server service and do a defrag if you wish - no harm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Andy" <anonymous@.discussions.microsoft.com> wrote in message news:12e6801c41674$4b7a4c20$a
601280a@.phx.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||OS level defrag is a good thing. SQL Server might report 0% fragmentation
for internal and internal fragmentation within it's data structures. But
that's just for SQL.
SQL Server can't know anything about where the bits are stored at the OS.
Consider a situation where SQL reports no fragmentation, and you're doing a
table scan. Imagine that the file with the big table is scattered all over
disk. Yes, there's be a benefit to OS frag in this case.
However, one thing to consider... defragging at the OS level doesn't always
have as big an impact as some people might expect. SQL Server pre-allocates
space when you create a file. If you create a 100M file on a disk with no
fragmentation... and put 1M of data on it. The file still uses 100M at the
OS and it would be contiguous if the file was created when the disk was not
fragmented... if the disk later becomes fragmented... that 100M file will
still be contiguous.
OS level frag tends to help the most with db files that have grown a number
of times since they were created... or if you're creating DB files on a
disks that are already fragmented...
also... don't forget that you'll need to shut down SQL Server before doing
the defrag. Open files aren't defragged...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||Sure, just like any other file access, defragmenting your database files
will improve performance if they're badly fragmented. I'm not sure if 20
fragments is that bad, however. It really depends on the size of the files
(if the files are very small, 20 fragments may be bad; if they're quite
large, it's probably not affecting contiguous data access too much). Either
way, if you don't mind the downtime (you'll have to shut down the server in
order to defrag the files), it certainly won't hurt. As for defrag tools, I
don't like Windows' built in one. I personally have gotten very good
results from O&O Software Defrag.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy
SERVER data and log file disk subsystem (or OS). I have
run the "Analyze" part of the windows disk defragmenter
and several of the data and log files have several
fragments within the file. Some of them have 20
fragments. Is there another tool that is preferred? Any
advice or past experienced is much appreciated.
Thanks,
Andy20 fragments is not much compared to the fragmentation you probably have int
ernally in the files. But sure,
stop the SQL Server service and do a defrag if you wish - no harm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Andy" <anonymous@.discussions.microsoft.com> wrote in message news:12e6801c41674$4b7a4c20$a
601280a@.phx.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||OS level defrag is a good thing. SQL Server might report 0% fragmentation
for internal and internal fragmentation within it's data structures. But
that's just for SQL.
SQL Server can't know anything about where the bits are stored at the OS.
Consider a situation where SQL reports no fragmentation, and you're doing a
table scan. Imagine that the file with the big table is scattered all over
disk. Yes, there's be a benefit to OS frag in this case.
However, one thing to consider... defragging at the OS level doesn't always
have as big an impact as some people might expect. SQL Server pre-allocates
space when you create a file. If you create a 100M file on a disk with no
fragmentation... and put 1M of data on it. The file still uses 100M at the
OS and it would be contiguous if the file was created when the disk was not
fragmented... if the disk later becomes fragmented... that 100M file will
still be contiguous.
OS level frag tends to help the most with db files that have grown a number
of times since they were created... or if you're creating DB files on a
disks that are already fragmented...
also... don't forget that you'll need to shut down SQL Server before doing
the defrag. Open files aren't defragged...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||Sure, just like any other file access, defragmenting your database files
will improve performance if they're badly fragmented. I'm not sure if 20
fragments is that bad, however. It really depends on the size of the files
(if the files are very small, 20 fragments may be bad; if they're quite
large, it's probably not affecting contiguous data access too much). Either
way, if you don't mind the downtime (you'll have to shut down the server in
order to defrag the files), it certainly won't hurt. As for defrag tools, I
don't like Windows' built in one. I personally have gotten very good
results from O&O Software Defrag.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy
Defragmenting Data Files
Hello all,
Will data/log files be defragmented by Windows Defrag even if they are still
attached?
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> Will data/log files be defragmented by Windows Defrag even if they are sti
ll attached?
>
Nope... You have to stop SQL Server, or detach the database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
Thanks for the help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44EDEAF8.80705@.realsqlguy.com.
.
> Conan Kelly wrote:
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know for sure if that is true for the Windows defragger but
certainly not for things like DiskKeeper. I am pretty sure that the windows
defragger was based on DiskKeepers technology so I suspect it may be true
for that as well. The makers of DiskKeeper vividly state that their tool
will defrag sql server files even if the server is live. Not that I am
recommending that practice but it is certainly possible.
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Andrew J. Kelly wrote:
> I don't know for sure if that is true for the Windows defragger but
> certainly not for things like DiskKeeper. I am pretty sure that the windo
ws
> defragger was based on DiskKeepers technology so I suspect it may be true
> for that as well. The makers of DiskKeeper vividly state that their tool
> will defrag sql server files even if the server is live. Not that I am
> recommending that practice but it is certainly possible.
>
Yeah, I know they claim that, but the thought of something manipulating
my database files WHILE THEY'RE BEING USED, just scares me a bit. Seems
very risky to me.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I hear you man.
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2EBEB.10703@.realsqlguy.com...
> Andrew J. Kelly wrote:
> Yeah, I know they claim that, but the thought of something manipulating my
> database files WHILE THEY'RE BEING USED, just scares me a bit. Seems very
> risky to me.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Will data/log files be defragmented by Windows Defrag even if they are still
attached?
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> Will data/log files be defragmented by Windows Defrag even if they are sti
ll attached?
>
Nope... You have to stop SQL Server, or detach the database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
Thanks for the help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44EDEAF8.80705@.realsqlguy.com.
.
> Conan Kelly wrote:
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know for sure if that is true for the Windows defragger but
certainly not for things like DiskKeeper. I am pretty sure that the windows
defragger was based on DiskKeepers technology so I suspect it may be true
for that as well. The makers of DiskKeeper vividly state that their tool
will defrag sql server files even if the server is live. Not that I am
recommending that practice but it is certainly possible.
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Andrew J. Kelly wrote:
> I don't know for sure if that is true for the Windows defragger but
> certainly not for things like DiskKeeper. I am pretty sure that the windo
ws
> defragger was based on DiskKeepers technology so I suspect it may be true
> for that as well. The makers of DiskKeeper vividly state that their tool
> will defrag sql server files even if the server is live. Not that I am
> recommending that practice but it is certainly possible.
>
Yeah, I know they claim that, but the thought of something manipulating
my database files WHILE THEY'RE BEING USED, just scares me a bit. Seems
very risky to me.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I hear you man.
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2EBEB.10703@.realsqlguy.com...
> Andrew J. Kelly wrote:
> Yeah, I know they claim that, but the thought of something manipulating my
> database files WHILE THEY'RE BEING USED, just scares me a bit. Seems very
> risky to me.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Defragmenting Data Files
Hello all,
Will data/log files be defragmented by Windows Defrag even if they are still attached?
--
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> Will data/log files be defragmented by Windows Defrag even if they are still attached?
>
Nope... You have to stop SQL Server, or detach the database.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
Thanks for the help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> Will data/log files be defragmented by Windows Defrag even if they are still attached?
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know for sure if that is true for the Windows defragger but
certainly not for things like DiskKeeper. I am pretty sure that the windows
defragger was based on DiskKeepers technology so I suspect it may be true
for that as well. The makers of DiskKeeper vividly state that their tool
will defrag sql server files even if the server is live. Not that I am
recommending that practice but it is certainly possible.
--
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> Will data/log files be defragmented by Windows Defrag even if they are
>> still attached?
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Andrew J. Kelly wrote:
> I don't know for sure if that is true for the Windows defragger but
> certainly not for things like DiskKeeper. I am pretty sure that the windows
> defragger was based on DiskKeepers technology so I suspect it may be true
> for that as well. The makers of DiskKeeper vividly state that their tool
> will defrag sql server files even if the server is live. Not that I am
> recommending that practice but it is certainly possible.
>
Yeah, I know they claim that, but the thought of something manipulating
my database files WHILE THEY'RE BEING USED, just scares me a bit. Seems
very risky to me.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I hear you man.
--
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2EBEB.10703@.realsqlguy.com...
> Andrew J. Kelly wrote:
>> I don't know for sure if that is true for the Windows defragger but
>> certainly not for things like DiskKeeper. I am pretty sure that the
>> windows defragger was based on DiskKeepers technology so I suspect it may
>> be true for that as well. The makers of DiskKeeper vividly state that
>> their tool will defrag sql server files even if the server is live. Not
>> that I am recommending that practice but it is certainly possible.
> Yeah, I know they claim that, but the thought of something manipulating my
> database files WHILE THEY'RE BEING USED, just scares me a bit. Seems very
> risky to me.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Will data/log files be defragmented by Windows Defrag even if they are still attached?
--
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> Will data/log files be defragmented by Windows Defrag even if they are still attached?
>
Nope... You have to stop SQL Server, or detach the database.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
Thanks for the help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> Will data/log files be defragmented by Windows Defrag even if they are still attached?
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know for sure if that is true for the Windows defragger but
certainly not for things like DiskKeeper. I am pretty sure that the windows
defragger was based on DiskKeepers technology so I suspect it may be true
for that as well. The makers of DiskKeeper vividly state that their tool
will defrag sql server files even if the server is live. Not that I am
recommending that practice but it is certainly possible.
--
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> Will data/log files be defragmented by Windows Defrag even if they are
>> still attached?
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Andrew J. Kelly wrote:
> I don't know for sure if that is true for the Windows defragger but
> certainly not for things like DiskKeeper. I am pretty sure that the windows
> defragger was based on DiskKeepers technology so I suspect it may be true
> for that as well. The makers of DiskKeeper vividly state that their tool
> will defrag sql server files even if the server is live. Not that I am
> recommending that practice but it is certainly possible.
>
Yeah, I know they claim that, but the thought of something manipulating
my database files WHILE THEY'RE BEING USED, just scares me a bit. Seems
very risky to me.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I hear you man.
--
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2EBEB.10703@.realsqlguy.com...
> Andrew J. Kelly wrote:
>> I don't know for sure if that is true for the Windows defragger but
>> certainly not for things like DiskKeeper. I am pretty sure that the
>> windows defragger was based on DiskKeepers technology so I suspect it may
>> be true for that as well. The makers of DiskKeeper vividly state that
>> their tool will defrag sql server files even if the server is live. Not
>> that I am recommending that practice but it is certainly possible.
> Yeah, I know they claim that, but the thought of something manipulating my
> database files WHILE THEY'RE BEING USED, just scares me a bit. Seems very
> risky to me.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Sunday, March 11, 2012
Defrag SQL Server
All
Should the drives where the SQL Server data and log files be defragmenetd on
a regular basis?
ThanksNot unless you overuse autogrow and shrink. OF the database files weren't fragmented when you
created then and if you only had a reasonable amount of grow operation per database file you
wouldn't have fragmented database files. Some info here as well:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:B33CE701-AA9C-4AAC-ADBC-6E7AEEEB5645@.microsoft.com...
> All
> Should the drives where the SQL Server data and log files be defragmenetd on
> a regular basis?
> Thanks|||Hi David
To add to Tibor's comments. To defragment the disc you would need to stop
SQL Server if you are using the defragmenter supplied with windows, on many
production systems this is not an option. Products such as Diskkeeper can do
this while the server is running.
For a server dedicated to SQL Server you should not need to do this if the
initial file sizes are sufficient and you don't continually shrink the files.
Make sure that you have regular log backups to enable the log file to be
re-used and keep the log file size more constant.
If you are running SQL Server on (say) a development machine or
demonstration system, defragmenting can be very beneficial, I have seen
performance improvements of around 30% just by defragmenting the disc on such
systems.
John
"David" wrote:
> All
> Should the drives where the SQL Server data and log files be defragmenetd on
> a regular basis?
> Thanks
Should the drives where the SQL Server data and log files be defragmenetd on
a regular basis?
ThanksNot unless you overuse autogrow and shrink. OF the database files weren't fragmented when you
created then and if you only had a reasonable amount of grow operation per database file you
wouldn't have fragmented database files. Some info here as well:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:B33CE701-AA9C-4AAC-ADBC-6E7AEEEB5645@.microsoft.com...
> All
> Should the drives where the SQL Server data and log files be defragmenetd on
> a regular basis?
> Thanks|||Hi David
To add to Tibor's comments. To defragment the disc you would need to stop
SQL Server if you are using the defragmenter supplied with windows, on many
production systems this is not an option. Products such as Diskkeeper can do
this while the server is running.
For a server dedicated to SQL Server you should not need to do this if the
initial file sizes are sufficient and you don't continually shrink the files.
Make sure that you have regular log backups to enable the log file to be
re-used and keep the log file size more constant.
If you are running SQL Server on (say) a development machine or
demonstration system, defragmenting can be very beneficial, I have seen
performance improvements of around 30% just by defragmenting the disc on such
systems.
John
"David" wrote:
> All
> Should the drives where the SQL Server data and log files be defragmenetd on
> a regular basis?
> Thanks
Defrag SQL Server
All
Should the drives where the SQL Server data and log files be defragmenetd on
a regular basis?
ThanksNot unless you overuse autogrow and shrink. OF the database files weren't fr
agmented when you
created then and if you only had a reasonable amount of grow operation per d
atabase file you
wouldn't have fragmented database files. Some info here as well:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:B33CE701-AA9C-4AAC-ADBC-6E7AEEEB5645@.microsoft.com...
> All
> Should the drives where the SQL Server data and log files be defragmenetd
on
> a regular basis?
> Thanks|||Hi David
To add to Tibor's comments. To defragment the disc you would need to stop
SQL Server if you are using the defragmenter supplied with windows, on many
production systems this is not an option. Products such as Diskkeeper can do
this while the server is running.
For a server dedicated to SQL Server you should not need to do this if the
initial file sizes are sufficient and you don't continually shrink the files
.
Make sure that you have regular log backups to enable the log file to be
re-used and keep the log file size more constant.
If you are running SQL Server on (say) a development machine or
demonstration system, defragmenting can be very beneficial, I have seen
performance improvements of around 30% just by defragmenting the disc on suc
h
systems.
John
"David" wrote:
> All
> Should the drives where the SQL Server data and log files be defragmenetd
on
> a regular basis?
> Thanks
Should the drives where the SQL Server data and log files be defragmenetd on
a regular basis?
ThanksNot unless you overuse autogrow and shrink. OF the database files weren't fr
agmented when you
created then and if you only had a reasonable amount of grow operation per d
atabase file you
wouldn't have fragmented database files. Some info here as well:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:B33CE701-AA9C-4AAC-ADBC-6E7AEEEB5645@.microsoft.com...
> All
> Should the drives where the SQL Server data and log files be defragmenetd
on
> a regular basis?
> Thanks|||Hi David
To add to Tibor's comments. To defragment the disc you would need to stop
SQL Server if you are using the defragmenter supplied with windows, on many
production systems this is not an option. Products such as Diskkeeper can do
this while the server is running.
For a server dedicated to SQL Server you should not need to do this if the
initial file sizes are sufficient and you don't continually shrink the files
.
Make sure that you have regular log backups to enable the log file to be
re-used and keep the log file size more constant.
If you are running SQL Server on (say) a development machine or
demonstration system, defragmenting can be very beneficial, I have seen
performance improvements of around 30% just by defragmenting the disc on suc
h
systems.
John
"David" wrote:
> All
> Should the drives where the SQL Server data and log files be defragmenetd
on
> a regular basis?
> Thanks
Defrag large production table
I currently have one table, highly utilized, and it is 80% fragmented. I
need to defrag the index, but due to log shipping, I am unable to defrag the
index without creating a 2 gig log. A log this size will hose the pipe
between our production and offsite sql server. Any ideas on how to defrag
without creating such a huge log?Hi
You are probably going to take this hit regardless, what you may want to try
is to stop the log shipping and re-syncronise afterwards. Alternatively drop
all the indexes and re-create them one at a time allowing the log to be
shipped in-between ( I would also make sure that all indexes are necessary
and test the system with alternative indexes).
What you would need to ascertain is why the index is so fragmented and try
and rectify this.
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag t
he
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||I forgot to add...
Have you considered partitioning this table?
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag t
he
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||Just started a new job and came accross tables fragmented 80% or more;
however, they also have log shipping. Users are complaining about
performance and I can see why, but resolving the fragmentation issue is more
of a task than first anticipated.
I will look into partitioning the table, but I want to see if I defrag some
of the tables before I start the partitioning project.
"John Bell" wrote:
[vbcol=seagreen]
> I forgot to add...
> Have you considered partitioning this table?
> John
> "Patrick" wrote:
>|||Hi Patrick
It sounds like you certainly need to look at what the indexes are and if
they are really needed or if a better indexing scheme could be employed.
John
"Patrick" wrote:
[vbcol=seagreen]
> Just started a new job and came accross tables fragmented 80% or more;
> however, they also have log shipping. Users are complaining about
> performance and I can see why, but resolving the fragmentation issue is mo
re
> of a task than first anticipated.
> I will look into partitioning the table, but I want to see if I defrag som
e
> of the tables before I start the partitioning project.
> "John Bell" wrote:
>|||Will switching from Full to Bulk recovery during DEFRAG and then switching
back from Bulk to Full recovery break log shipping?
"John Bell" wrote:
[vbcol=seagreen]
> Hi Patrick
> It sounds like you certainly need to look at what the indexes are and if
> they are really needed or if a better indexing scheme could be employed.
> John
> "Patrick" wrote:
>|||Hi Patrick
You can use log shipping on a BULK_LOGGED database, but I am not sure if you
can switch half way through.
Have you tried decreasing the log shipping interval (make it more frequent)?
Have easy would it be to switch of log shipping during this interval and
restore afterwards?
John
"Patrick" wrote:
[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||That won't help you at all. Creating an index in Bulk Logged mode will send
less data to the log. But the log backup will be just as large as before
since it copies the extents that were modified in a Bulk Logged operation to
the log backup file. IndexDefrag has the potential to send even more data
to the log file than a DBREINDEX. This is especially true if the file is
heavily fragmented. The only thing is you can control it better as to when
this happens. You can start INDEXDEFRAG and stop it shortly afterwards.
Issue a Log backup then restart it, stop it etc. until it is complete. You
can't get away from logging the data but you do have some options to deal
with it. Ultimately if this reindexing is a problem then Johns suggestions
for partitioning and proper fill factors is something you may want to look
into.
Andrew J. Kelly SQL MVP
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:D5B901A7-135E-40D3-AAFF-B9CC30D533E6@.microsoft.com...[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||Hi
A different approach to the stop/backup/restart method might be to use DBCC
SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
therefore limiting the number of indexes defragmented per go. Once all
completed then do a final DBCC DBREINDEX.
John
"Patrick" wrote:
[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||John,
Can you explain what you mean by (or want to accomplish) by doing a final
DBREINDEX? I am confused by the statement.
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...[vbcol=seagreen]
> Hi
> A different approach to the stop/backup/restart method might be to use
> DBCC
> SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> therefore limiting the number of indexes defragmented per go. Once all
> completed then do a final DBCC DBREINDEX.
> John
> "Patrick" wrote:
>
need to defrag the index, but due to log shipping, I am unable to defrag the
index without creating a 2 gig log. A log this size will hose the pipe
between our production and offsite sql server. Any ideas on how to defrag
without creating such a huge log?Hi
You are probably going to take this hit regardless, what you may want to try
is to stop the log shipping and re-syncronise afterwards. Alternatively drop
all the indexes and re-create them one at a time allowing the log to be
shipped in-between ( I would also make sure that all indexes are necessary
and test the system with alternative indexes).
What you would need to ascertain is why the index is so fragmented and try
and rectify this.
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag t
he
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||I forgot to add...
Have you considered partitioning this table?
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag t
he
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||Just started a new job and came accross tables fragmented 80% or more;
however, they also have log shipping. Users are complaining about
performance and I can see why, but resolving the fragmentation issue is more
of a task than first anticipated.
I will look into partitioning the table, but I want to see if I defrag some
of the tables before I start the partitioning project.
"John Bell" wrote:
[vbcol=seagreen]
> I forgot to add...
> Have you considered partitioning this table?
> John
> "Patrick" wrote:
>|||Hi Patrick
It sounds like you certainly need to look at what the indexes are and if
they are really needed or if a better indexing scheme could be employed.
John
"Patrick" wrote:
[vbcol=seagreen]
> Just started a new job and came accross tables fragmented 80% or more;
> however, they also have log shipping. Users are complaining about
> performance and I can see why, but resolving the fragmentation issue is mo
re
> of a task than first anticipated.
> I will look into partitioning the table, but I want to see if I defrag som
e
> of the tables before I start the partitioning project.
> "John Bell" wrote:
>|||Will switching from Full to Bulk recovery during DEFRAG and then switching
back from Bulk to Full recovery break log shipping?
"John Bell" wrote:
[vbcol=seagreen]
> Hi Patrick
> It sounds like you certainly need to look at what the indexes are and if
> they are really needed or if a better indexing scheme could be employed.
> John
> "Patrick" wrote:
>|||Hi Patrick
You can use log shipping on a BULK_LOGGED database, but I am not sure if you
can switch half way through.
Have you tried decreasing the log shipping interval (make it more frequent)?
Have easy would it be to switch of log shipping during this interval and
restore afterwards?
John
"Patrick" wrote:
[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||That won't help you at all. Creating an index in Bulk Logged mode will send
less data to the log. But the log backup will be just as large as before
since it copies the extents that were modified in a Bulk Logged operation to
the log backup file. IndexDefrag has the potential to send even more data
to the log file than a DBREINDEX. This is especially true if the file is
heavily fragmented. The only thing is you can control it better as to when
this happens. You can start INDEXDEFRAG and stop it shortly afterwards.
Issue a Log backup then restart it, stop it etc. until it is complete. You
can't get away from logging the data but you do have some options to deal
with it. Ultimately if this reindexing is a problem then Johns suggestions
for partitioning and proper fill factors is something you may want to look
into.
Andrew J. Kelly SQL MVP
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:D5B901A7-135E-40D3-AAFF-B9CC30D533E6@.microsoft.com...[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||Hi
A different approach to the stop/backup/restart method might be to use DBCC
SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
therefore limiting the number of indexes defragmented per go. Once all
completed then do a final DBCC DBREINDEX.
John
"Patrick" wrote:
[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||John,
Can you explain what you mean by (or want to accomplish) by doing a final
DBREINDEX? I am confused by the statement.
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...[vbcol=seagreen]
> Hi
> A different approach to the stop/backup/restart method might be to use
> DBCC
> SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> therefore limiting the number of indexes defragmented per go. Once all
> completed then do a final DBCC DBREINDEX.
> John
> "Patrick" wrote:
>
Defrag large production table
I currently have one table, highly utilized, and it is 80% fragmented. I
need to defrag the index, but due to log shipping, I am unable to defrag the
index without creating a 2 gig log. A log this size will hose the pipe
between our production and offsite sql server. Any ideas on how to defrag
without creating such a huge log?Hi
You are probably going to take this hit regardless, what you may want to try
is to stop the log shipping and re-syncronise afterwards. Alternatively drop
all the indexes and re-create them one at a time allowing the log to be
shipped in-between ( I would also make sure that all indexes are necessary
and test the system with alternative indexes).
What you would need to ascertain is why the index is so fragmented and try
and rectify this.
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag the
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||I forgot to add...
Have you considered partitioning this table?
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag the
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||Just started a new job and came accross tables fragmented 80% or more;
however, they also have log shipping. Users are complaining about
performance and I can see why, but resolving the fragmentation issue is more
of a task than first anticipated.
I will look into partitioning the table, but I want to see if I defrag some
of the tables before I start the partitioning project.
"John Bell" wrote:
> I forgot to add...
> Have you considered partitioning this table?
> John
> "Patrick" wrote:
> > I currently have one table, highly utilized, and it is 80% fragmented. I
> > need to defrag the index, but due to log shipping, I am unable to defrag the
> > index without creating a 2 gig log. A log this size will hose the pipe
> > between our production and offsite sql server. Any ideas on how to defrag
> > without creating such a huge log?
> >
> >|||Hi Patrick
It sounds like you certainly need to look at what the indexes are and if
they are really needed or if a better indexing scheme could be employed.
John
"Patrick" wrote:
> Just started a new job and came accross tables fragmented 80% or more;
> however, they also have log shipping. Users are complaining about
> performance and I can see why, but resolving the fragmentation issue is more
> of a task than first anticipated.
> I will look into partitioning the table, but I want to see if I defrag some
> of the tables before I start the partitioning project.
> "John Bell" wrote:
> > I forgot to add...
> >
> > Have you considered partitioning this table?
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > index without creating a 2 gig log. A log this size will hose the pipe
> > > between our production and offsite sql server. Any ideas on how to defrag
> > > without creating such a huge log?
> > >
> > >|||Will switching from Full to Bulk recovery during DEFRAG and then switching
back from Bulk to Full recovery break log shipping?
"John Bell" wrote:
> Hi Patrick
> It sounds like you certainly need to look at what the indexes are and if
> they are really needed or if a better indexing scheme could be employed.
> John
> "Patrick" wrote:
> > Just started a new job and came accross tables fragmented 80% or more;
> > however, they also have log shipping. Users are complaining about
> > performance and I can see why, but resolving the fragmentation issue is more
> > of a task than first anticipated.
> >
> > I will look into partitioning the table, but I want to see if I defrag some
> > of the tables before I start the partitioning project.
> >
> > "John Bell" wrote:
> >
> > > I forgot to add...
> > >
> > > Have you considered partitioning this table?
> > >
> > > John
> > >
> > > "Patrick" wrote:
> > >
> > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > without creating such a huge log?
> > > >
> > > >|||Hi Patrick
You can use log shipping on a BULK_LOGGED database, but I am not sure if you
can switch half way through.
Have you tried decreasing the log shipping interval (make it more frequent)?
Have easy would it be to switch of log shipping during this interval and
restore afterwards?
John
"Patrick" wrote:
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
> > Hi Patrick
> >
> > It sounds like you certainly need to look at what the indexes are and if
> > they are really needed or if a better indexing scheme could be employed.
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > Just started a new job and came accross tables fragmented 80% or more;
> > > however, they also have log shipping. Users are complaining about
> > > performance and I can see why, but resolving the fragmentation issue is more
> > > of a task than first anticipated.
> > >
> > > I will look into partitioning the table, but I want to see if I defrag some
> > > of the tables before I start the partitioning project.
> > >
> > > "John Bell" wrote:
> > >
> > > > I forgot to add...
> > > >
> > > > Have you considered partitioning this table?
> > > >
> > > > John
> > > >
> > > > "Patrick" wrote:
> > > >
> > > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > > without creating such a huge log?
> > > > >
> > > > >|||That won't help you at all. Creating an index in Bulk Logged mode will send
less data to the log. But the log backup will be just as large as before
since it copies the extents that were modified in a Bulk Logged operation to
the log backup file. IndexDefrag has the potential to send even more data
to the log file than a DBREINDEX. This is especially true if the file is
heavily fragmented. The only thing is you can control it better as to when
this happens. You can start INDEXDEFRAG and stop it shortly afterwards.
Issue a Log backup then restart it, stop it etc. until it is complete. You
can't get away from logging the data but you do have some options to deal
with it. Ultimately if this reindexing is a problem then Johns suggestions
for partitioning and proper fill factors is something you may want to look
into.
--
Andrew J. Kelly SQL MVP
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:D5B901A7-135E-40D3-AAFF-B9CC30D533E6@.microsoft.com...
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>> Hi Patrick
>> It sounds like you certainly need to look at what the indexes are and if
>> they are really needed or if a better indexing scheme could be employed.
>> John
>> "Patrick" wrote:
>> > Just started a new job and came accross tables fragmented 80% or more;
>> > however, they also have log shipping. Users are complaining about
>> > performance and I can see why, but resolving the fragmentation issue is
>> > more
>> > of a task than first anticipated.
>> >
>> > I will look into partitioning the table, but I want to see if I defrag
>> > some
>> > of the tables before I start the partitioning project.
>> >
>> > "John Bell" wrote:
>> >
>> > > I forgot to add...
>> > >
>> > > Have you considered partitioning this table?
>> > >
>> > > John
>> > >
>> > > "Patrick" wrote:
>> > >
>> > > > I currently have one table, highly utilized, and it is 80%
>> > > > fragmented. I
>> > > > need to defrag the index, but due to log shipping, I am unable to
>> > > > defrag the
>> > > > index without creating a 2 gig log. A log this size will hose the
>> > > > pipe
>> > > > between our production and offsite sql server. Any ideas on how to
>> > > > defrag
>> > > > without creating such a huge log?
>> > > >
>> > > >|||Hi
A different approach to the stop/backup/restart method might be to use DBCC
SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
therefore limiting the number of indexes defragmented per go. Once all
completed then do a final DBCC DBREINDEX.
John
"Patrick" wrote:
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
> > Hi Patrick
> >
> > It sounds like you certainly need to look at what the indexes are and if
> > they are really needed or if a better indexing scheme could be employed.
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > Just started a new job and came accross tables fragmented 80% or more;
> > > however, they also have log shipping. Users are complaining about
> > > performance and I can see why, but resolving the fragmentation issue is more
> > > of a task than first anticipated.
> > >
> > > I will look into partitioning the table, but I want to see if I defrag some
> > > of the tables before I start the partitioning project.
> > >
> > > "John Bell" wrote:
> > >
> > > > I forgot to add...
> > > >
> > > > Have you considered partitioning this table?
> > > >
> > > > John
> > > >
> > > > "Patrick" wrote:
> > > >
> > > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > > without creating such a huge log?
> > > > >
> > > > >|||John,
Can you explain what you mean by (or want to accomplish) by doing a final
DBREINDEX? I am confused by the statement.
--
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> Hi
> A different approach to the stop/backup/restart method might be to use
> DBCC
> SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> therefore limiting the number of indexes defragmented per go. Once all
> completed then do a final DBCC DBREINDEX.
> John
> "Patrick" wrote:
>> Will switching from Full to Bulk recovery during DEFRAG and then
>> switching
>> back from Bulk to Full recovery break log shipping?
>> "John Bell" wrote:
>> > Hi Patrick
>> >
>> > It sounds like you certainly need to look at what the indexes are and
>> > if
>> > they are really needed or if a better indexing scheme could be
>> > employed.
>> >
>> > John
>> >
>> > "Patrick" wrote:
>> >
>> > > Just started a new job and came accross tables fragmented 80% or
>> > > more;
>> > > however, they also have log shipping. Users are complaining about
>> > > performance and I can see why, but resolving the fragmentation issue
>> > > is more
>> > > of a task than first anticipated.
>> > >
>> > > I will look into partitioning the table, but I want to see if I
>> > > defrag some
>> > > of the tables before I start the partitioning project.
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > I forgot to add...
>> > > >
>> > > > Have you considered partitioning this table?
>> > > >
>> > > > John
>> > > >
>> > > > "Patrick" wrote:
>> > > >
>> > > > > I currently have one table, highly utilized, and it is 80%
>> > > > > fragmented. I
>> > > > > need to defrag the index, but due to log shipping, I am unable to
>> > > > > defrag the
>> > > > > index without creating a 2 gig log. A log this size will hose
>> > > > > the pipe
>> > > > > between our production and offsite sql server. Any ideas on how
>> > > > > to defrag
>> > > > > without creating such a huge log?
>> > > > >
>> > > > >|||Hi Andrew
The final dbreindex would be to defragment root and intermediate level
pages, and hopefully if the leaf level pages were already defragmented, less
work would be required to defragment them. It is a theory that would need to
be tested, certainly if the root/intermediate nodes were very fragmented
there may be less gain from the indexdefrags, there may be a point where you
don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be very
fragmented.
John
"Andrew J. Kelly" wrote:
> John,
> Can you explain what you mean by (or want to accomplish) by doing a final
> DBREINDEX? I am confused by the statement.
> --
> Andrew J. Kelly SQL MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> > Hi
> >
> > A different approach to the stop/backup/restart method might be to use
> > DBCC
> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> > therefore limiting the number of indexes defragmented per go. Once all
> > completed then do a final DBCC DBREINDEX.
> >
> > John
> >
> > "Patrick" wrote:
> >
> >> Will switching from Full to Bulk recovery during DEFRAG and then
> >> switching
> >> back from Bulk to Full recovery break log shipping?
> >>
> >> "John Bell" wrote:
> >>
> >> > Hi Patrick
> >> >
> >> > It sounds like you certainly need to look at what the indexes are and
> >> > if
> >> > they are really needed or if a better indexing scheme could be
> >> > employed.
> >> >
> >> > John
> >> >
> >> > "Patrick" wrote:
> >> >
> >> > > Just started a new job and came accross tables fragmented 80% or
> >> > > more;
> >> > > however, they also have log shipping. Users are complaining about
> >> > > performance and I can see why, but resolving the fragmentation issue
> >> > > is more
> >> > > of a task than first anticipated.
> >> > >
> >> > > I will look into partitioning the table, but I want to see if I
> >> > > defrag some
> >> > > of the tables before I start the partitioning project.
> >> > >
> >> > > "John Bell" wrote:
> >> > >
> >> > > > I forgot to add...
> >> > > >
> >> > > > Have you considered partitioning this table?
> >> > > >
> >> > > > John
> >> > > >
> >> > > > "Patrick" wrote:
> >> > > >
> >> > > > > I currently have one table, highly utilized, and it is 80%
> >> > > > > fragmented. I
> >> > > > > need to defrag the index, but due to log shipping, I am unable to
> >> > > > > defrag the
> >> > > > > index without creating a 2 gig log. A log this size will hose
> >> > > > > the pipe
> >> > > > > between our production and offsite sql server. Any ideas on how
> >> > > > > to defrag
> >> > > > > without creating such a huge log?
> >> > > > >
> >> > > > >
>
>|||OK I see what you were after now. But unfortunately it would not matter too
much in his case if the table was heavily or lightly fragmented. When you
issue a DBREINDEX it will completely rebuild the indexes in a new section of
the data files. This will always result in basically the same amount of
pages or extents being logged or needed to be backed up in the case of
BulkLogged recovery model. It is true that the work itself may be a little
easier to sort the data for the rebuild when there is less fragmentation.
But as far as log shipping goes it would not matter either way. Actually
that way would be worse since you would log it twice. Once for Indexdefrag
and once for DBreindex.
--
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:082B1590-3F81-4D82-8AD3-F008C974EC4B@.microsoft.com...
> Hi Andrew
> The final dbreindex would be to defragment root and intermediate level
> pages, and hopefully if the leaf level pages were already defragmented,
> less
> work would be required to defragment them. It is a theory that would need
> to
> be tested, certainly if the root/intermediate nodes were very fragmented
> there may be less gain from the indexdefrags, there may be a point where
> you
> don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be
> very
> fragmented.
> John
>
> "Andrew J. Kelly" wrote:
>> John,
>> Can you explain what you mean by (or want to accomplish) by doing a final
>> DBREINDEX? I am confused by the statement.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
>> > Hi
>> >
>> > A different approach to the stop/backup/restart method might be to use
>> > DBCC
>> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of
>> > fragmentation,
>> > therefore limiting the number of indexes defragmented per go. Once all
>> > completed then do a final DBCC DBREINDEX.
>> >
>> > John
>> >
>> > "Patrick" wrote:
>> >
>> >> Will switching from Full to Bulk recovery during DEFRAG and then
>> >> switching
>> >> back from Bulk to Full recovery break log shipping?
>> >>
>> >> "John Bell" wrote:
>> >>
>> >> > Hi Patrick
>> >> >
>> >> > It sounds like you certainly need to look at what the indexes are
>> >> > and
>> >> > if
>> >> > they are really needed or if a better indexing scheme could be
>> >> > employed.
>> >> >
>> >> > John
>> >> >
>> >> > "Patrick" wrote:
>> >> >
>> >> > > Just started a new job and came accross tables fragmented 80% or
>> >> > > more;
>> >> > > however, they also have log shipping. Users are complaining about
>> >> > > performance and I can see why, but resolving the fragmentation
>> >> > > issue
>> >> > > is more
>> >> > > of a task than first anticipated.
>> >> > >
>> >> > > I will look into partitioning the table, but I want to see if I
>> >> > > defrag some
>> >> > > of the tables before I start the partitioning project.
>> >> > >
>> >> > > "John Bell" wrote:
>> >> > >
>> >> > > > I forgot to add...
>> >> > > >
>> >> > > > Have you considered partitioning this table?
>> >> > > >
>> >> > > > John
>> >> > > >
>> >> > > > "Patrick" wrote:
>> >> > > >
>> >> > > > > I currently have one table, highly utilized, and it is 80%
>> >> > > > > fragmented. I
>> >> > > > > need to defrag the index, but due to log shipping, I am unable
>> >> > > > > to
>> >> > > > > defrag the
>> >> > > > > index without creating a 2 gig log. A log this size will hose
>> >> > > > > the pipe
>> >> > > > > between our production and offsite sql server. Any ideas on
>> >> > > > > how
>> >> > > > > to defrag
>> >> > > > > without creating such a huge log?
>> >> > > > >
>> >> > > > >
>>|||Hi Andrew
Thinking of it that way, you are right, the last DBREINDEX would increase
the logging. I would still favour a more controlled defragmentation than one
that would entailed killed of the reindexing!
John
"Andrew J. Kelly" wrote:
> OK I see what you were after now. But unfortunately it would not matter too
> much in his case if the table was heavily or lightly fragmented. When you
> issue a DBREINDEX it will completely rebuild the indexes in a new section of
> the data files. This will always result in basically the same amount of
> pages or extents being logged or needed to be backed up in the case of
> BulkLogged recovery model. It is true that the work itself may be a little
> easier to sort the data for the rebuild when there is less fragmentation.
> But as far as log shipping goes it would not matter either way. Actually
> that way would be worse since you would log it twice. Once for Indexdefrag
> and once for DBreindex.
> --
> Andrew J. Kelly SQL MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:082B1590-3F81-4D82-8AD3-F008C974EC4B@.microsoft.com...
> > Hi Andrew
> >
> > The final dbreindex would be to defragment root and intermediate level
> > pages, and hopefully if the leaf level pages were already defragmented,
> > less
> > work would be required to defragment them. It is a theory that would need
> > to
> > be tested, certainly if the root/intermediate nodes were very fragmented
> > there may be less gain from the indexdefrags, there may be a point where
> > you
> > don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be
> > very
> > fragmented.
> >
> > John
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> John,
> >>
> >> Can you explain what you mean by (or want to accomplish) by doing a final
> >> DBREINDEX? I am confused by the statement.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> >> > Hi
> >> >
> >> > A different approach to the stop/backup/restart method might be to use
> >> > DBCC
> >> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of
> >> > fragmentation,
> >> > therefore limiting the number of indexes defragmented per go. Once all
> >> > completed then do a final DBCC DBREINDEX.
> >> >
> >> > John
> >> >
> >> > "Patrick" wrote:
> >> >
> >> >> Will switching from Full to Bulk recovery during DEFRAG and then
> >> >> switching
> >> >> back from Bulk to Full recovery break log shipping?
> >> >>
> >> >> "John Bell" wrote:
> >> >>
> >> >> > Hi Patrick
> >> >> >
> >> >> > It sounds like you certainly need to look at what the indexes are
> >> >> > and
> >> >> > if
> >> >> > they are really needed or if a better indexing scheme could be
> >> >> > employed.
> >> >> >
> >> >> > John
> >> >> >
> >> >> > "Patrick" wrote:
> >> >> >
> >> >> > > Just started a new job and came accross tables fragmented 80% or
> >> >> > > more;
> >> >> > > however, they also have log shipping. Users are complaining about
> >> >> > > performance and I can see why, but resolving the fragmentation
> >> >> > > issue
> >> >> > > is more
> >> >> > > of a task than first anticipated.
> >> >> > >
> >> >> > > I will look into partitioning the table, but I want to see if I
> >> >> > > defrag some
> >> >> > > of the tables before I start the partitioning project.
> >> >> > >
> >> >> > > "John Bell" wrote:
> >> >> > >
> >> >> > > > I forgot to add...
> >> >> > > >
> >> >> > > > Have you considered partitioning this table?
> >> >> > > >
> >> >> > > > John
> >> >> > > >
> >> >> > > > "Patrick" wrote:
> >> >> > > >
> >> >> > > > > I currently have one table, highly utilized, and it is 80%
> >> >> > > > > fragmented. I
> >> >> > > > > need to defrag the index, but due to log shipping, I am unable
> >> >> > > > > to
> >> >> > > > > defrag the
> >> >> > > > > index without creating a 2 gig log. A log this size will hose
> >> >> > > > > the pipe
> >> >> > > > > between our production and offsite sql server. Any ideas on
> >> >> > > > > how
> >> >> > > > > to defrag
> >> >> > > > > without creating such a huge log?
> >> >> > > > >
> >> >> > > > >
> >>
> >>
> >>
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B4E0C43-B5BE-4631-AE23-0AF894DCC3F6@.microsoft.com...
> Hi Andrew
> Thinking of it that way, you are right, the last DBREINDEX would increase
> the logging. I would still favour a more controlled defragmentation than
> one
> that would entailed killed of the reindexing!
> John
Can you explain what you mean by "controlled defragmentation?" I know you
can use dbcc showcontig results in logic to determine whether or not to
defrag (i.e., "if more than 20% fragmented, then defrag"). And I know you
can run dbcc indexdefrag for a while and stop it at any point. But I was
unaware that you could control the amount of defragmentation dbcc
indexdefrag does.
Thanks
Karen|||Hi Karen
You have described what I would call a controlled defragmentation, only
defrag tables/indexes that are necessary, choosing whether to do a DBCC
DBREINDEX or DBCC INDEXDEFRAG as necessary, rather than having (say) a timer
that kills the process part way through.
Without partitioning then you may have to resort to killing off the reindex
process.
John
"Karen Collins" wrote:
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:8B4E0C43-B5BE-4631-AE23-0AF894DCC3F6@.microsoft.com...
> > Hi Andrew
> >
> > Thinking of it that way, you are right, the last DBREINDEX would increase
> > the logging. I would still favour a more controlled defragmentation than
> > one
> > that would entailed killed of the reindexing!
> >
> > John
> Can you explain what you mean by "controlled defragmentation?" I know you
> can use dbcc showcontig results in logic to determine whether or not to
> defrag (i.e., "if more than 20% fragmented, then defrag"). And I know you
> can run dbcc indexdefrag for a while and stop it at any point. But I was
> unaware that you could control the amount of defragmentation dbcc
> indexdefrag does.
> Thanks
> Karen
>
>
need to defrag the index, but due to log shipping, I am unable to defrag the
index without creating a 2 gig log. A log this size will hose the pipe
between our production and offsite sql server. Any ideas on how to defrag
without creating such a huge log?Hi
You are probably going to take this hit regardless, what you may want to try
is to stop the log shipping and re-syncronise afterwards. Alternatively drop
all the indexes and re-create them one at a time allowing the log to be
shipped in-between ( I would also make sure that all indexes are necessary
and test the system with alternative indexes).
What you would need to ascertain is why the index is so fragmented and try
and rectify this.
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag the
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||I forgot to add...
Have you considered partitioning this table?
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag the
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||Just started a new job and came accross tables fragmented 80% or more;
however, they also have log shipping. Users are complaining about
performance and I can see why, but resolving the fragmentation issue is more
of a task than first anticipated.
I will look into partitioning the table, but I want to see if I defrag some
of the tables before I start the partitioning project.
"John Bell" wrote:
> I forgot to add...
> Have you considered partitioning this table?
> John
> "Patrick" wrote:
> > I currently have one table, highly utilized, and it is 80% fragmented. I
> > need to defrag the index, but due to log shipping, I am unable to defrag the
> > index without creating a 2 gig log. A log this size will hose the pipe
> > between our production and offsite sql server. Any ideas on how to defrag
> > without creating such a huge log?
> >
> >|||Hi Patrick
It sounds like you certainly need to look at what the indexes are and if
they are really needed or if a better indexing scheme could be employed.
John
"Patrick" wrote:
> Just started a new job and came accross tables fragmented 80% or more;
> however, they also have log shipping. Users are complaining about
> performance and I can see why, but resolving the fragmentation issue is more
> of a task than first anticipated.
> I will look into partitioning the table, but I want to see if I defrag some
> of the tables before I start the partitioning project.
> "John Bell" wrote:
> > I forgot to add...
> >
> > Have you considered partitioning this table?
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > index without creating a 2 gig log. A log this size will hose the pipe
> > > between our production and offsite sql server. Any ideas on how to defrag
> > > without creating such a huge log?
> > >
> > >|||Will switching from Full to Bulk recovery during DEFRAG and then switching
back from Bulk to Full recovery break log shipping?
"John Bell" wrote:
> Hi Patrick
> It sounds like you certainly need to look at what the indexes are and if
> they are really needed or if a better indexing scheme could be employed.
> John
> "Patrick" wrote:
> > Just started a new job and came accross tables fragmented 80% or more;
> > however, they also have log shipping. Users are complaining about
> > performance and I can see why, but resolving the fragmentation issue is more
> > of a task than first anticipated.
> >
> > I will look into partitioning the table, but I want to see if I defrag some
> > of the tables before I start the partitioning project.
> >
> > "John Bell" wrote:
> >
> > > I forgot to add...
> > >
> > > Have you considered partitioning this table?
> > >
> > > John
> > >
> > > "Patrick" wrote:
> > >
> > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > without creating such a huge log?
> > > >
> > > >|||Hi Patrick
You can use log shipping on a BULK_LOGGED database, but I am not sure if you
can switch half way through.
Have you tried decreasing the log shipping interval (make it more frequent)?
Have easy would it be to switch of log shipping during this interval and
restore afterwards?
John
"Patrick" wrote:
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
> > Hi Patrick
> >
> > It sounds like you certainly need to look at what the indexes are and if
> > they are really needed or if a better indexing scheme could be employed.
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > Just started a new job and came accross tables fragmented 80% or more;
> > > however, they also have log shipping. Users are complaining about
> > > performance and I can see why, but resolving the fragmentation issue is more
> > > of a task than first anticipated.
> > >
> > > I will look into partitioning the table, but I want to see if I defrag some
> > > of the tables before I start the partitioning project.
> > >
> > > "John Bell" wrote:
> > >
> > > > I forgot to add...
> > > >
> > > > Have you considered partitioning this table?
> > > >
> > > > John
> > > >
> > > > "Patrick" wrote:
> > > >
> > > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > > without creating such a huge log?
> > > > >
> > > > >|||That won't help you at all. Creating an index in Bulk Logged mode will send
less data to the log. But the log backup will be just as large as before
since it copies the extents that were modified in a Bulk Logged operation to
the log backup file. IndexDefrag has the potential to send even more data
to the log file than a DBREINDEX. This is especially true if the file is
heavily fragmented. The only thing is you can control it better as to when
this happens. You can start INDEXDEFRAG and stop it shortly afterwards.
Issue a Log backup then restart it, stop it etc. until it is complete. You
can't get away from logging the data but you do have some options to deal
with it. Ultimately if this reindexing is a problem then Johns suggestions
for partitioning and proper fill factors is something you may want to look
into.
--
Andrew J. Kelly SQL MVP
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:D5B901A7-135E-40D3-AAFF-B9CC30D533E6@.microsoft.com...
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>> Hi Patrick
>> It sounds like you certainly need to look at what the indexes are and if
>> they are really needed or if a better indexing scheme could be employed.
>> John
>> "Patrick" wrote:
>> > Just started a new job and came accross tables fragmented 80% or more;
>> > however, they also have log shipping. Users are complaining about
>> > performance and I can see why, but resolving the fragmentation issue is
>> > more
>> > of a task than first anticipated.
>> >
>> > I will look into partitioning the table, but I want to see if I defrag
>> > some
>> > of the tables before I start the partitioning project.
>> >
>> > "John Bell" wrote:
>> >
>> > > I forgot to add...
>> > >
>> > > Have you considered partitioning this table?
>> > >
>> > > John
>> > >
>> > > "Patrick" wrote:
>> > >
>> > > > I currently have one table, highly utilized, and it is 80%
>> > > > fragmented. I
>> > > > need to defrag the index, but due to log shipping, I am unable to
>> > > > defrag the
>> > > > index without creating a 2 gig log. A log this size will hose the
>> > > > pipe
>> > > > between our production and offsite sql server. Any ideas on how to
>> > > > defrag
>> > > > without creating such a huge log?
>> > > >
>> > > >|||Hi
A different approach to the stop/backup/restart method might be to use DBCC
SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
therefore limiting the number of indexes defragmented per go. Once all
completed then do a final DBCC DBREINDEX.
John
"Patrick" wrote:
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
> > Hi Patrick
> >
> > It sounds like you certainly need to look at what the indexes are and if
> > they are really needed or if a better indexing scheme could be employed.
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > Just started a new job and came accross tables fragmented 80% or more;
> > > however, they also have log shipping. Users are complaining about
> > > performance and I can see why, but resolving the fragmentation issue is more
> > > of a task than first anticipated.
> > >
> > > I will look into partitioning the table, but I want to see if I defrag some
> > > of the tables before I start the partitioning project.
> > >
> > > "John Bell" wrote:
> > >
> > > > I forgot to add...
> > > >
> > > > Have you considered partitioning this table?
> > > >
> > > > John
> > > >
> > > > "Patrick" wrote:
> > > >
> > > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > > without creating such a huge log?
> > > > >
> > > > >|||John,
Can you explain what you mean by (or want to accomplish) by doing a final
DBREINDEX? I am confused by the statement.
--
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> Hi
> A different approach to the stop/backup/restart method might be to use
> DBCC
> SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> therefore limiting the number of indexes defragmented per go. Once all
> completed then do a final DBCC DBREINDEX.
> John
> "Patrick" wrote:
>> Will switching from Full to Bulk recovery during DEFRAG and then
>> switching
>> back from Bulk to Full recovery break log shipping?
>> "John Bell" wrote:
>> > Hi Patrick
>> >
>> > It sounds like you certainly need to look at what the indexes are and
>> > if
>> > they are really needed or if a better indexing scheme could be
>> > employed.
>> >
>> > John
>> >
>> > "Patrick" wrote:
>> >
>> > > Just started a new job and came accross tables fragmented 80% or
>> > > more;
>> > > however, they also have log shipping. Users are complaining about
>> > > performance and I can see why, but resolving the fragmentation issue
>> > > is more
>> > > of a task than first anticipated.
>> > >
>> > > I will look into partitioning the table, but I want to see if I
>> > > defrag some
>> > > of the tables before I start the partitioning project.
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > I forgot to add...
>> > > >
>> > > > Have you considered partitioning this table?
>> > > >
>> > > > John
>> > > >
>> > > > "Patrick" wrote:
>> > > >
>> > > > > I currently have one table, highly utilized, and it is 80%
>> > > > > fragmented. I
>> > > > > need to defrag the index, but due to log shipping, I am unable to
>> > > > > defrag the
>> > > > > index without creating a 2 gig log. A log this size will hose
>> > > > > the pipe
>> > > > > between our production and offsite sql server. Any ideas on how
>> > > > > to defrag
>> > > > > without creating such a huge log?
>> > > > >
>> > > > >|||Hi Andrew
The final dbreindex would be to defragment root and intermediate level
pages, and hopefully if the leaf level pages were already defragmented, less
work would be required to defragment them. It is a theory that would need to
be tested, certainly if the root/intermediate nodes were very fragmented
there may be less gain from the indexdefrags, there may be a point where you
don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be very
fragmented.
John
"Andrew J. Kelly" wrote:
> John,
> Can you explain what you mean by (or want to accomplish) by doing a final
> DBREINDEX? I am confused by the statement.
> --
> Andrew J. Kelly SQL MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> > Hi
> >
> > A different approach to the stop/backup/restart method might be to use
> > DBCC
> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> > therefore limiting the number of indexes defragmented per go. Once all
> > completed then do a final DBCC DBREINDEX.
> >
> > John
> >
> > "Patrick" wrote:
> >
> >> Will switching from Full to Bulk recovery during DEFRAG and then
> >> switching
> >> back from Bulk to Full recovery break log shipping?
> >>
> >> "John Bell" wrote:
> >>
> >> > Hi Patrick
> >> >
> >> > It sounds like you certainly need to look at what the indexes are and
> >> > if
> >> > they are really needed or if a better indexing scheme could be
> >> > employed.
> >> >
> >> > John
> >> >
> >> > "Patrick" wrote:
> >> >
> >> > > Just started a new job and came accross tables fragmented 80% or
> >> > > more;
> >> > > however, they also have log shipping. Users are complaining about
> >> > > performance and I can see why, but resolving the fragmentation issue
> >> > > is more
> >> > > of a task than first anticipated.
> >> > >
> >> > > I will look into partitioning the table, but I want to see if I
> >> > > defrag some
> >> > > of the tables before I start the partitioning project.
> >> > >
> >> > > "John Bell" wrote:
> >> > >
> >> > > > I forgot to add...
> >> > > >
> >> > > > Have you considered partitioning this table?
> >> > > >
> >> > > > John
> >> > > >
> >> > > > "Patrick" wrote:
> >> > > >
> >> > > > > I currently have one table, highly utilized, and it is 80%
> >> > > > > fragmented. I
> >> > > > > need to defrag the index, but due to log shipping, I am unable to
> >> > > > > defrag the
> >> > > > > index without creating a 2 gig log. A log this size will hose
> >> > > > > the pipe
> >> > > > > between our production and offsite sql server. Any ideas on how
> >> > > > > to defrag
> >> > > > > without creating such a huge log?
> >> > > > >
> >> > > > >
>
>|||OK I see what you were after now. But unfortunately it would not matter too
much in his case if the table was heavily or lightly fragmented. When you
issue a DBREINDEX it will completely rebuild the indexes in a new section of
the data files. This will always result in basically the same amount of
pages or extents being logged or needed to be backed up in the case of
BulkLogged recovery model. It is true that the work itself may be a little
easier to sort the data for the rebuild when there is less fragmentation.
But as far as log shipping goes it would not matter either way. Actually
that way would be worse since you would log it twice. Once for Indexdefrag
and once for DBreindex.
--
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:082B1590-3F81-4D82-8AD3-F008C974EC4B@.microsoft.com...
> Hi Andrew
> The final dbreindex would be to defragment root and intermediate level
> pages, and hopefully if the leaf level pages were already defragmented,
> less
> work would be required to defragment them. It is a theory that would need
> to
> be tested, certainly if the root/intermediate nodes were very fragmented
> there may be less gain from the indexdefrags, there may be a point where
> you
> don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be
> very
> fragmented.
> John
>
> "Andrew J. Kelly" wrote:
>> John,
>> Can you explain what you mean by (or want to accomplish) by doing a final
>> DBREINDEX? I am confused by the statement.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
>> > Hi
>> >
>> > A different approach to the stop/backup/restart method might be to use
>> > DBCC
>> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of
>> > fragmentation,
>> > therefore limiting the number of indexes defragmented per go. Once all
>> > completed then do a final DBCC DBREINDEX.
>> >
>> > John
>> >
>> > "Patrick" wrote:
>> >
>> >> Will switching from Full to Bulk recovery during DEFRAG and then
>> >> switching
>> >> back from Bulk to Full recovery break log shipping?
>> >>
>> >> "John Bell" wrote:
>> >>
>> >> > Hi Patrick
>> >> >
>> >> > It sounds like you certainly need to look at what the indexes are
>> >> > and
>> >> > if
>> >> > they are really needed or if a better indexing scheme could be
>> >> > employed.
>> >> >
>> >> > John
>> >> >
>> >> > "Patrick" wrote:
>> >> >
>> >> > > Just started a new job and came accross tables fragmented 80% or
>> >> > > more;
>> >> > > however, they also have log shipping. Users are complaining about
>> >> > > performance and I can see why, but resolving the fragmentation
>> >> > > issue
>> >> > > is more
>> >> > > of a task than first anticipated.
>> >> > >
>> >> > > I will look into partitioning the table, but I want to see if I
>> >> > > defrag some
>> >> > > of the tables before I start the partitioning project.
>> >> > >
>> >> > > "John Bell" wrote:
>> >> > >
>> >> > > > I forgot to add...
>> >> > > >
>> >> > > > Have you considered partitioning this table?
>> >> > > >
>> >> > > > John
>> >> > > >
>> >> > > > "Patrick" wrote:
>> >> > > >
>> >> > > > > I currently have one table, highly utilized, and it is 80%
>> >> > > > > fragmented. I
>> >> > > > > need to defrag the index, but due to log shipping, I am unable
>> >> > > > > to
>> >> > > > > defrag the
>> >> > > > > index without creating a 2 gig log. A log this size will hose
>> >> > > > > the pipe
>> >> > > > > between our production and offsite sql server. Any ideas on
>> >> > > > > how
>> >> > > > > to defrag
>> >> > > > > without creating such a huge log?
>> >> > > > >
>> >> > > > >
>>|||Hi Andrew
Thinking of it that way, you are right, the last DBREINDEX would increase
the logging. I would still favour a more controlled defragmentation than one
that would entailed killed of the reindexing!
John
"Andrew J. Kelly" wrote:
> OK I see what you were after now. But unfortunately it would not matter too
> much in his case if the table was heavily or lightly fragmented. When you
> issue a DBREINDEX it will completely rebuild the indexes in a new section of
> the data files. This will always result in basically the same amount of
> pages or extents being logged or needed to be backed up in the case of
> BulkLogged recovery model. It is true that the work itself may be a little
> easier to sort the data for the rebuild when there is less fragmentation.
> But as far as log shipping goes it would not matter either way. Actually
> that way would be worse since you would log it twice. Once for Indexdefrag
> and once for DBreindex.
> --
> Andrew J. Kelly SQL MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:082B1590-3F81-4D82-8AD3-F008C974EC4B@.microsoft.com...
> > Hi Andrew
> >
> > The final dbreindex would be to defragment root and intermediate level
> > pages, and hopefully if the leaf level pages were already defragmented,
> > less
> > work would be required to defragment them. It is a theory that would need
> > to
> > be tested, certainly if the root/intermediate nodes were very fragmented
> > there may be less gain from the indexdefrags, there may be a point where
> > you
> > don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be
> > very
> > fragmented.
> >
> > John
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> John,
> >>
> >> Can you explain what you mean by (or want to accomplish) by doing a final
> >> DBREINDEX? I am confused by the statement.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> >> > Hi
> >> >
> >> > A different approach to the stop/backup/restart method might be to use
> >> > DBCC
> >> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of
> >> > fragmentation,
> >> > therefore limiting the number of indexes defragmented per go. Once all
> >> > completed then do a final DBCC DBREINDEX.
> >> >
> >> > John
> >> >
> >> > "Patrick" wrote:
> >> >
> >> >> Will switching from Full to Bulk recovery during DEFRAG and then
> >> >> switching
> >> >> back from Bulk to Full recovery break log shipping?
> >> >>
> >> >> "John Bell" wrote:
> >> >>
> >> >> > Hi Patrick
> >> >> >
> >> >> > It sounds like you certainly need to look at what the indexes are
> >> >> > and
> >> >> > if
> >> >> > they are really needed or if a better indexing scheme could be
> >> >> > employed.
> >> >> >
> >> >> > John
> >> >> >
> >> >> > "Patrick" wrote:
> >> >> >
> >> >> > > Just started a new job and came accross tables fragmented 80% or
> >> >> > > more;
> >> >> > > however, they also have log shipping. Users are complaining about
> >> >> > > performance and I can see why, but resolving the fragmentation
> >> >> > > issue
> >> >> > > is more
> >> >> > > of a task than first anticipated.
> >> >> > >
> >> >> > > I will look into partitioning the table, but I want to see if I
> >> >> > > defrag some
> >> >> > > of the tables before I start the partitioning project.
> >> >> > >
> >> >> > > "John Bell" wrote:
> >> >> > >
> >> >> > > > I forgot to add...
> >> >> > > >
> >> >> > > > Have you considered partitioning this table?
> >> >> > > >
> >> >> > > > John
> >> >> > > >
> >> >> > > > "Patrick" wrote:
> >> >> > > >
> >> >> > > > > I currently have one table, highly utilized, and it is 80%
> >> >> > > > > fragmented. I
> >> >> > > > > need to defrag the index, but due to log shipping, I am unable
> >> >> > > > > to
> >> >> > > > > defrag the
> >> >> > > > > index without creating a 2 gig log. A log this size will hose
> >> >> > > > > the pipe
> >> >> > > > > between our production and offsite sql server. Any ideas on
> >> >> > > > > how
> >> >> > > > > to defrag
> >> >> > > > > without creating such a huge log?
> >> >> > > > >
> >> >> > > > >
> >>
> >>
> >>
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B4E0C43-B5BE-4631-AE23-0AF894DCC3F6@.microsoft.com...
> Hi Andrew
> Thinking of it that way, you are right, the last DBREINDEX would increase
> the logging. I would still favour a more controlled defragmentation than
> one
> that would entailed killed of the reindexing!
> John
Can you explain what you mean by "controlled defragmentation?" I know you
can use dbcc showcontig results in logic to determine whether or not to
defrag (i.e., "if more than 20% fragmented, then defrag"). And I know you
can run dbcc indexdefrag for a while and stop it at any point. But I was
unaware that you could control the amount of defragmentation dbcc
indexdefrag does.
Thanks
Karen|||Hi Karen
You have described what I would call a controlled defragmentation, only
defrag tables/indexes that are necessary, choosing whether to do a DBCC
DBREINDEX or DBCC INDEXDEFRAG as necessary, rather than having (say) a timer
that kills the process part way through.
Without partitioning then you may have to resort to killing off the reindex
process.
John
"Karen Collins" wrote:
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:8B4E0C43-B5BE-4631-AE23-0AF894DCC3F6@.microsoft.com...
> > Hi Andrew
> >
> > Thinking of it that way, you are right, the last DBREINDEX would increase
> > the logging. I would still favour a more controlled defragmentation than
> > one
> > that would entailed killed of the reindexing!
> >
> > John
> Can you explain what you mean by "controlled defragmentation?" I know you
> can use dbcc showcontig results in logic to determine whether or not to
> defrag (i.e., "if more than 20% fragmented, then defrag"). And I know you
> can run dbcc indexdefrag for a while and stop it at any point. But I was
> unaware that you could control the amount of defragmentation dbcc
> indexdefrag does.
> Thanks
> Karen
>
>
Subscribe to:
Posts (Atom)