Hidden parameters and defaults using Globals

Hi,
Firstly, I would like to know if there are any intentions to allow you
to hide parameters from the report designer, without having to log into
Report Manager after deployment and fiddle around?
Secondly, I have a problem. I have a report with 2 parameters. One
(userID) I want to set via URL string and one (reportDate) I want to
default to todays date. In the designer, I set the default for
reportDate to Globals!ExecutionTime. I then deploy my report. I now
want to be able to access the report in Report Manager and set the
Prompt string for userID to blank but I can't save this change unless I
either (a) uncheck the default box for reportDate or (b) give
reportDate a static default. Is there any way around this?
Thanks,
JoPlease check this related posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=2820671f-d7f0-46ee-a857-a2f048fd0cd5&sloc=en-us
It will also explain how to setup the UserID parameter so that you don't
need to change it in report manager afterwards.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
<tinyjo@.gmail.com> wrote in message
news:1116253063.200357.225100@.g47g2000cwa.googlegroups.com...
> Hi,
> Firstly, I would like to know if there are any intentions to allow you
> to hide parameters from the report designer, without having to log into
> Report Manager after deployment and fiddle around?
> Secondly, I have a problem. I have a report with 2 parameters. One
> (userID) I want to set via URL string and one (reportDate) I want to
> default to todays date. In the designer, I set the default for
> reportDate to Globals!ExecutionTime. I then deploy my report. I now
> want to be able to access the report in Report Manager and set the
> Prompt string for userID to blank but I can't save this change unless I
> either (a) uncheck the default box for reportDate or (b) give
> reportDate a static default. Is there any way around this?
> Thanks,
> Jo
>

Hidden Databases?

Hello,
on an instances log I see entrys which say that Databases "model4IDR" and
"master4IDR" are started. But through E.M. or sp_helpdb I don't see such
databases, among other things only the normal system databases.
What could be that?
Thank You
Joachim
These are not created by SQL Server. They are created by some 3:rd party program, possibly Backup
Exec (if my memory serves me).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:%237HVA1q5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hello,
> on an instances log I see entrys which say that Databases "model4IDR" and "master4IDR" are
> started. But through E.M. or sp_helpdb I don't see such databases, among other things only the
> normal system databases.
> What could be that?
> Thank You
> Joachim
sql

Hidden Databases?

Hello,
on an instances log I see entrys which say that Databases "model4IDR" and
"master4IDR" are started. But through E.M. or sp_helpdb I don't see such
databases, among other things only the normal system databases.
What could be that?
Thank You
JoachimThese are not created by SQL Server. They are created by some 3:rd party program, possibly Backup
Exec (if my memory serves me).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:%237HVA1q5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hello,
> on an instances log I see entrys which say that Databases "model4IDR" and "master4IDR" are
> started. But through E.M. or sp_helpdb I don't see such databases, among other things only the
> normal system databases.
> What could be that?
> Thank You
> Joachim

Hidden Databases?

Hello,
on an instances log I see entrys which say that Databases "model4IDR" and
"master4IDR" are started. But through E.M. or sp_helpdb I don't see such
databases, among other things only the normal system databases.
What could be that?
Thank You
JoachimThese are not created by SQL Server. They are created by some 3:rd party pro
gram, possibly Backup
Exec (if my memory serves me).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:%237HVA1q5FHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hello,
> on an instances log I see entrys which say that Databases "model4IDR" and
"master4IDR" are
> started. But through E.M. or sp_helpdb I don't see such databases, among o
ther things only the
> normal system databases.
> What could be that?
> Thank You
> Joachim

Hi guys, how to monitor and purge log files/transaction files? Tha

Hi guys, how to monitor and purge log files/transaction files in SQL Server?
ThanksBACKUP LOG file and then if you want to reduce physical size of the file run
DBCC SHRINKFILE command
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:8C79F464-72E9-4138-BF51-0824857DEEA4@.microsoft.com...
> Hi guys, how to monitor and purge log files/transaction files in SQL
> Server?
> Thanks
>

Hi guys, how to monitor and purge log files/transaction files? Tha

Hi guys, how to monitor and purge log files/transaction files in SQL Server?
ThanksBACKUP LOG file and then if you want to reduce physical size of the file run
DBCC SHRINKFILE command
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:8C79F464-72E9-4138-BF51-0824857DEEA4@.microsoft.com...
> Hi guys, how to monitor and purge log files/transaction files in SQL
> Server?
> Thanks
>

Hi guys, how to monitor and purge log files/transaction files? Tha

Hi guys, how to monitor and purge log files/transaction files in SQL Server?
Thanks
BACKUP LOG file and then if you want to reduce physical size of the file run
DBCC SHRINKFILE command
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:8C79F464-72E9-4138-BF51-0824857DEEA4@.microsoft.com...
> Hi guys, how to monitor and purge log files/transaction files in SQL
> Server?
> Thanks
>

Help-Recovering SQL 2000 Database.

