Managing sql Log File: log

Showing posts with label log. Show all posts

Friday, March 30, 2012

Marked "IN LOAD" messages

I do restores on a daily basis. The restores are taken from DISK backups. I
am not using backup devices.
I am noticing all these errors in the log file:
Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
When I do the restore, I am using the gui (rt click on database, select
restore).
Sometimes I need to restore over an existing DB, other times I restore to a
new database.
Any ideas? I want to make sure I am doing the restores correctly and remove
these errorsIs it possible if I do not specify "Force over existing Database" that it can
cause this error?
"Kristen" wrote:
> I do restores on a daily basis. The restores are taken from DISK backups. I
> am not using backup devices.
> I am noticing all these errors in the log file:
> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
> When I do the restore, I am using the gui (rt click on database, select
> restore).
> Sometimes I need to restore over an existing DB, other times I restore to a
> new database.
> Any ideas? I want to make sure I am doing the restores correctly and remove
> these errors|||Try using the 'WITH RECOVERY' OPTION and issue the restore command via TSQL
and not the Gui.
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
> Is it possible if I do not specify "Force over existing Database" that it
> can
> cause this error?
> "Kristen" wrote:
>> I do restores on a daily basis. The restores are taken from DISK
>> backups. I
>> am not using backup devices.
>> I am noticing all these errors in the log file:
>> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
>> When I do the restore, I am using the gui (rt click on database, select
>> restore).
>> Sometimes I need to restore over an existing DB, other times I restore to
>> a
>> new database.
>> Any ideas? I want to make sure I am doing the restores correctly and
>> remove
>> these errors|||I tried doing it in TSQL......didn't work
RESTORE DATABASE test2
FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
WITH RECOVERY,
MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
Bypassing recovery for database 'test2' because it is marked IN LOAD.
Starting up database 'test2'.
Recovery is checkpointing database 'test2' (58)
"Immy" wrote:
> Try using the 'WITH RECOVERY' OPTION and issue the restore command via TSQL
> and not the Gui.
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
> > Is it possible if I do not specify "Force over existing Database" that it
> > can
> > cause this error?
> >
> > "Kristen" wrote:
> >
> >> I do restores on a daily basis. The restores are taken from DISK
> >> backups. I
> >> am not using backup devices.
> >> I am noticing all these errors in the log file:
> >> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
> >> When I do the restore, I am using the gui (rt click on database, select
> >> restore).
> >> Sometimes I need to restore over an existing DB, other times I restore to
> >> a
> >> new database.
> >> Any ideas? I want to make sure I am doing the restores correctly and
> >> remove
> >> these errors
>
>|||So - to clarify, you did a full backup of your database, then copy the
database to a new location and then restore it?
What happens if you restore the backup on the same server that the backup
was taken from?
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
>I tried doing it in TSQL......didn't work
> RESTORE DATABASE test2
> FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> WITH RECOVERY,
> MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> Bypassing recovery for database 'test2' because it is marked IN LOAD.
> Starting up database 'test2'.
> Recovery is checkpointing database 'test2' (58)
>
> "Immy" wrote:
>> Try using the 'WITH RECOVERY' OPTION and issue the restore command via
>> TSQL
>> and not the Gui.
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
>> > Is it possible if I do not specify "Force over existing Database" that
>> > it
>> > can
>> > cause this error?
>> >
>> > "Kristen" wrote:
>> >
>> >> I do restores on a daily basis. The restores are taken from DISK
>> >> backups. I
>> >> am not using backup devices.
>> >> I am noticing all these errors in the log file:
>> >> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
>> >> When I do the restore, I am using the gui (rt click on database,
>> >> select
>> >> restore).
>> >> Sometimes I need to restore over an existing DB, other times I restore
>> >> to
>> >> a
>> >> new database.
>> >> Any ideas? I want to make sure I am doing the restores correctly and
>> >> remove
>> >> these errors
>>|||And obviously I mean in a different DB name and not over the existing one ;)
"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
>I tried doing it in TSQL......didn't work
> RESTORE DATABASE test2
> FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> WITH RECOVERY,
> MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> Bypassing recovery for database 'test2' because it is marked IN LOAD.
> Starting up database 'test2'.
> Recovery is checkpointing database 'test2' (58)
>
> "Immy" wrote:
>> Try using the 'WITH RECOVERY' OPTION and issue the restore command via
>> TSQL
>> and not the Gui.
>> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
>> news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
>> > Is it possible if I do not specify "Force over existing Database" that
>> > it
>> > can
>> > cause this error?
>> >
>> > "Kristen" wrote:
>> >
>> >> I do restores on a daily basis. The restores are taken from DISK
>> >> backups. I
>> >> am not using backup devices.
>> >> I am noticing all these errors in the log file:
>> >> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
>> >> When I do the restore, I am using the gui (rt click on database,
>> >> select
>> >> restore).
>> >> Sometimes I need to restore over an existing DB, other times I restore
>> >> to
>> >> a
>> >> new database.
>> >> Any ideas? I want to make sure I am doing the restores correctly and
>> >> remove
>> >> these errors
>>|||"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
>I tried doing it in TSQL......didn't work
> RESTORE DATABASE test2
> FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> WITH RECOVERY,
> MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> Bypassing recovery for database 'test2' because it is marked IN LOAD.
> Starting up database 'test2'.
> Recovery is checkpointing database 'test2' (58)
Why do you think these are "errors"? The sql server log file contains
messages, some of which may be errors but many of which are not. The
messages you see are standard messages that indicate a successful restore
(btw - I don't think you posted all the relevant messages since there should
be a "Database restored: Database: <dbname>, creation ..." message.|||I want to make sure the "IN LOAD" messages that I am getting will not create
any issues in the future. Do you know of any potential problems I could face
when getting this "IN LOAD" message
"Scott Morris" wrote:
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
> >I tried doing it in TSQL......didn't work
> >
> > RESTORE DATABASE test2
> > FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> > WITH RECOVERY,
> > MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> > MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> >
> > Bypassing recovery for database 'test2' because it is marked IN LOAD.
> > Starting up database 'test2'.
> > Recovery is checkpointing database 'test2' (58)
> Why do you think these are "errors"? The sql server log file contains
> messages, some of which may be errors but many of which are not. The
> messages you see are standard messages that indicate a successful restore
> (btw - I don't think you posted all the relevant messages since there should
> be a "Database restored: Database: <dbname>, creation ..." message.
>
>|||For this test, I took a backup, then restored it to a new database. When I
did that I got the error.
I am only working with 1 server so I did restore the backup on the same
server that the backup was taken from.
#2 - Most of the time, I need to restore a backup (from another DB) to an
existing database. I was using the GUI for this also. I think that may be
why I am having these messages in the log.
Can you give me an idea for the #2 scenario above what my RESTORE DATABASE
options should be?
Thanks for your help with this.
"Immy" wrote:
> So - to clarify, you did a full backup of your database, then copy the
> database to a new location and then restore it?
> What happens if you restore the backup on the same server that the backup
> was taken from?
>
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
> >I tried doing it in TSQL......didn't work
> >
> > RESTORE DATABASE test2
> > FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> > WITH RECOVERY,
> > MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> > MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> >
> > Bypassing recovery for database 'test2' because it is marked IN LOAD.
> > Starting up database 'test2'.
> > Recovery is checkpointing database 'test2' (58)
> >
> >
> > "Immy" wrote:
> >
> >> Try using the 'WITH RECOVERY' OPTION and issue the restore command via
> >> TSQL
> >> and not the Gui.
> >>
> >> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> >> news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
> >> > Is it possible if I do not specify "Force over existing Database" that
> >> > it
> >> > can
> >> > cause this error?
> >> >
> >> > "Kristen" wrote:
> >> >
> >> >> I do restores on a daily basis. The restores are taken from DISK
> >> >> backups. I
> >> >> am not using backup devices.
> >> >> I am noticing all these errors in the log file:
> >> >> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
> >> >> When I do the restore, I am using the gui (rt click on database,
> >> >> select
> >> >> restore).
> >> >> Sometimes I need to restore over an existing DB, other times I restore
> >> >> to
> >> >> a
> >> >> new database.
> >> >> Any ideas? I want to make sure I am doing the restores correctly and
> >> >> remove
> >> >> these errors
> >>
> >>
> >>
>
>|||No I didn't post all the messages. The database did successfully restore. I
want to understand what I am doing wrong when restoring these databases so
the messages are not written to the log. Most of the time I am taking a
backup of 1 database and restoring it to another existing DB. Using the GUI
for the restore.
Any ideas?
"Scott Morris" wrote:
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:5365DC0E-0F94-4109-A028-E775E00E8A10@.microsoft.com...
> >I tried doing it in TSQL......didn't work
> >
> > RESTORE DATABASE test2
> > FROM DISK = 'E:\MSSQL\Backup\LVTS_Min_03122007.bak'
> > WITH RECOVERY,
> > MOVE 'LVTS_Min_Data' TO 'E:\MSSQL\Backup\test2_data.mdf',
> > MOVE 'LVTS_Min_Log' TO 'E:\MSSQL\Backup\test2_log.ldf'
> >
> > Bypassing recovery for database 'test2' because it is marked IN LOAD.
> > Starting up database 'test2'.
> > Recovery is checkpointing database 'test2' (58)
> Why do you think these are "errors"? The sql server log file contains
> messages, some of which may be errors but many of which are not. The
> messages you see are standard messages that indicate a successful restore
> (btw - I don't think you posted all the relevant messages since there should
> be a "Database restored: Database: <dbname>, creation ..." message.
>
>|||If I do RESTORE DATABASE test WITH RECOVERY
it doesn't actually do a restore from a backup...it just sets the RECOVERY
option correct?
"Immy" wrote:
> Try using the 'WITH RECOVERY' OPTION and issue the restore command via TSQL
> and not the Gui.
> "Kristen" <Kristen@.discussions.microsoft.com> wrote in message
> news:9FE4EEAE-6332-4F67-BA23-6D1AA00878D3@.microsoft.com...
> > Is it possible if I do not specify "Force over existing Database" that it
> > can
> > cause this error?
> >
> > "Kristen" wrote:
> >
> >> I do restores on a daily basis. The restores are taken from DISK
> >> backups. I
> >> am not using backup devices.
> >> I am noticing all these errors in the log file:
> >> Bypassing recovery for database 'xxxxx' because it is marked IN LOAD.
> >> When I do the restore, I am using the gui (rt click on database, select
> >> restore).
> >> Sometimes I need to restore over an existing DB, other times I restore to
> >> a
> >> new database.
> >> Any ideas? I want to make sure I am doing the restores correctly and
> >> remove
> >> these errors
>
>|||"Kristen" <Kristen@.discussions.microsoft.com> wrote in message
news:D9C48AE9-91FC-4752-94CB-0428A569F826@.microsoft.com...
> No I didn't post all the messages. The database did successfully restore.
> I
> want to understand what I am doing wrong when restoring these databases so
> the messages are not written to the log. Most of the time I am taking a
> backup of 1 database and restoring it to another existing DB. Using the
> GUI
> for the restore.
> Any ideas?
There isn't anything wrong and you can't directly control these messages.
These messages are not errors. Again, not everything written to the log is
an error.

Marked "IN LOAD" messages

Wednesday, March 21, 2012

many problems, fixed?

Hi All-
SQL7.0 SP0 NT4. I was brought in to help fix this database, backups are
there, but corruption has been in the maintenance plan log for over a year.
I made backup of db and restored as different name on different disk array.
I ran DBCC CHECKDB on database, it reported 17,000+ consistency errors. I
next rebuilt indexes and ran CHECKDB again. Dropped errors to 202. Ran
CHECKDB with repair_rebuild option. This corrected some errors, now I am
down to 94. Since it was a copy I gave it a shot and ran CHECKDB with
repair_allow_data_loss option. After this ran, CHECKDB reports 0 errors. I
have compared row count on corrupt tables before and after the data_loss
option ran and my row count is the same before and after.
What else do I need to look at to see what data was loss with the
repair_data_loss option?
After ths is correct I will be working with the owner and app vendor to get
the sql and os at least patched to current levels if not upgraded.
Thanks!
I suppose you could check the size of the database and the number of indexes
in it. It doesn't sound like you have much of a choice but to go with the
repaired database anyway.
Good luck...
Ben
"Ryan Sanders" <rsanders> wrote in message
news:108nd8uapvjvbb2@.corp.supernews.com...
> Hi All-
>
> SQL7.0 SP0 NT4. I was brought in to help fix this database, backups are
> there, but corruption has been in the maintenance plan log for over a
year.
> I made backup of db and restored as different name on different disk
array.
> I ran DBCC CHECKDB on database, it reported 17,000+ consistency errors. I
> next rebuilt indexes and ran CHECKDB again. Dropped errors to 202. Ran
> CHECKDB with repair_rebuild option. This corrected some errors, now I am
> down to 94. Since it was a copy I gave it a shot and ran CHECKDB with
> repair_allow_data_loss option. After this ran, CHECKDB reports 0 errors.
I
> have compared row count on corrupt tables before and after the data_loss
> option ran and my row count is the same before and after.
>
> What else do I need to look at to see what data was loss with the
> repair_data_loss option?
>
> After ths is correct I will be working with the owner and app vendor to
get
> the sql and os at least patched to current levels if not upgraded.
>
> Thanks!
>
|||Can anyone provide any guidance?
Thanks!
Ryan
"Ryan Sanders" <rsanders> wrote in message news:<108nd8uapvjvbb2@.corp.supernews.com>...
> Hi All-
>
> SQL7.0 SP0 NT4. I was brought in to help fix this database, backups are
> there, but corruption has been in the maintenance plan log for over a year.
> I made backup of db and restored as different name on different disk array.
> I ran DBCC CHECKDB on database, it reported 17,000+ consistency errors. I
> next rebuilt indexes and ran CHECKDB again. Dropped errors to 202. Ran
> CHECKDB with repair_rebuild option. This corrected some errors, now I am
> down to 94. Since it was a copy I gave it a shot and ran CHECKDB with
> repair_allow_data_loss option. After this ran, CHECKDB reports 0 errors. I
> have compared row count on corrupt tables before and after the data_loss
> option ran and my row count is the same before and after.
>
> What else do I need to look at to see what data was loss with the
> repair_data_loss option?
>
> After ths is correct I will be working with the owner and app vendor to get
> the sql and os at least patched to current levels if not upgraded.
>
> Thanks!

many problems, fixed?

Hi All-
SQL7.0 SP0 NT4. I was brought in to help fix this database, backups are
there, but corruption has been in the maintenance plan log for over a year.
I made backup of db and restored as different name on different disk array.
I ran DBCC CHECKDB on database, it reported 17,000+ consistency errors. I
next rebuilt indexes and ran CHECKDB again. Dropped errors to 202. Ran
CHECKDB with repair_rebuild option. This corrected some errors, now I am
down to 94. Since it was a copy I gave it a shot and ran CHECKDB with
repair_allow_data_loss option. After this ran, CHECKDB reports 0 errors. I
have compared row count on corrupt tables before and after the data_loss
option ran and my row count is the same before and after.
What else do I need to look at to see what data was loss with the
repair_data_loss option?
After ths is correct I will be working with the owner and app vendor to get
the sql and os at least patched to current levels if not upgraded.
Thanks!I suppose you could check the size of the database and the number of indexes
in it. It doesn't sound like you have much of a choice but to go with the
repaired database anyway.
Good luck...
Ben
"Ryan Sanders" <rsanders> wrote in message
news:108nd8uapvjvbb2@.corp.supernews.com...
> Hi All-
>
> SQL7.0 SP0 NT4. I was brought in to help fix this database, backups are
> there, but corruption has been in the maintenance plan log for over a
year.
> I made backup of db and restored as different name on different disk
array.
> I ran DBCC CHECKDB on database, it reported 17,000+ consistency errors. I
> next rebuilt indexes and ran CHECKDB again. Dropped errors to 202. Ran
> CHECKDB with repair_rebuild option. This corrected some errors, now I am
> down to 94. Since it was a copy I gave it a shot and ran CHECKDB with
> repair_allow_data_loss option. After this ran, CHECKDB reports 0 errors.
I
> have compared row count on corrupt tables before and after the data_loss
> option ran and my row count is the same before and after.
>
> What else do I need to look at to see what data was loss with the
> repair_data_loss option?
>
> After ths is correct I will be working with the owner and app vendor to
get
> the sql and os at least patched to current levels if not upgraded.
>
> Thanks!
>|||Can anyone provide any guidance?
Thanks!
Ryan
"Ryan Sanders" <rsanders> wrote in message news:<108nd8uapvjvbb2@.corp.supernews.com>...
> Hi All-
>
> SQL7.0 SP0 NT4. I was brought in to help fix this database, backups are
> there, but corruption has been in the maintenance plan log for over a year.
> I made backup of db and restored as different name on different disk array.
> I ran DBCC CHECKDB on database, it reported 17,000+ consistency errors. I
> next rebuilt indexes and ran CHECKDB again. Dropped errors to 202. Ran
> CHECKDB with repair_rebuild option. This corrected some errors, now I am
> down to 94. Since it was a copy I gave it a shot and ran CHECKDB with
> repair_allow_data_loss option. After this ran, CHECKDB reports 0 errors. I
> have compared row count on corrupt tables before and after the data_loss
> option ran and my row count is the same before and after.
>
> What else do I need to look at to see what data was loss with the
> repair_data_loss option?
>
> After ths is correct I will be working with the owner and app vendor to get
> the sql and os at least patched to current levels if not upgraded.
>
> Thanks!

many problems, fixed?

Hi All-
SQL7.0 SP0 NT4. I was brought in to help fix this database, backups are
there, but corruption has been in the maintenance plan log for over a year.
I made backup of db and restored as different name on different disk array.
I ran DBCC CHECKDB on database, it reported 17,000+ consistency errors. I
next rebuilt indexes and ran CHECKDB again. Dropped errors to 202. Ran
CHECKDB with repair_rebuild option. This corrected some errors, now I am
down to 94. Since it was a copy I gave it a shot and ran CHECKDB with
repair_allow_data_loss option. After this ran, CHECKDB reports 0 errors. I
have compared row count on corrupt tables before and after the data_loss
option ran and my row count is the same before and after.
What else do I need to look at to see what data was loss with the
repair_data_loss option?
After ths is correct I will be working with the owner and app vendor to get
the sql and os at least patched to current levels if not upgraded.
Thanks!I suppose you could check the size of the database and the number of indexes
in it. It doesn't sound like you have much of a choice but to go with the
repaired database anyway.
Good luck...
Ben
"Ryan Sanders" <rsanders> wrote in message
news:108nd8uapvjvbb2@.corp.supernews.com...
> Hi All-
>
> SQL7.0 SP0 NT4. I was brought in to help fix this database, backups are
> there, but corruption has been in the maintenance plan log for over a
year.
> I made backup of db and restored as different name on different disk
array.
> I ran DBCC CHECKDB on database, it reported 17,000+ consistency errors. I
> next rebuilt indexes and ran CHECKDB again. Dropped errors to 202. Ran
> CHECKDB with repair_rebuild option. This corrected some errors, now I am
> down to 94. Since it was a copy I gave it a shot and ran CHECKDB with
> repair_allow_data_loss option. After this ran, CHECKDB reports 0 errors.
I
> have compared row count on corrupt tables before and after the data_loss
> option ran and my row count is the same before and after.
>
> What else do I need to look at to see what data was loss with the
> repair_data_loss option?
>
> After ths is correct I will be working with the owner and app vendor to
get
> the sql and os at least patched to current levels if not upgraded.
>
> Thanks!
>|||Can anyone provide any guidance?
Thanks!
Ryan
"Ryan Sanders" <rsanders> wrote in message news:<108nd8uapvjvbb2@.corp.supernews.com>...[vbco
l=seagreen]
> Hi All-
>
> SQL7.0 SP0 NT4. I was brought in to help fix this database, backups are
> there, but corruption has been in the maintenance plan log for over a year
.
> I made backup of db and restored as different name on different disk array
.
> I ran DBCC CHECKDB on database, it reported 17,000+ consistency errors. I
> next rebuilt indexes and ran CHECKDB again. Dropped errors to 202. Ran
> CHECKDB with repair_rebuild option. This corrected some errors, now I am
> down to 94. Since it was a copy I gave it a shot and ran CHECKDB with
> repair_allow_data_loss option. After this ran, CHECKDB reports 0 errors.
I
> have compared row count on corrupt tables before and after the data_loss
> option ran and my row count is the same before and after.
>
> What else do I need to look at to see what data was loss with the
> repair_data_loss option?
>
> After ths is correct I will be working with the owner and app vendor to ge
t
> the sql and os at least patched to current levels if not upgraded.
>
> Thanks![/vbcol]

many DISTINCT queries

Hi,

I have a table that contains log data, usually around a million records. The
table has about 10 columns with various attributes of the logged data,
nothing special. We're using SQL Server 2000.

Some of the columns (for example "category") have duplicate values
throughout the records. We have a web page that queries the table to show
all the unique columns, for example:

select distinct CATEGORY from table TEST

Obviously the server has to scan all rows in order to get all unique columns
which takes quite a while, especially since that web page contains several
of these types of queries. We also have a MAX(DATE) and MIN(DATE) query that
also add to the load.

I already created indexes on the CATEGORY (actually on all categories)
column which might help a little but I'm pretty sure that there has got to
be a better way.

I also create a view (select distinct CATEGORY from table TEST) and tried to
index it, but it won't let me index a query that contains a DISTINCT
statement.

Isn't there a way to create an index that contains only the distinct values?
Is there another way to speed this up?

Thanks for any hints!"Florian" <REMOVEUPPERCASEwizard_oz@.gmx.net> wrote in message
news:_cOUb.13574$F23.3296@.newsread2.news.pas.earth link.net...
> Hi,
> I have a table that contains log data, usually around a million records.
The
> table has about 10 columns with various attributes of the logged data,
> nothing special. We're using SQL Server 2000.
> Some of the columns (for example "category") have duplicate values
> throughout the records. We have a web page that queries the table to show
> all the unique columns, for example:
> select distinct CATEGORY from table TEST
> Obviously the server has to scan all rows in order to get all unique
columns
> which takes quite a while, especially since that web page contains several
> of these types of queries. We also have a MAX(DATE) and MIN(DATE) query
that
> also add to the load.
> I already created indexes on the CATEGORY (actually on all categories)
> column which might help a little but I'm pretty sure that there has got to
> be a better way.
> I also create a view (select distinct CATEGORY from table TEST) and tried
to
> index it, but it won't let me index a query that contains a DISTINCT
> statement.
> Isn't there a way to create an index that contains only the distinct
values?
> Is there another way to speed this up?
>
> Thanks for any hints!

If only you load/update the data relatively infrequently, then you could
create a 'lookup' table for each attribute, and populate them from the main
table after loading it (rather like the dimensions in a star schema):

insert into dbo.Categories (Category)
select distinct Category
from dbo.Test

Your client code could then query the lookup tables instead of the log
table. If you want to use indexed views, then you could create a view like
this:

create view dbo.Categories
with schemabinding
as
select Category, count_big(*) as 'Occurrences'
from dbo.Test
group by Category

That should be indexable, although there are quite a few other restrictions,
so you would need to check them out. But having multiple indexed views on
the table would make data modifications much slower, so if the data changes
frequently you might have to use some sort of lookup table approach anyway.

Simon|||> select distinct CATEGORY from table TEST
Try experimenting with group by instead of distinct. Also in query
analyzer, enable view execution plan, to get an idea what mssql is
doing. "select category from mytable group by category"|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4023d837$1_1@.news.bluewin.ch...
> "Florian" <REMOVEUPPERCASEwizard_oz@.gmx.net> wrote in message
> news:_cOUb.13574$F23.3296@.newsread2.news.pas.earth link.net...
> > Hi,
> > I have a table that contains log data, usually around a million records.
> The
> > table has about 10 columns with various attributes of the logged data,
> > nothing special. We're using SQL Server 2000.
> > Some of the columns (for example "category") have duplicate values
> > throughout the records. We have a web page that queries the table to
show
> > all the unique columns, for example:
> > select distinct CATEGORY from table TEST
> > Obviously the server has to scan all rows in order to get all unique
> columns
> > which takes quite a while, especially since that web page contains
several
> > of these types of queries. We also have a MAX(DATE) and MIN(DATE) query
> that
> > also add to the load.
> > I already created indexes on the CATEGORY (actually on all categories)
> > column which might help a little but I'm pretty sure that there has got
to
> > be a better way.
> > I also create a view (select distinct CATEGORY from table TEST) and
tried
> to
> > index it, but it won't let me index a query that contains a DISTINCT
> > statement.
> > Isn't there a way to create an index that contains only the distinct
> values?
> > Is there another way to speed this up?
> > Thanks for any hints!
> If only you load/update the data relatively infrequently, then you could
> create a 'lookup' table for each attribute, and populate them from the
main
> table after loading it (rather like the dimensions in a star schema):
> insert into dbo.Categories (Category)
> select distinct Category
> from dbo.Test
> Your client code could then query the lookup tables instead of the log
> table. If you want to use indexed views, then you could create a view like
> this:
> create view dbo.Categories
> with schemabinding
> as
> select Category, count_big(*) as 'Occurrences'
> from dbo.Test
> group by Category
> That should be indexable, although there are quite a few other
restrictions,
> so you would need to check them out. But having multiple indexed views on
> the table would make data modifications much slower, so if the data
changes
> frequently you might have to use some sort of lookup table approach
anyway.

Thanks, I tried the indexed view and that seems to work OK now, pretty
fast - can't complain. Data shouldn't be updated that often so that should
be OK. Otherwise I might have to go with a lookup table - but it's not a
real good solution for our scenario for reasons I'm not going to bore
anybody with :)

Thanks!|||"louis nguyen" <louisducnguyen@.hotmail.com> wrote in message
news:b0e9d53.0402061216.5d4f6e56@.posting.google.co m...
> > select distinct CATEGORY from table TEST
> Try experimenting with group by instead of distinct. Also in query
> analyzer, enable view execution plan, to get an idea what mssql is
> doing. "select category from mytable group by category"

Yes, the group thing worked great - I also analyzed the execution plan and
now it's only returning the actual number of rows I'm getting - not the
whole table anymore.

Thanks.

Manually truncate a LOG file

SQL 7.0
How do I manually truncate a LOG file?
Thanks,
Don
BACKUP LOG database_name
WITH TRUNCATE_ONLY
BOL describes it nicely.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> SQL 7.0
> How do I manually truncate a LOG file?
> Thanks,
> Don
>
|||That's the command I used on my 30GB LOG file and after
it ran without errors, all the pink turned to blue in EM
and the space remained the same.
How can I get rid of all of this space in the LOG file?
Thanks,
Don

>--Original Message--
>BACKUP LOG database_name
> WITH TRUNCATE_ONLY
>BOL describes it nicely.
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>
>.
>
|||I do that command and then go into EM and right-click on DB.
Choose SHRINK files.
Click on the FILES button - brings you to another pop-up window - choose the
LOG file from the DROPDOWN. Then click OK (the correct check box should be
already the default). This window disappears - then I cancel off the main
window, so I don't shrink the DB itself.
I wish I knew the SQL command string to do this action - EM does it in a
very cumbersome fashion.
But the LOG is 1024 K after this operation - so I know it works!!
"Don" wrote:

> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
> message
>
|||Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
going to truncate the log why not set the recovery mode to SIMPLE?
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:546301c4c908$6a3ab450$a401280a@.phx.gbl...[vbcol=seagreen]
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
> message
|||Andrew,
Don is using SQL 7. So best option is that he can enable the TRUNCATE LOG
ON CHECKPOINT option using sp_dboption.
Don,
Enable the database option TRUNCATE LOG ON CHECKPOINT and execute below:-
backup log dbname with truncate_only
go
DBCC SHRINKFILE (see books online)
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$gLdzRyEHA.3844@.TK2MSFTNGP09.phx.gbl...
> Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
> going to truncate the log why not set the recovery mode to SIMPLE?
> --
> Andrew J. Kelly SQL MVP
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
>
sql

Manually truncate a LOG file

SQL 7.0
How do I manually truncate a LOG file?
Thanks,
DonBACKUP LOG database_name
WITH TRUNCATE_ONLY
BOL describes it nicely.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> SQL 7.0
> How do I manually truncate a LOG file?
> Thanks,
> Don
>|||That's the command I used on my 30GB LOG file and after
it ran without errors, all the pink turned to blue in EM
and the space remained the same.
How can I get rid of all of this space in the LOG file?
Thanks,
Don
>--Original Message--
>BACKUP LOG database_name
> WITH TRUNCATE_ONLY
>BOL describes it nicely.
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>
>.
>|||I do that command and then go into EM and right-click on DB.
Choose SHRINK files.
Click on the FILES button - brings you to another pop-up window - choose the
LOG file from the DROPDOWN. Then click OK (the correct check box should be
already the default). This window disappears - then I cancel off the main
window, so I don't shrink the DB itself.
I wish I knew the SQL command string to do this action - EM does it in a
very cumbersome fashion.
But the LOG is 1024 K after this operation - so I know it works!!
"Don" wrote:
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
> >--Original Message--
> >BACKUP LOG database_name
> > WITH TRUNCATE_ONLY
> >
> >BOL describes it nicely.
> >
> >--
> >Mike Epprecht, Microsoft SQL Server MVP
> >Zurich, Switzerland
> >
> >
> >IM: mike@.epprecht.net
> >
> >MVP Program: http://www.microsoft.com/mvp
> >
> >Blog: http://www.msmvps.com/epprecht/
> >
> >"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> >> SQL 7.0
> >>
> >> How do I manually truncate a LOG file?
> >>
> >> Thanks,
> >> Don
> >>
> >
> >
> >.
> >
>|||Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
going to truncate the log why not set the recovery mode to SIMPLE?
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>>--Original Message--
>>BACKUP LOG database_name
>> WITH TRUNCATE_ONLY
>>BOL describes it nicely.
>>--
>>Mike Epprecht, Microsoft SQL Server MVP
>>Zurich, Switzerland
>>
>>IM: mike@.epprecht.net
>>MVP Program: http://www.microsoft.com/mvp
>>Blog: http://www.msmvps.com/epprecht/
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>>
>>.|||Andrew,
Don is using SQL 7. So best option is that he can enable the TRUNCATE LOG
ON CHECKPOINT option using sp_dboption.
Don,
Enable the database option TRUNCATE LOG ON CHECKPOINT and execute below:-
backup log dbname with truncate_only
go
DBCC SHRINKFILE (see books online)
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$gLdzRyEHA.3844@.TK2MSFTNGP09.phx.gbl...
> Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
> going to truncate the log why not set the recovery mode to SIMPLE?
> --
> Andrew J. Kelly SQL MVP
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
>> That's the command I used on my 30GB LOG file and after
>> it ran without errors, all the pink turned to blue in EM
>> and the space remained the same.
>> How can I get rid of all of this space in the LOG file?
>> Thanks,
>> Don
>>--Original Message--
>>BACKUP LOG database_name
>> WITH TRUNCATE_ONLY
>>BOL describes it nicely.
>>--
>>Mike Epprecht, Microsoft SQL Server MVP
>>Zurich, Switzerland
>>
>>IM: mike@.epprecht.net
>>MVP Program: http://www.microsoft.com/mvp
>>Blog: http://www.msmvps.com/epprecht/
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>> SQL 7.0
>> How do I manually truncate a LOG file?
>> Thanks,
>> Don
>>
>>.
>

Manually truncate a LOG file

SQL 7.0
How do I manually truncate a LOG file?
Thanks,
DonBACKUP LOG database_name
WITH TRUNCATE_ONLY
BOL describes it nicely.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
> SQL 7.0
> How do I manually truncate a LOG file?
> Thanks,
> Don
>|||That's the command I used on my 30GB LOG file and after
it ran without errors, all the pink turned to blue in EM
and the space remained the same.
How can I get rid of all of this space in the LOG file?
Thanks,
Don

>--Original Message--
>BACKUP LOG database_name
> WITH TRUNCATE_ONLY
>BOL describes it nicely.
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Zurich, Switzerland
>
>IM: mike@.epprecht.net
>MVP Program: http://www.microsoft.com/mvp
>Blog: http://www.msmvps.com/epprecht/
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:64e601c4c903$3e4969e0$a301280a@.phx.gbl...
>
>.
>|||I do that command and then go into EM and right-click on DB.
Choose SHRINK files.
Click on the FILES button - brings you to another pop-up window - choose the
LOG file from the DROPDOWN. Then click OK (the correct check box should be
already the default). This window disappears - then I cancel off the main
window, so I don't shrink the DB itself.
I wish I knew the SQL command string to do this action - EM does it in a
very cumbersome fashion.
But the LOG is 1024 K after this operation - so I know it works!!
"Don" wrote:

> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>
> message
>|||Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
going to truncate the log why not set the recovery mode to SIMPLE?
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:546301c4c908$6a3ab450$a401280a@.phx.gbl...[vbcol=seagreen]
> That's the command I used on my 30GB LOG file and after
> it ran without errors, all the pink turned to blue in EM
> and the space remained the same.
> How can I get rid of all of this space in the LOG file?
> Thanks,
> Don
>
> message|||Andrew,
Don is using SQL 7. So best option is that he can enable the TRUNCATE LOG
ON CHECKPOINT option using sp_dboption.
Don,
Enable the database option TRUNCATE LOG ON CHECKPOINT and execute below:-
backup log dbname with truncate_only
go
DBCC SHRINKFILE (see books online)
Thanks
Hari
SQL Server MVP
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$gLdzRyEHA.3844@.TK2MSFTNGP09.phx.gbl...
> Take a look at the DBCC SHRINKFILE option in BooksOnLine. If you are only
> going to truncate the log why not set the recovery mode to SIMPLE?
> --
> Andrew J. Kelly SQL MVP
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:546301c4c908$6a3ab450$a401280a@.phx.gbl...
>

manually truncate a log

Monday, March 19, 2012

manually truncate a log

I have a log.ldf file that has grown to around 150 GB ... I can't really do
a
BACKUP of the database to truncate it.... it seems to struggle with that...
.
is there another way that I can MANUALLY truncate this log file?
help..How full is the log? Do you do any t-log backups at all? Its
possible you can just shrink it if the log is somewhat empty.
Otherwise, take a full backup, switch to simple recovery, shrink the
log, set back to Full and schedule your t-logs to be backed up on a
regular basis (we do our critical systems every 10 minutes, and our
important systems every two hours)
On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
wrote:
> I have a log.ldf file that has grown to around 150 GB ... I can't really d
o a
> BACKUP of the database to truncate it.... it seems to struggle with that.
..
> is there another way that I can MANUALLY truncate this log file?
> help..|||Also: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1174062754.069322.283110@.y80g2000hsf.googlegroups.com...
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>|||The 'actual' log file shows its size as 154,081,280 KB
when I run DBCC Shrinkfile it says:
DbID: 12
FileId: 2
CurrentSize: 19260160
MinimumSize: 640
UsedPages: 19260160
EstimatedPages: 640
when I run this... the actual file size does not change.. and if I try to do
a backup of the transaction log... the system runs and then fails...
"PSPDBA" wrote:

> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>
>|||"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:4AE50DC8-86DF-40D6-8610-B3CD572D070B@.microsoft.com...
> The 'actual' log file shows its size as 154,081,280 KB
>
BACKUP LOG <dbname> WITH TRUNCATEONLY
But not it'll invalidate your backup chain (which I guess doesn't exist.)
Once you do this, do a FULL database backup and then setup transaction
backups to run more often.
[vbcol=seagreen]
> when I run DBCC Shrinkfile it says:
> DbID: 12
> FileId: 2
> CurrentSize: 19260160
> MinimumSize: 640
> UsedPages: 19260160
> EstimatedPages: 640
> when I run this... the actual file size does not change.. and if I try to
> do
> a backup of the transaction log... the system runs and then fails...
> "PSPDBA" wrote:
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

manually truncate a log

I have a log.ldf file that has grown to around 150 GB ... I can't really do a
BACKUP of the database to truncate it.... it seems to struggle with that...
is there another way that I can MANUALLY truncate this log file?
help..How full is the log? Do you do any t-log backups at all? Its
possible you can just shrink it if the log is somewhat empty.
Otherwise, take a full backup, switch to simple recovery, shrink the
log, set back to Full and schedule your t-logs to be backed up on a
regular basis (we do our critical systems every 10 minutes, and our
important systems every two hours)
On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
wrote:
> I have a log.ldf file that has grown to around 150 GB ... I can't really do a
> BACKUP of the database to truncate it.... it seems to struggle with that...
> is there another way that I can MANUALLY truncate this log file?
> help..|||Also: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1174062754.069322.283110@.y80g2000hsf.googlegroups.com...
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
>> I have a log.ldf file that has grown to around 150 GB ... I can't really do a
>> BACKUP of the database to truncate it.... it seems to struggle with that...
>> is there another way that I can MANUALLY truncate this log file?
>> help..
>|||The 'actual' log file shows its size as 154,081,280 KB
when I run DBCC Shrinkfile it says:
DbID: 12
FileId: 2
CurrentSize: 19260160
MinimumSize: 640
UsedPages: 19260160
EstimatedPages: 640
when I run this... the actual file size does not change.. and if I try to do
a backup of the transaction log... the system runs and then fails...
"PSPDBA" wrote:
> How full is the log? Do you do any t-log backups at all? Its
> possible you can just shrink it if the log is somewhat empty.
> Otherwise, take a full backup, switch to simple recovery, shrink the
> log, set back to Full and schedule your t-logs to be backed up on a
> regular basis (we do our critical systems every 10 minutes, and our
> important systems every two hours)
> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
> wrote:
> > I have a log.ldf file that has grown to around 150 GB ... I can't really do a
> > BACKUP of the database to truncate it.... it seems to struggle with that...
> >
> > is there another way that I can MANUALLY truncate this log file?
> >
> > help..
>
>|||"MSUTech" <MSUTech@.discussions.microsoft.com> wrote in message
news:4AE50DC8-86DF-40D6-8610-B3CD572D070B@.microsoft.com...
> The 'actual' log file shows its size as 154,081,280 KB
>
BACKUP LOG <dbname> WITH TRUNCATEONLY
But not it'll invalidate your backup chain (which I guess doesn't exist.)
Once you do this, do a FULL database backup and then setup transaction
backups to run more often.
> when I run DBCC Shrinkfile it says:
> DbID: 12
> FileId: 2
> CurrentSize: 19260160
> MinimumSize: 640
> UsedPages: 19260160
> EstimatedPages: 640
> when I run this... the actual file size does not change.. and if I try to
> do
> a backup of the transaction log... the system runs and then fails...
> "PSPDBA" wrote:
>> How full is the log? Do you do any t-log backups at all? Its
>> possible you can just shrink it if the log is somewhat empty.
>> Otherwise, take a full backup, switch to simple recovery, shrink the
>> log, set back to Full and schedule your t-logs to be backed up on a
>> regular basis (we do our critical systems every 10 minutes, and our
>> important systems every two hours)
>> On Mar 16, 11:57 am, MSUTech <MSUT...@.discussions.microsoft.com>
>> wrote:
>> > I have a log.ldf file that has grown to around 150 GB ... I can't
>> > really do a
>> > BACKUP of the database to truncate it.... it seems to struggle with
>> > that...
>> >
>> > is there another way that I can MANUALLY truncate this log file?
>> >
>> > help..
>>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

