how do i look through the log to see updates to tables in sql sever 2005?

hi. changes were made to a dababase and we need to read the log file to see how much exactly was changed and what. how do i read the log file in sql server 2005 with out buying 3rd party software?I posted a reply in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=655927&SiteID=1|||

hi winston,

if you want a cheaper alternative to know what has changed

you can use a database snapshot solution

as a baseline for comparison.

there's no easy way of looking at the logs

you have to use a 3rd party tools such as lumigent or apex sql

joey

|||thanks lads. i ended up getting ApexSQL Log.

How do I find the source of mystery backups?

I went into the Activity Log to look for one thing, and found something else
very troubling. Starting one week ago, something is triggering weekly full
backups and daily transaction log backups, but they are not on my backup
plan, nor can I tell from the log where the backup files are being dumped.
The regularity tells me that this is something that's been scheduled, but I
have checked all maintenance plans, and all jobs, and I see nothing that
correlates to the times of these backups or to the specific databases.
Can something outside of Sql run these backups?
I have changed the server property to record all logins and not just failed
logins, but what else can I do to trace what is happening?
Our tech just said it could be nothing more than the tape backups of the Sql
server, but I don't understand how the copying of mdf and ldf files to tape
could generate entries in the sql activity log that are identical in wording
to the entries made from my regularly scheduled backups.
Oh, and the puzzle that first drew me to the activity log: Starting last
week, the same time that this all started, my differential backups were the
same size as full backups. Is there a connection?
On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
<BevKaufman@.discussions.microsoft.com> wrote:

>I went into the Activity Log to look for one thing, and found something else
>very troubling. Starting one week ago, something is triggering weekly full
>backups and daily transaction log backups, but they are not on my backup
>plan, nor can I tell from the log where the backup files are being dumped.
A great deal of information on every BACKUP and RESTORE command is
logged in the msdb database. I haven't played with it recently - and
if you mentioned the version of SQL Server you are using I missed it -
but I am pretty sure you will find the destination of each BACKUP
somewhere there.
Roy Harvey
Beacon Falls, CT
|||Yes, to add to Roy's comment, all the information is on some msdb tables.
The user who performed the backup is on the user_name field on the backupset
table. Some other information about the backups is on the backupfile and
backupmediafamily system tables.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Roy Harvey (SQL Server MVP)" wrote:

> On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
> <BevKaufman@.discussions.microsoft.com> wrote:
>
> A great deal of information on every BACKUP and RESTORE command is
> logged in the msdb database. I haven't played with it recently - and
> if you mentioned the version of SQL Server you are using I missed it -
> but I am pretty sure you will find the destination of each BACKUP
> somewhere there.
> Roy Harvey
> Beacon Falls, CT
>
|||Hi,
Try this under msdb database:
select distinct * from backupset,backupfile,backupmediafamily where
backupset.backup_set_id=backupfile.backup_set_id
and backupset.media_set_id=backupmediafamily.media_set _id
order by backup_start_date desc
Manu
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Yes, to add to Roy's comment, all the information is on some msdb tables.
> The user who performed the backup is on the user_name field on the backupset
> table. Some other information about the backups is on the backupfile and
> backupmediafamily system tables.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Roy Harvey (SQL Server MVP)" wrote:

How do I find the source of mystery backups?