Hi Vyas,
Sorry for the late reply,
The error log just says the below and halts at the last step.
2004-09-13 17:09:54.39 spid11 Starting up database 'jennifertest'.
2004-09-13 17:10:02.75 spid3 Recovery complete.
2004-09-13 17:10:02.75 spid3 SQL global counter collection task is
created.
Do you want any other file like SQLDump?
TIA
--
Santosh
Can you check the SQL Server error log and post the error messages here? You
will find the log in MSSQL\Log folder under the installation path.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
news:eHXmsUcmEHA.3352@.TK2MSFTNGP10.phx.gbl...
> Friends,
>
> The SQL server 2000 was running fine until all of a Sudden I got this
> message,
>
> SQL Server is aborting. Fatal exception c0000005 caught.
>
> The Service would not start, and I need help in Recovering it.
>
> Any help is greatly Appreciated.
>
> --
>
> Santosh
>
>The messages you posted indicate that the SQL Server is probably running
fine. Is that the case, or are you still not able to start your SQL Server?
What happens when you try to start your MSSQLServer service from the
services applet?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
news:%23cqNlEnmEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi Vyas,
> Sorry for the late reply,
> The error log just says the below and halts at the last step.
> 2004-09-13 17:09:54.39 spid11 Starting up database 'jennifertest'.
> 2004-09-13 17:10:02.75 spid3 Recovery complete.
> 2004-09-13 17:10:02.75 spid3 SQL global counter collection task is
> created.
> Do you want any other file like SQLDump?
> TIA
> --
> Santosh
> Can you check the SQL Server error log and post the error messages here?
You
> will find the log in MSSQL\Log folder under the installation path.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> news:eHXmsUcmEHA.3352@.TK2MSFTNGP10.phx.gbl...
> > Friends,
> >
> > The SQL server 2000 was running fine until all of a Sudden I got this
> > message,
> >
> > SQL Server is aborting. Fatal exception c0000005 caught.
> >
> > The Service would not start, and I need help in Recovering it.
> >
> > Any help is greatly Appreciated.
> >
> > --
> >
> > Santosh
> >
> >
>
>|||When I try from the Services I get an error saying the Services Could not be
started, and the SQL server does not start at all.
Also I am not able to connect to the Database via Enterprise Manager.
Currently I have shut down the server, but I remember getting a message
something like "c000005 Dump".
--
Santosh
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OwbAc3nmEHA.1136@.TK2MSFTNGP10.phx.gbl...
> The messages you posted indicate that the SQL Server is probably running
> fine. Is that the case, or are you still not able to start your SQL
> Server?
> What happens when you try to start your MSSQLServer service from the
> services applet?
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> news:%23cqNlEnmEHA.1656@.TK2MSFTNGP09.phx.gbl...
>> Hi Vyas,
>> Sorry for the late reply,
>> The error log just says the below and halts at the last step.
>> 2004-09-13 17:09:54.39 spid11 Starting up database 'jennifertest'.
>> 2004-09-13 17:10:02.75 spid3 Recovery complete.
>> 2004-09-13 17:10:02.75 spid3 SQL global counter collection task is
>> created.
>> Do you want any other file like SQLDump?
>> TIA
>> --
>> Santosh
>> Can you check the SQL Server error log and post the error messages here?
> You
>> will find the log in MSSQL\Log folder under the installation path.
>> --
>> Vyas, MVP (SQL Server)
>> http://vyaskn.tripod.com/
>>
>> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
>> news:eHXmsUcmEHA.3352@.TK2MSFTNGP10.phx.gbl...
>> > Friends,
>> >
>> > The SQL server 2000 was running fine until all of a Sudden I got this
>> > message,
>> >
>> > SQL Server is aborting. Fatal exception c0000005 caught.
>> >
>> > The Service would not start, and I need help in Recovering it.
>> >
>> > Any help is greatly Appreciated.
>> >
>> > --
>> >
>> > Santosh
>> >
>> >
>>
>>
>|||>> something like "c000005 Dump".
That indicates an Access Violation, and all AVs are bugs. So I suggest you
open a case with Microsoft. They will refund your money, if it turns out to
be a bug.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
news:u20RwAomEHA.3756@.TK2MSFTNGP09.phx.gbl...
> When I try from the Services I get an error saying the Services Could not
be
> started, and the SQL server does not start at all.
> Also I am not able to connect to the Database via Enterprise Manager.
> Currently I have shut down the server, but I remember getting a message
> something like "c000005 Dump".
> --
> Santosh
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:OwbAc3nmEHA.1136@.TK2MSFTNGP10.phx.gbl...
> > The messages you posted indicate that the SQL Server is probably running
> > fine. Is that the case, or are you still not able to start your SQL
> > Server?
> > What happens when you try to start your MSSQLServer service from the
> > services applet?
> > --
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> >
> >
> > "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> > news:%23cqNlEnmEHA.1656@.TK2MSFTNGP09.phx.gbl...
> >> Hi Vyas,
> >>
> >> Sorry for the late reply,
> >> The error log just says the below and halts at the last step.
> >>
> >> 2004-09-13 17:09:54.39 spid11 Starting up database 'jennifertest'.
> >> 2004-09-13 17:10:02.75 spid3 Recovery complete.
> >> 2004-09-13 17:10:02.75 spid3 SQL global counter collection task is
> >> created.
> >>
> >> Do you want any other file like SQLDump?
> >>
> >> TIA
> >>
> >> --
> >>
> >> Santosh
> >>
> >> Can you check the SQL Server error log and post the error messages
here?
> > You
> >> will find the log in MSSQL\Log folder under the installation path.
> >> --
> >> Vyas, MVP (SQL Server)
> >> http://vyaskn.tripod.com/
> >>
> >>
> >> "Santosh" <santoshNoSpam@.NoSpam.net> wrote in message
> >> news:eHXmsUcmEHA.3352@.TK2MSFTNGP10.phx.gbl...
> >> > Friends,
> >> >
> >> > The SQL server 2000 was running fine until all of a Sudden I got this
> >> > message,
> >> >
> >> > SQL Server is aborting. Fatal exception c0000005 caught.
> >> >
> >> > The Service would not start, and I need help in Recovering it.
> >> >
> >> > Any help is greatly Appreciated.
> >> >
> >> > --
> >> >
> >> > Santosh
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>