Manually roll-forward transactions

I have a full backup of my database, a transaction log backup (taken after
the full). The data files have been deleted, but I do have the transaction
log. After the last transaction log backup, there where a few important
transactions that happened that I need to roll-forward. Is there a way to
get those transactions back if I only have the transaction log, and not a
backup of it?
Thanks in advance.Use the following command:
BACKUP LOG databasename WITH NO_TRUNCATE
This will back up your current log even if the data files are not available.
Then you'll be able to restore your database using your last full backup and
subsequent log backups.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> I have a full backup of my database, a transaction log backup (taken after
> the full). The data files have been deleted, but I do have the
transaction
> log. After the last transaction log backup, there where a few important
> transactions that happened that I need to roll-forward. Is there a way to
> get those transactions back if I only have the transaction log, and not a
> backup of it?
> Thanks in advance.
>|||So if I start up SQL Server, after I have lost my data files (from a lost
disk that housed them), I will be able to do a backup of the log? Wouldn't
the currpted status of my database prevent me from doing the tran log
backup?
Paul
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> Use the following command:
> BACKUP LOG databasename WITH NO_TRUNCATE
> This will back up your current log even if the data files are not
available.
> Then you'll be able to restore your database using your last full backup
and
> subsequent log backups.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> > I have a full backup of my database, a transaction log backup (taken
after
> > the full). The data files have been deleted, but I do have the
> transaction
> > log. After the last transaction log backup, there where a few important
> > transactions that happened that I need to roll-forward. Is there a way
to
> > get those transactions back if I only have the transaction log, and not
a
> > backup of it?
> >
> > Thanks in advance.
> >
> >
>|||No, it doesn't prevent you from doing it. Actually, it's something very easy
to test. Just stop sql server, delete data files, restart sql server and
then issue the "magic" command.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:uKVQ0$p6DHA.2644@.TK2MSFTNGP11.phx.gbl...
> So if I start up SQL Server, after I have lost my data files (from a lost
> disk that housed them), I will be able to do a backup of the log?
Wouldn't
> the currpted status of my database prevent me from doing the tran log
> backup?
> Paul
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Use the following command:
> > BACKUP LOG databasename WITH NO_TRUNCATE
> > This will back up your current log even if the data files are not
> available.
> > Then you'll be able to restore your database using your last full backup
> and
> > subsequent log backups.
> >
> > --
> > Carlos E. Rojas
> > SQL Server MVP
> > Co-Author SQL Server 2000 Programming by Example
> >
> >
> > "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> > news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> > > I have a full backup of my database, a transaction log backup (taken
> after
> > > the full). The data files have been deleted, but I do have the
> > transaction
> > > log. After the last transaction log backup, there where a few
important
> > > transactions that happened that I need to roll-forward. Is there a
way
> to
> > > get those transactions back if I only have the transaction log, and
not
> a
> > > backup of it?
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>

