A Different Kind of MSSQL Question...

Hey all!
This may sound like a weird one but i couldn't think of a better audience to ask!

If you are a SQL Server DBA (or were, or aspire to be, or play one on T.V., etc.) and the CIO of your 2.something billion $ a year company has offered you an 1 hour forum to sit down and ask him anything you want with the pretense of getting a straight answer, what would you ask?

I know this offers an excellent opportunity to cut up a bit...and do so if you must (keeping us entertained around here is important too!)...but i'm looking for work related, SQL related, direction, strategy, etc, etc type stuff.
Not: 'Whats it like to drive a $250k 'benz to work everyday?' OR 'Can i have .5% of your $2,000,000 bonus this year?'

While i genuinely have those questions in my mind...i'm not looking to waste his $5,000 an hour ass's time - and I only have 1 hour to chew his ear off.

What do ya'll think?...not knowing the type of business you're involved w/ ...I guess I would take the slant of trying to find out how secure the IT division is within the company...job security these days is fleeting, I've been fortunate only 3 co.'s ni 25 years...

...on the lighter side...I'd want to know what he's investing in!!!|||Depends on what you want. A raise, promotion, job security, new career, to marry his daughter, to make an impression, or to be forgotten. What is the industry? Is he technical? Find how how to educate him about your field and it's importance. Ask him what his vision for the company is and where it will be in 5 or 15 years. How long does he plan on being at the helm? What would he do differently if he had to build the company again (assuming he did build it). Ask him who is competitors are and what about them he fears. Ask how you and your department can help him and your company be more competitive. Find out what his interests and hobbies are, what his family is like, where he grew up, make small talk about his home town or college. Ask him what the most difficult part of running a 2 billion$ company is. What is the most enjoyable part? Ask him why he does it - gets up every day and comes to work instead of retiring. Ask to take him to lunch. Do you want to run a 2 billion$ company some day?|||The CIO? As in not the CEO? I would probably want to see what the overall strategy for the department is What are the big projects coming down the line that always seem to take the line support folks by surprise.

Planning on consolidating all of the SQL Servers in the company onto one big Unisys machine?
Declaring parts of the SQL Server landscape to be High Availability?
Designing a Disaster Recovery (or is that Business Continuity, now?) site? Maybe develop plans for one?
What is the strategy for growing the department, or hiring strategy? Just hire College grads who may be eager, but are not very experienced, or hire veterans? One way, you are in an endless training loop. The other way may limit your career growth opportunities, if too many people get hired above you.
Then of course, what are the training options? Will people get transferred to other teams regularly? Get trained regularly?
Which brings us to strategies for new technologies. Wait for stuff to get de-supported before upgrading, or use all beta software?

There are a lot of questions to choose from, but I think the most important one would be: "Do you read DBForums?"|||"Do you read DBForums?"
HAH! If so...i hope he at least appreciates my interest in getting something out of this opportunity! (Though i'd wonder who was steering the ship if he's knocking off around here!)

Depends on what you want.
Looking for some glimpse into the motivations behind the man and/or the title. I believe in order to come to some understanding about someone you must first understand what moves him (beyond the basic electomagnetism technically moving everthing).

How long does he plan on being at the helm?
oooo...good one.

type of business you're involved w/
Where else can you generate a 2+ billion $ a year revenue stream these days...drug dealing, of course...uh...i mean Healthcare.


Thanks all!

Cheers,
Oddsql

A binary field problem

why does mssql encrypt or convert the values (entered through sql query into a binary field) into hexadecimal values. When I query and print them from php, it displays some unicode characters. How do I solve this?Hi
shaktishakya
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

It will help Experts in the forum in solving/underestanding your problem in a better way.

Please follow the posting guidelines in every new post/reply.

4 Transaction Log questions

1) Can one find the location of the 'Transaction Log' at: <Hard
Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?

2) Is it safe to delete it, as SQL will create a new Transaction Log when it
realises none already exist?

3) When trying to do 2) I get error message that file is in use, even though
SQL Server is closed. Any suggestions?

4) Can anyone shed light on the error message 'Write on ... <my path
here>... failed, status = 112. BACKUP LOG is terminating abnormally'? I get
this when trying to manually backup the Transaction Log 'All Tasks - Backup'