HELP--cannot shrink the transaction log

Hello:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980s
I have done both of those things--no success...
PLEASE HELP!!!
childofthe1980s
"Tibor Karaszi" wrote:

> You specify the *logical name of the file*. See the sysfiles table. Also, see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>
|||DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
"Tibor Karaszi" wrote:

> Can you post the commands you tried to execute?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>
|||Where you IN the database in question? I believe that is required.
Roy Harvey
Beacon Falls, CT
On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
<childofthe1980s@.discussions.microsoft.com> wrote:

>Hello:
>I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>analyzer in SQL 2000 against the master database.
>What is the EXACT syntax? You see, the name of the database is JC and the
>log file is named GPSJClog.ldf. I have tried to place both names in the
>syntax of the statement but with no success. I keep getting a message saying
>that the file name is not in sysfiles.
>This is what I have tried:
>DBCC SHRINKFILE (GPJClog.ldf , 2)
>Please help!
>childofthe1980s
|||Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"Roy Harvey" wrote:

> Where you IN the database in question? I believe that is required.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> <childofthe1980s@.discussions.microsoft.com> wrote:
>
|||You needed to use the Logical name not the physical one. Drop the .ldf and
you should be golden.
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:C3E65A22-82FF-405A-8EC9-B770E5206E4E@.microsoft.com...[vbcol=seagreen]
> DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
>
> "Tibor Karaszi" wrote:
|||Not true. You can use either the ID or the LOGICAL name not the physical
name. And hopefully the db is not in FULL recovery mode or you just lost the
log chain with that truncate.
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...[vbcol=seagreen]
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
> it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
>
>
> "Roy Harvey" wrote:
|||No, Andrew, I tried using the logical file name without the ldf and it did
not work either. So, yes, the documentation needs updating.
"Andrew J. Kelly" wrote:

> Not true. You can use either the ID or the LOGICAL name not the physical
> name. And hopefully the db is not in FULL recovery mode or you just lost the
> log chain with that truncate.
> --
> Andrew J. Kelly SQL MVP
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
>
>

HELP--cannot shrink the transaction log