Manually roll-forward transactions

quote:

> I have a full backup of my database, a transaction log backup (taken after
> the full). The data files have been deleted, but I do have the

transaction

quote:

> log. After the last transaction log backup, there where a few important
> transactions that happened that I need to roll-forward. Is there a way to
> get those transactions back if I only have the transaction log, and not a
> backup of it?
> Thanks in advance.
>

|||So if I start up SQL Server, after I have lost my data files (from a lost
disk that housed them), I will be able to do a backup of the log? Wouldn't
the currpted status of my database prevent me from doing the tran log
backup?
Paul
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...

quote:

> Use the following command:
> BACKUP LOG databasename WITH NO_TRUNCATE
> This will back up your current log even if the data files are not

available.

quote:

> Then you'll be able to restore your database using your last full backup

and

quote:

> subsequent log backups.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
after[QUOTE]
> transaction
to[QUOTE]
a[QUOTE]
>

|||No, it doesn't prevent you from doing it. Actually, it's something very easy
to test. Just stop sql server, delete data files, restart sql server and
then issue the "magic" command.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:uKVQ0$p6DHA.2644@.TK2MSFTNGP11.phx.gbl...

quote:

> So if I start up SQL Server, after I have lost my data files (from a lost
> disk that housed them), I will be able to do a backup of the log?