Thanks for any replies to my ignorance.See inline:
"TZoner" <tzoner@.hotmail.com> wrote in message
news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> 1) Can one find the location of the 'Transaction Log' at: <Hard
> Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
I am not sure what you are asking but sp_helpfile will give the names and
locations of the files associated with a current database.
> 2) Is it safe to delete it, as SQL will create a new Transaction Log when
it
> realises none already exist?
I would not recommend deleting any logfile. If SQLServer is running then you
won't be able to as the file is locked. If you have detatched the database
or SQL Server is not running, you may have problems re-attaching the
database or connecting to it once SQL Server is running.

> 3) When trying to do 2) I get error message that file is in use, even
though
> SQL Server is closed. Any suggestions?
I doubt if you have stopped the service, but in any case you should not be
deleting it.
If all you want to do is move the file then check out
http://support.microsoft.com/defaul...kb;EN-US;224071

If you want to shrink it check out
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_da2_1uzr.asp

> 4) Can anyone shed light on the error message 'Write on ... <my path
> here>... failed, status = 112. BACKUP LOG is terminating abnormally'? I
get
> this when trying to manually backup the Transaction Log 'All Tasks -
Backup'
I don't know what this error is, at a guess there is a lack of disk space.
Maybe there is more information in the SQL Server log file or in the Event
log. You will also get errors if you backup to a mapped network drive.

John
>
> Thanks for any replies to my ignorance.|||John, thanks for the valuable information.

Where is the 'Transaction Log' located? I can't find the answer sp_helpfile
or after endless searches on google.

Does a Transaction Log exist per database in SQL Server? When backup occurs,
does these Transaction Log shrink?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> See inline:
> "TZoner" <tzoner@.hotmail.com> wrote in message
> news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> I am not sure what you are asking but sp_helpfile will give the names and
> locations of the files associated with a current database.
> > 2) Is it safe to delete it, as SQL will create a new Transaction Log
when
> it
> > realises none already exist?
> I would not recommend deleting any logfile. If SQLServer is running then
you
> won't be able to as the file is locked. If you have detatched the database
> or SQL Server is not running, you may have problems re-attaching the
> database or connecting to it once SQL Server is running.
> > 3) When trying to do 2) I get error message that file is in use, even
> though
> > SQL Server is closed. Any suggestions?
> I doubt if you have stopped the service, but in any case you should not be
> deleting it.
> If all you want to do is move the file then check out
> http://support.microsoft.com/defaul...kb;EN-US;224071
> If you want to shrink it check out
http://msdn.microsoft.com/library/d...-us/architec/8_
> ar_da2_1uzr.asp
> > 4) Can anyone shed light on the error message 'Write on ... <my path
> > here>... failed, status = 112. BACKUP LOG is terminating abnormally'? I
> get
> > this when trying to manually backup the Transaction Log 'All Tasks -
> Backup'
> I don't know what this error is, at a guess there is a lack of disk space.
> Maybe there is more information in the SQL Server log file or in the Event
> log. You will also get errors if you backup to a mapped network drive.
> John
> > Thanks for any replies to my ignorance.|||Your transction logs are held in the ldf file which sp_help would give e.g
for Northwind you would get something like:

Name FileId filename filegroup Size Maxsize Growth Usage
Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB Unlimited
10% log only

Northwind_log is the internal logical name for the Transaction Log file
located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a log
file.

The log file will not automatically shrink on backup, see the link I posted
in the last reply regarding how to shrink it.

John