Hello:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message saying
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sYou specify the *logical name of the file*. See the sysfiles table. Also, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> Hello:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message saying
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s|||I have done both of those things--no success...
PLEASE HELP!!!
childofthe1980s
"Tibor Karaszi" wrote:
> You specify the *logical name of the file*. See the sysfiles table. Also, see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> > Hello:
> >
> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> > analyzer in SQL 2000 against the master database.
> >
> > What is the EXACT syntax? You see, the name of the database is JC and the
> > log file is named GPSJClog.ldf. I have tried to place both names in the
> > syntax of the statement but with no success. I keep getting a message saying
> > that the file name is not in sysfiles.
> >
> > This is what I have tried:
> >
> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> > Please help!
> >
> > childofthe1980s
>|||Can you post the commands you tried to execute?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>I have done both of those things--no success...
> PLEASE HELP!!!
> childofthe1980s
> "Tibor Karaszi" wrote:
>> You specify the *logical name of the file*. See the sysfiles table. Also, see
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
>> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> > Hello:
>> >
>> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>> > analyzer in SQL 2000 against the master database.
>> >
>> > What is the EXACT syntax? You see, the name of the database is JC and the
>> > log file is named GPSJClog.ldf. I have tried to place both names in the
>> > syntax of the statement but with no success. I keep getting a message saying
>> > that the file name is not in sysfiles.
>> >
>> > This is what I have tried:
>> >
>> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> > Please help!
>> >
>> > childofthe1980s
>>|||DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
"Tibor Karaszi" wrote:
> Can you post the commands you tried to execute?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
> >I have done both of those things--no success...
> >
> > PLEASE HELP!!!
> >
> > childofthe1980s
> >
> > "Tibor Karaszi" wrote:
> >
> >> You specify the *logical name of the file*. See the sysfiles table. Also, see
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> >> > Hello:
> >> >
> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >> > analyzer in SQL 2000 against the master database.
> >> >
> >> > What is the EXACT syntax? You see, the name of the database is JC and the
> >> > log file is named GPSJClog.ldf. I have tried to place both names in the
> >> > syntax of the statement but with no success. I keep getting a message saying
> >> > that the file name is not in sysfiles.
> >> >
> >> > This is what I have tried:
> >> >
> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> > Please help!
> >> >
> >> > childofthe1980s
> >>
> >>
>|||Where you IN the database in question? I believe that is required.
Roy Harvey
Beacon Falls, CT
On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
<childofthe1980s@.discussions.microsoft.com> wrote:
>Hello:
>I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
>analyzer in SQL 2000 against the master database.
>What is the EXACT syntax? You see, the name of the database is JC and the
>log file is named GPSJClog.ldf. I have tried to place both names in the
>syntax of the statement but with no success. I keep getting a message saying
>that the file name is not in sysfiles.
>This is what I have tried:
>DBCC SHRINKFILE (GPJClog.ldf , 2)
>Please help!
>childofthe1980s|||Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"Roy Harvey" wrote:
> Where you IN the database in question? I believe that is required.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> <childofthe1980s@.discussions.microsoft.com> wrote:
> >Hello:
> >
> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
> >analyzer in SQL 2000 against the master database.
> >
> >What is the EXACT syntax? You see, the name of the database is JC and the
> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >syntax of the statement but with no success. I keep getting a message saying
> >that the file name is not in sysfiles.
> >
> >This is what I have tried:
> >
> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >
> >Please help!
> >
> >childofthe1980s
>|||You needed to use the Logical name not the physical one. Drop the .ldf and
you should be golden.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:C3E65A22-82FF-405A-8EC9-B770E5206E4E@.microsoft.com...
> DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
>
> "Tibor Karaszi" wrote:
>> Can you post the commands you tried to execute?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message
>> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>> >I have done both of those things--no success...
>> >
>> > PLEASE HELP!!!
>> >
>> > childofthe1980s
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> You specify the *logical name of the file*. See the sysfiles table.
>> >> Also, see
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>> >> > Hello:
>> >> >
>> >> > I'm trying to shrink the transaction log by running DBCC SHRINKFILE
>> >> > in query
>> >> > analyzer in SQL 2000 against the master database.
>> >> >
>> >> > What is the EXACT syntax? You see, the name of the database is JC
>> >> > and the
>> >> > log file is named GPSJClog.ldf. I have tried to place both names in
>> >> > the
>> >> > syntax of the statement but with no success. I keep getting a
>> >> > message saying
>> >> > that the file name is not in sysfiles.
>> >> >
>> >> > This is what I have tried:
>> >> >
>> >> > DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> > Please help!
>> >> >
>> >> > childofthe1980s
>> >>
>> >>
>>|||Not true. You can use either the ID or the LOGICAL name not the physical
name. And hopefully the db is not in FULL recovery mode or you just lost the
log chain with that truncate.
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
> it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
>
>
> "Roy Harvey" wrote:
>> Where you IN the database in question? I believe that is required.
>> Roy Harvey
>> Beacon Falls, CT
>> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >Hello:
>> >
>> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >query
>> >analyzer in SQL 2000 against the master database.
>> >
>> >What is the EXACT syntax? You see, the name of the database is JC and
>> >the
>> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >syntax of the statement but with no success. I keep getting a message
>> >saying
>> >that the file name is not in sysfiles.
>> >
>> >This is what I have tried:
>> >
>> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >
>> >Please help!
>> >
>> >childofthe1980s|||No, Andrew, I tried using the logical file name without the ldf and it did
not work either. So, yes, the documentation needs updating.
"Andrew J. Kelly" wrote:
> Not true. You can use either the ID or the LOGICAL name not the physical
> name. And hopefully the db is not in FULL recovery mode or you just lost the
> log chain with that truncate.
> --
> Andrew J. Kelly SQL MVP
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
> > Nevermind. I figured it out.
> >
> > I had to run the following scripts against each of the databases:
> >
> > BACKUP LOG JC with TRUNCATE_ONLY
> > DBCC SHRINKFILE (2, 20)
> >
> > You see, the support documentation on the web did not say that you have to
> > take the fileID of the database--2 in the syntax above-- in order to make
> > it
> > work! You get the number 2 from running the following query against the
> > database:
> >
> > select * from sysfiles
> >
> > It would have been nice if this had been in the documentation!
> >
> > Thanks!
> >
> > childofthe1980s
> >
> >
> >
> >
> >
> > "Roy Harvey" wrote:
> >
> >> Where you IN the database in question? I believe that is required.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> >> <childofthe1980s@.discussions.microsoft.com> wrote:
> >>
> >> >Hello:
> >> >
> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
> >> >query
> >> >analyzer in SQL 2000 against the master database.
> >> >
> >> >What is the EXACT syntax? You see, the name of the database is JC and
> >> >the
> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
> >> >syntax of the statement but with no success. I keep getting a message
> >> >saying
> >> >that the file name is not in sysfiles.
> >> >
> >> >This is what I have tried:
> >> >
> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
> >> >
> >> >Please help!
> >> >
> >> >childofthe1980s
> >>
>
>|||> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
The documentation clearly states that you can use *either* the logical filename or the file id. The
documentation is correct. The command you posted had 'GPSJCLog.ldf' for the logical filename, and I
guess that you by mistake put the *physical* filename there instead of the *logical* filename. If
you show ut the output from below, we can say for sure:
SELECT name, filename FROM sysfiles
I have shrunk database files occasionally and I always used the logical filename. In addition,
here's a script (executed on my SQL Server 2000) which clearly show the usage of the *logical*
filename:
USE master
CREATE DATABASE myTestShrink ON PRIMARY
(NAME = N'myTestShrink', FILENAME = N'C:\myTestShrink.mdf'
,SIZE = 1024KB , FILEGROWTH = 10%)
LOG ON
(NAME = N'myTestShrink_log', FILENAME = N'C:\myTestShrink_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
GO
--Increase log file size
BACKUP DATABASE myTestShrink TO DISK = 'nul'
CREATE TABLE myTestShrink..test(c1 int identity, c2 char(2000))
INSERT INTO myTestShrink..test(c2)
SELECT TOP 10000 'hello' FROM sysobjects, syscolumns
GO
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
--Empty the log
BACKUP LOG myTestShrink WITH NO_LOG
--Check the logical and physical files names
SELECT name, filename FROM myTestShrink..sysfiles
--Perform the shrink, ***based on logical filename***
USE myTestShrink
GO
DBCC SHRINKFILE('myTestShrink_log', 2)
--Check log file size
SELECT name, size*8192/(1024*1024) AS SizeInMb FROM myTestShrink..sysfiles WHERE groupid = 0
USE master
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:04A6393F-154E-4A1A-946B-0768F7B47F85@.microsoft.com...
> No, Andrew, I tried using the logical file name without the ldf and it did
> not work either. So, yes, the documentation needs updating.
> "Andrew J. Kelly" wrote:
>> Not true. You can use either the ID or the LOGICAL name not the physical
>> name. And hopefully the db is not in FULL recovery mode or you just lost the
>> log chain with that truncate.
>> --
>> Andrew J. Kelly SQL MVP
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
>> > Nevermind. I figured it out.
>> >
>> > I had to run the following scripts against each of the databases:
>> >
>> > BACKUP LOG JC with TRUNCATE_ONLY
>> > DBCC SHRINKFILE (2, 20)
>> >
>> > You see, the support documentation on the web did not say that you have to
>> > take the fileID of the database--2 in the syntax above-- in order to make
>> > it
>> > work! You get the number 2 from running the following query against the
>> > database:
>> >
>> > select * from sysfiles
>> >
>> > It would have been nice if this had been in the documentation!
>> >
>> > Thanks!
>> >
>> > childofthe1980s
>> >
>> >
>> >
>> >
>> >
>> > "Roy Harvey" wrote:
>> >
>> >> Where you IN the database in question? I believe that is required.
>> >>
>> >> Roy Harvey
>> >> Beacon Falls, CT
>> >>
>> >> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
>> >> <childofthe1980s@.discussions.microsoft.com> wrote:
>> >>
>> >> >Hello:
>> >> >
>> >> >I'm trying to shrink the transaction log by running DBCC SHRINKFILE in
>> >> >query
>> >> >analyzer in SQL 2000 against the master database.
>> >> >
>> >> >What is the EXACT syntax? You see, the name of the database is JC and
>> >> >the
>> >> >log file is named GPSJClog.ldf. I have tried to place both names in the
>> >> >syntax of the statement but with no success. I keep getting a message
>> >> >saying
>> >> >that the file name is not in sysfiles.
>> >> >
>> >> >This is what I have tried:
>> >> >
>> >> >DBCC SHRINKFILE (GPJClog.ldf , 2)
>> >> >
>> >> >Please help!
>> >> >
>> >> >childofthe1980s
>> >>
>>

HELP--cannot shrink the transaction log

Hello:
I'm trying to shrink the transaction log by running DBCC SHRINKFILE in query
analyzer in SQL 2000 against the master database.
What is the EXACT syntax? You see, the name of the database is JC and the
log file is named GPSJClog.ldf. I have tried to place both names in the
syntax of the statement but with no success. I keep getting a message sayin
g
that the file name is not in sysfiles.
This is what I have tried:
DBCC SHRINKFILE (GPJClog.ldf , 2)
Please help!
childofthe1980sYou specify the *logical name of the file*. See the sysfiles table. Also, se
e
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
> Hello:
> I'm trying to shrink the transaction log by running DBCC SHRINKFILE in que
ry
> analyzer in SQL 2000 against the master database.
> What is the EXACT syntax? You see, the name of the database is JC and the
> log file is named GPSJClog.ldf. I have tried to place both names in the
> syntax of the statement but with no success. I keep getting a message say
ing
> that the file name is not in sysfiles.
> This is what I have tried:
> DBCC SHRINKFILE (GPJClog.ldf , 2)
> Please help!
> childofthe1980s|||I have done both of those things--no success...
PLEASE HELP!!!
childofthe1980s
"Tibor Karaszi" wrote:

> You specify the *logical name of the file*. See the sysfiles table. Also,
see
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in mes
sage
> news:268984E4-5D32-48E5-8438-7252EE44255A@.microsoft.com...
>|||Can you post the commands you tried to execute?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...[vbcol=seagreen]
>I have done both of those things--no success...
> PLEASE HELP!!!
> childofthe1980s
> "Tibor Karaszi" wrote:
>|||DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
"Tibor Karaszi" wrote:

> Can you post the commands you tried to execute?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in mes
sage
> news:C2E7133B-E6A1-4B09-A351-C242160C2FA3@.microsoft.com...
>|||Where you IN the database in question? I believe that is required.
Roy Harvey
Beacon Falls, CT
On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
<childofthe1980s@.discussions.microsoft.com> wrote:

>Hello:
>I'm trying to shrink the transaction log by running DBCC SHRINKFILE in quer
y
>analyzer in SQL 2000 against the master database.
>What is the EXACT syntax? You see, the name of the database is JC and the
>log file is named GPSJClog.ldf. I have tried to place both names in the
>syntax of the statement but with no success. I keep getting a message sayi
ng
>that the file name is not in sysfiles.
>This is what I have tried:
>DBCC SHRINKFILE (GPJClog.ldf , 2)
>Please help!
>childofthe1980s|||Nevermind. I figured it out.
I had to run the following scripts against each of the databases:
BACKUP LOG JC with TRUNCATE_ONLY
DBCC SHRINKFILE (2, 20)
You see, the support documentation on the web did not say that you have to
take the fileID of the database--2 in the syntax above-- in order to make it
work! You get the number 2 from running the following query against the
database:
select * from sysfiles
It would have been nice if this had been in the documentation!
Thanks!
childofthe1980s
"Roy Harvey" wrote:

> Where you IN the database in question? I believe that is required.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 21 Nov 2006 14:04:01 -0800, childofthe1980s
> <childofthe1980s@.discussions.microsoft.com> wrote:
>
>|||You needed to use the Logical name not the physical one. Drop the .ldf and
you should be golden.
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:C3E65A22-82FF-405A-8EC9-B770E5206E4E@.microsoft.com...[vbcol=seagreen]
> DBCC SHRINKFILE ('GPSJCLog.ldf', 2)
>
> "Tibor Karaszi" wrote:
>|||Not true. You can use either the ID or the LOGICAL name not the physical
name. And hopefully the db is not in FULL recovery mode or you just lost the
log chain with that truncate.
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...[vbcol=seagreen]
> Nevermind. I figured it out.
> I had to run the following scripts against each of the databases:
> BACKUP LOG JC with TRUNCATE_ONLY
> DBCC SHRINKFILE (2, 20)
> You see, the support documentation on the web did not say that you have to
> take the fileID of the database--2 in the syntax above-- in order to make
> it
> work! You get the number 2 from running the following query against the
> database:
> select * from sysfiles
> It would have been nice if this had been in the documentation!
> Thanks!
> childofthe1980s
>
>
> "Roy Harvey" wrote:
>|||No, Andrew, I tried using the logical file name without the ldf and it did
not work either. So, yes, the documentation needs updating.
"Andrew J. Kelly" wrote:

> Not true. You can use either the ID or the LOGICAL name not the physical
> name. And hopefully the db is not in FULL recovery mode or you just lost t
he
> log chain with that truncate.
> --
> Andrew J. Kelly SQL MVP
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:F4F525D1-24BC-4482-A164-55B00F0336F6@.microsoft.com...
>
>

HELP: Transaction Log Backup Files

Hello world,
I am having some diffulty finding the method to truncate my MSSQL
transaction logs files.
I am successful backing up the system and user tables and the logs ( using
a maintianace wizard, But there isnt an option for logfile truncate'
1) Do Maintance Wizards truncate the log files automatically?
2) WHERE can i find this option?
RussTruncating the transaction log from Query Analyzer:
BACKUP LOG database_name WITH NO_LOG or TRUNCATE_ONLY
This statement will never backup log actualy but truncate inactive portion of log file.
It's better idea to let the SQL Server do all that through maintenance plans and regular log backup bacause after every log backup SQL will truncate inactive portion of log file.
Also you should check db recovery options!
More info BOL - "Truncating the Transaction Log"
hope it helps|||Why would you want to truncate the log file regularly? It only cost performance etc to truncate it
and then having it autogrow back...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Stephen Driscoll" <info@.fnc.co.uk> wrote in message news:%23bjBigKwDHA.2460@.TK2MSFTNGP10.phx.gbl...
> Hello world,
> I am having some diffulty finding the method to truncate my MSSQL
> transaction logs files.
> I am successful backing up the system and user tables and the logs ( using
> a maintianace wizard, But there isnt an option for logfile truncate'
> 1) Do Maintance Wizards truncate the log files automatically?
> 2) WHERE can i find this option?
> Russ
>