Wouldn't

quote:

> the currpted status of my database prevent me from doing the tran log
> backup?
> Paul
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> available.
> and
> after
important[QUOTE]
way[QUOTE]
> to
not[QUOTE]
> a
>

Manual Log Shipping Restore Log Problems

Hi,
I am implementing manual log shipping between a production server and a
standby server.
I have done the follwing:
1. On prod server created 2 devices for db and log.
2. created 2 jobs which will backup db/log,move to network share and restore
to standby server using something like this
BACKUP LOG newnorthwind TO backup_log WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
3. my problem is that when scheduling the log every 15 mins the log will
still have the same file name and even i tried having datetime stamp but the
backup time may differ from one log to other and the restore has to have some
kind of dynamic settings that will find the last log backup time.
Can anyone suggest as how i am do that and i dont want to erase the log file.
Mnay thanks
Anup
Keep a table that keeps track of which log files you've already "Shipped"
Greg Jackson
PDX, Oregon
|||But How do i get that info when I am restoring the logs to the standby table
"pdxJaxon" wrote:

> Keep a table that keeps track of which log files you've already "Shipped"
>
> Greg Jackson
> PDX, Oregon
>
>
|||you name the t-logs with a datetime in the name
DBNAME_TLOG_200506080800.trn
have a process that copies all the trn logs over to the target server
on the target server read al files in the directory and put them in a temp
table sorted by name
this table is (LogsToApply)
walk the records one at a time and apply them in order IF THEY are not
already listed in the LogsApplied Table.
once you've applied a log, add it's name to the LogsApplied table.
that's it.
I could probably dig the script up for you, but it's been over 2 years since
I did this.
It works great.
GAJ