I went into the Activity Log to look for one thing, and found something else
very troubling. Starting one week ago, something is triggering weekly full
backups and daily transaction log backups, but they are not on my backup
plan, nor can I tell from the log where the backup files are being dumped.
The regularity tells me that this is something that's been scheduled, but I
have checked all maintenance plans, and all jobs, and I see nothing that
correlates to the times of these backups or to the specific databases.
Can something outside of Sql run these backups?
I have changed the server property to record all logins and not just failed
logins, but what else can I do to trace what is happening?
Our tech just said it could be nothing more than the tape backups of the Sql
server, but I don't understand how the copying of mdf and ldf files to tape
could generate entries in the sql activity log that are identical in wording
to the entries made from my regularly scheduled backups.
Oh, and the puzzle that first drew me to the activity log: Starting last
week, the same time that this all started, my differential backups were the
same size as full backups. Is there a connection?> Can something outside of Sql run these backups?
SQL Server doesn't backup something by itself. It is always something outside SQL Server that
initiates the backup (logs on and executes the backup command). Most often, this is an SQL Server
Agent job (like for instance a maint plan).
> Our tech just said it could be nothing more than the tape backups of the Sql
> server, but I don't understand how the copying of mdf and ldf files to tape
> could generate entries in the sql activity log that are identical in wording
> to the entries made from my regularly scheduled backups.
I'm confused? Are you copying the database files directly? Or are you using some kind of snapshot
technology (VSS)? You could try to stop the SQL Server VSS writer service to find out, but be aware
that your snapshot backups (if such are done) will not be proper while the service is off.
Also, you can use Profiler to log what is happening a these times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:6F3E1830-A4D2-41E9-945C-86C2C30338A4@.microsoft.com...
>I went into the Activity Log to look for one thing, and found something else
> very troubling. Starting one week ago, something is triggering weekly full
> backups and daily transaction log backups, but they are not on my backup
> plan, nor can I tell from the log where the backup files are being dumped.
> The regularity tells me that this is something that's been scheduled, but I
> have checked all maintenance plans, and all jobs, and I see nothing that
> correlates to the times of these backups or to the specific databases.
> Can something outside of Sql run these backups?
> I have changed the server property to record all logins and not just failed
> logins, but what else can I do to trace what is happening?
> Our tech just said it could be nothing more than the tape backups of the Sql
> server, but I don't understand how the copying of mdf and ldf files to tape
> could generate entries in the sql activity log that are identical in wording
> to the entries made from my regularly scheduled backups.
> Oh, and the puzzle that first drew me to the activity log: Starting last
> week, the same time that this all started, my differential backups were the
> same size as full backups. Is there a connection?
>|||On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
<BevKaufman@.discussions.microsoft.com> wrote:
>I went into the Activity Log to look for one thing, and found something else
>very troubling. Starting one week ago, something is triggering weekly full
>backups and daily transaction log backups, but they are not on my backup
>plan, nor can I tell from the log where the backup files are being dumped.
A great deal of information on every BACKUP and RESTORE command is
logged in the msdb database. I haven't played with it recently - and
if you mentioned the version of SQL Server you are using I missed it -
but I am pretty sure you will find the destination of each BACKUP
somewhere there.
Roy Harvey
Beacon Falls, CT|||Yes, to add to Roy's comment, all the information is on some msdb tables.
The user who performed the backup is on the user_name field on the backupset
table. Some other information about the backups is on the backupfile and
backupmediafamily system tables.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Roy Harvey (SQL Server MVP)" wrote:
> On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
> <BevKaufman@.discussions.microsoft.com> wrote:
> >I went into the Activity Log to look for one thing, and found something else
> >very troubling. Starting one week ago, something is triggering weekly full
> >backups and daily transaction log backups, but they are not on my backup
> >plan, nor can I tell from the log where the backup files are being dumped.
> A great deal of information on every BACKUP and RESTORE command is
> logged in the msdb database. I haven't played with it recently - and
> if you mentioned the version of SQL Server you are using I missed it -
> but I am pretty sure you will find the destination of each BACKUP
> somewhere there.
> Roy Harvey
> Beacon Falls, CT
>|||Hi,
Try this under msdb database:
select distinct * from backupset,backupfile,backupmediafamily where
backupset.backup_set_id=backupfile.backup_set_id
and backupset.media_set_id=backupmediafamily.media_set_id
order by backup_start_date desc
Manu
"Ben Nevarez" wrote:
> Yes, to add to Roy's comment, all the information is on some msdb tables.
> The user who performed the backup is on the user_name field on the backupset
> table. Some other information about the backups is on the backupfile and
> backupmediafamily system tables.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Roy Harvey (SQL Server MVP)" wrote:
> > On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
> > <BevKaufman@.discussions.microsoft.com> wrote:
> >
> > >I went into the Activity Log to look for one thing, and found something else
> > >very troubling. Starting one week ago, something is triggering weekly full
> > >backups and daily transaction log backups, but they are not on my backup
> > >plan, nor can I tell from the log where the backup files are being dumped.
> >
> > A great deal of information on every BACKUP and RESTORE command is
> > logged in the msdb database. I haven't played with it recently - and
> > if you mentioned the version of SQL Server you are using I missed it -
> > but I am pretty sure you will find the destination of each BACKUP
> > somewhere there.
> >
> > Roy Harvey
> > Beacon Falls, CT
> >

How do I find the source of mystery backups?