"TZoner" <tzoner@.hotmail.com> wrote in message
news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> John, thanks for the valuable information.
> Where is the 'Transaction Log' located? I can't find the answer
sp_helpfile
> or after endless searches on google.
> Does a Transaction Log exist per database in SQL Server? When backup
occurs,
> does these Transaction Log shrink?
>
>
>
>
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > See inline:
> > "TZoner" <tzoner@.hotmail.com> wrote in message
> > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> > I am not sure what you are asking but sp_helpfile will give the names
and
> > locations of the files associated with a current database.
> > > > 2) Is it safe to delete it, as SQL will create a new Transaction Log
> when
> > it
> > > realises none already exist?
> > I would not recommend deleting any logfile. If SQLServer is running then
> you
> > won't be able to as the file is locked. If you have detatched the
database
> > or SQL Server is not running, you may have problems re-attaching the
> > database or connecting to it once SQL Server is running.
> > > > 3) When trying to do 2) I get error message that file is in use, even
> > though
> > > SQL Server is closed. Any suggestions?
> > I doubt if you have stopped the service, but in any case you should not
be
> > deleting it.
> > If all you want to do is move the file then check out
> > http://support.microsoft.com/defaul...kb;EN-US;224071
> > If you want to shrink it check out
http://msdn.microsoft.com/library/d...-us/architec/8_
> > ar_da2_1uzr.asp
> > > > 4) Can anyone shed light on the error message 'Write on ... <my path
> > > here>... failed, status = 112. BACKUP LOG is terminating abnormally'?
I
> > get
> > > this when trying to manually backup the Transaction Log 'All Tasks -
> > Backup'
> > I don't know what this error is, at a guess there is a lack of disk
space.
> > Maybe there is more information in the SQL Server log file or in the
Event
> > log. You will also get errors if you backup to a mapped network drive.
> > John
> > > > > Thanks for any replies to my ignorance.
> > >|||John you've nearly solved my dilemma!!

As per your excellent like I was able to work out that running the 2
following lines will reduce the Transaction File back to it's size!!!
Hooray!!

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

However, because MyDb has it's Transaction File full, hence I can't execute
the above two lines. How do I run these commands when MyDb won't let me do
anything to it? To test that the two commands worked I created a dummy MyDb,
grew its Transaction File then executed the two lines and it worked 100%.

Thank you so much for your suggestions!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> Your transction logs are held in the ldf file which sp_help would give e.g
> for Northwind you would get something like:
> Name FileId filename filegroup Size Maxsize Growth Usage
> Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
> Unlimited 10% data only
> Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB Unlimited
> 10% log only
> Northwind_log is the internal logical name for the Transaction Log file
> located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a
log
> file.
> The log file will not automatically shrink on backup, see the link I
posted
> in the last reply regarding how to shrink it.
> John
> "TZoner" <tzoner@.hotmail.com> wrote in message
> news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > John, thanks for the valuable information.
> > Where is the 'Transaction Log' located? I can't find the answer
> sp_helpfile
> > or after endless searches on google.
> > Does a Transaction Log exist per database in SQL Server? When backup
> occurs,
> > does these Transaction Log shrink?
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > See inline:
> > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> > > I am not sure what you are asking but sp_helpfile will give the names
> and
> > > locations of the files associated with a current database.
> > > > > > 2) Is it safe to delete it, as SQL will create a new Transaction Log
> > when
> > > it
> > > > realises none already exist?
> > > I would not recommend deleting any logfile. If SQLServer is running
then
> > you
> > > won't be able to as the file is locked. If you have detatched the
> database
> > > or SQL Server is not running, you may have problems re-attaching the
> > > database or connecting to it once SQL Server is running.
> > > > > > > 3) When trying to do 2) I get error message that file is in use,
even
> > > though
> > > > SQL Server is closed. Any suggestions?
> > > I doubt if you have stopped the service, but in any case you should
not
> be
> > > deleting it.
> > > If all you want to do is move the file then check out
> > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > If you want to shrink it check out
> http://msdn.microsoft.com/library/d...-us/architec/8_
> > > ar_da2_1uzr.asp
> > > > > > > 4) Can anyone shed light on the error message 'Write on ... <my path
> > > > here>... failed, status = 112. BACKUP LOG is terminating
abnormally'?
> I
> > > get
> > > > this when trying to manually backup the Transaction Log 'All Tasks -
> > > Backup'
> > > I don't know what this error is, at a guess there is a lack of disk
> space.
> > > Maybe there is more information in the SQL Server log file or in the
> Event
> > > log. You will also get errors if you backup to a mapped network drive.
> > > > John
> > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > >|||Hi

I am not sure what you mean by not work. As in the link previously posted,
if you last virtual log is in use the file will not shrink