HELP: Point in time restore : HOW ?

I have full backup of database at 13:00
and another full backup at 17:00.
I've made backup of transaction log at 17:05
When I try to restore database to state at
15:10 (point in time) , the dialogue in Enterprise Manager
says that only time after 17:05 is valid.
It seems to me that I've done something wrong at 17:05 while taking
trans. log backup.
But, again, if I have full backups at 13:00 and 17:00 restoring
database to point in time at 15:10 should be possible ?!
Any help is appreciated.
Pagus
Hi Pagus,
Point in time recovery can be done with tlogs.... had you taken tlogs after
15:00 full backup?
Thanks
GYK
"Pagus" wrote:

> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>
|||To summarize what u currently have :
[1] First Full Database Backup @. 13:00
[2] Second Full Database Backup @. 17:00
[3] TLog @. 17:05
Trying to Point-In-Time restore to 15:10
Considering that the Point-In-Time restore is before the second DB backup,
the Second DB backup is irrelevent.
There are a few things that u shud check
[1] Was the DB in valid state when you issued the Backup @. 13:00
[2] Check the duration (Period) for the TLog backup. It should have an
end time of 17:05 and start time of what ?
Thanks,
Gopi
"Pagus" wrote:

> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>
|||Pagus <pagus@.writeme.com> wrote in message news:<5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com>. ..
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
Always used transaction logs to do a point in time restore.
Vincento
|||Hi there,
a point in time restore is only possible when using a transaction log
to do the restore. I know it probably doesnt help you but for future
reference here is the way to do it.
first restore the full backup leaving the database in no-recovery mode
then restore the transaction log backup to the point in time that you
want, this time recoverying the database after the restore completes.
Depending upon the size of your database it might be worth looking at
doing a full backup just once per day and several transaction log
backups throughout the day every hour, or couple of hours.