I went into the Activity Log to look for one thing, and found something else
very troubling. Starting one week ago, something is triggering weekly full
backups and daily transaction log backups, but they are not on my backup
plan, nor can I tell from the log where the backup files are being dumped.
The regularity tells me that this is something that's been scheduled, but I
have checked all maintenance plans, and all jobs, and I see nothing that
correlates to the times of these backups or to the specific databases.
Can something outside of Sql run these backups?
I have changed the server property to record all logins and not just failed
logins, but what else can I do to trace what is happening?
Our tech just said it could be nothing more than the tape backups of the Sql
server, but I don't understand how the copying of mdf and ldf files to tape
could generate entries in the sql activity log that are identical in wording
to the entries made from my regularly scheduled backups.
Oh, and the puzzle that first drew me to the activity log: Starting last
week, the same time that this all started, my differential backups were the
same size as full backups. Is there a connection?> Can something outside of Sql run these backups?
SQL Server doesn't backup something by itself. It is always something outsid
e SQL Server that
initiates the backup (logs on and executes the backup command). Most often,
this is an SQL Server
Agent job (like for instance a maint plan).

> Our tech just said it could be nothing more than the tape backups of the S
ql
> server, but I don't understand how the copying of mdf and ldf files to tap
e
> could generate entries in the sql activity log that are identical in wordi
ng
> to the entries made from my regularly scheduled backups.
I'm confused? Are you copying the database files directly? Or are you using
some kind of snapshot
technology (VSS)? You could try to stop the SQL Server VSS writer service to
find out, but be aware
that your snapshot backups (if such are done) will not be proper while the s
ervice is off.
Also, you can use Profiler to log what is happening a these times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:6F3E1830-A4D2-41E9-945C-86C2C30338A4@.microsoft.com...
>I went into the Activity Log to look for one thing, and found something els
e
> very troubling. Starting one week ago, something is triggering weekly ful
l
> backups and daily transaction log backups, but they are not on my backup
> plan, nor can I tell from the log where the backup files are being dumped.
> The regularity tells me that this is something that's been scheduled, but
I
> have checked all maintenance plans, and all jobs, and I see nothing that
> correlates to the times of these backups or to the specific databases.
> Can something outside of Sql run these backups?
> I have changed the server property to record all logins and not just faile
d
> logins, but what else can I do to trace what is happening?
> Our tech just said it could be nothing more than the tape backups of the S
ql
> server, but I don't understand how the copying of mdf and ldf files to tap
e
> could generate entries in the sql activity log that are identical in wordi
ng
> to the entries made from my regularly scheduled backups.
> Oh, and the puzzle that first drew me to the activity log: Starting last
> week, the same time that this all started, my differential backups were th
e
> same size as full backups. Is there a connection?
>|||On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
<BevKaufman@.discussions.microsoft.com> wrote:

>I went into the Activity Log to look for one thing, and found something els
e
>very troubling. Starting one week ago, something is triggering weekly full
>backups and daily transaction log backups, but they are not on my backup
>plan, nor can I tell from the log where the backup files are being dumped.
A great deal of information on every BACKUP and RESTORE command is
logged in the msdb database. I haven't played with it recently - and
if you mentioned the version of SQL Server you are using I missed it -
but I am pretty sure you will find the destination of each BACKUP
somewhere there.
Roy Harvey
Beacon Falls, CT|||Yes, to add to Roy's comment, all the information is on some msdb tables.
The user who performed the backup is on the user_name field on the backupset
table. Some other information about the backups is on the backupfile and
backupmediafamily system tables.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Roy Harvey (SQL Server MVP)" wrote:

> On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
> <BevKaufman@.discussions.microsoft.com> wrote:
>
> A great deal of information on every BACKUP and RESTORE command is
> logged in the msdb database. I haven't played with it recently - and
> if you mentioned the version of SQL Server you are using I missed it -
> but I am pretty sure you will find the destination of each BACKUP
> somewhere there.
> Roy Harvey
> Beacon Falls, CT
>|||Hi,
Try this under msdb database:
select distinct * from backupset,backupfile,backupmediafamily where
backupset.backup_set_id=backupfile.backup_set_id
and backupset.media_set_id=backupmediafamily.media_set_id
order by backup_start_date desc
Manu
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Yes, to add to Roy's comment, all the information is on some msdb tables.
> The user who performed the backup is on the user_name field on the backups
et
> table. Some other information about the backups is on the backupfile and
> backupmediafamily system tables.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Roy Harvey (SQL Server MVP)" wrote:
>

How do I find out what prevents my log file from being truncated