"TZoner" <tzoner@.hotmail.com> wrote in message
news:3ef9a575$0$31280$afc38c87@.news.optusnet.com.a u...
> John you've nearly solved my dilemma!!
> As per your excellent like I was able to work out that running the 2
> following lines will reduce the Transaction File back to it's size!!!
> Hooray!!
> BACKUP LOG MyDb WITH TRUNCATE_ONLY
> DBCC SHRINKFILE (MyDb_log)
> However, because MyDb has it's Transaction File full, hence I can't
execute
> the above two lines. How do I run these commands when MyDb won't let me do
> anything to it? To test that the two commands worked I created a dummy
MyDb,
> grew its Transaction File then executed the two lines and it worked 100%.
> Thank you so much for your suggestions!
>
>
>
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> > Your transction logs are held in the ldf file which sp_help would give
e.g
> > for Northwind you would get something like:
> > Name FileId filename filegroup Size Maxsize Growth
Usage
> > Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
> > Unlimited 10% data only
> > Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB
Unlimited
> > 10% log only
> > Northwind_log is the internal logical name for the Transaction Log file
> > located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a
> log
> > file.
> > The log file will not automatically shrink on backup, see the link I
> posted
> > in the last reply regarding how to shrink it.
> > John
> > "TZoner" <tzoner@.hotmail.com> wrote in message
> > news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > > John, thanks for the valuable information.
> > > > Where is the 'Transaction Log' located? I can't find the answer
> > sp_helpfile
> > > or after endless searches on google.
> > > > Does a Transaction Log exist per database in SQL Server? When backup
> > occurs,
> > > does these Transaction Log shrink?
> > > > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > > See inline:
> > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > > > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> > > > I am not sure what you are asking but sp_helpfile will give the
names
> > and
> > > > locations of the files associated with a current database.
> > > > > > > > 2) Is it safe to delete it, as SQL will create a new Transaction
Log
> > > when
> > > > it
> > > > > realises none already exist?
> > > > I would not recommend deleting any logfile. If SQLServer is running
> then
> > > you
> > > > won't be able to as the file is locked. If you have detatched the
> > database
> > > > or SQL Server is not running, you may have problems re-attaching the
> > > > database or connecting to it once SQL Server is running.
> > > > > > > > > > 3) When trying to do 2) I get error message that file is in use,
> even
> > > > though
> > > > > SQL Server is closed. Any suggestions?
> > > > I doubt if you have stopped the service, but in any case you should
> not
> > be
> > > > deleting it.
> > > > If all you want to do is move the file then check out
> > > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > > > If you want to shrink it check out
> > > http://msdn.microsoft.com/library/d...-us/architec/8_
> > > > ar_da2_1uzr.asp
> > > > > > > > > > 4) Can anyone shed light on the error message 'Write on ... <my
path
> > > > > here>... failed, status = 112. BACKUP LOG is terminating
> abnormally'?
> > I
> > > > get
> > > > > this when trying to manually backup the Transaction Log 'All
Tasks -
> > > > Backup'
> > > > I don't know what this error is, at a guess there is a lack of disk
> > space.
> > > > Maybe there is more information in the SQL Server log file or in the
> > Event
> > > > log. You will also get errors if you backup to a mapped network
drive.
> > > > > > John
> > > > > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > > > > > > > > >|||Hi

I am not sure what you mean by the transaction log being full. It would
normally wrap around or expand if you have the disk space.

What does DBCC SQLPERF(LOGSPACE) say?

Have you looked at the output from DBCC OPENTRAN ( 'database_name' )?

You may want to look at:
http://www.support.microsoft.com/?id=256650
http://support.microsoft.com/defaul...kb;EN-US;110139
http://support.microsoft.com/defaul...=kb;EN-US;62866

John