Manual Log Shipping Restore Log Problems

Hi,
I am implementing manual log shipping between a production server and a
standby server.
I have done the follwing:
1. On prod server created 2 devices for db and log.
2. created 2 jobs which will backup db/log,move to network share and restore
to standby server using something like this
BACKUP LOG newnorthwind TO backup_log WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
3. my problem is that when scheduling the log every 15 mins the log will
still have the same file name and even i tried having datetime stamp but the
backup time may differ from one log to other and the restore has to have som
e
kind of dynamic settings that will find the last log backup time.
Can anyone suggest as how i am do that and i dont want to erase the log file
.
Mnay thanks
AnupKeep a table that keeps track of which log files you've already "Shipped"
Greg Jackson
PDX, Oregon|||But How do i get that info when I am restoring the logs to the standby table
"pdxJaxon" wrote:

> Keep a table that keeps track of which log files you've already "Shipped"
>
> Greg Jackson
> PDX, Oregon
>
>|||you name the t-logs with a datetime in the name
DBNAME_TLOG_200506080800.trn
have a process that copies all the trn logs over to the target server
on the target server read al files in the directory and put them in a temp
table sorted by name
this table is (LogsToApply)
walk the records one at a time and apply them in order IF THEY are not
already listed in the LogsApplied Table.
once you've applied a log, add it's name to the LogsApplied table.
that's it.
I could probably dig the script up for you, but it's been over 2 years since
I did this.
It works great.
GAJ