Hi
I think I need a little help on Log file architecture.
I have a database where I temporarily disabled my log backup schedules. We
did some updates on the database, and I didn't wanted the backup jobs to
kick in in the middle of the update.
Now I've enabled the log backups again, but of course my logfiles has now
grown to a size that it will never reach again in normal production. I'd
therefore like to shrink the logfile to a more feasible size, but I don't
seem to have much luck with that.
I've backed up the logfile, but when I then issue a DBCC SHRINKFILE command,
I get the message -
'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
use.'
If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
open transaction but that reports no active transactions.
My question is now, how do I find out that it is that prevents me from
shrinking the file. I assume that I'll have to chekc the Virtual Log Files,
but how do I do this? I've looked in BOL but I can't find any description of
how I can "see" the logfile "structure"? I know that this has been covered
in this newsgroup before, but I can't find any threads that helps me right
now.
Regards
Steen
Steen
What target size have you specified in DBCC SHRINKFILE command? Try to
increase it.
DECLARE @.db INT
SELECT @.db=db_id()
DBCC LOGINFO (@.db)
Pay attention on status column . If you see that value=2 is the last row
that means you have 'opened' ',in process' transaction
Perfom dummy inserst on order to move this value=2 at the top and then try
to shrink file
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
command,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log
Files,
> but how do I do this? I've looked in BOL but I can't find any description
of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>
|||Steen
Have a look at these articles.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default...b;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Regards
John
"Steen Persson" wrote:

> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE command,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log Files,
> but how do I do this? I've looked in BOL but I can't find any description of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>
>
|||Hi Uri
Thanks for your reply.
I have actually tried to increase the target size, but that still doesn't
seems to do the trick. Also if I look at the physical file, it is approx. 21
GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 11
% i.e. the file should in theory only be taking up approx. 2 GB. Also if I
run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
records has the status 2. I can't find any describtion in BOL of the LOGINFO
command, so I'm not quite sure how to read it, but does the status 0 means
that it's "old" transactions that can be removed?
It might very well be that I still have some active transcations that
prevents my logfile from shrinking, but it puzzles me that I don't seems to
be able to "see" these transcations anywhere.
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> What target size have you specified in DBCC SHRINKFILE command? Try to
> increase it.
> DECLARE @.db INT
> SELECT @.db=db_id()
> DBCC LOGINFO (@.db)
> Pay attention on status column . If you see that value=2 is the last
> row that means you have 'opened' ',in process' transaction
> Perfom dummy inserst on order to move this value=2 at the top and
> then try to shrink file
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
|||The file can only be shrunk from the end towards the beginning. You need to get 0 at the end. backup
the log to see if that makes you have 0 at the end. If not, do some dummy modifications so the head
of the log moves toward the beginning (in 2000, the shrink command should do this for you). Then
backup again. When you have 0 at the end, you can shrink it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hi Uri
> Thanks for your reply.
> I have actually tried to increase the target size, but that still doesn't
> seems to do the trick. Also if I look at the physical file, it is approx. 21
> GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 11
> % i.e. the file should in theory only be taking up approx. 2 GB. Also if I
> run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
> records has the status 2. I can't find any describtion in BOL of the LOGINFO
> command, so I'm not quite sure how to read it, but does the status 0 means
> that it's "old" transactions that can be removed?
> It might very well be that I still have some active transcations that
> prevents my logfile from shrinking, but it puzzles me that I don't seems to
> be able to "see" these transcations anywhere.
> Regards
> Steen
>
>
> Uri Dimant wrote:
>
|||Hi Tibor
I've also read that SQL 2000 fills in the "dummy" records when I do the
SHRINKFILE, but I think I've missed something. You say that the dummys are
being inserted by the SHRINKFILE command and then I have to backup again and
then I can run the SHRINKFILE once more. I haven't got that little "twist"
until now, so it sounds like it's the 2nd. backup I'm missing. When I think
about it I should have thought about that, but from BOL I had got the
impression that SQL2000 did it "behind the scenes".
After I've done the backup once more, it works like a charm....:-).
Thanks for your inputs everybody...
Regards
Steen
Tibor Karaszi wrote:[vbcol=seagreen]
> The file can only be shrunk from the end towards the beginning. You
> need to get 0 at the end. backup the log to see if that makes you
> have 0 at the end. If not, do some dummy modifications so the head of
> the log moves toward the beginning (in 2000, the shrink command
> should do this for you). Then backup again. When you have 0 at the
> end, you can shrink it.
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx.gbl...

How do I find out what prevents my log file from being truncated