"TZoner" <tzoner@.hotmail.com> wrote in message
news:3ef9a575$0$31280$afc38c87@.news.optusnet.com.a u...
> John you've nearly solved my dilemma!!
> As per your excellent like I was able to work out that running the 2
> following lines will reduce the Transaction File back to it's size!!!
> Hooray!!
> BACKUP LOG MyDb WITH TRUNCATE_ONLY
> DBCC SHRINKFILE (MyDb_log)
> However, because MyDb has it's Transaction File full, hence I can't
execute
> the above two lines. How do I run these commands when MyDb won't let me do
> anything to it? To test that the two commands worked I created a dummy
MyDb,
> grew its Transaction File then executed the two lines and it worked 100%.
> Thank you so much for your suggestions!
>
>
>
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> > Your transction logs are held in the ldf file which sp_help would give
e.g
> > for Northwind you would get something like:
> > Name FileId filename filegroup Size Maxsize Growth
Usage
> > Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
> > Unlimited 10% data only
> > Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB
Unlimited
> > 10% log only
> > Northwind_log is the internal logical name for the Transaction Log file
> > located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a
> log
> > file.
> > The log file will not automatically shrink on backup, see the link I
> posted
> > in the last reply regarding how to shrink it.
> > John
> > "TZoner" <tzoner@.hotmail.com> wrote in message
> > news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > > John, thanks for the valuable information.
> > > > Where is the 'Transaction Log' located? I can't find the answer
> > sp_helpfile
> > > or after endless searches on google.
> > > > Does a Transaction Log exist per database in SQL Server? When backup
> > occurs,
> > > does these Transaction Log shrink?
> > > > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > > See inline:
> > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > > > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> > > > I am not sure what you are asking but sp_helpfile will give the
names
> > and
> > > > locations of the files associated with a current database.
> > > > > > > > 2) Is it safe to delete it, as SQL will create a new Transaction
Log
> > > when
> > > > it
> > > > > realises none already exist?
> > > > I would not recommend deleting any logfile. If SQLServer is running
> then
> > > you
> > > > won't be able to as the file is locked. If you have detatched the
> > database
> > > > or SQL Server is not running, you may have problems re-attaching the
> > > > database or connecting to it once SQL Server is running.
> > > > > > > > > > 3) When trying to do 2) I get error message that file is in use,
> even
> > > > though
> > > > > SQL Server is closed. Any suggestions?
> > > > I doubt if you have stopped the service, but in any case you should
> not
> > be
> > > > deleting it.
> > > > If all you want to do is move the file then check out
> > > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > > > If you want to shrink it check out
> > > http://msdn.microsoft.com/library/d...-us/architec/8_
> > > > ar_da2_1uzr.asp
> > > > > > > > > > 4) Can anyone shed light on the error message 'Write on ... <my
path
> > > > > here>... failed, status = 112. BACKUP LOG is terminating
> abnormally'?
> > I
> > > > get
> > > > > this when trying to manually backup the Transaction Log 'All
Tasks -
> > > > Backup'
> > > > I don't know what this error is, at a guess there is a lack of disk
> > space.
> > > > Maybe there is more information in the SQL Server log file or in the
> > Event
> > > > log. You will also get errors if you backup to a mapped network
drive.
> > > > > > John
> > > > > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > > > > > > > > >|||Ooops!!!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3ef9abc1$0$10629$ed9e5944@.reading.news.pipex. net...
> Hi
> I am not sure what you mean by not work. As in the link previously posted,
> if you last virtual log is in use the file will not shrink
>
> "TZoner" <tzoner@.hotmail.com> wrote in message
> news:3ef9a575$0$31280$afc38c87@.news.optusnet.com.a u...
> > John you've nearly solved my dilemma!!
> > As per your excellent like I was able to work out that running the 2
> > following lines will reduce the Transaction File back to it's size!!!
> > Hooray!!
> > BACKUP LOG MyDb WITH TRUNCATE_ONLY
> > DBCC SHRINKFILE (MyDb_log)
> > However, because MyDb has it's Transaction File full, hence I can't
> execute
> > the above two lines. How do I run these commands when MyDb won't let me
do
> > anything to it? To test that the two commands worked I created a dummy
> MyDb,
> > grew its Transaction File then executed the two lines and it worked
100%.
> > Thank you so much for your suggestions!
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> > > Your transction logs are held in the ldf file which sp_help would give
> e.g
> > > for Northwind you would get something like:
> > > > Name FileId filename filegroup Size Maxsize Growth
> Usage
> > > Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
> > > Unlimited 10% data only
> > > Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB
> Unlimited
> > > 10% log only
> > > > Northwind_log is the internal logical name for the Transaction Log
file
> > > located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is
a
> > log
> > > file.
> > > > The log file will not automatically shrink on backup, see the link I
> > posted
> > > in the last reply regarding how to shrink it.
> > > > John
> > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > > > John, thanks for the valuable information.
> > > > > > Where is the 'Transaction Log' located? I can't find the answer
> > > sp_helpfile
> > > > or after endless searches on google.
> > > > > > Does a Transaction Log exist per database in SQL Server? When backup
> > > occurs,
> > > > does these Transaction Log shrink?
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > > > See inline:
> > > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > > > > Disk>\Program Files\Microsoft SQL
Server\MSSQL\Data\MyDb_Log.ldf?
> > > > > I am not sure what you are asking but sp_helpfile will give the
> names
> > > and
> > > > > locations of the files associated with a current database.
> > > > > > > > > > 2) Is it safe to delete it, as SQL will create a new Transaction
> Log
> > > > when
> > > > > it
> > > > > > realises none already exist?
> > > > > I would not recommend deleting any logfile. If SQLServer is
running
> > then
> > > > you
> > > > > won't be able to as the file is locked. If you have detatched the
> > > database
> > > > > or SQL Server is not running, you may have problems re-attaching
the
> > > > > database or connecting to it once SQL Server is running.
> > > > > > > > > > > > > 3) When trying to do 2) I get error message that file is in use,
> > even
> > > > > though
> > > > > > SQL Server is closed. Any suggestions?
> > > > > I doubt if you have stopped the service, but in any case you
should
> > not
> > > be
> > > > > deleting it.
> > > > > If all you want to do is move the file then check out
> > > > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > > > > > If you want to shrink it check out
> > > > > > http://msdn.microsoft.com/library/d...-us/architec/8_
> > > > > ar_da2_1uzr.asp
> > > > > > > > > > > > > 4) Can anyone shed light on the error message 'Write on ... <my
> path
> > > > > > here>... failed, status = 112. BACKUP LOG is terminating
> > abnormally'?
> > > I
> > > > > get
> > > > > > this when trying to manually backup the Transaction Log 'All
> Tasks -
> > > > > Backup'
> > > > > I don't know what this error is, at a guess there is a lack of
disk
> > > space.
> > > > > Maybe there is more information in the SQL Server log file or in
the
> > > Event
> > > > > log. You will also get errors if you backup to a mapped network
> drive.
> > > > > > > > John
> > > > > > > > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > > > > > > > > > > > > > > > > > >|||John

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