HELP: Point in time restore : HOW ?

I have full backup of database at 13:00
and another full backup at 17:00.

I've made backup of transaction log at 17:05

When I try to restore database to state at
15:10 (point in time) , the dialogue in Enterprise Manager
says that only time after 17:05 is valid.

It seems to me that I've done something wrong at 17:05 while taking
trans. log backup.

But, again, if I have full backups at 13:00 and 17:00 restoring
database to point in time at 15:10 should be possible ?!

Any help is appreciated.

PagusRestore the full backup from 13:00 with NORECOVERY
Restore the tLog backup to 15:10 with Recovery

Should work.

Rick

"Pagus" <pagus@.writeme.com> wrote in message
news:5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
> Restore the full backup from 13:00 with NORECOVERY
> Restore the tLog backup to 15:10 with Recovery

I have not done this, but you may want to restore tlog backup to 15:10 with
a standby file.

I THINK you can then restore in time past 15:10 again w/o having to do the
full restore.

(hmm, I should try this now that I think about... too tired right now
though. :-)

> Should work.
>
> Rick|||> (hmm, I should try this now that I think about... too tired right now
> though. :-)

No need to Greg. I have a demo script just for this. You can restore the same tlog several times,
going forward in time, using STANDBY. I also checked with MS and this is a tested and supported
method. Here are my comments on the topic:
http://www.karaszi.com/SQLServer/in...veral_times.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Mbndd.312510$bp1.26137@.twister.nyroc.rr.com.. .
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Restore the full backup from 13:00 with NORECOVERY
>> Restore the tLog backup to 15:10 with Recovery
> I have not done this, but you may want to restore tlog backup to 15:10 with
> a standby file.
> I THINK you can then restore in time past 15:10 again w/o having to do the
> full restore.
> (hmm, I should try this now that I think about... too tired right now
> though. :-)
>
>>
>> Should work.
>>
>>
>> Rick
>>
>>|||Pagus <pagus@.writeme.com> wrote in message news:<5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com>...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus

Always used transaction logs to do a point in time restore.

Vincento|||Hi there,

a point in time restore is only possible when using a transaction log
to do the restore. I know it probably doesnt help you but for future
reference here is the way to do it.

first restore the full backup leaving the database in no-recovery mode
then restore the transaction log backup to the point in time that you
want, this time recoverying the database after the restore completes.

Depending upon the size of your database it might be worth looking at
doing a full backup just once per day and several transaction log
backups throughout the day every hour, or couple of hours.

HELP: Point in time restore : HOW ?

I have full backup of database at 13:00
and another full backup at 17:00.
I've made backup of transaction log at 17:05
When I try to restore database to state at
15:10 (point in time) , the dialogue in Enterprise Manager
says that only time after 17:05 is valid.
It seems to me that I've done something wrong at 17:05 while taking
trans. log backup.
But, again, if I have full backups at 13:00 and 17:00 restoring
database to point in time at 15:10 should be possible ?!
Any help is appreciated.
PagusRestore the full backup from 13:00 with NORECOVERY
Restore the tLog backup to 15:10 with Recovery
Should work.
Rick
"Pagus" <pagus@.writeme.com> wrote in message
news:5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||Hi Pagus,
Point in time recovery can be done with tlogs.... had you taken tlogs after
15:00 full backup?
Thanks
GYK
"Pagus" wrote:
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
> Restore the full backup from 13:00 with NORECOVERY
> Restore the tLog backup to 15:10 with Recovery
I have not done this, but you may want to restore tlog backup to 15:10 with
a standby file.
I THINK you can then restore in time past 15:10 again w/o having to do the
full restore.
(hmm, I should try this now that I think about... too tired right now
though. :-)
> Should work.
>
> Rick
>|||To summarize what u currently have :
[1] First Full Database Backup @. 13:00
[2] Second Full Database Backup @. 17:00
[3] TLog @. 17:05
Trying to Point-In-Time restore to 15:10
Considering that the Point-In-Time restore is before the second DB backup,
the Second DB backup is irrelevent.
There are a few things that u shud check
[1] Was the DB in valid state when you issued the Backup @. 13:00
[2] Check the duration (Period) for the TLog backup. It should have an
end time of 17:05 and start time of what ?
Thanks,
Gopi
"Pagus" wrote:
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||> (hmm, I should try this now that I think about... too tired right now
> though. :-)
No need to Greg. I have a demo script just for this. You can restore the same tlog several times,
going forward in time, using STANDBY. I also checked with MS and this is a tested and supported
method. Here are my comments on the topic:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Mbndd.312510$bp1.26137@.twister.nyroc.rr.com...
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Restore the full backup from 13:00 with NORECOVERY
>> Restore the tLog backup to 15:10 with Recovery
> I have not done this, but you may want to restore tlog backup to 15:10 with
> a standby file.
> I THINK you can then restore in time past 15:10 again w/o having to do the
> full restore.
> (hmm, I should try this now that I think about... too tired right now
> though. :-)
>
>> Should work.
>>
>> Rick
>>
>|||Pagus <pagus@.writeme.com> wrote in message news:<5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com>...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
Always used transaction logs to do a point in time restore.
Vincento|||Hi there,
a point in time restore is only possible when using a transaction log
to do the restore. I know it probably doesnt help you but for future
reference here is the way to do it.
first restore the full backup leaving the database in no-recovery mode
then restore the transaction log backup to the point in time that you
want, this time recoverying the database after the restore completes.
Depending upon the size of your database it might be worth looking at
doing a full backup just once per day and several transaction log
backups throughout the day every hour, or couple of hours.

HELP: Point in time restore : HOW ?

I have full backup of database at 13:00
and another full backup at 17:00.
I've made backup of transaction log at 17:05
When I try to restore database to state at
15:10 (point in time) , the dialogue in Enterprise Manager
says that only time after 17:05 is valid.
It seems to me that I've done something wrong at 17:05 while taking
trans. log backup.
But, again, if I have full backups at 13:00 and 17:00 restoring
database to point in time at 15:10 should be possible ?!
Any help is appreciated.
PagusHi Pagus,
Point in time recovery can be done with tlogs.... had you taken tlogs after
15:00 full backup?
Thanks
GYK
"Pagus" wrote:

> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||To summarize what u currently have :
[1] First Full Database Backup @. 13:00
[2] Second Full Database Backup @. 17:00
[3] TLog @. 17:05
Trying to Point-In-Time restore to 15:10
Considering that the Point-In-Time restore is before the second DB backup,
the Second DB backup is irrelevent.
There are a few things that u shud check
[1] Was the DB in valid state when you issued the Backup @. 13:00
[2] Check the duration (Period) for the TLog backup. It should have an
end time of 17:05 and start time of what ?
Thanks,
Gopi
"Pagus" wrote:

> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||Pagus <pagus@.writeme.com> wrote in message news:<5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com>
..
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
Always used transaction logs to do a point in time restore.
Vincento|||Hi there,
a point in time restore is only possible when using a transaction log
to do the restore. I know it probably doesnt help you but for future
reference here is the way to do it.
first restore the full backup leaving the database in no-recovery mode
then restore the transaction log backup to the point in time that you
want, this time recoverying the database after the restore completes.
Depending upon the size of your database it might be worth looking at
doing a full backup just once per day and several transaction log
backups throughout the day every hour, or couple of hours.

HELP: Point in time restore

Pagus,
you options are:
(a) Restore full backup 1: 13:00
(b) Restore full backup 2: 17:00
(c) Restore full backup 2 and log 1 17:05
(d) Restore full backup 2 and do a point-in-time restore
of the log to any time between 17:00 and 17:05
To restore to 15:10, you'd need to have backed up a log
between the 2 database backups. Point-in-time is only
possible as part of the log restore, which has a concept
of transactions performed at specific times, while a
database backup only knows when the backup was created.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
Yup, full backup at 17:00 was mistake !
Many thanks Paul,
Pagus
On Wed, 20 Oct 2004 01:52:13 -0700, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:

>Pagus,
>you options are:
>(a) Restore full backup 1: 13:00
>(b) Restore full backup 2: 17:00
>(c) Restore full backup 2 and log 1 17:05
>(d) Restore full backup 2 and do a point-in-time restore
>of the log to any time between 17:00 and 17:05
>To restore to 15:10, you'd need to have backed up a log
>between the 2 database backups. Point-in-time is only
>possible as part of the log restore, which has a concept
>of transactions performed at specific times, while a
>database backup only knows when the backup was created.
>HTH,
>Paul Ibison (SQL Server MVP)
>
>

Help: Error Log

Dear all,
After I checked the SQL Server Log i found error like this:
1. Error: 17883, Severity: 1, State: 0
What does it mean?
2. Login failed for user 'sa'.
Almost every minute this error occured.
Does someone know what's happening to my SQL Server?
Thanks
Robert Lie
Robert,
1. This message means your scheduler appears to be hung. See the following
for more info:
New concurrency and scheduling diagnostics added to SQL Server
http://support.microsoft.com/kb/319892/
2. This means something is trying to login to SQL Server (and failing). Run
SQL Profiler to see what this is. Whatever it is, it shouldn't be using the
sa account.
Mark.
"Robert Lie" wrote:

> Dear all,
> After I checked the SQL Server Log i found error like this:
> 1. Error: 17883, Severity: 1, State: 0
> What does it mean?
> 2. Login failed for user 'sa'.
> Almost every minute this error occured.
> Does someone know what's happening to my SQL Server?
> Thanks
> Robert Lie
>

Help: Error Log

Dear all,
After I checked the SQL Server Log i found error like this:
1. Error: 17883, Severity: 1, State: 0
What does it mean?
2. Login failed for user 'sa'.
Almost every minute this error occured.
Does someone know what's happening to my SQL Server?
Thanks
Robert LieRobert,
1. This message means your scheduler appears to be hung. See the following
for more info:
New concurrency and scheduling diagnostics added to SQL Server
http://support.microsoft.com/kb/319892/
2. This means something is trying to login to SQL Server (and failing). Run
SQL Profiler to see what this is. Whatever it is, it shouldn't be using the
sa account.
Mark.
"Robert Lie" wrote:

> Dear all,
> After I checked the SQL Server Log i found error like this:
> 1. Error: 17883, Severity: 1, State: 0
> What does it mean?
> 2. Login failed for user 'sa'.
> Almost every minute this error occured.
> Does someone know what's happening to my SQL Server?
> Thanks
> Robert Lie
>

Help: Error Log

Dear all,
After I checked the SQL Server Log i found error like this:
1. Error: 17883, Severity: 1, State: 0
What does it mean?
2. Login failed for user 'sa'.
Almost every minute this error occured.
Does someone know what's happening to my SQL Server?
Thanks
Robert LieRobert,
1. This message means your scheduler appears to be hung. See the following
for more info:
New concurrency and scheduling diagnostics added to SQL Server
http://support.microsoft.com/kb/319892/
2. This means something is trying to login to SQL Server (and failing). Run
SQL Profiler to see what this is. Whatever it is, it shouldn't be using the
sa account.
Mark.
"Robert Lie" wrote:
> Dear all,
> After I checked the SQL Server Log i found error like this:
> 1. Error: 17883, Severity: 1, State: 0
> What does it mean?
> 2. Login failed for user 'sa'.
> Almost every minute this error occured.
> Does someone know what's happening to my SQL Server?
> Thanks
> Robert Lie
>