Hi
I think I need a little help on Log file architecture.
I have a database where I temporarily disabled my log backup schedules. We
did some updates on the database, and I didn't wanted the backup jobs to
kick in in the middle of the update.
Now I've enabled the log backups again, but of course my logfiles has now
grown to a size that it will never reach again in normal production. I'd
therefore like to shrink the logfile to a more feasible size, but I don't
seem to have much luck with that.
I've backed up the logfile, but when I then issue a DBCC SHRINKFILE command,
I get the message -
'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
use.'
If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
open transaction but that reports no active transactions.
My question is now, how do I find out that it is that prevents me from
shrinking the file. I assume that I'll have to chekc the Virtual Log Files,
but how do I do this? I've looked in BOL but I can't find any description of
how I can "see" the logfile "structure"? I know that this has been covered
in this newsgroup before, but I can't find any threads that helps me right
now.
Regards
SteenSteen
What target size have you specified in DBCC SHRINKFILE command? Try to
increase it.
DECLARE @.db INT
SELECT @.db=db_id()
DBCC LOGINFO (@.db)
Pay attention on status column . If you see that value=2 is the last row
that means you have 'opened' ',in process' transaction
Perfom dummy inserst on order to move this value=2 at the top and then try
to shrink file
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
command,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log
Files,
> but how do I do this? I've looked in BOL but I can't find any description
of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>|||Steen
Have a look at these articles.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Regards
John
"Steen Persson" wrote:

> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE comman
d,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log Files
,
> but how do I do this? I've looked in BOL but I can't find any description
of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>
>|||Hi Uri
Thanks for your reply.
I have actually tried to increase the target size, but that still doesn't
seems to do the trick. Also if I look at the physical file, it is approx. 21
GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 11
% i.e. the file should in theory only be taking up approx. 2 GB. Also if I
run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
records has the status 2. I can't find any describtion in BOL of the LOGINFO
command, so I'm not quite sure how to read it, but does the status 0 means
that it's "old" transactions that can be removed?
It might very well be that I still have some active transcations that
prevents my logfile from shrinking, but it puzzles me that I don't seems to
be able to "see" these transcations anywhere.
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> What target size have you specified in DBCC SHRINKFILE command? Try to
> increase it.
> DECLARE @.db INT
> SELECT @.db=db_id()
> DBCC LOGINFO (@.db)
> Pay attention on status column . If you see that value=2 is the last
> row that means you have 'opened' ',in process' transaction
> Perfom dummy inserst on order to move this value=2 at the top and
> then try to shrink file
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...|||The file can only be shrunk from the end towards the beginning. You need to
get 0 at the end. backup
the log to see if that makes you have 0 at the end. If not, do some dummy mo
difications so the head
of the log moves toward the beginning (in 2000, the shrink command should do
this for you). Then
backup again. When you have 0 at the end, you can shrink it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx
.gbl...
> Hi Uri
> Thanks for your reply.
> I have actually tried to increase the target size, but that still doesn't
> seems to do the trick. Also if I look at the physical file, it is approx.
21
> GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 1
1
> % i.e. the file should in theory only be taking up approx. 2 GB. Also if I
> run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
> records has the status 2. I can't find any describtion in BOL of the LOGIN
FO
> command, so I'm not quite sure how to read it, but does the status 0 means
> that it's "old" transactions that can be removed?
> It might very well be that I still have some active transcations that
> prevents my logfile from shrinking, but it puzzles me that I don't seems t
o
> be able to "see" these transcations anywhere.
> Regards
> Steen
>
>
> Uri Dimant wrote:
>|||Hi Tibor
I've also read that SQL 2000 fills in the "dummy" records when I do the
SHRINKFILE, but I think I've missed something. You say that the dummys are
being inserted by the SHRINKFILE command and then I have to backup again and
then I can run the SHRINKFILE once more. I haven't got that little "twist"
until now, so it sounds like it's the 2nd. backup I'm missing. When I think
about it I should have thought about that, but from BOL I had got the
impression that SQL2000 did it "behind the scenes".
After I've done the backup once more, it works like a charm....:-).
Thanks for your inputs everybody...
Regards
Steen
Tibor Karaszi wrote:[vbcol=seagreen]
> The file can only be shrunk from the end towards the beginning. You
> need to get 0 at the end. backup the log to see if that makes you
> have 0 at the end. If not, do some dummy modifications so the head of
> the log moves toward the beginning (in 2000, the shrink command
> should do this for you). Then backup again. When you have 0 at the
> end, you can shrink it.
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx.gbl...

How do I find out what prevents my log file from being truncated