If a Transaction File has grown from originally 1Mb to 100Mb, executing the
above 2 lines in a stored procedure will shrink that Transaction File to as
close to 1Mb as possible. This is exactly what I urgently need to do.
However, if the Transaction File is full, hence I'm prevented from running
the stored procedure, how do I execute these 2 lines of code against MyDb?
I've tried using the Query Analyser. Line 1 executes correctly. However line
2 causes the error "Server: Msg 8985, Level 16, State 1, Line 1. Could not
locate file 'MyDb_Log' in sysfiles". I've manually verified that MyDb_Log
does indeed exist in the sysfiles table of MyDb.

Any suggestions?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3ef9abc1$0$10629$ed9e5944@.reading.news.pipex. net...
> Hi
> I am not sure what you mean by not work. As in the link previously posted,
> if you last virtual log is in use the file will not shrink
>
> "TZoner" <tzoner@.hotmail.com> wrote in message
> news:3ef9a575$0$31280$afc38c87@.news.optusnet.com.a u...
> > John you've nearly solved my dilemma!!
> > As per your excellent like I was able to work out that running the 2
> > following lines will reduce the Transaction File back to it's size!!!
> > Hooray!!
> > BACKUP LOG MyDb WITH TRUNCATE_ONLY
> > DBCC SHRINKFILE (MyDb_log)
> > However, because MyDb has it's Transaction File full, hence I can't
> execute
> > the above two lines. How do I run these commands when MyDb won't let me
do
> > anything to it? To test that the two commands worked I created a dummy
> MyDb,
> > grew its Transaction File then executed the two lines and it worked
100%.
> > Thank you so much for your suggestions!
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> > > Your transction logs are held in the ldf file which sp_help would give
> e.g
> > > for Northwind you would get something like:
> > > > Name FileId filename filegroup Size Maxsize Growth
> Usage
> > > Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
> > > Unlimited 10% data only
> > > Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB
> Unlimited
> > > 10% log only
> > > > Northwind_log is the internal logical name for the Transaction Log
file
> > > located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is
a
> > log
> > > file.
> > > > The log file will not automatically shrink on backup, see the link I
> > posted
> > > in the last reply regarding how to shrink it.
> > > > John
> > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > > > John, thanks for the valuable information.
> > > > > > Where is the 'Transaction Log' located? I can't find the answer
> > > sp_helpfile
> > > > or after endless searches on google.
> > > > > > Does a Transaction Log exist per database in SQL Server? When backup
> > > occurs,
> > > > does these Transaction Log shrink?
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > > > See inline:
> > > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > > > > Disk>\Program Files\Microsoft SQL
Server\MSSQL\Data\MyDb_Log.ldf?
> > > > > I am not sure what you are asking but sp_helpfile will give the
> names
> > > and
> > > > > locations of the files associated with a current database.
> > > > > > > > > > 2) Is it safe to delete it, as SQL will create a new Transaction