Manual Log Shipping Restore Log Problems

Hi,
I am implementing manual log shipping between a production server and a
standby server.
I have done the follwing:
1. On prod server created 2 devices for db and log.
2. created 2 jobs which will backup db/log,move to network share and restore
to standby server using something like this
BACKUP LOG newnorthwind TO backup_log WITH INIT, NO_TRUNCATE
WAITFOR DELAY '00:00:05'
3. my problem is that when scheduling the log every 15 mins the log will
still have the same file name and even i tried having datetime stamp but the
backup time may differ from one log to other and the restore has to have some
kind of dynamic settings that will find the last log backup time.
Can anyone suggest as how i am do that and i dont want to erase the log file.
Mnay thanks
AnupKeep a table that keeps track of which log files you've already "Shipped"
Greg Jackson
PDX, Oregon|||But How do i get that info when I am restoring the logs to the standby table
"pdxJaxon" wrote:
> Keep a table that keeps track of which log files you've already "Shipped"
>
> Greg Jackson
> PDX, Oregon
>
>|||you name the t-logs with a datetime in the name
DBNAME_TLOG_200506080800.trn
have a process that copies all the trn logs over to the target server
on the target server read al files in the directory and put them in a temp
table sorted by name
this table is (LogsToApply)
walk the records one at a time and apply them in order IF THEY are not
already listed in the LogsApplied Table.
once you've applied a log, add it's name to the LogsApplied table.
that's it.
I could probably dig the script up for you, but it's been over 2 years since
I did this.
It works great.
GAJ