Hi
I think I need a little help on Log file architecture.
I have a database where I temporarily disabled my log backup schedules. We
did some updates on the database, and I didn't wanted the backup jobs to
kick in in the middle of the update.
Now I've enabled the log backups again, but of course my logfiles has now
grown to a size that it will never reach again in normal production. I'd
therefore like to shrink the logfile to a more feasible size, but I don't
seem to have much luck with that.
I've backed up the logfile, but when I then issue a DBCC SHRINKFILE command,
I get the message -
'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
use.'
If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
open transaction but that reports no active transactions.
My question is now, how do I find out that it is that prevents me from
shrinking the file. I assume that I'll have to chekc the Virtual Log Files,
but how do I do this? I've looked in BOL but I can't find any description of
how I can "see" the logfile "structure"? I know that this has been covered
in this newsgroup before, but I can't find any threads that helps me right
now.
Regards
SteenSteen
What target size have you specified in DBCC SHRINKFILE command? Try to
increase it.
DECLARE @.db INT
SELECT @.db=db_id()
DBCC LOGINFO (@.db)
Pay attention on status column . If you see that value=2 is the last row
that means you have 'opened' ',in process' transaction
Perfom dummy inserst on order to move this value=2 at the top and then try
to shrink file
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
command,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log
Files,
> but how do I do this? I've looked in BOL but I can't find any description
of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>|||Steen
Have a look at these articles.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Regards
John
"Steen Persson" wrote:
> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE command,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log Files,
> but how do I do this? I've looked in BOL but I can't find any description of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>
>|||Hi Uri
Thanks for your reply.
I have actually tried to increase the target size, but that still doesn't
seems to do the trick. Also if I look at the physical file, it is approx. 21
GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 11
% i.e. the file should in theory only be taking up approx. 2 GB. Also if I
run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
records has the status 2. I can't find any describtion in BOL of the LOGINFO
command, so I'm not quite sure how to read it, but does the status 0 means
that it's "old" transactions that can be removed?
It might very well be that I still have some active transcations that
prevents my logfile from shrinking, but it puzzles me that I don't seems to
be able to "see" these transcations anywhere.
Regards
Steen
Uri Dimant wrote:
> Steen
> What target size have you specified in DBCC SHRINKFILE command? Try to
> increase it.
> DECLARE @.db INT
> SELECT @.db=db_id()
> DBCC LOGINFO (@.db)
> Pay attention on status column . If you see that value=2 is the last
> row that means you have 'opened' ',in process' transaction
> Perfom dummy inserst on order to move this value=2 at the top and
> then try to shrink file
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> I think I need a little help on Log file architecture.
>> I have a database where I temporarily disabled my log backup
>> schedules. We did some updates on the database, and I didn't wanted
>> the backup jobs to kick in in the middle of the update.
>> Now I've enabled the log backups again, but of course my logfiles
>> has now grown to a size that it will never reach again in normal
>> production. I'd therefore like to shrink the logfile to a more
>> feasible size, but I don't seem to have much luck with that.
>> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
>> command, I get the message -
>> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are
>> in use.'
>> If I then run DBCC SQLPERF (Logspace) it tells me that log file
>> space used is 0.68127996 %. I've then tried to run a DBCC OPENTRAN
>> to see if I've any open transaction but that reports no active
>> transactions.
>> My question is now, how do I find out that it is that prevents me
>> from shrinking the file. I assume that I'll have to chekc the
>> Virtual Log Files, but how do I do this? I've looked in BOL but I
>> can't find any description of how I can "see" the logfile
>> "structure"? I know that this has been covered in this newsgroup
>> before, but I can't find any threads that helps me right now.
>> Regards
>> Steen|||The file can only be shrunk from the end towards the beginning. You need to get 0 at the end. backup
the log to see if that makes you have 0 at the end. If not, do some dummy modifications so the head
of the log moves toward the beginning (in 2000, the shrink command should do this for you). Then
backup again. When you have 0 at the end, you can shrink it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hi Uri
> Thanks for your reply.
> I have actually tried to increase the target size, but that still doesn't
> seems to do the trick. Also if I look at the physical file, it is approx. 21
> GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 11
> % i.e. the file should in theory only be taking up approx. 2 GB. Also if I
> run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
> records has the status 2. I can't find any describtion in BOL of the LOGINFO
> command, so I'm not quite sure how to read it, but does the status 0 means
> that it's "old" transactions that can be removed?
> It might very well be that I still have some active transcations that
> prevents my logfile from shrinking, but it puzzles me that I don't seems to
> be able to "see" these transcations anywhere.
> Regards
> Steen
>
>
> Uri Dimant wrote:
>> Steen
>> What target size have you specified in DBCC SHRINKFILE command? Try to
>> increase it.
>> DECLARE @.db INT
>> SELECT @.db=db_id()
>> DBCC LOGINFO (@.db)
>> Pay attention on status column . If you see that value=2 is the last
>> row that means you have 'opened' ',in process' transaction
>> Perfom dummy inserst on order to move this value=2 at the top and
>> then try to shrink file
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> I think I need a little help on Log file architecture.
>> I have a database where I temporarily disabled my log backup
>> schedules. We did some updates on the database, and I didn't wanted
>> the backup jobs to kick in in the middle of the update.
>> Now I've enabled the log backups again, but of course my logfiles
>> has now grown to a size that it will never reach again in normal
>> production. I'd therefore like to shrink the logfile to a more
>> feasible size, but I don't seem to have much luck with that.
>> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
>> command, I get the message -
>> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are
>> in use.'
>> If I then run DBCC SQLPERF (Logspace) it tells me that log file
>> space used is 0.68127996 %. I've then tried to run a DBCC OPENTRAN
>> to see if I've any open transaction but that reports no active
>> transactions.
>> My question is now, how do I find out that it is that prevents me
>> from shrinking the file. I assume that I'll have to chekc the
>> Virtual Log Files, but how do I do this? I've looked in BOL but I
>> can't find any description of how I can "see" the logfile
>> "structure"? I know that this has been covered in this newsgroup
>> before, but I can't find any threads that helps me right now.
>> Regards
>> Steen
>|||Hi Tibor
I've also read that SQL 2000 fills in the "dummy" records when I do the
SHRINKFILE, but I think I've missed something. You say that the dummys are
being inserted by the SHRINKFILE command and then I have to backup again and
then I can run the SHRINKFILE once more. I haven't got that little "twist"
until now, so it sounds like it's the 2nd. backup I'm missing. When I think
about it I should have thought about that, but from BOL I had got the
impression that SQL2000 did it "behind the scenes".
After I've done the backup once more, it works like a charm....:-).
Thanks for your inputs everybody...
Regards
Steen
Tibor Karaszi wrote:
> The file can only be shrunk from the end towards the beginning. You
> need to get 0 at the end. backup the log to see if that makes you
> have 0 at the end. If not, do some dummy modifications so the head of
> the log moves toward the beginning (in 2000, the shrink command
> should do this for you). Then backup again. When you have 0 at the
> end, you can shrink it.
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx.gbl...
>> Hi Uri
>> Thanks for your reply.
>> I have actually tried to increase the target size, but that still
>> doesn't seems to do the trick. Also if I look at the physical file,
>> it is approx. 21 GB. When running DBCC SQLPERF(LogSpace) it tells me
>> that space in use is 11 % i.e. the file should in theory only be
>> taking up approx. 2 GB. Also if I run DBCC LOGINFO it gives me 1641
>> records, and only the last approx 200 records has the status 2. I
>> can't find any describtion in BOL of the LOGINFO command, so I'm not
>> quite sure how to read it, but does the status 0 means that it's
>> "old" transactions that can be removed?
>> It might very well be that I still have some active transcations that
>> prevents my logfile from shrinking, but it puzzles me that I don't
>> seems to be able to "see" these transcations anywhere.
>> Regards
>> Steen
>>
>>
>> Uri Dimant wrote:
>> Steen
>> What target size have you specified in DBCC SHRINKFILE command? Try
>> to increase it.
>> DECLARE @.db INT
>> SELECT @.db=db_id()
>> DBCC LOGINFO (@.db)
>> Pay attention on status column . If you see that value=2 is the last
>> row that means you have 'opened' ',in process' transaction
>> Perfom dummy inserst on order to move this value=2 at the top and
>> then try to shrink file
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
>> Hi
>> I think I need a little help on Log file architecture.
>> I have a database where I temporarily disabled my log backup
>> schedules. We did some updates on the database, and I didn't wanted
>> the backup jobs to kick in in the middle of the update.
>> Now I've enabled the log backups again, but of course my logfiles
>> has now grown to a size that it will never reach again in normal
>> production. I'd therefore like to shrink the logfile to a more
>> feasible size, but I don't seem to have much luck with that.
>> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
>> command, I get the message -
>> 'Cannot shrink log file 2 (xxxxxx) because all logical log files
>> are in use.'
>> If I then run DBCC SQLPERF (Logspace) it tells me that log file
>> space used is 0.68127996 %. I've then tried to run a DBCC OPENTRAN
>> to see if I've any open transaction but that reports no active
>> transactions.
>> My question is now, how do I find out that it is that prevents me
>> from shrinking the file. I assume that I'll have to chekc the
>> Virtual Log Files, but how do I do this? I've looked in BOL but I
>> can't find any description of how I can "see" the logfile
>> "structure"? I know that this has been covered in this newsgroup
>> before, but I can't find any threads that helps me right now.
>> Regards
>> Steen