> Log
> > > > when
> > > > > it
> > > > > > realises none already exist?
> > > > > I would not recommend deleting any logfile. If SQLServer is
running
> > then
> > > > you
> > > > > won't be able to as the file is locked. If you have detatched the
> > > database
> > > > > or SQL Server is not running, you may have problems re-attaching
the
> > > > > database or connecting to it once SQL Server is running.
> > > > > > > > > > > > > 3) When trying to do 2) I get error message that file is in use,
> > even
> > > > > though
> > > > > > SQL Server is closed. Any suggestions?
> > > > > I doubt if you have stopped the service, but in any case you
should
> > not
> > > be
> > > > > deleting it.
> > > > > If all you want to do is move the file then check out
> > > > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > > > > > If you want to shrink it check out
> > > > > > http://msdn.microsoft.com/library/d...-us/architec/8_
> > > > > ar_da2_1uzr.asp
> > > > > > > > > > > > > 4) Can anyone shed light on the error message 'Write on ... <my
> path
> > > > > > here>... failed, status = 112. BACKUP LOG is terminating
> > abnormally'?
> > > I
> > > > > get
> > > > > > this when trying to manually backup the Transaction Log 'All
> Tasks -
> > > > > Backup'
> > > > > I don't know what this error is, at a guess there is a lack of
disk
> > > space.
> > > > > Maybe there is more information in the SQL Server log file or in
the
> > > Event
> > > > > log. You will also get errors if you backup to a mapped network
> drive.
> > > > > > > > John
> > > > > > > > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > > > > > > > > > > > > > > > > > >|||Hi

Try using the file id instead of the logical file name.

sp_helpfile will give your bother the logical name and the file id.

John