Manual log shipping

Hello:
I've implemented a stand-by server solution, where the
tran log backup from the primary server gets restored to
the secondary server at every 15-min interval.
I understand that there are some limitations with this
approach (could not implement MS SLS as our business unit
could not afford to purchase the Ent. Ed.), and was
wondering if anyone has encountered any other issues or
observations when implementing a similar manual log
shipping process, other than my own observations listed
below:
Log Shipping will fail if...
- ...there are any open connections to the database where
the transaction log files are restored to; though querying
tables using the fully qualified name is possible from
another database connection or via a linked server
connection.
I've also had one incident where my log shipping process
failed due to a LSN out of sync issue. This happened when
I ran a BCP IN operation. Other times, both BCP and BULK
INSERT operations ran successfully, funnelling changes to
the secondary server's database as expected.
Thank you for all your responses.
Regards,
- Rob.Your observations are correct. Log shipping will fail if there are =users connected to the database. I am wondering if perhaps someone =changed the dboptions when you had the log shipping fail after a BCP =import.
And no, I have not experienced any other issues. The custom log =shipping approach works very well.
You can find a script that will kill any connections to the specified =database here:
http://sqlguy.home.comcast.net/logship.htm
-- Keith
"Rob" <anonymous@.discussions.microsoft.com> wrote in message =news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
> Hello:
> > I've implemented a stand-by server solution, where the > tran log backup from the primary server gets restored to > the secondary server at every 15-min interval.
> > I understand that there are some limitations with this > approach (could not implement MS SLS as our business unit > could not afford to purchase the Ent. Ed.), and was > wondering if anyone has encountered any other issues or > observations when implementing a similar manual log > shipping process, other than my own observations listed > below:
> > Log Shipping will fail if...
> > - ...there are any open connections to the database where > the transaction log files are restored to; though querying > tables using the fully qualified name is possible from > another database connection or via a linked server > connection.
> > I've also had one incident where my log shipping process > failed due to a LSN out of sync issue. This happened when > I ran a BCP IN operation. Other times, both BCP and BULK > INSERT operations ran successfully, funnelling changes to > the secondary server's database as expected.
> > Thank you for all your responses.
> > Regards,
> > - Rob.|||Rather than doing a KILL command on each SPID, a cleaner
way to do it is to put the database in single user mode
with rollback immediate for the duration of the log
restore and then put it back in multi user mode. This
works very well. Here's an example:
alter database database_name set SINGLE_USER with rollback
immediate
restore log database_name from disk
= 'c:\database_name_log.bak' with standby
= 'c:\standby\database_name.bak'
alter database database_name set MULTI_USER
>--Original Message--
>Your observations are correct. Log shipping will fail if
there are users connected to the database. I am wondering
if perhaps someone changed the dboptions when you had the
log shipping fail after a BCP import.
>And no, I have not experienced any other issues. The
custom log shipping approach works very well.
>You can find a script that will kill any connections to
the specified database here:
>http://sqlguy.home.comcast.net/logship.htm
>--
>Keith
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where the
>> tran log backup from the primary server gets restored
to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with this
>> approach (could not implement MS SLS as our business
unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
where
>> the transaction log files are restored to; though
querying
>> tables using the fully qualified name is possible from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
process
>> failed due to a LSN out of sync issue. This happened
when
>> I ran a BCP IN operation. Other times, both BCP and
BULK
>> INSERT operations ran successfully, funnelling changes
to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>.
>|||Agreed. I need to update the web page.
-- Keith
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message =news:13bbf01c3f7d4$804fb2a0$a001280a@.phx.gbl...
> Rather than doing a KILL command on each SPID, a cleaner > way to do it is to put the database in single user mode > with rollback immediate for the duration of the log > restore and then put it back in multi user mode. This > works very well. Here's an example:
> > alter database database_name set SINGLE_USER with rollback > immediate
> > restore log database_name from disk > =3D 'c:\database_name_log.bak' with standby > =3D 'c:\standby\database_name.bak'
> > alter database database_name set MULTI_USER
> > >--Original Message--
> >Your observations are correct. Log shipping will fail if > there are users connected to the database. I am wondering > if perhaps someone changed the dboptions when you had the > log shipping fail after a BCP import. > >
> >And no, I have not experienced any other issues. The > custom log shipping approach works very well.
> >
> >You can find a script that will kill any connections to > the specified database here:
> >http://sqlguy.home.comcast.net/logship.htm
> >
> >-- > >Keith
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in > message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
> >> Hello:
> >> > >> I've implemented a stand-by server solution, where the > >> tran log backup from the primary server gets restored > to > >> the secondary server at every 15-min interval.
> >> > >> I understand that there are some limitations with this > >> approach (could not implement MS SLS as our business > unit > >> could not afford to purchase the Ent. Ed.), and was > >> wondering if anyone has encountered any other issues or > >> observations when implementing a similar manual log > >> shipping process, other than my own observations listed > >> below:
> >> > >> Log Shipping will fail if...
> >> > >> - ...there are any open connections to the database > where > >> the transaction log files are restored to; though > querying > >> tables using the fully qualified name is possible from > >> another database connection or via a linked server > >> connection.
> >> > >> I've also had one incident where my log shipping > process > >> failed due to a LSN out of sync issue. This happened > when > >> I ran a BCP IN operation. Other times, both BCP and > BULK > >> INSERT operations ran successfully, funnelling changes > to > >> the secondary server's database as expected.
> >> > >> Thank you for all your responses.
> >> > >> Regards,
> >> > >> - Rob.
> >.
> >|||But even in single user mode, there could be multiple
connections to the database, which can cause manual log
shipping failures. In this case, I find killing all user
connections more effective, to ensure no connections
exists prior to restoring either the full backup and/or
the tran log.
Thanks.
>--Original Message--
>Rather than doing a KILL command on each SPID, a cleaner
>way to do it is to put the database in single user mode
>with rollback immediate for the duration of the log
>restore and then put it back in multi user mode. This
>works very well. Here's an example:
>alter database database_name set SINGLE_USER with
rollback
>immediate
>restore log database_name from disk
>= 'c:\database_name_log.bak' with standby
>= 'c:\standby\database_name.bak'
>alter database database_name set MULTI_USER
>>--Original Message--
>>Your observations are correct. Log shipping will fail
if
>there are users connected to the database. I am
wondering
>if perhaps someone changed the dboptions when you had the
>log shipping fail after a BCP import.
>>And no, I have not experienced any other issues. The
>custom log shipping approach works very well.
>>You can find a script that will kill any connections to
>the specified database here:
>>http://sqlguy.home.comcast.net/logship.htm
>>--
>>Keith
>>
>>"Rob" <anonymous@.discussions.microsoft.com> wrote in
>message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where the
>> tran log backup from the primary server gets restored
>to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with this
>> approach (could not implement MS SLS as our business
>unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues
or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations
listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
>where
>> the transaction log files are restored to; though
>querying
>> tables using the fully qualified name is possible from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
>process
>> failed due to a LSN out of sync issue. This happened
>when
>> I ran a BCP IN operation. Other times, both BCP and
>BULK
>> INSERT operations ran successfully, funnelling changes
>to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>>.
>.
>|||Putting it in 'single user mode with rollback immediate'
will disconnect any currnet connections to the db and then
put it in single user mode for the process to restore the
log. Since it's in single user mode, only the process
that is restoring the log can connect. Once the restore
is done, just put it back into multi user mode.
>--Original Message--
>But even in single user mode, there could be multiple
>connections to the database, which can cause manual log
>shipping failures. In this case, I find killing all user
>connections more effective, to ensure no connections
>exists prior to restoring either the full backup and/or
>the tran log.
>Thanks.
>>--Original Message--
>>Rather than doing a KILL command on each SPID, a cleaner
>>way to do it is to put the database in single user mode
>>with rollback immediate for the duration of the log
>>restore and then put it back in multi user mode. This
>>works very well. Here's an example:
>>alter database database_name set SINGLE_USER with
>rollback
>>immediate
>>restore log database_name from disk
>>= 'c:\database_name_log.bak' with standby
>>= 'c:\standby\database_name.bak'
>>alter database database_name set MULTI_USER
>>--Original Message--
>>Your observations are correct. Log shipping will fail
>if
>>there are users connected to the database. I am
>wondering
>>if perhaps someone changed the dboptions when you had
the
>>log shipping fail after a BCP import.
>>And no, I have not experienced any other issues. The
>>custom log shipping approach works very well.
>>You can find a script that will kill any connections to
>>the specified database here:
>>http://sqlguy.home.comcast.net/logship.htm
>>--
>>Keith
>>
>>"Rob" <anonymous@.discussions.microsoft.com> wrote in
>>message news:1332101c3f7bf$64a958e0$a301280a@.phx.gbl...
>> Hello:
>> I've implemented a stand-by server solution, where
the
>> tran log backup from the primary server gets restored
>>to
>> the secondary server at every 15-min interval.
>> I understand that there are some limitations with
this
>> approach (could not implement MS SLS as our business
>>unit
>> could not afford to purchase the Ent. Ed.), and was
>> wondering if anyone has encountered any other issues
>or
>> observations when implementing a similar manual log
>> shipping process, other than my own observations
>listed
>> below:
>> Log Shipping will fail if...
>> - ...there are any open connections to the database
>>where
>> the transaction log files are restored to; though
>>querying
>> tables using the fully qualified name is possible
from
>> another database connection or via a linked server
>> connection.
>> I've also had one incident where my log shipping
>>process
>> failed due to a LSN out of sync issue. This happened
>>when
>> I ran a BCP IN operation. Other times, both BCP and
>>BULK
>> INSERT operations ran successfully, funnelling
changes
>>to
>> the secondary server's database as expected.
>> Thank you for all your responses.
>> Regards,
>> - Rob.
>>.
>>.
>.
>

Friday, March 30, 2012

Marked "IN LOAD" messages

Marked "IN LOAD" messages

Marked "IN LOAD" messages

Wednesday, March 21, 2012

many problems, fixed?

many problems, fixed?

many problems, fixed?

many DISTINCT queries

Manually truncate a LOG file

Manually truncate a LOG file

Manually truncate a LOG file

manually truncate a log

Monday, March 19, 2012

manually truncate a log

manually truncate a log

Manually roll-forward transactions

Manually roll-forward transactions

Manual Log Shipping Restore Log Problems

Manual Log Shipping Restore Log Problems

Manual Log Shipping Restore Log Problems

Manual log shipping

Managing sql Log File

Blog Archive

About Me