How do I figure out where SSIS logs are?

Dear all,

I was wondering how to open .LOG files created for a SSIS execution package.

Thanks in advance,

Open them in a text editor. I'm a bag fan of Textpad (www.textpad.com)

You control where they are. You tell tell the package where to log to.

-Jamie

|||

Hi Jamie,

Come on man, textpad? Is my favourite without a doubt, I like it a lot too.

I was saying, in what path ssis leave its logs. where?

Thanks again

|||

Like I said previous, the logs are put wherever you tell SSIS to put them. That can be a text file (it is you, not SSIS, that defines where that file resides), SQL Server (in which case they'll be in the msdn..sysdtspackageslog90 table if memory serves), event log, SQL Profiler or an XML file.

-Jamie

How do I figure out time and date at the same time?

Dear all,
I've got some problems with date and time (very silly, I know)
select * from crm_1 where log < datepart(yyyy,getdate())
and log < datepart(mm,getdate())
and log < datepart(dd,getdate())
The aforementioned query doesn't find this value:
2005-06-09 08:27:17.810
CREATE TABLE [dbo].[CRM_1] (
[ipcliente] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
[log] [datetime] NOT NULL)
GO
Thanks a lot and regards,I've elaborated on that topic here: http://www.karaszi.com/SQLServer/in...ime
.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:59611C1B-3D45-407E-B593-3D4CB5C9A0E8@.microsoft.com...
> Dear all,
> I've got some problems with date and time (very silly, I know)
> select * from crm_1 where log < datepart(yyyy,getdate())
> and log < datepart(mm,getdate())
> and log < datepart(dd,getdate())
> The aforementioned query doesn't find this value:
> 2005-06-09 08:27:17.810
> CREATE TABLE [dbo].[CRM_1] (
> [ipcliente] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
> [log] [datetime] NOT NULL)
> GO
> Thanks a lot and regards,|||Try this:
select * from crm_1 where datepart(yyyy,log) < datepart(yyyy,getdate())
and datepart(mm,log) < datepart(mm,getdate())
and datepart(dd,log) < datepart(dd,getdate())
Or better yet, do some more reading as Tibor suggests.
ML|||It has been very useful. thanks a milion
"Tibor Karaszi" wrote:

> I've elaborated on that topic here: http://www.karaszi.com/SQLServer/in...i
me.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:59611C1B-3D45-407E-B593-3D4CB5C9A0E8@.microsoft.com...
>

how do i empty the transaction log

good day to everybody

I have here again encountered the problem that the transaction log came too big that it consume my 10 gig hardisk. How do I empty this in a simplest way.

I follow the error message to backup the transaction log to empty the table, but i think it's still the same. I did not see the difference, in short nothing happens. it doesn't empty the transaction log.

So what I did is make another database with different name (ex. tempdatabase), and import the tables including the record on the original database... then delete the original database which the transaction log got too big, and after that make a new database with the name of the original database and again import from the tempdatabase, and manually create the index for every table. It works OK, but is there any other way aside from my own way which is much easier and not time consuming.?

Thank you very much . . . and any help is very much appreciated.
MadixHi Madix,

Do you have any replications on the database whose log is too large? And if there is, is it transactional or merge replication? And what is the recovery mode of your database?

BACKUP LOG without NO TRUNCATE should help to make the logical log file shorter. But it may not reduce the size of a physical log file. You may consider to shrink the log (see BOL "Shrinking the transaction log").

Thanks,
-Peng|||actually sir, i use SQL Server database as my back-end database for my VISUAL FOXPRO front-end, and it has no other problem except that one. So I did not bother to study some of the feature of SQL capabilities. I do not know what the sql server is doing inside of my system.

I make a database, add a table, & select a key field in SQL Server then close it, set the back-up time, that's all i know in SQL. that is why when i encounter this problem, i've got a headache using my own way. it waste time. Maybe its time to know and ask from the experts what is the shortiest solution to solve this problem.

so far that is the only problem i encounter using it in Visual Foxpro.

Thanks for any help..

madix|||The simplest way to resolve this is to change the recovery mode to Simple.
This will remove the need for a transaction log, but may not meet your DR requirements.|||Hi,

U can also try this command below

DUMP TRAN <DATABASE NAME> WITH NO_LOG

Regards
Sachin

How do i disable Windows Authentication mode to log into the DB?

How do i disable Windows Authentication mode and just have SQL Server Authentication mode?

is it even possible?

Yes possible. Right click on the Server -> Properties -> Security.|||

theres only two choices:

Windows Authentication mode OR BOTH Windows and SQL Authentication.

I just want SQL Authentication.

|||You cannot disable windows authentication entirely. Also, enabling windows authentication does not mean every windows user can login. The windows login should be added to the logins module to access the db.