"TZoner" <tzoner@.hotmail.com> wrote in message
news:3ef9aec4$0$31274$afc38c87@.news.optusnet.com.a u...
> John
> BACKUP LOG MyDb WITH TRUNCATE_ONLY
> DBCC SHRINKFILE (MyDb_log)
> If a Transaction File has grown from originally 1Mb to 100Mb, executing
the
> above 2 lines in a stored procedure will shrink that Transaction File to
as
> close to 1Mb as possible. This is exactly what I urgently need to do.
> However, if the Transaction File is full, hence I'm prevented from running
> the stored procedure, how do I execute these 2 lines of code against MyDb?
> I've tried using the Query Analyser. Line 1 executes correctly. However
line
> 2 causes the error "Server: Msg 8985, Level 16, State 1, Line 1. Could not
> locate file 'MyDb_Log' in sysfiles". I've manually verified that MyDb_Log
> does indeed exist in the sysfiles table of MyDb.
> Any suggestions?
>
>
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3ef9abc1$0$10629$ed9e5944@.reading.news.pipex. net...
> > Hi
> > I am not sure what you mean by not work. As in the link previously
posted,
> > if you last virtual log is in use the file will not shrink
> > "TZoner" <tzoner@.hotmail.com> wrote in message
> > news:3ef9a575$0$31280$afc38c87@.news.optusnet.com.a u...
> > > John you've nearly solved my dilemma!!
> > > > As per your excellent like I was able to work out that running the 2
> > > following lines will reduce the Transaction File back to it's size!!!
> > > Hooray!!
> > > > BACKUP LOG MyDb WITH TRUNCATE_ONLY
> > > DBCC SHRINKFILE (MyDb_log)
> > > > However, because MyDb has it's Transaction File full, hence I can't
> > execute
> > > the above two lines. How do I run these commands when MyDb won't let
me
> do
> > > anything to it? To test that the two commands worked I created a dummy
> > MyDb,
> > > grew its Transaction File then executed the two lines and it worked
> 100%.
> > > > Thank you so much for your suggestions!
> > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> > > > Your transction logs are held in the ldf file which sp_help would
give
> > e.g
> > > > for Northwind you would get something like:
> > > > > > Name FileId filename filegroup Size Maxsize Growth
> > Usage
> > > > Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008
KB
> > > > Unlimited 10% data only
> > > > Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB
> > Unlimited
> > > > 10% log only
> > > > > > Northwind_log is the internal logical name for the Transaction Log
> file
> > > > located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it
is
> a
> > > log
> > > > file.
> > > > > > The log file will not automatically shrink on backup, see the link I
> > > posted
> > > > in the last reply regarding how to shrink it.
> > > > > > John
> > > > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > > > > John, thanks for the valuable information.
> > > > > > > > Where is the 'Transaction Log' located? I can't find the answer
> > > > sp_helpfile
> > > > > or after endless searches on google.
> > > > > > > > Does a Transaction Log exist per database in SQL Server? When
backup
> > > > occurs,
> > > > > does these Transaction Log shrink?
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > > > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > > > > See inline:
> > > > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > > > > 1) Can one find the location of the 'Transaction Log' at:
<Hard
> > > > > > > Disk>\Program Files\Microsoft SQL
> Server\MSSQL\Data\MyDb_Log.ldf?
> > > > > > I am not sure what you are asking but sp_helpfile will give the
> > names
> > > > and
> > > > > > locations of the files associated with a current database.
> > > > > > > > > > > > 2) Is it safe to delete it, as SQL will create a new
Transaction
> > Log
> > > > > when
> > > > > > it
> > > > > > > realises none already exist?
> > > > > > I would not recommend deleting any logfile. If SQLServer is
> running
> > > then
> > > > > you
> > > > > > won't be able to as the file is locked. If you have detatched
the
> > > > database
> > > > > > or SQL Server is not running, you may have problems re-attaching
> the
> > > > > > database or connecting to it once SQL Server is running.
> > > > > > > > > > > > > > > > 3) When trying to do 2) I get error message that file is in
use,
> > > even
> > > > > > though
> > > > > > > SQL Server is closed. Any suggestions?
> > > > > > I doubt if you have stopped the service, but in any case you
> should
> > > not
> > > > be
> > > > > > deleting it.
> > > > > > If all you want to do is move the file then check out
> > > > > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > > > > > > > If you want to shrink it check out
> > > > > > > > > > http://msdn.microsoft.com/library/d...-us/architec/8_
> > > > > > ar_da2_1uzr.asp
> > > > > > > > > > > > > > > > 4) Can anyone shed light on the error message 'Write on ...
<my
> > path
> > > > > > > here>... failed, status = 112. BACKUP LOG is terminating
> > > abnormally'?
> > > > I
> > > > > > get
> > > > > > > this when trying to manually backup the Transaction Log 'All
> > Tasks -
> > > > > > Backup'
> > > > > > I don't know what this error is, at a guess there is a lack of
> disk
> > > > space.
> > > > > > Maybe there is more information in the SQL Server log file or in
> the
> > > > Event
> > > > > > log. You will also get errors if you backup to a mapped network
> > drive.
> > > > > > > > > > John
> > > > > > > > > > > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >|||Hi there, I've also got the error "Server: Msg 8985, Level 16, State 1, Line 1. Could not locate file 'MyDb_Log' in sysfiles" when trying to shrink one of my databases's log files. I think I know the cause of the problem. When you de-attach a database, rename the database file and then re-attach the database then this error message will apear. The solution, delete that current database and create a new one but NEVER EVER rename the file name of the database

3rd party Backup Software and MSSQL 2000

Can SQL 2000 initiate backups to 3rd party applications (like LiteSpeed
and BrightStor's SQL Backup Agent)? My goal is to be able to manage
backup and restores completely through SQL Enterprise Manager and Query
Analyzer without having to logon to the Server and using the 3rd party
backup application's software. Is this possible through SQL?

Thank you,
Stephanie"Stephanie" <stuttle@.centene.com> wrote in message
news:1125609584.963975.200390@.g44g2000cwa.googlegr oups.com...
> Can SQL 2000 initiate backups to 3rd party applications (like LiteSpeed
> and BrightStor's SQL Backup Agent)? My goal is to be able to manage
> backup and restores completely through SQL Enterprise Manager and Query
> Analyzer without having to logon to the Server and using the 3rd party
> backup application's software. Is this possible through SQL?

Yes.

> Thank you,
> Stephanie